在處理大量數據插入時,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 中,我們可以利用異步編程、事務管理和批處理等特性來優化批量插入操作。通過選擇適合您特定需求的方法,您可以顯著提高數據插入的性能和可靠性。
該文章在 2024/11/26 12:06:19 編輯過
400 186 1886
