如何向存储过程传递List<T>参数实现批量插入SQL Server数据表

你当前使用的是单次插入单条数据的存储过程，有两种常用实现方案，可根据你的数据量选择：

方案1：遍历列表逐个调用存储过程 #

适合数据量较小的场景，无需修改现有存储过程和表结构，实现成本最低。
完整代码如下：

using Dapper;
using System.Data;
using System.Data.SqlClient;

// 建议用using块管理连接，自动释放资源无需手动关闭
using var conn = new SqlConnection(connStr);
conn.Open();

var procedure = "myspMyTest";                     

var list = new List<TEST2>();
list.Add(new TEST2 { A = "daskjf", B = 453, C = DateTime.Now });
list.Add(new TEST2 { A = "hjdgkfl", B = 456456, C = DateTime.Now });

// 遍历每个对象单次调用存储过程
foreach (var item in list)
{
    var parameters = new DynamicParameters();
    parameters.Add("@ColA", item.A);
    parameters.Add("@ColB", item.B);
    parameters.Add("@ColC", item.C);
    conn.Execute(procedure, parameters, commandType: CommandType.StoredProcedure);
}

方案2：表值参数批量插入 #

适合数据量较大的场景，仅需一次数据库交互，性能远高于逐次调用。
需要先修改SQL侧的定义：

1. 新增自定义表值类型

CREATE TYPE dbo.TEST2Type AS TABLE
(
    ColA varchar(10),
    ColB int,
    ColC datetime
)

2. 新增支持批量插入的存储过程

CREATE PROCEDURE dbo.myspMyTest_Batch
    @InsertData dbo.TEST2Type READONLY
AS
BEGIN
    INSERT INTO Test.dbo.MyTest (ColA, ColB, ColC)
    SELECT ColA, ColB, ColC FROM @InsertData
END

3. C#侧代码如下

using Dapper;
using System.Data;
using System.Data.SqlClient;

using var conn = new SqlConnection(connStr);
conn.Open();

var list = new List<TEST2>();
list.Add(new TEST2 { A = "daskjf", B = 453, C = DateTime.Now });
list.Add(new TEST2 { A = "hjdgkfl", B = 456456, C = DateTime.Now });

// 构造和表值类型匹配的DataTable
var dt = new DataTable();
dt.Columns.Add("ColA", typeof(string));
dt.Columns.Add("ColB", typeof(int));
dt.Columns.Add("ColC", typeof(DateTime));
foreach (var item in list)
{
    dt.Rows.Add(item.A, item.B, item.C);
}

var parameters = new DynamicParameters();
parameters.Add("@InsertData", dt.AsTableValuedParameter("dbo.TEST2Type"));

// 单次调用完成批量插入
conn.Execute("myspMyTest_Batch", parameters, commandType: CommandType.StoredProcedure);

注意事项 #

• 如果单次插入数据量小于100条，优先选方案1，开发成本更低
• 表值参数的字段顺序、类型要和自定义的TYPE完全匹配，否则会报参数错误
• 所有代码依赖Dapper库，需要提前通过Nuget安装

内容的提问来源于stack exchange，提问作者ChiaHsien Lin