方法1: 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; }
方法2: 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; }
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;
}
using(var trans2= conn2.BeginTransaction())
{
.....分别处理两个数据库操作
}一样。这无所谓“更优”。最好的办法就是在业务上进行安排和设计,不要依赖数据库事务。比如说有几百个点联网卖票,难道一个点卖票的过程中要理解为“数据库事务”?根本不可能。人家会先把票从“待卖”移动到“正在交易”中包括起来,然后假设没有成功卖出,则过3分钟就会自动放回到“待卖”中再此投入使用。而不会弄个简单的“数据库事务”这种纸上谈兵的技术。
http://eatpockyboy.blog.163.com/blog/static/116734640201010631937236/