实体框架：如何将多个存储过程放入事务中？

Question

我已经进行了大量搜索，但无法找到一个简单的答案。

我有两个存储过程，它们都被函数导入到DBContext对象中：

1. InsertA（）
2. InsertB（）

我想将它们放在一个事务中。（例如，如果InsertB（）失败，则回滚InsertA（））

我该怎么做？我可以声明一个TransactionScope对象并将其包装在这两个存储过程周围吗？

谢谢

- c830

---

为什么不创建一个包装存储过程，该存储过程可以在事务中调用这两个过程，或者如果这些过程总是要一起调用，则将逻辑合并到单个过程中？ - Aaron Bertrand

这两个过程是遗留代码。它们都非常庞大。 - c830

好的，但你仍然可以编写一个非常小的过程作为包装器。 - Aaron Bertrand

Answer 1

您需要将操作注册到事务范围中，方法如下：

using(TransactionScope tranScope = new TransactionScope()) 
{
  InsertA();
  InsertB();

  tranScope.Complete();
}

发生错误时，事务范围将自动回滚。当然，您仍然需要处理异常并执行您的异常处理设计所指示的操作（如记录日志等）。但是，除非您手动调用 Complete()，否则在 using 范围结束时事务将回滚。

除非您在同一个事务范围内打开其他数据库连接（请参见 此处），否则事务范围不会升级为分布式事务。

这是需要知道的重要信息，否则您需要在涉及此操作的所有服务器上配置 MSDTC（Web、中间层、SQL Server）。因此，只要事务不升级为分布式事务，就没有问题。

注意： 要微调事务选项（例如超时和隔离级别），请查看此 TransactionScope 构造函数。默认隔离级别为可串行化。

附加示例：此处。

- Marcel N.

Answer 2

您可以使用TransactionScope对象或者SqlConnection.BeginTransaction方法来处理事务。但是请注意，使用TransactionScope时，如果调用不同数据库中的存储过程，事务可能会升级为分布式事务。分布式事务可能会消耗大量资源。

如何使用sqlConnection.BeginTransaction...(http://msdn.microsoft.com/en-us/library/86773566.aspx)

private static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction("SampleTransaction");

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
            command.ExecuteNonQuery();
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
            command.ExecuteNonQuery();

            // Attempt to commit the transaction.
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);

            // Attempt to roll back the transaction.
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                Console.WriteLine("  Message: {0}", ex2.Message);
            }
        }
    }
}

如何使用TransactionScope...(http://msdn.microsoft.com/zh-cn/library/system.transactions.transactionscope.aspx)

// This function takes arguments for 2 connection strings and commands to create a transaction 
// involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the 
// transaction is rolled back. To test this code, you can connect to two different databases 
// on the same server by altering the connection string, or to another 3rd party RDBMS by 
// altering the code in the connection2 code block.
static public int CreateTransactionScope(
    string connectString1, string connectString2,
    string commandText1, string commandText2)
{
    // Initialize the return value to zero and create a StringWriter to display results.
    int returnValue = 0;
    System.IO.StringWriter writer = new System.IO.StringWriter();

    try
    {
        // Create the TransactionScope to execute the commands, guaranteeing
        // that both commands can commit or roll back as a single unit of work.
        using (TransactionScope scope = new TransactionScope())
        {
            using (SqlConnection connection1 = new SqlConnection(connectString1))
            {
                // Opening the connection automatically enlists it in the 
                // TransactionScope as a lightweight transaction.
                connection1.Open();

                // Create the SqlCommand object and execute the first command.
                SqlCommand command1 = new SqlCommand(commandText1, connection1);
                returnValue = command1.ExecuteNonQuery();
                writer.WriteLine("Rows to be affected by command1: {0}", returnValue);

                // If you get here, this means that command1 succeeded. By nesting
                // the using block for connection2 inside that of connection1, you
                // conserve server and network resources as connection2 is opened
                // only when there is a chance that the transaction can commit.   
                using (SqlConnection connection2 = new SqlConnection(connectString2))
                {
                    // The transaction is escalated to a full distributed
                    // transaction when connection2 is opened.
                    connection2.Open();

                    // Execute the second command in the second database.
                    returnValue = 0;
                    SqlCommand command2 = new SqlCommand(commandText2, connection2);
                    returnValue = command2.ExecuteNonQuery();
                    writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
                }
            }

            // The Complete method commits the transaction. If an exception has been thrown,
            // Complete is not  called and the transaction is rolled back.
            scope.Complete();

        }

    }
    catch (TransactionAbortedException ex)
    {
        writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
    }
    catch (ApplicationException ex)
    {
        writer.WriteLine("ApplicationException Message: {0}", ex.Message);
    }

    // Display messages.
    Console.WriteLine(writer.ToString());

    return returnValue;
}

- Joe Web

---

但是为了完成这个任务，需要编写大量的代码，而使用适当的事务支持，只需几行代码即可完成。 - Marcel N.