C# BULK INSERT导入大数据文件数据到SqlServer

BULK INSERT 的核心原理

BULK INSERT 是一种通过数据库原生接口高效批量导入数据的技术，其核心原理是绕过逐条插入的 SQL 解析和执行开销，直接将数据以二进制流或批量记录的形式传输到数据库。
在.NET中，主要通过 ​SqlBulkCopy 类​（针对 SQL Server）或 ORM 框架（如 SqlSugar、EF）封装的高效方法实现

NET 中调用 BULK INSERT 的常见方式

1. 直接执行 T-SQL 命令
   在 .NET 中可通过 SqlCommand 执行 BULK INSERT 语句，但需确保数据文件路径对 SQL Server 可见（如共享目录或服务器本地路径）：

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    string sql = @"BULK INSERT MyTable FROM 'C:\data.csv' 
                   with(FIRSTROW=2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', MAXERRORS=1000)";
    SqlCommand cmd = new SqlCommand(sql, conn);
    cmd.ExecuteNonQuery();
}

注意：需授予 SQL Server 服务账户对数据文件的读取权限。

2. 使用 SqlBulkCopy 类
   .NET 提供 SqlBulkCopy 类，直接从内存数据（如 DataTable 或 IDataReader）批量导入，无需依赖物理文件：

using (var connection = new SqlConnection(connectionString)) 
{
    connection.Open();
    using (var bulkCopy = new SqlBulkCopy(connection)) 
    {
        bulkCopy.DestinationTableName = "Customers";
        bulkCopy.BatchSize = 1000;  // 每批次插入量，优化内存与性能
        bulkCopy.BulkCopyTimeout = 600;  // 超时时间（秒）
        // 列映射（解决字段名不一致问题）
        bulkCopy.ColumnMappings.Add("Id", "CustomerId");
        bulkCopy.ColumnMappings.Add("Name", "CustomerName");
        // 数据转换为 DataTable 或直接读取 IDataReader
        var dataTable = ConvertToDataTable(customers);
        bulkCopy.WriteToServer(dataTable);
    }
}

优势：
客户端处理：无需将数据写入临时文件。
灵活映射：通过 ColumnMappings 处理列顺序不一致问题

3. ORM 框架的批量操作
   ​Entity Framework (EF)：
   使用 AddRange + SaveChanges 实现小批量插入，但超大数据量需依赖第三方库（如 EF Core 的 BulkExtensions）或切换至原生 SqlBulkCopy。
   ​SqlSugar：
   提供 Fastest().BulkCopy() 方法，底层封装数据库最佳批量 API（如 SQL Server 的 SqlBulkCopy），简化代码并支持分页处理

db.Fastest<Order>().PageSize(100000).BulkCopy(dataList);

关键参数与优化策略

1. 性能优化
   批处理大小（BATCHSIZE）
   设置合理的 BATCHSIZE（如 5000-10000 行）以平衡内存占用与事务提交频率6。
   表锁（TABLOCK）
   添加 WITH (TABLOCK) 减少锁竞争，提升并发导入效率。
   禁用约束与触发器
   默认情况下 BULK INSERT 忽略约束检查，可通过 CHECK_CONSTRAINTS 和 FIRE_TRIGGERS 按需启用。

2. 错误处理
   容错机制
   设置 MAXERRORS 参数允许部分数据失败（如 MAXERRORS = 100），避免整体操作中断。
   错误日志
   通过 ERRORFILE 指定错误文件路径，记录导入失败的行及原因

常见问题与解决方案

1. 权限不足
   确保 SQL Server 服务账户有文件读取权限。
   使用 SqlBulkCopy 避免文件路径依赖36。

2. 数据类型不匹配
   使用格式文件（FORMATFILE）显式定义列类型。
   在 SqlBulkCopy 中预处理数据确保与目标表结构一致。

3. 性能瓶颈
   分批次提交（BATCHSIZE）减少事务日志压力。
   禁用索引和触发器，导入完成后重建。

总结

在 .NET 中实现高效批量数据导入时：

1. 优先选择 SqlBulkCopy：适用于客户端数据源，无需处理文件权限。
2. BULK INSERT 补充场景：适合服务器端已有标准化数据文件的定期导入。
3. 优化核心参数：合理设置批处理大小、锁机制及错误容忍度，结合业务需求选择编码与格式控制策略