如何在C# ADO.NET中高效批量更新多行数据？

核心优化思路：减少数据库往返次数 #

当前代码循环执行单条UPDATE，哪怕分了事务，4000条数据也会产生4000次数据库请求，这是性能瓶颈的核心原因。以下是几个实用的提速方案：

1. 用CASE WHEN合并批量更新语句 #

把多组更新逻辑合并到单条UPDATE语句中，通过CASE WHEN匹配Id来设置对应Name，大幅减少数据库请求次数。建议分批次处理（比如每200条一批），避免SQL语句过长超出数据库限制。

示例代码：

using (var db = new DbConnection())
{
    db.OpenTransaction();
    try
    {
        // 按200条分批处理，可根据实际调整批次大小
        var batches = itemsToUpdate.Chunk(200);
        foreach (var batch in batches)
        {
            DbQuery q = db.CreateQuery();
            StringBuilder updateSql = new StringBuilder("UPDATE Product SET Name = CASE Id ");
            
            foreach (var item in batch)
            {
                updateSql.AppendLine($"WHEN {item.Id} THEN @Name_{item.Id} ");
                q.AddParameter($"@Name_{item.Id}", item.Name);
            }
            
            updateSql.AppendLine("END WHERE Id IN (");
            updateSql.AppendLine(string.Join(", ", batch.Select(item => item.Id)));
            updateSql.AppendLine(")");
            
            q += updateSql.ToString();
            db.ExecuteQuery();
        }
        db.CommintTransaction();
    }
    catch
    {
        db.RollbackTransaction();
        throw;
    }
}

注：Chunk是.NET 6+特性，低版本可自行实现分批逻辑。

2. 使用表值参数（TVP） #

如果你的数据库支持表值参数（如SQL Server），可以定义自定义表类型，将待更新数据以表形式传入数据库，一次关联完成批量更新，效率极高。

步骤1：在数据库创建表类型

CREATE TYPE ProductUpdateType AS TABLE (Id INT, Name NVARCHAR(255))

步骤2：代码中使用表参数

using (var db = new DbConnection())
{
    db.OpenTransaction();
    try
    {
        DbQuery q = db.CreateQuery();
        q += @"
UPDATE p
SET p.Name = upd.Name
FROM Product p
JOIN @ProductUpdates upd ON p.Id = upd.Id";
        
        // 构造表参数数据
        DataTable updateTable = new DataTable();
        updateTable.Columns.Add("Id", typeof(int));
        updateTable.Columns.Add("Name", typeof(string));
        foreach (var item in itemsToUpdate)
        {
            updateTable.Rows.Add(item.Id, item.Name);
        }
        
        // 假设DbQuery支持添加表参数，具体实现依你的DbConnection类调整
        q.AddParameter("@ProductUpdates", updateTable, SqlDbType.Structured, "ProductUpdateType");
        db.ExecuteQuery();
        
        db.CommintTransaction();
    }
    catch
    {
        db.RollbackTransaction();
        throw;
    }
}

3. 修复原代码的事务逻辑bug #

原代码存在两处逻辑问题，会额外增加性能开销：

• 提交事务后未重置counter，导致后续每一次循环都触发提交，频繁提交会增加数据库负担
• 最后提交事务的判断条件错误，应该是事务处于打开状态时提交，而非!db.IsTransactionOpen

修正后的事务逻辑：

int counter = 0;
foreach (var item in itemsToUpdate)
{
    if (!db.IsTransactionOpen)
    { 
        db.OpenTransaction();
    }

    // 原查询执行代码...

    counter++;
    if (counter >= 100)
    {
        db.CommintTransaction();
        counter = 0; // 重置计数器
    }
}

// 提交剩余未处理的事务
if (db.IsTransactionOpen)
{ 
    db.CommintTransaction();
}

其他辅助优化建议 #

• 确保Product表的Id字段是主键或有索引，加速WHERE子句的匹配效率
• 若存在不必要的触发器或约束，可暂时禁用（需评估业务风险）
• 检查数据库连接池配置，确保连接池开启且最大连接数足够

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