Use sqlbulkcopy in. net2.0 for mass data migration

This article from: http://www.cnblogs.com/justicfu/archive/2006/06/23/433887.html

In. net1.1, it is not convenient to insert all data in the entire datatable into the database in batches or migrate data between different data sources. In. net2.0, several new classes are added to the sqlclient namespace to help us migrate data in batches through datatable or datareader. Data sources can come from relational databases, XML files, or even WebServices. One of the most important classes is the sqlbulkcopy class, which can help us easily migrate data from the data source to the target database.
The following is a simple example to illustrate the use of this class:

Datetime starttime;
Protected   Void Button#click ( Object Sender, eventargs E)
{
Starttime = Datetime. now;
String Srcconstring;
String Desconstring;
Sqlconnection srccon =   New Sqlconnection ();
Sqlconnection descon =   New Sqlconnection ();
Sqlcommand srccom =   New Sqlcommand ();
Sqldataadapter srcadapter =   New Sqldataadapter ();
Datatable dt =   New Datatable ();
Srcconstring =
Configurationmanager. connectionstrings [ " Srcdbconnectionstring " ]. Connectionstring;
Desconstring =
Configurationmanager. connectionstrings [ " Desdbconnectionstring " ]. Connectionstring;
Srccon. connectionstring = Srcconstring;
Srccom. Connection = Srccon;
Srccom. commandtext =   " Select * from [srctable] " ;
Srccom. commandtype = Commandtype. text;
Srccom. Connection. open ();
Srcadapter. selectcommand = Srccom;
Srcadapter. Fill (DT );
Sqlbulkcopy desbulkop;
Desbulkop =   New Sqlbulkcopy (desconstring,
Sqlbulkcopyoptions. useinternaltransaction );
Desbulkop. bulkcopytimeout =   500000000 ;
Desbulkop. sqlrowscopied + =
New Sqlrowscopiedeventhandler (onrowscopied );
Desbulkop. policyafter = DT. Rows. count;
Try
{
Desbulkop. destinationtablename= "Srctable";
Desbulkop. writetoserver (DT );
}
Catch (Exception ex)
{
Lblresult. Text=Ex. message;
}
Finally
{
Srccon. Close ();
Descon. Close ();
}
}

Private   Void Onrowscopied ( Object Sender, sqlrowscopiedeventargs ARGs)
{
Lblcounter. Text + = Args. rowscopied. tostring () +   " Rows are copied <br> " ;
Timespan copytime = Datetime. Now - Starttime;
Lblcounter. Text + =   " Copy time: "   + Copytime. Seconds. tostring () +   " . "   + Copytime. milliseconds. tostring () +   " Seconds " ;
}

Then we will analyze these rows.Code:Sqlbulkcopy desbulkop;
Desbulkop= NewSqlbulkcopy (desconstring, sqlbulkcopyoptions. useinternaltransaction );

For a sqlbulkcopy instance, the constructor specifies the target database. Using sqlbulkcopyoptions. useinternaltransaction means that the Migration action is specified in a transaction. If an error or exception occurs during data migration, rollback will occur. For other options, see msdn.

Desbulkop. bulkcopytimeout =   500000000 ;

Timeout time of operation completion

Desbulkop. sqlrowscopied + =   New Sqlrowscopiedeventhandler (onrowscopied );
Desbulkop. policyafter = DT. Rows. count;
Try
{
Desbulkop. destinationtablename= "Srctable";
Desbulkop. writetoserver (DT );
}

The yyafter attribute specifies the number of data rows before the notification event processing. Here, it specifies the number of rows in the table and adds the sqlrowscopied event to output the Time of the entire migration process. The writetoserver method copies the data source to the target database. Before using the writetoserver method, you must specify the destinationtablename attribute, that is, the table name of the target database,

We can also define a transaction, for example:

Sqltransaction transaction;
Transaction =
Srccom. Connection. begintransaction ();
Sqlbulkcopy desbulkop;
Desbulkop =   New Sqlbulkcopy ( New Sqlconnection (desconstring ),
Sqlbulkcopyoptions. Default,
Transaction );

Try
{
//..
}
Catch {}
Finally
{
Transaction. Commit ();
}

another sqlbulkcopycolumnmapping class allows data source fields to be mapped to different fields in the target data. That is to say, if the target data and source data column names are different, you can use this class for ing: sqlbulkcopycolumnmapping colmap = New sqlbulkcopycolumnmapping ( " srccol " , " descol " );
desbulkop. columnmappings. add (colmap);

Or you can directly add a ing:Desbulkop. columnmappings. Add ("Srccol","Descol");

Performance problems:
I used the above example to test that I migrated about 20 thousand records, and it took less than one second. I should say that the performance is still good. In addition, when you use SQL profile to monitor migration events, you can see that there are only a few request records. It is said that using sqlbulkcopy can greatly reduce the data migration time.