Go to SqlBulkCopy to copy data in batches

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. While
In. Net2.0, several new classes are added to the SQLClient namespace to help us migrate data in batches through DataTable or DataReader. The data source can be from
It is a database or XML file, or even WebService returns results. One of the most important classes is the SqlBulkCopy class, which can help us conveniently set the number of data sources
Migrate data to the target database.
The following is a simple example to illustrate the use of this class:

First: web. config

<ConnectionStrings>
<Add name = "srcDBConnection" connectionString = "server =.; database = pubs; uid = sa; pwd ="/>
<Add name = "desDBConnection" connectionString = "server =.; database = NorthWind; uid = sa; pwd ="/>
</ConnectionStrings>

C # file: If the front-end does not Copy the file, there will be a button, a Label

Using System;
Using System. Data;
Using System. Configuration;
Using System. Collections;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;
Using System. Data. SqlClient;

Public partial class ASP_NET: System. Web. UI. Page
...{
Private DateTime startTime;

Protected void button#click (object sender, EventArgs e)
...{
StartTime = DateTime. Now;
String srcConnString = "";
String desConnString = "";
SqlConnection srcConnection = new SqlConnection ();
SqlConnection desConnection = new SqlConnection ();
SqlCommand sqlcmd = new SqlCommand ();
SqlDataAdapter da = new SqlDataAdapter ();
DataTable dt = new DataTable ();
// SrcConnString = ConfigurationManager. ConnectionStrings ["srcDBConnection"]. ConnectionString;
DesConnString = ConfigurationManager. ConnectionStrings ["desDBConnection"]. ToString ();
// SrcConnection. ConnectionString = srcConnString;
SrcConnection. ConnectionString = desConnString;
Sqlcmd. Connection = srcConnection;
// Sqlcmd. commandtext = "select * from jobs ";
Sqlcmd. commandtext = "select * from ABC ";
Sqlcmd. commandtype = commandtype. text;
Sqlcmd. Connection. open ();
Da. selectcommand = sqlcmd;
Da. Fill (DT );

Sqlbulkcopy SBC = new sqlbulkcopy (desconnstring, sqlbulkcopyoptions. useinternaltransaction );
SBC. bulkcopytimeout = 5000;
Sbc. SqlRowsCopied + = new SqlRowsCopiedEventHandler (OnRowsCopied );
Sbc. policyafter = dt. Rows. Count;

Try
...{
// Sbc. DestinationTableName = "jobs ";
Sbc. DestinationTableName = "bcd ";
Sbc. WriteToServer (dt );
}
Catch (Exception ex)
...{
LblCounter. Text = ex. Message. ToString ();
}
Finally
...{
Sqlcmd. Clone ();
SrcConnection. Close ();
DesConnection. 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 ";
}
}

Code Analysis:

SqlBulkCopy sbc = new SqlBulkCopy (desConnString, 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.

Sbc. BulkCopyTimeout = 5000000; // specify the Timeout time after the operation is completed.

Sbc. SqlRowsCopied + = new SqlRowsCopiedEventHandler (OnRowsCopied );
Sbc. policyafter = dt. Rows. Count;

Try
...{
// Sbc. DestinationTableName = "jobs ";
Sbc. DestinationTableName = "bcd ";
Sbc. WriteToServer (dt );
}
Policyafter
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
DestinationTableName attribute, that is, the name of the target database table,

Performance: It took me nearly 8 minutes to insert 0.68 million pieces of data using proc in SQL, and 53.234 seconds to use SqlBulkCopy ~, The efficiency is seven times higher! However, we do not do the bottom layer of this aspect now. Haha, paste a test stored procedure you have written into it so that you can learn it easily.

Create table abc
(
Aid int identity (1, 1) primary key,
Adesc varchar (50) not null
)
Go

**********************/
Create proc addData
As
Declare @ I int
Set @ I = 1
While @ I <1000000
Begin
Insert into abc values ('testdescription ')
Set @ I = @ I + 1
End
Go

Select * into titles from pubs. dbo. titles where 1> 3. Copy the table structure across databases.

From http://blog.csdn.net/huaer1011/archive/2008/04/21/2312361.aspx