如何在这种情况下处理SQL事务？ [英] How to handle sql transaction in this scenario?

问题描述

我是一个C＃程序员。我想清楚这个复杂的概念。

I am a C# programmer. I want to clear this complex concept.

如果有2个数据库：A和B，假设我要插入的两个，但首先在A，然后B中说，如果记录，而在发生异常分贝B插入。的情况是，在B崩溃，交易与分贝也应该回滚。我有什么做的？

If there are 2 databases: A and B. Suppose I want to insert records in both but first in A and then in B. Say if while inserting in db B an exception occurs. The situation is that if B crashes, transaction with db A should also be rolled back. What do I have to do?

我知道我可以使用的SqlTransaction对象SqlConnectionString类。我能有一些code这个？

I know I can use SqlTransaction object with SqlConnectionString class. Can I have some code for this?

推荐答案

已经在这里问：<一href="http://stackoverflow.com/questions/1063502/how-to-implement-transaction-over-multiple-databases">How实现事务在多个数据库的。

这是最好的答案 keithwarren7 ：

使用TransactionScope类像这样

use the TransactionScope class like this

using(TransactionScope ts = new TransactionScope())
{
  //all db code here

  // if error occurs jump out of the using block and it will dispose and rollback

  ts.Complete();
}

本课程将自动转换为分布式事务，如果有必要的。

The class will automatically convert to a distributed transaction if necessary.

编辑：添加解释，原来的答案

您已经有了在MSDN一个很好的例子：<一href="http://msdn.microsoft.com/fr-fr/library/system.transactions.transactionscope%28v=vs.80%29.aspx" rel="nofollow">http://msdn.microsoft.com/fr-fr/library/system.transactions.transactionscope%28v=vs.80%29.aspx.

You've got a good example in the MSDN : http://msdn.microsoft.com/fr-fr/library/system.transactions.transactionscope%28v=vs.80%29.aspx.

此示例演示了如何使用2数据库连接在一个TransactionScope的。

This example shows you how to use 2 Database Connections in one TransactionScope.

    // 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))
        {
            try
            {
                // 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))
                    try
                    {
                        // 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);
                    }
                    catch (Exception ex)
                    {
                        // Display information that command2 failed.
                        writer.WriteLine("returnValue for command2: {0}", returnValue);
                        writer.WriteLine("Exception Message2: {0}", ex.Message);
                    }
            }
            catch (Exception ex)
            {
                // Display information that command1 failed.
                writer.WriteLine("returnValue for command1: {0}", returnValue);
                writer.WriteLine("Exception Message1: {0}", ex.Message);
            }
        }

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

这篇关于如何在这种情况下处理SQL事务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！