Question
I have the following sql query:
BEGIN TRAN;
UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = xxx;
INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', xxx);
ROLLBACK TRAN;
and this is for a list of id's. eg.
var fooIds = new [] { 1, 2, 3, 4, 5, 6 };
so then I expect this..
BEGIN TRAN;
UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = 1;
INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', 1);
UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = 2;
INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', 2);
UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = 3;
INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', 3);
ROLLBACK TRAN;
Can this be done with Dapper?
NOTE: If the TRAN makes this hard, I can drop that.
Accepted Answer
Dapper has only minimal support for altering queries internally (it supports list expansion for IN, literal injection, and some OPTION/UNKNOWN tweaks. You have two options here:
- use
StringBuilderto create a single large operation that you can execute (this can be parameterized via a dictionary) - move the transaction to ADO.NET rather than TSQL
For the latter, perhaps something like:
using(var tran = conn.BeginTransaction())
{
try
{
conn.Execute(@"
UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = @id;
INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', @id);",
fooIds.Select(id => new { id }), transaction: tran);
}
finally // in this example, we always want to rollback
{
tran.Rollback();
}
}
Popular Answer
you can do something like this:
using (var connection = new SqlConnection(yourConnectionString))
{
connection.Open();
using (var tx = connection.BeginTransaction())
{
foreach (var fooId in fooIds)
{
connection.Execute("UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = @id; INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', @id);", new {id = fooId}, tx);
}
tx.Rollback();
}
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why