如何使用.NET和Dapper.NET执行此sql语句?(How can I execute this sql statement with .NET and Dapper.NET?)
我有以下sql查询:
BEGIN TRAN; UPDATE [dbo].[Foo] SET StatusType = 2 WHERE FooId = xxx; INSERT INTO [dbo].[FooNotes] (FooId, Note) VALUES ('blah....', xxx); ROLLBACK TRAN;这是一个id的列表。 例如。
var fooIds = new [] { 1, 2, 3, 4, 5, 6 };所以我希望这个......
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;这可以用Dapper完成吗?
注意:如果TRAN使这很难,我可以放弃它。
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.
最满意答案
Dapper对内部更改查询的支持很少(它支持IN列表扩展,文字注入和一些OPTION / UNKNOWN调整。这里有两个选项:
使用StringBuilder创建一个可以执行的大型操作(可以通过字典进行参数化) 将事务移动到ADO.NET而不是TSQL对于后者,可能是这样的:
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(); } }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 StringBuilder to create a single large operation that you can execute (this can be parameterized via a dictionary) move the transaction to ADO.NET rather than TSQLFor 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(); } }更多推荐
发布评论