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

来源：互联网发布：广告页设计软件编辑：程序博客网时间：2024/05/16 15:54

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

0 0