Tutorials to .com

Tutorials to .com » Dotnet » Foundation » . net2.0 for the use of large quantities SqlBulkCopy data migration

. net2.0 for the use of large quantities SqlBulkCopy data migration

Print View , by: iSee ,Total views: 20 ,Word Count: 756 ,Date: Fri, 21 Aug 2009 Time: 3:59 PM

In. Net1.1 of whether it is for the bulk of the DataTable to insert all the data to the database in, or between different data sources of migration, are not very convenient. In. Net2.0 in, SQLClient namespace increase of several new categories to help us through the DataTable or DataReader bulk data migration. Data sources can come from relational databases or xml documents and even to return to the results of WebService. One of the most important category is a category SqlBulkCopy, can easily use it to help us the data of the data migration source to the target database.
We first adopted the following example illustrates a simple use of this category:

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";
)
Followed by a detailed analysis of these lines of code:
SqlBulkCopy DesBulkOp;
DesBulkOp = new SqlBulkCopy (DesConString, SqlBulkCopyOptions.UseInternalTransaction); Mr. SqlBulkCopy as an example, the constructor of the specified target database, migration refers to the use of SqlBulkCopyOptions.UseInternalTransaction action specified in a Transaction which, if the data migration in a wrong or abnormal will occur rollback . Other options please refer to MSDN.

DesBulkOp.BulkCopyTimeout = 500000000;
Timeout specified time of the operation is completed

DesBulkOp.SqlRowsCopied + = new SqlRowsCopiedEventHandler (OnRowsCopied);
DesBulkOp.NotifyAfter = dt.Rows.Count;
try
(
DesBulkOp.DestinationTableName = "SrcTable";
DesBulkOp.WriteToServer (dt);
)
NotifyAfter attributes specified notification event to inform the data pre-processing lines, where the specified number of rows for the table, and add events SqlRowsCopied output time of the migration process. WriteToServer is prepared to copy the data source to the target database. WriteToServer in the use of methods must be specified before DestinationTableName attributes, that is, the target database table name,

We also can define a Transaction, such as:

SqlTransaction Transaction;
Transaction =
SrcCom.Connection.BeginTransaction ();
SqlBulkCopy DesBulkOp;
DesBulkOp = new SqlBulkCopy (new SqlConnection (DesConString),
SqlBulkCopyOptions.Default,
Transaction);

try
(
/ / ..
)
catch ()
finally
(
Transaction.Commit ();
)
SqlBulkCopyColumnMapping there is another type, allowing the data source is mapped to the target data field names of the different fields. That is to say if the target data and source data is not at the same time of listing can be used to carry out this type of mapping:
SqlBulkCopyColumnMapping ColMap = new SqlBulkCopyColumnMapping ( "SrcCol", "DesCol");
DesBulkOp.ColumnMappings.Add (ColMap);
Or you can directly add mapping:
DesBulkOp.ColumnMappings.Add ( "SrcCol", "DesCol");
Performance issues:
I use to test the above example, the relocation of about 20,000 records, the time spent less than one second, it should be said that a good performance. In addition, the use of SQL Profile to monitor migration of events, you can see very few requests for records, only a few only. Reportedly could greatly reduce the use of SqlBulkCopy data migration time.

http://justicfu.cnblogs.com/archive/2006/06/23/433887.html


.NET foundation Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.