如何使用.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 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(); } }

更多推荐