C# 中如何构建动态参数化 SQL 查询字符串实现批量数据写入 SQL Server

认知确认 #

你的认知没有偏差，循环单条插入的效率确实非常低，核心瓶颈在于每次插入都要产生独立的网络往返开销、SQL语句编译开销，当插入记录数超过10条时，批量插入的性能优势就会非常明显。

加密列的存在不会影响批量插入的实现，只要你使用参数化赋值、参数类型和加密列的定义完全匹配，无论是Always Encrypted还是服务端透明加密，都可以和单条插入一样正常工作。

推荐实现方案 #

方案1：表值参数（TVP）批量插入（优先推荐） #

这是SQL Server原生支持的标准批量插入方案，无参数数量限制、性能最优、注入风险为0。

实现步骤

1. 首先在SQL Server中创建和目标表结构匹配的自定义表类型：

CREATE TYPE dbo.YourInsertRecordType AS TABLE (
    Id INT,
    BusinessField NVARCHAR(100),
    EncryptedColumn VARBINARY(256) -- 类型和实际加密列保持完全一致
)

2. 在Function App代码中构造对应结构的参数，执行批量插入（以C#为例）：

// 构造和自定义表类型结构一致的DataTable
var insertTable = new DataTable();
insertTable.Columns.Add("Id", typeof(int));
insertTable.Columns.Add("BusinessField", typeof(string));
insertTable.Columns.Add("EncryptedColumn", typeof(byte[])); // 加密列对应类型保持一致

// 填充Concurrent List数据到DataTable
foreach (var record in yourConcurrentList)
{
    insertTable.Rows.Add(record.Id, record.BusinessField, record.EncryptedValue);
}

// 执行批量插入
using (var conn = new SqlConnection(yourDbConnectionString))
{
    conn.Open();
    var command = new SqlCommand(@"
        INSERT INTO dbo.YourTargetTable (Id, BusinessField, EncryptedColumn)
        SELECT Id, BusinessField, EncryptedColumn FROM @BatchRecords
    ", conn);
    
    // 配置表值参数
    var tvpParam = command.Parameters.AddWithValue("@BatchRecords", insertTable);
    tvpParam.SqlDbType = SqlDbType.Structured;
    tvpParam.TypeName = "dbo.YourInsertRecordType"; // 和SQL中创建的自定义表类型名一致
    
    command.ExecuteNonQuery();
}

方案2：参数化批量INSERT语句拼接（轻量场景适用） #

无需修改数据库结构，适合小批量插入场景，就是你提到的拼接单条SQL的实现方式，注意绝对不能直接拼接字段值，所有值必须绑定独立参数。

实现步骤

1. 遍历列表生成参数占位符和对应参数集合
2. 拼接为单条INSERT语句执行，示例代码如下：

if (!yourConcurrentList.Any()) return;

var sqlBuilder = new StringBuilder("INSERT INTO dbo.YourTargetTable (Id, BusinessField, EncryptedColumn) VALUES ");
var paramList = new List<SqlParameter>();
var recordIndex = 0;

foreach (var record in yourConcurrentList)
{
    // 为每条记录生成独立的参数占位符
    sqlBuilder.Append($"(@Id{recordIndex}, @Biz{recordIndex}, @Encrypt{recordIndex}),");
    // 绑定对应参数
    paramList.Add(new SqlParameter($"@Id{recordIndex}", record.Id));
    paramList.Add(new SqlParameter($"@Biz{recordIndex}", record.BusinessField));
    paramList.Add(new SqlParameter($"@Encrypt{recordIndex}", record.EncryptedValue));
    recordIndex++;
}

// 移除最后多余的逗号
sqlBuilder.Length--;

// 执行语句
using (var conn = new SqlConnection(yourDbConnectionString))
{
    conn.Open();
    var command = new SqlCommand(sqlBuilder.ToString(), conn);
    command.Parameters.AddRange(paramList.ToArray());
    command.ExecuteNonQuery();
}

注意事项 #

• 方案2单批次插入记录数不要超过1000条，SQL Server单个查询的参数上限为2100，超出时需要拆分批次处理
• 两种方案都需要保证加密列对应的参数类型、长度和数据库定义完全一致，否则会触发加密校验失败
• 记录量级超过1万条时，优先选择TVP方案，性能比拼接参数方案高30%以上

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