新增多筆資料的好幫手 - SqlBulkCopy

前情提要:

因為專案要求須要開發一支程式，需要定期將A資料庫中的多個資料表更新到B資料庫，由於需要搬動多個資料表的大筆資料便找到了這個方法 SqlBulkCopy。

SqlBulkCopy介紹 : 

SqlBulkCopy 可讓您有效率地大量載入具有另一個來源的資料之 SQL Server 資料表。

此方法可以一次將大量的來源資料寫入MSSQL，來源可以是 DataRow[] 、DataTable、DataReader，本次的使用方式為取得A資料表資料後，將資料轉成要新增入資料庫的DataTable，再將DataTable使用WriteToServer方法將DataTable內的資料複製到SqlBulkCopy物件的目標資料表中。

SqlBulkCopyOptions : 

下方列出了SqlBulkCopyOptions的方法，通常比較會使用到的是

1. KeepIdentity：保留來源的PK值，否則會出現複製過去的資料產生標識列發現變化的情況！
2.  KeepNulls： 資料為空時不填入預設值。

//將取得資料轉成DataTable 型態
System.Data.DataTable dt = ToDataTableByList(model);
//設定連線字串、使用方式、SqlTransaction
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity, sqlTrans))
{
    //目的資料表名稱
    bulkCopy.DestinationTableName = "資料表名稱";
    //批次執行資料數量
    bulkCopy.BatchSize = 1000;
    //Timeout設定
    bulkCopy.BulkCopyTimeout = 600;

    try
    {
        //將資料複製到指定資料表中
        bulkCopy.WriteToServer(dt);
    }
    catch (Exception)
    {
        flag = false;
        //復原交易
        sqlTrans.Rollback();
    }
    finally
    {
        //認可交易
        sqlTrans.Commit();
        //執行其他功能
    }
}
                    
                    
                     

心得:

在這之前也有需要匯入大量資料的需求產生，可是當時不知道這個做法，所以只能組好資料後利用程式去執行Insert的SQL語法，這樣既費時又很難去進行除錯。

當使用這個方式後大大減少了執行的時間及效能，令人感覺發現了另外一片天，分享給大家希望有幫助到需要的人。

來源參考：

SqlBulkCopy

[.net mvc][SQL] 大量批次新增 Bulk Insert (SqlBulkCopy) - 屎弟夫