In .Net1.1, it is not very convenient to batch insert all the data in the entire DataTable into the database or to migrate between different data sources. In .Net2.0, several new classes have been added under the SQLClient namespace to help us migrate data in batches through DataTable or DataReader. The data source can come from a relational database or XML file, or even the results returned by WebService. One of the most important classes is the SqlBulkCopy class, which can easily help us migrate data from the data source to the target database.
Let's first illustrate the use of this class through a simple example:
DateTime startTime;
protected void Button1_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.NotifyAfter = 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 analyze these lines of code in detail:
SqlBulkCopy DesBulkOp;
DesBulkOp = new SqlBulkCopy(DesConString, SqlBulkCopyOptions.UseInternalTransaction); First generate 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, a rollback will occur. . Please refer to MSDN for other options.
DesBulkOp.BulkCopyTimeout = 500000000;
Specify the Timeout time for the completion of the operation
DesBulkOp.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowsCopied);
DesBulkOp.NotifyAfter = dt.Rows.Count;
try
{
DesBulkOp.DestinationTableName = "SrcTable";
DesBulkOp.WriteToServer(dt);
}
The NotifyAfter attribute specifies the number of data rows to be processed before the notification event. Here, it is specified as the number of rows in the table, and the SqlRowsCopied event is added 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 first specify the DestinationTableName attribute, which is the table name of the target database.
We can also define a Transaction ourselves, for example:
SqlTransaction Transaction;
Transaction =
SrcCom.Connection.BeginTransaction();
SqlBulkCopy DesBulkOp;
DesBulkOp = new SqlBulkCopy(new SqlConnection(DesConString),
SqlBulkCopyOptions.Default,
Transaction);
try
{
//..
}
catch{}
finally
{
Transaction.Commit();
}
There is also a SqlBulkCopyColumnMapping class that allows data source fields to be mapped to differently named fields in the target data. That is to say, if the column names of the target data and source data are different, you can use this class for mapping:
SqlBulkCopyColumnMapping ColMap = new SqlBulkCopyColumnMapping("SrcCol", "DesCol");
DesBulkOp.ColumnMappings.Add(ColMap);
Or you can add mapping directly:
DesBulkOp.ColumnMappings.Add("SrcCol", "DesCol");
Performance issues:
I used the above example to test and migrated about 20,000 records. It took less than one second. I should say that the performance is still good. In addition, using SQL Profile to monitor migration events, you can see that there are very few request records, only a few. It is said that using SqlBulkCopy can greatly reduce the time of data migration.