使用「SqlBulkCopy」進行大量資料寫入

一、前言

使用 Entity Framework 產生的資料庫模型進行大量資料的寫入是相當緩慢的，所幸認識到 SqlBulkCopy 類別，它可以有效率地進行大量資料的寫入，筆者因為它減少很多等待的時間。

二、開發環境

• 整合開發環境：Visual Studio 2019
• 語言：C#
• .net framework：4.6.2

三、範例資料庫

圖、範例資料庫

四、範例 專案結構 和 CSV結構

圖、範例 專案結構 和 CSV結構

五、範例 ViewModel

圖、範例 ViewModel

• 原始碼(C#)

public class TestVm
{
    public string a1 { get; set; }
    public string a2 { get; set; }
}

六、SqlBulkCopy 程式碼

圖、SqlBulkCopy 程式碼

• 原始碼(C#)

//取得資料庫連線字串
var conn = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

using (var sb = new SqlBulkCopy(conn))
{
    sb.DestinationTableName = "Test";
    sb.WriteToServer(dt, DataRowState.Added);
}

圖、執行結果

七、完整程式碼

public class BulkController : ApiController
{
    [HttpGet]
    public string Add() 
    {
        try
        {
            //csv 轉 ViewModel
            var data = GetCsvData();
            //ViewModel 轉 DateTable
            var dt = GetDataTable(data);

            //取得資料庫連線字串
            var conn = WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            using (var sb = new SqlBulkCopy(conn))
            {
                sb.DestinationTableName = "Test";
                sb.WriteToServer(dt, DataRowState.Added);
            }

            return "success";
        }
        catch (Exception e) 
        {
            return "fail";
        }
    }

    private DataTable GetDataTable(List<TestVm> data)
    {
        var dt = new DataTable();
        dt.Columns.Add("Seq", typeof(int));
        dt.Columns.Add("a1", typeof(string));
        dt.Columns.Add("a2", typeof(string));
        data.ForEach((x) =>
        {
            var row = dt.NewRow();
            //Seq 為 IDENTITY 的欄位，因此寫入資料表，會自動加 1
            row["Seq"] = 0;
            row["a1"] = x.a1;
            row["a2"] = x.a2;
            dt.Rows.Add(row);
        });
        return dt;
    }

    private List<TestVm> GetCsvData()
    {
        //參考  '使用「CsvHelper」讀取、寫入 CSV 檔案' 文章 相關連結有
        var path = HostingEnvironment.MapPath("~/Template/test.csv");
        using (var reader = new StreamReader(path))
        using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
        {
            return csv.GetRecords<TestVm>().ToList();
        }
    }
}

八、相關連結

1. 使用「Visual Studio 2019」建置一個有 Mvc 和 Web Api 架構的網站
2. 使用「CsvHelper」讀取、寫入 CSV 檔案
3. 好用的資料庫管理工具「SQL Server 資料庫專案」
4. 「NLog」與「Sql Server」資料庫的完美搭配
5. 使用「ADO.NET」 進行資料庫的讀取、新增、修改、刪除的操作
6. 使用「Entity Framework Tools」建立資料庫關聯模型
7. https://weitechshare.blogspot.com/2020/12/code-first.html
8. 使用「SqlBulkCopy」進行大量資料寫入
9. 「linqkit」動態組裝 LINQ 條件的好用套件
10. 「Dapper」一個高效率輕量型 ORM 套件

九、參考

1. SqlBulkCopy 類別