如需运行分布式事务,需启动Distributed Transaction Coordinator服务,如启动该服务报错,输入CMD命令msdtc -resetlog即可。
T-SQL:
Create Table TranTable
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Priority TINYINT--最大值255
)
C#:
string sql1 = "INSERT INTO TranTable VALUES(1)";//正常插入
string sql2 = "INSERT INTO TranTable VALUES(256)";//插入异常
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 { 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"; } }