使用SqlBulkCopy进行大批量数据迁移

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) 

{ 
} 

finally 

{ 

SrcCon.Close(); 

DesCon.Close(); 

} 

} 



private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args) 

{ 

lblCounter.Text += args.RowsCopied.ToString() + " rows are copied

"; 

TimeSpan copyTime = DateTime.Now - startTime; 

lblCounter.Text += "Copy Time:" + copyTime.Seconds.ToString() + "." + 
copyTime.Milliseconds.ToString() + " seconds"; 

} 

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); 

} 

SqlTransaction Transaction; 

Transaction = 

SrcCom.Connection.BeginTransaction(); 

SqlBulkCopy DesBulkOp; 

DesBulkOp = new SqlBulkCopy(new SqlConnection(DesConString), 

SqlBulkCopyOptions.Default, 

Transaction); 



try 

{ 

//.. 

} 

catch{} 

finally 

{ 

Transaction.Commit(); 

} 

SqlBulkCopyColumnMapping ColMap = new SqlBulkCopyColumnMapping("SrcCol", "DesCol"); 

DesBulkOp.ColumnMappings.Add(ColMap); 

DesBulkOp.ColumnMappings.Add("SrcCol", "DesCol");