C# .NET — SQL Bulk Insert 批量写入-编程技术-CodeUp Hub

在处理大量数据插入时,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# .NET — SQL Bulk Insert 批量写入