In a C # application, you use table-valued parameters to filter duplicates, bulk import data to the database, and return duplicate data

　　In many cases, the application needs to implement the Excel data import function, the data if only dozens of, or hundreds, even thousands, the speed is OK.

But not only that, if the customer provides you with the Excel itself has duplicate data, or some of the data in Excel already exists in the database, then, before inserting data into the database you have to weigh, if not exist before importing

Usually, we will upload the first step of the data in Excel read into memory, and then through the loop to draw a piece of data, and then for each data with a key field to go to the database to check the weight, if there is not to do things, if

Does not exist inserts a piece of data into the database. In this way, each of our data will be two times with the database, it is well known that every connection to the database that takes time, a lot of times a considerable impact on performance. If you have thousands of data,

If you want to know how slow this import process is, especially the Web application, it is likely that the program will be terminated after my request has not been executed.

Of course, there is a way, is to spell SQL, each loop a data, first of all, if not repeat, I write a SQL statement saved in a variable, until the loop to the last bar, you may spell a number of INSERT statements, and finally sent to the database once

Yes, but have you ever thought that once the amount of data is too large, tens of thousands of, or hundreds of thousands of pieces of data to spell the string can imagine how long, sent to the database will be truncated. Not to mention still can not escape every time to query duplicate plight.

In general, the closer the SQL statement from the database side, the more efficient the execution, it is possible, I have to insert the data collection one time to the database, so that the judgment is repeated, insert, return duplication of the work of the database to execute all? So I just

To have a contact with the database, wait for the database to give me the result is OK. So here's what I'm going to talk about. Use table-valued parameters to accomplish this function.

For the sake of telling, the tables in the example are relatively simple

1. First we create a table (the process of building the library here I do not explain, for everyone to see clearly, as far as possible to remove irrelevant script line )

1 CREATE TABLE [dbo].[bulktesttable](2     [Id] [int]  not NULL,3     [UserName] [nvarchar]( +)NULL,4     [PWD] [varchar]( -)NULL,5 PRIMARY KEY CLUSTERED 6 (7     [Id] ASC8) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY]9) on [PRIMARY]Ten  One GO

2. Create a table-valued type

1  Use [Bulktestdb]2 GO3 4 /** * * * object:userdefinedtabletype [dbo].    [Bulktesttabletype] Script date:07/08/2015 16:04:38 * * * * **/5 CREATETYPE[dbo].[Bulktesttabletype]  as TABLE(6     [Id] [int] NULL,7     [UserName] [nvarchar]( +)NULL,8     [PWD] [varchar]( -)NULL9 )Ten GO

3. Write BULK Insert stored procedures

1 CREATE procedure [dbo]. [usp_bulktesttable_import]
2 (@paratable--The bulktesttabletype here is the table type defined above, You don't actually have to manipulate it, just pass in a table to it in the program and read from it

3  as

4 Insert  intodbo. Bulktesttable (ID,USERNAME,PWD)
5 Select *  from @paratable A
6 WHERE  not EXISTS(Selectb.ID fromBulktesttable BWHEREb.ID=a.id)

7 SELECT * from @paratable A where EXISTS (SELECT b.id from bulktesttable B WHERE b.id=a.id) --Query for duplicate records
8 GO

Here, my id is not autogrow, just the primary key, so here is the ID to determine whether the record is unique or duplicate

Next, write an import method in C # code that calls this stored procedure.

The parameter dt Here is the DataTable we generated when we imported Excel, the table structure of this DataTable is the same as our defined table value "bulktesttabletype" structure, the return value is the duplicate record we want 

1  public  static   DataSet Batchinsert (DataTable DT)  2   { 3  SqlParameter parameter =  "  @paratable  "  ,DT);  4  parameter. SqlDbType = sqldbtype.structured;  5  parameter. TypeName =  bulktesttabletype"  ; //The type name here should be the same as the table value name we defined

6 sqlparameter[] SqlParameters = New sqlparameter[] {parameter};

 7return  sqlhelper.runprocedure (commandtype.storedprocedure,  " Usp_bulktesttable_import ", sqlparameters); 

8  }

The above is the complete code, of course, how to create a table in memory, how to write the database access method I do not explain here, we all know. Small white write articles, we communicate together, do not spray

In a C # application, you use table-valued parameters to filter duplicates, bulk import data to the database, and return duplicate data