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

http://www.cnblogs.com/gossip/archive/2009/02/16/1392028.html

 

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

一。TransactionScope类

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

 

  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;
             }

 

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

 

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;
             }