如何使用.NET和Dapper.NET执行此SQL语句？

Question

我有以下的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难以实现，我可以放弃它。

- Pure.Krome

Answer 1

Dapper在内部仅支持最小限度的查询修改（它支持IN列表扩展、文字注入和一些OPTION/UNKNOWN微调）。你有两个选择：

1. 使用StringBuilder创建一个可执行的大型操作（可以通过字典进行参数化）。
2. 将事务移到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();
    }
}

- Marc Gravell

---

啊，好的 - 所以我正在尝试理解这个......所以这仍然使用Dapper，但是tran是手动创建的（而不是在查询中创建），因为Dapper只是在现有连接上 :sparkles: ？ - Pure.Krome

@Pure.Krome 不需要闪光效果 - 我们明确将事务作为参数传递。 - Marc Gravell

啊，是的，我在审查代码片段时错过了最后一个参数。很简单 - 完全有道理。谢谢你的工作，马克 - 继续为正义而战 :) :) :cheers: - Pure.Krome

哦 - 我必须在创建事务范围之前手动调用 conn.Open()（基于您在此线程中其他答案的评论）？ - Pure.Krome

@Pure 不是一个“事务范围” - 它是一个 ADO.NET 事务。 “事务范围”是一个单独但相关的概念。但是，连接需要处于打开状态才能开始交易。 - Marc Gravell

Answer 2

你可以这样做：

你可以像这样做：

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();
    }
}

- Alex Erygin

---

ADO.NET的小问题：在打开连接之前，您无法使用connection.BeginTransaction()。 - Marc Gravell

这样做会导致进行2次 fooIds.Count 的往返数据库操作，而不是一次。 - Pure.Krome