c#如何批量插入数据_c#批量插入数据完整教程与实战案例

C# 万级数据批量插入：SqlBulkCopy 实战精要

在C#中进行大规模数据插入，性能是首要考量。当数据量达到万级甚至更高时，常规的逐条插入方法会迅速成为性能瓶颈。那么，有没有一种既高效又稳定的解决方案呢？答案是肯定的。

用 SqlBulkCopy 实现高速批量插入

开门见山地说，在C#生态中，处理万级以上数据批量插入任务，SqlBulkCopy几乎是唯一正确的选择。它的工作原理是绕过了SQL语句的解析层，直接利用SQL Server的原生批量加载协议。这种“走后门”的方式，带来的性能提升是数量级的——通常能达到普通INSERT INTO ... VALUES循环操作的10到50倍。

一个常见的性能误区是试图用ExecuteNonQuery配合参数化INSERT循环来模拟批量操作。结果往往是，插入一万条数据可能需要耗时30秒以上。而同样的数据量，交给SqlBulkCopy处理，通常在300毫秒内就能完成。这个差距，足以决定一个功能的体验是“流畅”还是“卡顿”。

在实际使用时，有几个关键点需要把握：

• 适用范围：SqlBulkCopy是SQL Server（包括Azure SQL）的“亲儿子”，专为其优化。它不适用于MySQL、PostgreSQL等其他数据库。
• 前置条件：目标表必须已经存在。SqlBulkCopy只管插入，不会自动建表，也不会对字段映射做智能校验——映射错了，它要么静默跳过，要么直接报错。
• 数据源选择：推荐使用DataTable或IDataReader作为数据源。尽量避免直接传递IEnumerable，因为这会触发完整的枚举和反射过程，反而会拖累性能。
• 核心配置：BatchSize（批次大小，建议在1000到10000之间）、DestinationTableName（目标表名，务必使用全名如[dbo].[Orders]）、EnableStreaming = true（处理海量数据时启用，能有效减少内存占用）。

DataTable 构造时字段顺序与类型必须严格匹配目标表

这是新手最容易栽跟头的地方：代码运行不报错，但数据就是插不进去；或者数值被截断了，日期变成了1900-01-01。问题的根源，十有八九是DataTable的列顺序或数据类型与数据库表结构没有严格对齐。

举个例子，假设目标表是这样定义的：

CREATE TABLE Orders (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    OrderNo NVARCHAR(20) NOT NULL,
    Amount DECIMAL(18,2),
    CreatedAt DATETIME2
)

那么，对应的DataTable就必须严格按照这个顺序来添加列，并且数据类型要兼容：

var dt = new DataTable();
dt.Columns.Add("OrderNo", typeof(string));      // 注意：第一列不是“Id”
dt.Columns.Add("Amount", typeof(decimal));
dt.Columns.Add("CreatedAt", typeof(DateTime));  // 这里不能用 DateTime? 或 string

还有一个细节：如果数据库表中的某个字段允许为NULL，那么DataTable中对应列的AllowDBNull属性也必须设为true。否则，SqlBulkCopy会抛出InvalidOperationException异常。

处理主键冲突：用临时表 + MERGE 替代直接插入

SqlBulkCopy本身的设计哲学是“高速写入”，因此它不支持类似ON CONFLICT或IGNORE这样的冲突处理逻辑。一旦遇到主键或唯一键重复，它会直接报错并中断整个操作。但在真实的业务场景中，“存在则更新，不存在则插入”的需求非常普遍。

这时，一个经典的解决方案是分两步走：

• 第一步，暂存数据：先用SqlBulkCopy将数据高速导入一个结构相同的临时表（例如#staging_orders）。
• 第二步，合并数据：再执行一条T-SQL的MERGE语句，将临时表中的数据智能地合并到正式表中。

下面是一个典型的MERGE语句示例：

MERGE Orders AS tgt
USING #staging_orders AS src
ON tgt.OrderNo = src.OrderNo
WHEN MATCHED THEN
    UPDATE SET Amount = src.Amount, CreatedAt = src.CreatedAt
WHEN NOT MATCHED THEN
    INSERT (OrderNo, Amount, CreatedAt) VALUES (src.OrderNo, src.Amount, src.CreatedAt);

需要注意的是，以#为前缀的临时表是会话级别的，会话结束后会自动清理，无需手动删除。如果需要在多个数据库连接间共享临时数据，可以使用##前缀的全局临时表，但必须特别注意并发安全问题。

异步与事务控制：BulkCopy 本身不支持 async，但可包裹在 TransactionScope 中

SqlBulkCopy的WriteToServer方法是一个同步阻塞调用，即便在.NET 6及更高版本中，也没有提供原生的WriteToServerAsync方法。如果希望不阻塞主线程，通常的做法是用Task.Run将其包裹起来在后台线程执行，但要切记，数据库连接对象不能跨线程共享。

事务控制则是另一个更关键的话题。默认情况下，SqlBulkCopy的每个批次（Batch）都是一个独立的事务。这意味着如果中途失败，只有当前批次的数据会被回滚。如果需要保证整批操作的原子性（要么全部成功，要么全部回滚），就必须显式地传入一个已开启的SqlTransaction对象。

using var conn = new SqlConnection(connStr);
conn.Open();
using var tx = conn.BeginTransaction();
using var bulk = new SqlBulkCopy(conn) {
    DestinationTableName = "[dbo].[Orders]",
    SqlTransaction = tx  // ⚠️ 这是实现原子操作的关键
};
bulk.WriteToServer(dt);
tx.Commit(); // 全部成功后提交，否则调用 tx.Rollback()

还有一个容易被忽略的细节：默认情况下，SqlBulkCopy会禁用目标表上的触发器以追求极致速度。如果业务逻辑依赖这些触发器，需要显式设置FireTriggers = true，但这会带来明显的性能损耗。