如何回滚已使用SqlTransaction执行的所有SqlTransaction？

Question

我有以下代码：

public void Execute(string Query, params SqlParameter[] Parameters)
{
    using (var Connection = new SqlConnection(Configuration.ConnectionString))
    {
        Connection.Open();

        using (var Command = new SqlCommand(Query, Connection))
        {
            if (Parameters.Length > 0)
            {
                Command.Parameters.Clear();
                Command.Parameters.AddRange(Parameters);
            }

            Command.ExecuteNonQuery();
        }
    }
}

对于不同的查询，该方法可以调用2或3次，但方式相同。

例如：

1. 插入雇员
2. 插入员工证书
3. 在这里，在另一个表上更新员工的级别会导致失败。例如

如果第3点失败，所有已提交的命令都不应该执行，必须回滚。

我知道我可以把SqlTransaction放在上面，并使用Commit()方法。但是如果第三点失败了呢？我认为第3点只会回滚，而其他第1、2点不会吗？如何解决这个问题，我应该怎么做？

我应该使用SqlCommand[]数组吗？我该怎么办？

我只发现了类似的问题，但在CodeProject中：

看这里

Answer 1

在不更改Execute方法的情况下，您可以这样做

    var tranOpts = new TransactionOptions()
    {
         IsolationLevel = IsolationLevel.ReadCommitted,
         Timeout = TransactionManager.MaximumTimeout
    };

    using (var tran = new TransactionScope(TransactionScopeOption.Required, tranOpts)
    {
       Execute("INSERT ...");
       Execute("INSERT ...");
       Execute("UPDATE ...");

       tran.Complete();
    }

SqlClient将缓存事务中登记的内部SqlConnection，并将其重用以供每个调用执行。因此，您甚至会得到一个本地(而不是分布式)事务。

这在这里的文档中都有解释：System.Transactions与Server的集成

Answer 2

有几种方法可以做到。

可能涉及更改代码最少和复杂度最小的方式是将多个SQL语句链接到单个查询中。为运行多个语句的Query参数构建一个字符串是非常好的，包括BEGIN TRANSACTION、COMMIT和(如果需要的话) ROLLBACK。基本上，在C#代码中保存一个完整的存储过程。这也有一个很好的好处，使您的过程更容易使用版本控制。

但还是觉得有点无趣。

降低这种影响的一种方法是标记Execute()方法私有。然后，为每个查询在类中添加一个方法。通过这种方式，长SQL字符串是孤立的，当您使用数据库时，感觉更像是使用本地API。对于更复杂的应用程序，这可能是一个完全独立的程序集，有几种类型管理逻辑功能区，其中核心方法(如Exectue() )是internal。无论如何，这是一个好主意，不管您最后如何支持事务。

说到过程，存储过程也是处理这个问题的一个非常好的方法。有一个存储过程来完成所有的工作，并在准备就绪时调用它。

另一个选项是重载该方法以接受多个查询和参数集合：

public void Execute(string TransactionName, string[] Queries, params SqlParameter[][] Parameters)
{
    using (var Connection = new SqlConnection(Configuration.ConnectionString))
    using (var Transaction = new SqlTransaction(TransactionName))
    {
        connection.Transaction = Transaction;
        Connection.Open();
        try 
        {
            for (int i = 0; i < Queries.Length; i++)
            {
                using (var Command = new SqlCommand(Queries[i], Connection))
                {
                    command.Transaction = Transaction;
                    if (Parameters[i].Length > 0)
                    {
                        Command.Parameters.Clear();
                        Command.Parameters.AddRange(Parameters);
                    }                
                    Command.ExecuteNonQuery();
                }
            }
            Transaction.Commit();
        }
        catch(Exception ex)
        {
            Transaction.Rollback();
            throw; //I'm assuming you're handling exceptions at a higher level in the code
        }
    }
}

虽然我不确定params关键字如何与数组一起工作.我只是没有尝试过这种选择，但类似的方法会奏效的。这里的缺点还在于，让后面的查询依赖于早期查询的结果并不简单，即使没有参数的查询也仍然需要参数数组作为占位符。

最后一个选项是扩展保存Execute()方法的类型以支持事务。这里的诀窍是让这种类型成为static是常见的(也是可取的)，但是支持事务需要重用公共连接和事务对象。考虑到事务的隐含的长期运行性质，您必须一次支持多个事务，这意味着同时支持实例和实现IDisposable。

Answer 3

using (var connection = new SqlConnection(Configuration.ConnectionString))
            {
                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction;
                connection.Open();
                transaction = connection.BeginTransaction("Transaction");

                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    if (Parameters.Length > 0)
                    {
                        command.Parameters.Clear();
                        command.Parameters.AddRange(Parameters);
                    }
                    command.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (Exception ex2)
                    {
                       //trace
                    }
                }

            }