如何在Dapper中传递多个记录以使用一个sql语句进行更新 [英] How to pass multiple records to update with one sql statement in Dapper

问题描述

我正在尝试使用一条Update语句来更新具有不同值的多条记录（我并不是试图将许多行更新为具有相同的值，这很简单）。这是我现在正在尝试的方法：

I am attempting to use one single Update statement to update multiple records with different values (I'm not trying to update many rows to have the same values which is pretty straight forward). Here's what I'm trying right now:

    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循环将数据推送到数据库中。

Is this sort of bulk update possible with Dapper? I would rather do the update in bulk rather than using a for loop to push the data into the DB.

推荐答案

在Dapper中，用一条语句当前看来不可能这样。考虑到幕后要做些什么，这是完全可以理解的。

Looks like this isn't currently possible with one statement in Dapper. That's totally understandable when considering what would need done under the covers to accomplish this.

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

What I ended up doing was using 3 statements to create a temp table, populate it the data that needs updated, and then call an update with a inner join to my temp table:

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快，因为我要更新约60万行。

I believe this was faster than calling update in a loop since I was updating about 600K rows.

这篇关于如何在Dapper中传递多个记录以使用一个sql语句进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！