SqlBulkCopy(大数据量拷贝)

///

/// 批量执行SqlBulkCopy数据迁移操作
///

/// 数据源
/// 执行前需要执行的脚本，如先清空表等，可为空
/// 目标数据库表名称
///
public static bool ExeSqlBulkCopy(DataTable dtblSource, string strPreSqlSentence,string strDestinationTablesName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
bool blReturnValue = false; connection.Open();

//请在插入数据的同时检查约束，如果发生错误调用 sqlbulkTransaction事务
SqlTransaction sqlbulkTransaction = connection.BeginTransaction();

if (strPreSqlSentence != "")
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = connection; sqlCmd.Transaction = sqlbulkTransaction;

try
{
sqlCmd.CommandText = strPreSqlSentence; sqlCmd.ExecuteNonQuery(); blReturnValue = true;
}
catch (Exception exc)
{
Log.WriteLog("批量插入前语句失败", exc); sqlbulkTransaction.Rollback(); connection.Close(); return false;
}
}

SqlBulkCopy BulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);

BulkCopy.DestinationTableName = strDestinationTablesName; BulkCopy.BulkCopyTimeout = 1200;

try
{
BulkCopy.WriteToServer(dtblSource); sqlbulkTransaction.Commit(); blReturnValue = true;
}
catch (Exception exp)
{
sqlbulkTransaction.Rollback(); Log.WriteLog("批量插入语句失败", exp); blReturnValue = false;
}
finally
{
BulkCopy.Close(); connection.Close();
}

return blReturnValue;
}
}