[转载]在不同的用户或数据库中使用事务

C#中跨数据库增删改的事务控制

 在程序的开发过程中，对数据库的增删改通常要用到事务，用来实现的全部更新和全部回滚。单数据库还比较好做，如果遇到同时操作多个数据库增删改，可以用下面的方法

 一。TransactionScope 类

     TransactionScope 类是framework2.0 新增的一个类，在System.Transactions命名空间中，使用时必须先添加System.Transactions 引用；另外还要在windows控制面版-->管理工具-->服务-->Distributed Transaction Coordinator-->属性-->启动，启动这个服务.示例代码如下：

 

Code    try             {                  using  (TransactionScope scope  =   new  TransactionScope())                 {                      // 更新northwind数据库的Employees表                      using  (SqlConnection conOne  =   new  SqlConnection( " server=.;uid=sa;pwd=123;database=northwind " ))                     {                         conOne.Open();                         SqlCommand command  =   new  SqlCommand( " update Employees set lastname='chen' where employeeid='1' " , conOne);                          int  i  =  command.ExecuteNonQuery();                     }                      // 更新pubs数据库的jobs表                      using  (SqlConnection conTwo  =   new  SqlConnection( " server=.;uid=sa;pwd=123;database=pubs " ))                     {                         conTwo.Open();                         SqlCommand command  =   new  SqlCommand( " update jobs set job_desc='chen' where job_id='1' " , conTwo);                          int  i  =  command.ExecuteNonQuery();                     }                     scope.Complete();   // 提交事物                 }             }              catch  (Exception ex)        // 发生异常后自动回滚             {                  // throw;             }

 

 

 

二。另一种方法就是建立两个事务，两个连接了。代码如下；

 

Code     SqlConnection conNorthwind  =   new  SqlConnection( " server=.;uid=sa;pwd=123;database=northwind " );             SqlConnection conPubs  =   new  SqlConnection( " server=.;uid=sa;pwd=123;database=pubs " );             SqlCommand commandNorthwind  =   new  SqlCommand();             SqlCommand commandPubs  =   new  SqlCommand();              try             {                 conNorthwind.Open();                 conPubs.Open();                  // 更新northwind数据库的Employees表                 SqlTransaction tranNorthwind  =  conNorthwind.BeginTransaction();                 commandNorthwind.Connection  =  conNorthwind;                 commandNorthwind.Transaction  =  tranNorthwind;                 commandNorthwind.CommandText  =   " update Employees set lastname='chen' where employeeid='1' " ;                  int  i  =  commandNorthwind.ExecuteNonQuery();                  // 更新pubs数据库的jobs表                 SqlTransaction tranPubs  =  conPubs.BeginTransaction();                 commandPubs.Connection  =  conPubs;                 commandPubs.Transaction  =  tranPubs;                 commandPubs.CommandText  =   " update jobs set job_desc='chen' where job_id='1' " ;                  int  k  =  commandPubs.ExecuteNonQuery();                  // throw new Exception();                  // 提交事务                 commandNorthwind.Transaction.Commit();                 conNorthwind.Close();                 commandPubs.Transaction.Commit();                 conPubs.Close();             }              catch  (Exception ex)             {                  // 回滚事务                  if  (commandNorthwind.Transaction  !=   null   &&  conNorthwind  !=   null )                 {                     commandNorthwind.Transaction.Rollback();                     conNorthwind.Close();                 }                  if  (commandPubs.Transaction !=   null   &&  conPubs  !=   null )                 {                     commandPubs.Transaction.Rollback();                     conPubs.Close();                 }                  // throw;             }