多数据库事务处理

在一个数据库中实现事务是没什么问题，当时项目中常常会遇到多个数据库交叉事务的情况，这个方法使用两个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();
   }
  }