C#使用SqlTransaction事務回滾與SqlBulkCopy批量插入數據

C#中批量處理數據，有時候因為一條記錄導致整個批量處理失敗。這時候肯能會導致數據不全等問題，這時候我們可以使用SqlTransaction來進行事務回滾，即是要麼全部成功要麼全部不成功。如下代碼

  //測試事務回滾
        public static string GetMsgBySJ()
        {
            var msg = "";
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = conn.CreateCommand();
            conn.Open();//打開之後開啟事務
            SqlTransaction tran = conn.BeginTransaction();//開啟事務
            cmd.Transaction = tran;//將事務應用於CMD
            try
            {
                cmd.CommandText = " INSERT into t_student VALUES ('huage1','11','男神') ";
                cmd.ExecuteNonQuery();
                cmd.CommandText = " INSERT into t_student VALUES ('huage','11','女神','') ";
                cmd.ExecuteNonQuery();
                tran.Commit();//提交事務（不提交不會回滾錯誤）
                msg = "插入成功";
            }
            catch (Exception ex)
            {
                tran.Rollback();
                msg = "插入失敗，事物回滾";
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                cmd.Dispose();
                tran.Dispose();
            }
            return msg;
        }

上面測試代碼，INSERT into t_student VALUES ('huage1','11','男神')這條記錄其實已經插入資料庫，但是因為下條語句操作失敗導致插入數據錯誤，這時候這個Rollback()函數會將資料庫表中的數據還原到操作表之前，

也就是說第一條執行成功的語句也會被刪掉（如果有自增Id的話，可以去資料庫中再插入數據查看Id是否不再連續）。

　　下麵在介紹一個SqlBulkCopy批量快速插入數據的方法，如果數據量大的話用迴圈語句進行數據的插入那肯定會使程式變的巨慢，這時候使用SqlBulkCopy來進行插入數據的話就會顯得很優越

下麵以插入10000行數據做測試。

 //批量將數據導入目的表
        public static void DtDrTable(DataTable dt, string tableName)
        {
            try
            {
                //這邊可以使用事務回滾機制
                SqlBulkCopy bcp = new SqlBulkCopy(connStr);
                //指定目標資料庫的表名
                bcp.DestinationTableName = tableName;
                //每一批次的行數
                bcp.BatchSize = 100 * 100;
                //建立數據源表欄位和目標表中的列之間的映射
                //----既然dt的列名需要與表明完全一致，直接迴圈dt的列即可----//
                foreach (DataColumn dc in dt.Columns)
                    bcp.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                //寫入資料庫表 dt 是數據源DataTable
                bcp.WriteToServer(dt);
                //關閉SqlBulkCopy實例
                bcp.Close();
            }
            catch (Exception ex)
            {                
                throw ex;
            }
        }

使用SqlBulkCopy進行數據插入的時候，要使得DataTable中的列名，類型與資料庫表要完全一致（除了自增Id），下麵是調用上面方法的例子

 Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();
            DataTable dtTemp = new DataTable();
            DataColumn[] columns = new DataColumn[]
            {
             new DataColumn("Name",typeof(string)),
             new DataColumn("Age",typeof(Int32)),
             new DataColumn("Sex",typeof(string)),
             new DataColumn("test",typeof(string)),
            };
            dtTemp.Columns.AddRange(columns);
            //構造一個10000行的DataTable
            for (var i = 0; i < 100 * 100; i++)
            {
                var n_row = dtTemp.NewRow();
                var tt = i + 1;
                n_row["Name"] = "Name" + tt;
                n_row["Age"] = tt;
                n_row["Sex"] = "Sex" + tt;
                n_row["test"] = "test" + tt;
                dtTemp.Rows.Add(n_row);
            }
            stopWatch.Stop();
            Console.WriteLine("構造一萬行的Datatable所需時間：" + stopWatch.Elapsed);

            Stopwatch stopwatcj = new Stopwatch();
            stopwatcj.Start();
            DtDrTable(dtTemp, "t_student");
            stopwatcj.Stop();
            Console.WriteLine("10000行數據批量插入表中所需時間：" + stopwatcj.Elapsed);

執行上述測試代碼的控制台程式，結果如下：

甚至一秒時間都沒到，在記憶體中構建記憶體表雖然更快，但是很費記憶體

　　下麵展示一個可回滾的批量插入（暫時未測試）

        //批量將數據導入目的表可回滾
        public static void TranBatchImportData(DataTable dt, string tableName)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn, SqlBulkCopyOptions.UseInternalTransaction, tran))
                {
                    sqlBC.BatchSize = 100 * 100;
                    //sqlBC.BulkCopyTimeout = 60;//超時之前操作完成所允許的秒數
                    sqlBC.DestinationTableName = tableName;
                    foreach (DataColumn dc in dt.Columns)
                        sqlBC.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);

                    sqlBC.WriteToServer(dt);
                    tran.Commit();
                }
            }
        }