I have number of records that I have loaded from database and I want to update set couple values before I return them. The only main requirement I do not want multiple commands updating each record one by one.
Please note my ids are GUIDs(uniqueidentifier)
so far I have my code as
public IEnumerable<Person> GetUnprocessedPeople(int batchSize)
{
List<Queue_ImportQueue> list;
using (IDbConnection db = OpenedConnection)
{
string peopleList = $"SELECT TOP({batchSize}) * FROM [dbo].[Person]";
list = db.Query<Person>(peopleList).ToList();
using (IDbTransaction transactionScope = db.BeginTransaction(IsolationLevel.Serializable))
{
string updateQuery = $"UPDATE [dbo].[Person] SET Created = GETDATE() WHERE Id ='@ids'";
try
{
db.Execute(updateQuery, new { Id = list.Select(x => x.Id) }, transactionScope);
transactionScope.Commit();
}
catch (Exception ex)
{
transactionScope.Rollback();
throw;
}
}
}
return list;
}
OK the solution was quite simple. There is no need for specific casting. just ensure you have all arguments correct. So extract that has fixed the query for me:
string updateQuery = $"UPDATE [dbo].[Person] SET Created = GETDATE() WHERE Id ='@Ids'";
...
db.Execute(updateQuery, new { Ids = list.Select(x => x.Id) }, transactionScope);