在处理大量数据插入时,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
