多資料庫交易處理

在一個資料庫中實現事務是沒什麼問題，當時項目中常常會遇到多個資料庫交叉事務的情況，這個方法使用兩個SqlTransaction 來處理這兩個資料庫中的事務，當一個更新不成功兩個都要復原。

public void TransactionDebug()
  {

   string sql1 = @"Data Source=XXXXXX;Initial Catalog=stuDB;Integrated Security=True";
   string sql2 = @"Data Source=XXXXXX;Initial Catalog=Northwind;Integrated Security=True";

   SqlConnection conn1 = new SqlConnection(sql1);
   SqlConnection conn2 = new SqlConnection(sql2);

   string sqlUpdate1 = "UPDATE stuInfo SET stuAddress='江南' WHERE stuNO='s25301'";
   string sqlUpdate2 = "UPDATE Products SET ProductName='Chian' WHERE ProductID=1";

   SqlCommand sc1 = new SqlCommand(sqlUpdate1, conn1);
   SqlCommand sc2 = new SqlCommand(sqlUpdate2, conn2);

   conn1.Open();
   SqlTransaction sqlTran1 = conn1.BeginTransaction();
   conn2.Open();
   SqlTransaction sqlTran2 = conn2.BeginTransaction();

   int effectrow=0;

   using(TransactionScope tranScope = new TransactionScope())
   {
    try
    {
     sc1.Transaction = sqlTran1;
     effectrow += sc1.ExecuteNonQuery();

     sc2.Transaction = sqlTran2;
     effectrow += sc2.ExecuteNonQuery();
    }
    catch(SqlException ex)
    {
     sqlTran1.Rollback();
     sqlTran2.Rollback();
     conn1.Close();
     conn2.Close();

     throw ex;
    }
    if(effectrow == 2)
    {
     sqlTran1.Commit();
     sqlTran2.Commit();
    }
    else
    {
     sqlTran1.Rollback();
     sqlTran2.Rollback();
    }

    conn1.Close();
    conn2.Close();
   }
  }