如何在Dapper中使用一个SQL语句更新多条记录？

Question

如何在Dapper中使用一个SQL语句更新多条记录？

3

我想使用一条Update语句来更新多个记录，这些记录具有不同的值（我不是试图将许多行更新为相同的值，这是非常直接的）。这是我现在尝试的：

    using (var cn = GetOpenConnection()) {

        // get items where we need to set calculated fields that will now be persisted in the DB
        var items = cn.Query<MaintenanceItem>("select TOP 500 * from [Maintenance] where Tolerance IS NOT NULL");

        foreach (var mi in maintItems)
        {
            // Set calculated fields on multiple recrods
            logic.CalculateToleranceFields(mi, true);
        }


        var updateInput = items.Select(a => new {a.ToleranceMonths, a.ToleranceDays, a.ToleranceHours, a.ToleranceLandings, a.ToleranceCycles, a.ToleranceRIN }).ToList();

       // THIS DOESN'T WORK - attempting to update multiple rows with different values
       var numResults = cn.Execute(@"UPDATE rm 
                SET rm.ToleranceMonths=ur.ToleranceMonths, 
                rm.ToleranceDays=ur.ToleranceDays, 
                rm.ToleranceHours=ur.ToleranceHours, 
                rm.ToleranceLandings=ur.ToleranceLandings, 
                rm.ToleranceCycles=ur.ToleranceCycles, 
                rm.ToleranceRIN=ur.ToleranceRIN 
            from [RoutineItems] rm
            Inner Join @UpdatedRecords ur ON rm.AircraftId=ur.AircraftId AND rm.ItemNumber=ur.ItemNumber", updateInput);

        Assert.IsTrue(numResults == maintItems.Count());

    }

使用Dapper可以实现这种批量更新吗？我宁愿批量更新，而不是使用for循环将数据推送到数据库中。

- Kento

1

你可以在Sql Server中创建用户类型，或者通过使用XML和创建临时表等方法来模拟批量插入功能。进行谷歌搜索，有很多在线示例可供参考。 - MethodMan

2个回答

1

我知道这个帖子有点旧了。但是，你可以像这样做，而不是使用临时表。这样语法会更加美观。

string sql = @"UPDATE rm 
    SET rm.ToleranceMonths=@ToleranceMonths, 
    rm.ToleranceDays=@ToleranceDays, 
    rm.ToleranceHours=@ToleranceHours, 
    rm.ToleranceLandings=@ToleranceLandings, 
    rm.ToleranceCycles=@ToleranceCycles, 
    rm.ToleranceRIN=@ToleranceRIN 
    FROM [RoutineItems] rm
    WHERE rm.AircraftId=@AircraftId AND rm.ItemNumber=@ItemNumber
    ";

var numResults = cn.Execute(sql, updateInput);

- Mr. Hagge

这个看起来在外观上更好，但请记住更新语句仍将逐个内部执行。因此，如果您想要执行实际的批量更新以使目标表不被长时间锁定，则据我所知临时表方法是唯一的选择。 - Sal

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Kento · Accepted Answer

看起来在Dapper中目前不能通过一条语句实现这个功能。考虑到需要在底层完成的工作，这是完全可以理解的。

我最终做的是使用3个语句创建一个临时表，填充需要更新的数据，然后使用内部连接调用更新：

cn.Execute(@"create table #routineUpdatedRecords
                        (
                            AircraftId int, 
                            ItemNumber int,
                            ToleranceMonths int,
                            ToleranceDays int,
                            ToleranceLandings int,
                            ToleranceCycles decimal(12,2),
                            ToleranceRIN decimal(12,2),
                            ToleranceHours decimal(12,2)
                        );");


cn.Execute(@"Insert INTO #routineUpdatedRecords 
    VALUES(@AircraftId, @ItemNumber, @ToleranceMonths, @ToleranceDays, 
@ToleranceLandings, @ToleranceCycles, @ToleranceRIN, @ToleranceHours)", updateInput);

var numResults = cn.Execute(@"UPDATE rm 
                                SET rm.ToleranceMonths=ur.ToleranceMonths, 
                                rm.ToleranceDays=ur.ToleranceDays, 
                                rm.ToleranceHours=ur.ToleranceHours, 
                                rm.ToleranceLandings=ur.ToleranceLandings, 
                                rm.ToleranceCycles=ur.ToleranceCycles, 
                                rm.ToleranceRIN=ur.ToleranceRIN 
                            from [RoutineItems] rm
                            Inner Join #routineUpdatedRecords ur ON rm.AircraftId=ur.AircraftId AND rm.ItemNumber=ur.ItemNumber");

我认为这比在循环中调用update更快，因为我需要更新大约600K行。