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

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

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