SqlBulkCopy(大数据量拷贝)

/// <summary>
/// 批量执行SqlBulkCopy数据迁移操作
/// </summary>
/// <param name="dtblSource">数据源</param>
/// <param name="strPreSqlSentence">执行前需要执行的脚本，如先清空表等，可为空</param>
/// <param name="strDestinationTablesName">目标数据库表名称</param>
/// <returns></returns>
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;
    }
}