在處理大量資料插入時,SQL Bulk Insert是一種高效的方法。本文將介紹如何在C# .NET中使用SQL Bulk Insert,並提供多個實用示例。
1. 基本的Bulk Insert操作
首先,讓我們看一個基本的Bulk Insert操作示例:
public class BulkInsertExample
{
public void PerformBulkInsert(List < Customer > customers, string connectionString)
{
try
{
using(var connection = new SqlConnection(connectionString))
{
connection.Open();
using(var bulkCopy = new SqlBulkCopy(connection))
{
// 設定目標表名
bulkCopy.DestinationTableName = "Customers";
// 設定批次插入的大小
bulkCopy.BatchSize = 1000;
// 對映列名
bulkCopy.ColumnMappings.Add("Id", "Id");
bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.ColumnMappings.Add("Email", "Email");
// 設定超時時間
bulkCopy.BulkCopyTimeout = 600; // 10分鐘
var dataTable = ConvertToDataTable(customers);
bulkCopy.WriteToServer(dataTable);
}
}
}
catch (Exception ex)
{
// 處理異常
Console.WriteLine($ "批次插入時發生錯誤: {ex.Message}");
throw;
}
}
private DataTable ConvertToDataTable(List < Customer > customers)
{
var dataTable = new DataTable();
// 新增列
dataTable.Columns.Add("Id", typeof(int));
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Email", typeof(string));
// 新增行
foreach(var customer in customers)
{
dataTable.Rows.Add(customer.Id, customer.Name, customer.Email);
}
return dataTable;
}
}
public class Customer
{
public int Id
{
get;
set;
}
public string Name
{
get;
set;
}
public string Email
{
get;
set;
}
}
// 使用示例
class Program
{
static void Main(string[] args)
{
// 連線字串
string connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"
// 建立測試資料
var customers = new List < Customer >
{
new Customer
{
Id = 1, Name = "John Doe", Email = "john@example.com"
},
new Customer
{
Id = 2, Name = "Jane Smith", Email = "jane@example.com"
},
new Customer
{
Id = 3, Name = "Bob Johnson", Email = "bob@example.com"
}
};
// 建立BulkInsertExample例項
var bulkInsert = new BulkInsertExample();
try
{
// 執行批次插入
bulkInsert.PerformBulkInsert(customers, connectionString);
Console.WriteLine("批次插入成功完成！");
}
catch (Exception ex)
{
Console.WriteLine($ "發生錯誤: {ex.Message}");
}
}
}
這個例子展示瞭如何將Customer物件列表批次插入到資料庫中。
2. 使用非同步方法
.NET 支援非同步操作,這對於大量資料插入特別有用:
public async Task PerformBulkInsertAsync(List < Customer > customers, string connectionString)
{
using(var connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
using(var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "Customers";
bulkCopy.BatchSize = 1000;
var dataTable = ConvertToDataTable(customers);
await bulkCopy.WriteToServerAsync(dataTable);
}
}
}
3. 對映列名
如果資料來源的列名與目標表不完全匹配,可以使用列對映:
public void PerformBulkInsertWithMapping(List < Customer > customers, string connectionString)
{
using(var connection = new SqlConnection(connectionString))
{
connection.Open();
using(var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "Customers";
bulkCopy.BatchSize = 1000;
bulkCopy.ColumnMappings.Add("Id", "CustomerId");
bulkCopy.ColumnMappings.Add("Name", "CustomerName");
bulkCopy.ColumnMappings.Add("Email", "CustomerEmail");
var dataTable = ConvertToDataTable(customers);
bulkCopy.WriteToServer(dataTable);
}
}
}
4. 使用事務
在批次插入過程中使用事務可以確保資料的一致性:
public void PerformBulkInsertWithTransaction(List < Customer > customers, string connectionString)
{
using(var connection = new SqlConnection(connectionString))
{
connection.Open();
using(var transaction = connection.BeginTransaction())
{
try
{
using(var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.DestinationTableName = "Customers";
bulkCopy.BatchSize = 1000;
var dataTable = ConvertToDataTable(customers);
bulkCopy.WriteToServer(dataTable);
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
}
5. 處理大量資料
對於非常大的資料集,可以考慮分批處理:
public async Task PerformLargeBulkInsertAsync(IEnumerable < Customer > customers, string connectionString, int batchSize = 10000)
{
using(var connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
using(var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "Customers";
bulkCopy.BatchSize = batchSize;
var dataTable = new DataTable();
dataTable.Columns.Add("Id", typeof(int));
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Email", typeof(string));
foreach(var batch in customers.Chunk(batchSize))
{
dataTable.Clear();
foreach(var customer in batch)
{
dataTable.Rows.Add(customer.Id, customer.Name, customer.Email);
}
await bulkCopy.WriteToServerAsync(dataTable);
}
}
}
}
這個方法使用了C# 引入的Chunk方法來分批處理大量資料。
結論
SQL Bulk Insert是處理大量資料插入的有效方法。在.NET 中,我們可以利用非同步程式設計、事務管理和批處理等特性來最佳化批次插入操作。透過選擇適合您特定需求的方法,您可以顯著提高資料插入的效能和可靠性。
