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

下面我们先通过一个简单的例子说明这个类的使用

DateTime startTime;

protected void Button_Click(object sender EventArgs e)

{

startTime = DateTimeNow;

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 =

ConfigurationManagerConnectionStrings[SrcDBConnectionString]ConnectionString;

DesConString =

ConfigurationManagerConnectionStrings[DesDBConnectionString]ConnectionString;

SrcConConnectionString = SrcConString;

SrcComConnection = SrcCon;

SrcComCommandText = SELECT * From [SrcTable];

SrcComCommandType = CommandTypeText;

SrcComConnectionOpen();

SrcAdapterSelectCommand = SrcCom;

SrcAdapterFill(dt);

SqlBulkCopy DesBulkOp;

DesBulkOp = new SqlBulkCopy(DesConString

SqlBulkCopyOptionsUseInternalTransaction);

DesBulkOpBulkCopyTimeout = ;

DesBulkOpSqlRowsCopied +=

new SqlRowsCopiedEventHandler(OnRowsCopied);

DesBulkOpNotifyAfter = dtRowsCount;

try

{

DesBulkOpDestinationTableName = SrcTable;

DesBulkOpWriteToServer(dt);

}

catch (Exception ex)

{

}

finally

{

SrcConClose();

DesConClose();

}

}

private void OnRowsCopied(object sender SqlRowsCopiedEventArgs args)

{

lblCounterText += argsRowsCopiedToString() + rows are copied

;

TimeSpan copyTime = DateTimeNow startTime;

lblCounterText += Copy Time: + copyTimeSecondsToString() + + copyTimeMillisecondsToString() + seconds;

}

接着具体分析这几行代码

SqlBulkCopy DesBulkOp;

DesBulkOp = new SqlBulkCopy(DesConString SqlBulkCopyOptionsUseInternalTransaction);先生成SqlBulkCopy 实例构造函数指定了目标数据库使用SqlBulkCopyOptionsUseInternalTransaction是指迁移动作指定在一个Transaction当中如果数据迁移中产生错误或异常将发生回滚其他选项请参考MSDN

DesBulkOpBulkCopyTimeout = ;

指定操作完成的Timeout时间

DesBulkOpSqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowsCopied);

DesBulkOpNotifyAfter = dtRowsCount;

try

{

DesBulkOpDestinationTableName = SrcTable;

DesBulkOpWriteToServer(dt);

}

NotifyAfter属性指定通知通知事件前处理的数据行数在这里指定为表的行数并添加SqlRowsCopied事件输出整个迁移过程的时间WriteToServer方法就是将数据源拷备到目标数据库在使用WriteToServer方法之前必须先指定DestinationTableName属性也就是目标数据库的表名

我们还可以自己定义一个Transaction例如

SqlTransaction Transaction;

Transaction =

SrcComConnectionBeginTransaction();

SqlBulkCopy DesBulkOp;

DesBulkOp = new SqlBulkCopy(new SqlConnection(DesConString)

SqlBulkCopyOptionsDefault

Transaction);

try

{

//

}

catch{}

finally

{

TransactionCommit();

}

另外还有一个SqlBulkCopyColumnMapping类可以让数据源字段映射到目标数据中命名不同的字段上也就是说如果目标数据和源数据的列名不同时可以用这个类进行映射

SqlBulkCopyColumnMapping ColMap = new SqlBulkCopyColumnMapping(SrcCol DesCol);

DesBulkOpColumnMappingsAdd(ColMap);

或者可以直接添加映射

DesBulkOpColumnMappingsAdd(SrcCol DesCol);

性能问题

我使用上面的例子测试迁移了万条左右的记录花的时间不到一秒应改说性能还是不错的另外使用SQL Profile监视迁移事件可以看见请求记录非常少只有几条而已据说使用SqlBulkCopy可以大大减少数据迁移的时间