Use sqlbulkcopy to load data in batches (sqlclient only)

In the past, when accessing SQL Server 2000, if a large number of data records need to be added to the database, such as downloading a large number of data records from the host system, NCR teradata, Oracle, and other database systems, we want to add them quickly to SQL Server 2000, either by calling the BULK INSERT syntax for the T-SQL, by executing the select into syntax through the linked server, or by executing the bcp.exe tool, you can also use the bulk insert task of DTs or the quick loading setting of the transform data task.

 

However, to complete batch loading through a self-compiled program, you can only use C/C ++ to call the bulk API of oledb or ODBC and cannot execute it through objects such as ADO. Net or ADO.

 

ADO. NET 2.0 sqlclient provides a new class called sqlbulkcopy, which allows a large amount of data in the dataset or the datareader to directly read a large number of records through the stream, you can quickly add these records to the data table of the target database. However, we should note that it is not a bcp.exe tool that we generally use. It can read a large amount of data from a separator file and selectively combine it with a format file) load records to the database, or export the data in the database into a file. However, because dataset can integrate XML data, the sqlbulkcopy type can still be used to easily transfer XML file data to the database through dataset.

 

You can use the sqlbulkcopy class to quickly write large volumes of data. for SQL Server optimization, you can write datarow data, able, and datareader, and map different data column names.

Writetoserver (datatable) write data table

Writetoserver (datarow []) Batch write data rows

Writetoserver (datatable, datarowstate) writes data to the database table by row

Writetoserver (idatareader) writes a datareader object

 

The following is an example:

 

Using (sqlconnection sqlcon =

New sqlconnection ("Data Source = 192.168.80.242; user id = OA; Password = oapassword; initial catalog = test "))

{

Sqlcon. open ();

Using (sqlbulkcopy BCP = new sqlbulkcopy (sqlcon ))

{

BCP. bulkcopytimeout = 3000;

BCP. destinationtablename = "DBO. test01 ";

BCP. columnmappings. Add ("ID", "ID ");

BCP. columnmappings. Add ("name1", "name1 ");

BCP. columnmappings. Add ("name2", "name2 ");

BCP. columnmappings. Add ("name3", "name3 ");

// Map to different names

BCP. columnmappings. Add ("changedname4", "name4 ");

BCP. writetoserver (DT );

Sqlcon. Close ();

 

}

}

 

Note the following when using sqlbulkcopy:

1. confirm that a large-capacity update is required to perform this operation. (use other channels to store dozens of rows of data as much as possible ).

2.

3. Confirm data consistency and check mechanism to avoid primary key conflicts and data inconsistency.

4.

3. sqlbulkcopy may change the metadata of the target table (for example, when the constraint check is disabled ). In this case, concurrent Snapshot isolation transactions that access large-capacity inserted tables will fail.

 

4. sqlbulkcopy will update the lock for large capacity under the database. Pay attention to the concurrency to avoid timeout for other connections due to long wait. (For the 2005 lock compatibility list)