1 using System; 2 using System.Data.SqlClient; 3 using System.Transactions; 4  5 namespace SomeDBTransaction 6 { 7     class Program 8     { 9         static void Main(string[] args)10         {11             string con1 = "SERVER=.; DATABASE=db1; UID=sa; PWD=llh";12             string con2 = "SERVER=.; DATABASE=db2; UID=sa; PWD=llh";13             string sqlStr1 = "U_t1";14             string sqlStr2 = "U_t1";15 16             int resu=CreateTransactionScope(con1, con2, sqlStr1, sqlStr2);17             Console.WriteLine("受影响的行数："+resu);18 19             Console.Read();20         }21 22         // This function takes arguments for 2 connection strings and commands to create a transaction  23         // involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the  24         // transaction is rolled back. To test this code, you can connect to two different databases  25         // on the same server by altering the connection string, or to another 3rd party RDBMS by  26         // altering the code in the connection2 code block. 27         static public int CreateTransactionScope(28             string connectString1, string connectString2,29             string commandText1, string commandText2)30         {31             // Initialize the return value to zero and create a StringWriter to display results. 32             int returnValue = http://www.mamicode.com/0;33             System.IO.StringWriter writer = new System.IO.StringWriter();34 35             try36             {37                 // Create the TransactionScope to execute the commands, guaranteeing 38                 // that both commands can commit or roll back as a single unit of work. 39                 using (TransactionScope scope = new TransactionScope())40                 {41                     using (SqlConnection connection1 = new SqlConnection(connectString1))42                     {43                         // Opening the connection automatically enlists it in the  44                         // TransactionScope as a lightweight transaction.45                         connection1.Open();46 47                         // Create the SqlCommand object and execute the first command.48                         SqlCommand command1 = new SqlCommand(commandText1, connection1);49                         command1.CommandType = System.Data.CommandType.StoredProcedure;50                         returnValue =http://www.mamicode.com/ command1.ExecuteNonQuery();51                         writer.WriteLine("Rows to be affected by command1: {0}", returnValue);52 53                         // If you get here, this means that command1 succeeded. By nesting 54                         // the using block for connection2 inside that of connection1, you 55                         // conserve server and network resources as connection2 is opened 56                         // only when there is a chance that the transaction can commit.    57                         using (SqlConnection connection2 = new SqlConnection(connectString2))58                         {59                             // The transaction is escalated to a full distributed 60                             // transaction when connection2 is opened.61                             connection2.Open();62 63                             // Execute the second command in the second database.64                             returnValue = http://www.mamicode.com/0;65                             SqlCommand command2 = new SqlCommand(commandText2, connection2);66                             command1.CommandType = System.Data.CommandType.StoredProcedure;67                             returnValue =http://www.mamicode.com/ command2.ExecuteNonQuery();68                             writer.WriteLine("Rows to be affected by command2: {0}", returnValue);69                         }70                     }71 72                     // The Complete method commits the transaction. If an exception has been thrown, 73                     // Complete is not  called and the transaction is rolled back.74                     scope.Complete();75 76                 }77 78             }79             catch (TransactionAbortedException ex)80             {81                 writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);82             }83             catch (ApplicationException ex)84             {85                 writer.WriteLine("ApplicationException Message: {0}", ex.Message);86             }87             catch (Exception ex)88             {89                 writer.WriteLine("ERROR: {0}",ex.Message);90             }91             // Display messages.92             Console.WriteLine(writer.ToString());93 94             return returnValue;95         }96     }97 98 99 }