SqlBulkCopy 使用指南

一、SqlBulkCopy(批量複製)使用方法

SqlBulkCopy 是一種高效的數據批量導入工具，可以通過使用 SqlBulkCopy，將大量數據快速地從一個數據源複製到另一個數據源。這裡介紹一下如何使用 SqlBulkCopy:

// 創建連接 Opening the Connection  
using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

// 創建 SqlBulkCopy 對象 Creating a SqlBulkCopy object  
using SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
bulkCopy.DestinationTableName = "dbo.DestinationTable";

try
{
    // 設置列映射 Mapping Columns  
    bulkCopy.ColumnMappings.Add("SourceColumn1", "DestinationColumn1");
    bulkCopy.ColumnMappings.Add("SourceColumn2", "DestinationColumn2");
    bulkCopy.ColumnMappings.Add("SourceColumn3", "DestinationColumn3");

    // 將數據寫入目標表 WriteToServer  
    bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

二、SqlBulkCopy 教程

SqlBulkCopy 使用大致流程： 創建連接 -> 創建 SqlBulkCopy 對象並設置 DestinationTableName -> 設置 ColumnMappings（可選）-> 將數據寫入目標表 WriteToServer。

其中，ColumnMappings 屬性提供一種方法，使我們可以將源表列映射到目標表列。源表列的名稱必須與目標表列的名稱相同。

ColumnMappings 的正確設置可以確保數據正確地轉移，如果源表列的數量與目標表列的數量不同，則會引發異常。

三、SqlBulkCopy mysql

SqlBulkCopy 主要用於 SQL Server，但是也可以用於 MySql 資料庫。

使用 MySql 的 SqlBulkCopy，需要通過 NuGet 安裝 MySql.Data.DataMySqlclient，並且使用 MySqlBulkCopy 對象來代替 SqlBulkCopy 對象。

// 創建連接 Opening the Connection  
using MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();

// 創建 MySqlBulkCopy 對象 Creating a MySqlBulkCopy object  
using MySqlBulkCopy bulkCopy = new MySqlBulkCopy(connection);
bulkCopy.DestinationTableName = "dbo.DestinationTable";

try
{
    // 設置列映射 Mapping Columns  
    bulkCopy.ColumnMappings.Add("SourceColumn1", "DestinationColumn1");
    bulkCopy.ColumnMappings.Add("SourceColumn2", "DestinationColumn2");
    bulkCopy.ColumnMappings.Add("SourceColumn3", "DestinationColumn3");

    // 將數據寫入目標表 WriteToServer  
    bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

四、SqlBulkCopy 報錯

在使用 SqlBulkCopy 進行數據傳輸時，需要注意以下幾個常見報錯：

（1）The given ColumnMapping does not match up with any column in the source or destination

這個報錯意味著 ColumnMappings 屬性設置有誤，即源表與目標表的列名對不上。

（2）Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

這個報錯意味著在執行 BulkCopy 操作時，超時時間過短，需要增加超時時間。

（3）There are not enough columns in this [source] table to match the number of columns in the [destination] table.

這個報錯意味著源表與目標表的列數不一致，需要檢查是否設置了正確的 ColumnMappings。

若出現其他報錯，可以參考官方文檔或者 Stack Overflow。

五、SqlBulkCopy 替換重複行

在進行大批量數據複製時，有時候會遇到目標表中已經有部分相同的數據，這時候就需要解決重複數據的問題。SqlBulkCopy 里有一個重要的屬性叫做 WriteToServerOptions，它有一個枚舉值叫做 ReuseRows。在 ReuseRows 被設置為 True 時，SqlBulkCopy 會為相同的行分配相同的內存，以便於使用資源。

SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls);
bulkCopy.DestinationTableName = "dbo.DestinationTable";
bulkCopy.WriteToServerOptions = WriteToServerOptions.ReuseRows;

六、SqlBulkCopy 多線程

使用 SqlBulkCopy 進行大批量數據複製時，為了提高效率，可以將數據分成多個塊，再使用多線程同時進行數據傳輸。

for (int i = 0; i < numOfTasks; i++)
{
    int start = i * batchSize;
    int end = i == numOfTasks - 1 ? dt.Rows.Count - 1 : start + batchSize - 1;
    List<DataRow> chunk = dt.AsEnumerable().Skip(start).Take(end - start + 1).ToList();

    BulkCopyTask bulkCopyTask = new BulkCopyTask(connectionString, "dbo.DestinationTable", chunk);
    Task task = new Task(bulkCopyTask.Execute);
    tasks.Add(task);
    task.Start();
}
Task.WaitAll(tasks.ToArray());

七、SqlBulkCopy 計算列

SqlBulkCopy 還可以用來計算列。

首先，定義一個計算列：

DataColumn totalColumn = new DataColumn("Total", typeof(decimal));
totalColumn.Expression = "UnitPrice * Quantity";
dt.Columns.Add(totalColumn);

代碼中，Total 列的值等於 UnitPrice 列與 Quantity 列的乘積。

然後，在進行 BulkCopy 時，將使用 BulkCopyColumnMapping 來匹配列：

foreach (DataColumn column in dt.Columns)
{
    if (column.Expression != "")
        bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
    else
        bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}

八、SqlBulkCopy為什麼快

SqlBulkCopy 的快速主要利用了批量傳輸和使用 BCP 格式的優勢：

（1）批量傳輸：當遇到大批量數據傳輸時，使用 SqlBulkCopy 可以取代 ADO.NET 的數據表格填充方法，它比普通的數據插入操作快得多，因為它直接傳輸數據到資料庫而不是一行一行條目的複製。

（2）使用BCP格式：對於大批量的數據處理，SqlBulkCopy 使用 BCP 格式，這是一種高速數據傳輸的格式，可以將數據快速地進行傳輸。BCP 格式實質上是將數據存儲為二進位格式，再進行快速傳輸與重構，因此速度非常快。

九、SqlBulkCopy使用場景

SqlBulkCopy 適用於以下場景：

（1）大批量數據複製：SqlBulkCopy 使用高效的 BCP 格式進行數據複製，所以非常適合進行大批量數據複製。

（2）數據倉庫數據裝載：SqlBulkCopy 可以將大量數據快速地裝載到數據倉庫中。

（3）數據遷移：使用 SqlBulkCopy，可以將數據從一個數據源快速地遷移到另一個數據源中。

總結

本文介紹了 SqlBulkCopy 的使用方法，並舉例說明了不同情況下的使用方式，包括數據映射、數據轉移、多線程操作、計算列和優勢等。通過了解 SqlBulkCopy 的使用方法和使用場景，我們可以更好地利用這個工具來進行大批量數據複製和數據遷移，提高數據處理效率。