SqlSugar批量修改

原创于 2025-11-30 19:07:08 发布 · 177 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#前端 #c# #开发语言 #.NET #DotNET

数据库ORM 专栏收录该内容

7 篇文章

订阅专栏

在实际业务系统中，批量修改（更新）操作是极其常见的需求。SqlSugar提供了多种高效的批量修改方案，能够满足不同场景下的性能要求。本文将全面解析SqlSugar的批量修改技术。

1. 基础批量修改方法

1.1 条件批量更新

// 根据条件批量更新
var result = db.Updateable<Order>()
    .SetColumns(o => new Order()
    {
        Status = OrderStatus.Completed,
        UpdateTime = DateTime.Now,
        UpdateBy = "admin"
    })
    .Where(o => o.CreateTime < DateTime.Now.AddDays(-30) && o.Status == OrderStatus.Pending)
    .ExecuteCommand();

// 异步版本
var result = await db.Updateable<Order>()
    .SetColumns(o => o.Status == OrderStatus.Completed)
    .Where(o => o.Id.In(ids))
    .ExecuteCommandAsync();

1.2 实体列表批量更新

// 更新实体列表（根据主键）
var orders = GetOrdersToUpdate();
var result = db.Updateable(orders).ExecuteCommand();

// 只更新特定列
var result = db.Updateable(orders)
    .UpdateColumns(o => new { o.Status, o.UpdateTime })
    .ExecuteCommand();

// 忽略空值更新
var result = db.Updateable(orders)
    .IgnoreColumns(ignoreAllNull: true)
    .ExecuteCommand();

2. 高性能批量修改方案

2.1 分页批量更新（大数据量推荐）

public async Task<long> BatchUpdateInPages<T>(
    Expression<Func<T, bool>> whereExpression,
    Expression<Func<T, T>> updateExpression,
    int pageSize = 1000) where T : class, new()
{
    var totalUpdated = 0L;
    var hasMoreData = true;
    var pageIndex = 0;

    while (hasMoreData)
    {
        try
        {
            // 先查询要更新的ID（避免长时间锁表）
            var idsToUpdate = await db.Queryable<T>()
                .Where(whereExpression)
                .OrderBy("Id")
                .Select("Id")
                .Skip(pageIndex * pageSize)
                .Take(pageSize)
                .SelectColumns("Id")
                .ToListAsync();

            if (!idsToUpdate.Any())
            {
                hasMoreData = false;
                break;
            }

            var idList = idsToUpdate.Select(x => (x as dynamic).Id).ToList();
            
            // 执行批量更新
            var updatedCount = await db.Updateable<T>()
                .SetColumns(updateExpression)
                .Where($"Id IN ({string.Join(",", idList)})")
                .ExecuteCommandAsync();

            totalUpdated += updatedCount;
            pageIndex++;

            Console.WriteLine($"已更新 {totalUpdated} 条记录...");
            
            // 避免数据库压力过大
            if (hasMoreData)
                await Task.Delay(50);
        }
        catch (Exception ex)
        {
            Console.WriteLine($"第{pageIndex + 1}页更新失败: {ex.Message}");
            // 可以选择继续或中断
            break;
        }
    }

    return totalUpdated;
}

// 使用示例
var result = await BatchUpdateInPages<Order>(
    o => o.Status == OrderStatus.Pending && o.CreateTime < DateTime.Now.AddDays(-1),
    o => new Order 
    { 
        Status = OrderStatus.Expired, 
        UpdateTime = DateTime.Now 
    });

2.2 使用Case When批量更新不同值

// 根据不同条件设置不同值
public async Task<int> BatchUpdateWithCaseWhen(List<OrderUpdateDto> updates)
{
    // 构建Case When语句
    var caseWhenSql = new StringBuilder();
    var parameters = new List<SugarParameter>();
    
    for (int i = 0; i < updates.Count; i++)
    {
        var update = updates[i];
        caseWhenSql.AppendLine($"WHEN Id = @id{i} THEN @status{i}");
        parameters.Add(new SugarParameter($"@id{i}", update.OrderId));
        parameters.Add(new SugarParameter($"@status{i}", (int)update.NewStatus));
    }

    var sql = $@"
        UPDATE Orders 
        SET Status = CASE 
            {caseWhenSql}
            ELSE Status 
        END,
        UpdateTime = @updateTime
        WHERE Id IN ({string.Join(",", updates.Select(u => u.OrderId))})";

    parameters.Add(new SugarParameter("@updateTime", DateTime.Now));

    return await db.Ado.ExecuteCommandAsync(sql, parameters);
}

3. 高级批量修改技巧

3.1 带事务的级联更新

public async Task<bool> BatchUpdateWithTransaction(List<long> orderIds, OrderStatus newStatus)
{
    try
    {
        db.Ado.BeginTran();
        
        // 1. 更新主订单表
        var mainResult = await db.Updateable<Order>()
            .SetColumns(o => new Order 
            { 
                Status = newStatus,
                UpdateTime = DateTime.Now 
            })
            .Where(o => orderIds.Contains(o.Id))
            .ExecuteCommandAsync();
            
        // 2. 更新订单明细表
        var detailResult = await db.Updateable<OrderDetail>()
            .SetColumns(od => new OrderDetail 
            { 
                Status = newStatus,
                UpdateTime = DateTime.Now 
            })
            .Where(od => orderIds.Contains(od.OrderId))
            .ExecuteCommandAsync();
            
        // 3. 记录操作日志
        await db.Insertable(orderIds.Select(id => new UpdateLog
        {
            OrderId = id,
            OldStatus = OrderStatus.Pending, // 需要实际查询旧值
            NewStatus = newStatus,
            UpdateTime = DateTime.Now
        }).ToList()).ExecuteCommandAsync();

        db.Ado.CommitTran();
        return true;
    }
    catch (Exception ex)
    {
        db.Ado.RollbackTran();
        _logger.LogError(ex, "批量更新事务失败");
        return false;
    }
}

3.2 基于查询的批量更新

// 使用子查询进行批量更新
public async Task<int> UpdateBasedOnQuery()
{
    // 示例：将超过30天未完成的订单标记为过期
    return await db.Updateable<Order>()
        .SetColumns(o => o.Status == OrderStatus.Expired)
        .SetColumns(o => o.UpdateTime == DateTime.Now)
        .Where(o => o.Status == OrderStatus.Pending && 
                   SqlFunc.Subquery<OrderLog>()
                   .Where(log => log.OrderId == o.Id && log.Action == "Create")
                   .Max(log => log.CreateTime) < DateTime.Now.AddDays(-30))
        .ExecuteCommandAsync();
}

// 使用Join进行批量更新
public async Task<int> UpdateWithJoin()
{
    return await db.Updateable<Order>()
        .InnerJoin<User>((o, u) => o.UserId == u.Id)
        .SetColumns((o, u) => new Order 
        { 
            Priority = u.VipLevel > 2 ? "High" : "Normal" 
        })
        .Where((o, u) => u.CreateTime < DateTime.Now.AddYears(-1))
        .ExecuteCommandAsync();
}

SqlSugar批量修改的最佳实践建议：