Question
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());
}
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.
Accepted Answer
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.
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");
I believe this was faster than calling update in a loop since I was updating about 600K rows.