C# 資料庫大量新增資料的魔法｜深入解析 SqlBulkCopy
最近在工作碰到需要將大量的五六百萬筆的資料insert到測試環境資料庫來Tuning SQL語法，這時候如果還是用那種「一筆一筆慢慢insert」的做法，效率差得驚人。這時 C# 就提供 SqlBulkCopy 提供了簡單且高效率的工具，能快速將大批量數據匯入 SQL Server。
在這篇文章中，我們來了解 SqlBulkCopy 的基本概念、使用方法，接下來，我就用輕鬆小白的方式和你介紹怎麼上手，用最短時間讓你搞懂怎麼應用它，還會附一點我自己實戰的小撇步！
一、什麼是 SqlBulkCopy？
SqlBulkCopy 是 ADO.NET 中的一個類別，它能夠極速地將大量資料從一個來源（例如 DataTable、DataReader 或是物件集合）寫入到 SQL Server 資料庫的目標資料表中。它的底層原理類似於 SQL Server 的 BULK INSERT T-SQL 命令或 bcp.exe 工具，繞過了逐筆新增資料的紀錄和檢查，從而實現了驚人的寫入效能。
二、如何使用 SqlBulkCopy？一步步教學
步驟 1：準備目標資料表
首先，我們在 SQL Server 中建立一個用來接收資料的資料表。
SQL
CREATE TABLE [dbo].[Products](
[ProductID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[StandardCost] [money] NOT NULL,
[ListPrice] [money] NOT NULL,
[EntryDate] [datetime] NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([ProductID] ASC)
)步驟 2：準備來源資料
在 C# 程式中，我們通常會從檔案、API 或其他資料庫中取得資料。為了簡化範例，我們直接在記憶體中建立一個 DataTable 來模擬我們的資料來源。
C#
// 建立一個 DataTable 並定義其結構
var productsTable = new DataTable("Products");
productsTable.Columns.Add("ProductID", typeof(int));
productsTable.Columns.Add("Name", typeof(string));
productsTable.Columns.Add("ProductNumber", typeof(string));
productsTable.Columns.Add("StandardCost", typeof(decimal));
productsTable.Columns.Add("ListPrice", typeof(decimal));
productsTable.Columns.Add("EntryDate", typeof(DateTime));
// 產生 50,000 筆測試資料
for (int i = 1; i <= 50000; i++)
{
productsTable.Rows.Add(
i,
$"Product Name {i}",
$"PN-{i:D8}",
10.5m * i,
15.5m * i,
DateTime.Now
);
}步驟 3：執行 Bulk Copy
這是最關鍵的一步。我們需要建立資料庫連線，並實例化 SqlBulkCopy 來執行操作。
C#
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
// 建立 SqlBulkCopy 物件
using (var bulkCopy = new SqlBulkCopy(connection))
{
//設定目標資料表名稱
bulkCopy.DestinationTableName = "dbo.Products";
try
{
// 執行寫入！
Console.WriteLine("開始大量寫入資料...");
await bulkCopy.WriteToServerAsync(productsTable);
Console.WriteLine("資料寫入完成！");
}
catch (Exception ex)
{
Console.WriteLine($"發生錯誤：{ex.Message}");
}
}
}就這樣！執行這段程式碼，您會發現 50,000 筆資料幾乎在瞬間就寫入了資料庫。
三、設定BatchSize跟Timeout
假如你真的一次超大量的資料，例如10萬筆、100萬筆，有時候資料庫更有效率，還可以調整這幾個參數：
- BatchSize：一次傳幾筆。預設是所有資料一次傳，要是中間失敗就全掛。你可以設5000、10000，每次送5K筆，傳太多容易塞爆，傳太少沒效率，選個適合你的數字。
C#
bulkCopy.BatchSize = 5000; - BulkCopyTimeout：逾時時間，超過的話就中斷。預設30秒，可依照需求調整。
C#
bulkCopy.BulkCopyTimeout = 180; //三分鐘 四、效能大對決：For 迴圈 vs. SqlBulkCopy
方法 1：傳統 For 迴圈
C#
using System.Diagnostics;
var stopwatch = new Stopwatch();
stopwatch.Start();
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = @"INSERT INTO [dbo].[Products]
([ProductID], [Name], [ProductNumber], [StandardCost], [ListPrice], [EntryDate])
VALUES
(@ProductID, @Name, @ProductNumber, @StandardCost, @ListPrice, @EntryDate)";
command.Parameters.Add("@ProductID", SqlDbType.Int);
command.Parameters.Add("@Name", SqlDbType.NVarChar, 50);
command.Parameters.Add("@ProductNumber", SqlDbType.NVarChar, 25);
command.Parameters.Add("@StandardCost", SqlDbType.Money);
command.Parameters.Add("@ListPrice", SqlDbType.Money);
command.Parameters.Add("@EntryDate", SqlDbType.DateTime);
foreach (DataRow row in productsTable.Rows)
{
command.Parameters["@ProductID"].Value = row["ProductID"];
command.Parameters["@Name"].Value = row["Name"];
command.Parameters["@ProductNumber"].Value = row["ProductNumber"];
command.Parameters["@StandardCost"].Value = row["StandardCost"];
command.Parameters["@ListPrice"].Value = row["ListPrice"];
command.Parameters["@EntryDate"].Value = row["EntryDate"];
command.ExecuteNonQuery();
}
}
}
stopwatch.Stop();
Console.WriteLine($"For 迴圈逐筆 INSERT 花費時間: {stopwatch.ElapsedMilliseconds} ms");方法 2：使用SqlBulkCopy
C#
stopwatch.Restart(); // 重置計時器
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "dbo.Products";
await bulkCopy.WriteToServerAsync(productsTable);
}
}
stopwatch.Stop();
Console.WriteLine($"SqlBulkCopy 花費時間: {stopwatch.ElapsedMilliseconds} ms");在我的電腦上執行後，結果非常驚人：
For 迴圈逐筆 INSERT 花費時間: 7064 ms
SqlBulkCopy 花費時間: 243 ms
數字會因硬體和網路環境而異，但效能差距達到數十倍甚至更高是很正常的！這就是為什麼在處理大量資料時，SqlBulkCopy 絕對是我們的首選。
五、效能調校與進階技巧
BatchSize
這個屬性用來設定每一批次要傳送多少筆資料到伺服器。預設值為 0，表示所有資料會被當作一個批次處理。
- 優點：設定適當的
BatchSize(例如 5000 或 10000) 可以減少記憶體壓力，因為不需要一次將所有資料載入記憶體。
C#
bulkCopy.BatchSize = 10000;BulkCopyTimeout
設定操作的逾時時間（秒）。如果您的資料量極大，或是網路環境不穩定，可以將這個值設高一點，避免操作因逾時而失敗。預設為 30 秒。
C#
bulkCopy.BulkCopyTimeout = 120; // 逾時時間設為 120 秒SqlBulkCopyOptions
SqlBulkCopyOptions.TableLock：在操作期間鎖定整個目標資料表。這可以大幅提升效能，因為減少了逐筆資料列的鎖定成本，但會暫時阻止其他使用者讀取或寫入該資料表。
SqlBulkCopyOptions.UseInternalTransaction：只要中途有任何一筆資料出錯，它就會自動取消整個任務，不會留下成功一半、失敗一半的爛攤子，確保資料庫的資料是乾淨完整的。
C#
var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null);結語
SqlBulkCopy 是 C# 開發者在處理大量資料新增時不可或缺的利器。它不僅效能卓越，而且使用上非常靈活。下次當您面臨類似的資料處理需求時，不妨拋棄傳統的迴圈 INSERT 模式，試試 SqlBulkCopy 帶來的極速快感吧！
