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