Question
I'm trying to update numerous records with different values using a single Update statement (I'm not trying to update many rows with the same values, which is quite simple). I'm now attempting the following:
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 Dapper capable of handling this kind of mass update? Instead of utilizing a for loop to send the data into the database, I would want to do the update in bulk.
Accepted Answer
This seems to be impossible right now in Dapper with just one statement. When you think what would need to be done behind the scenes to make this happen, it is very comprehensible.
In the end, I created a temporary table, filled it with the information that needed to be updated, and then called an update with an inner join to my temporary table using three statements:
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");
Given that I was updating roughly 600K rows, I think this was quicker than calling update in a loop.