本文转载：http://www.cnblogs.com/jhxk/articles/2696307.html
http://liubaolongg.blog.163.com/blog/static/21386802201222631355218/
.NET事务
- ADO.NET事务
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySqlServer"].ConnectionString))
{
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction())
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.Transaction = tran;
try
{
cmd.CommandText = sql1;
cmd.ExecuteNonQuery();
cmd.CommandText = sql2;
cmd.ExecuteNonQuery();
tran.Commit();
Response.Write("Ok");
}
catch (SqlException ex)
{
tran.Rollback();
Response.Write("Error:" + ex.Message);
}
}
}
conn.Close();
} ADO.NET 显式事务占用资源少、速度快，但功能简单，只能管理单一对象和单一持久资源间的事务，比如想在数据库 B 插入失败，则回滚对数据库 A 的操作，就无法用这种 ADO.NET 显式事务来实现。
- 隐式事务(TransactionScope)
隐式事务不具有Commit、Roolback方法。 using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySqlServer"].ConnectionString))
{
using (TransactionScope ts = new TransactionScope())
{
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
try
{
cmd.CommandText = "insert into TranTable(Priority) values(1)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into TranTable(Priority) values(256)";
cmd.ExecuteNonQuery();
ts.Complete();
Response.Write("Ok");
}
catch (SqlException ex)
{
Response.Write("Error:" + ex.Message);
}
}
}
conn.Close();
} TransactionScope没有和数据库直接关联，那是怎么实现用事务的方式执行语句的呢？ 如果我们在连接字符串里面加上Enlist=false;，再执行上面的代码，发现插入了一条1的记录，说明并不是以事务方式执行的。Enlist默认为true，SqlClient会自动检测是否存在事务，如果有事务，则自动登记到事务中。
- 显示事务(CommittableTransaction)
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MySqlServer"].ConnectionString))
{
using (CommittableTransaction ct = new CommittableTransaction())
{
conn.Open();
conn.EnlistTransaction(ct);//将连接登记到事务
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
try
{
cmd.CommandText = "insert into TranTable(Priority) values(1)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into TranTable(Priority) values(256)";
cmd.ExecuteNonQuery();
ct.Commit();
Response.Write("Ok");
}
catch (SqlException ex)
{
ct.Rollback();
Response.Write("Error:" + ex.Message);
}
}
}
conn.Close();
}
- WebService(TransactionOption)
首先引用using System.EnterpriseServices;，然后设置属性TransactionOption = TransactionOption.Required。 设置TransactionOption.Disabled、TransactionOption.NotSupported、TransactionOption.Supported表示不参与事务。 设置TransactionOption.Required、TransactionOption.RequiresNew表示创建一个新的事务。意思是说当TransactionOption的属性为Required或 RequiresNew的WEB服务方法调用另一个TransactionOption的属性为Required或RequiresNew的WEB服务方法时，每个WEB服务方法将参与他们自己的事务，因为Web Service方法只能用作事务中的根对象。 PS：WEB服务方法的TransactionOption默认属性为Disabled 提交事务ContextUtil.SetComplete(); 回滚事务ContextUtil.SetAbort(); [WebMethod(TransactionOption = TransactionOption.Required)]
public string HelloWorld()
{
try
{
ContextUtil.EnableCommit();//开启事务
SqlConnection con = new SqlConnection("server=.;uid=sa;pwd=sa;database=db.mdf;");
SqlCommand cmd = new SqlCommand("update users set name = 'yangxing' where id = 5", con);
con.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = "update users1 set name = 'yangxing1' where id = 6";//users1表不存在，执行该语句报错
cmd.ExecuteNonQuery();//抛出异常
ContextUtil.SetComplete();//提交事务
return "true";
}
catch
{
ContextUtil.SetAbort();//回滚事务
return "false";
}
}
自动事务处理
在方法之前增加属性[AutoComplete(true)]，这样如果方法执行时没有异常就默认提交，如果有异常则这个方法就会回滚。 using System;
using System.Data.SqlClient;
using System.EnterpriseServices;//企业级服务COM+事务
namespace ClassTran
{
[Transaction(TransactionOption.Required)]
public class OrderData2 : ServicedComponent
{
//自动事务
[AutoComplete(true)]
public string WorkTran()
{
string msg = "";
string conString = "data source=127.0.0.1;database=codematic;
user id=sa;password=";
SqlConnection myConnection = new SqlConnection(conString);
myConnection.Open();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
try
{
myCommand.CommandText = "update P_Product set Name='电脑2'
where Id=";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "update P_Product set Name='电脑3'
where Id=";
myCommand.ExecuteNonQuery();
msg ="成功!";
}
catch (Exception ex)
{
msg = "失败:"+ex.Message;
}
finally
{
myConnection.Close();
}
return msg;
}
}
}