在处理大量数据插入时,SQL Bulk Insert是一种高效的方法。本文将介绍如何在C# .NET中使用SQL Bulk Insert,并提供多个实用示例。

1. 基本的Bulk Insert操作

首先,让我们看一个基本的Bulk Insert操作示例:

C#
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 支持异步操作,这对于大量数据插入特别有用:

C#
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. 映射列名

如果数据源的列名与目标表不完全匹配,可以使用列映射:

C#
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. 使用事务

在批量插入过程中使用事务可以确保数据的一致性:

C#
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. 处理大量数据

对于非常大的数据集,可以考虑分批处理:

C#
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 中,我们可以利用异步编程、事务管理和批处理等特性来优化批量插入操作。通过选择适合您特定需求的方法,您可以显著提高数据插入的性能和可靠性。