.Net常用事务
1、SQL事务
优点:执行效率最佳
限制:事务上下文仅在数据库中调用,难以实现复杂的业务逻辑。
例:
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 CREATE PROCEDURE SP_DeleteOrder
2 (
3 @OrderID INT
4 )
5 AS
6 BEGIN TRANSACTION DeleteOrder
7
8 DELETE FROM [OrderDetail] WHERE OrderID = @OrderID
9 DELETE FROM [Order] WHERE OrderID = @OrderID
10
11 IF @@ERROR > 0
12 BEGIN
13 ROLLBACK TRANSACTION DeleteOrder
14 END
15
16 COMMIT TRANSACTION DeleteOrder
17
2 (
3 @OrderID INT
4 )
5 AS
6 BEGIN TRANSACTION DeleteOrder
7
8 DELETE FROM [OrderDetail] WHERE OrderID = @OrderID
9 DELETE FROM [Order] WHERE OrderID = @OrderID
10
11 IF @@ERROR > 0
12 BEGIN
13 ROLLBACK TRANSACTION DeleteOrder
14 END
15
16 COMMIT TRANSACTION DeleteOrder
17
2、ADO.NET 事务
在ADO.NET 中,可以使用Connection 和Transaction 对象来控制事务。若要执行事务,请执行下列操作:
调用Connection 对象的BeginTransaction 方法来标记事务的开始。
将Transaction 对象分配给要执行的Command的Transaction 属性。
执行所需的命令。
调用Transaction 对象的Commit 方法来完成事务,或调用Rollback 方法来取消事务。
优点:简单,效率和数据库事务差不多快。
缺点:事务执行在数据库连接层上,所以你需要在事务过程中手动的维护一个连接。
例:
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);
2 conn.Open();
3 SqlTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted);
4 SqlCommand cmd = new SqlCommand();
5 cmd.Connection = conn;
6 cmd.Transaction = tx;
7
8 try
9 {
10 cmd.CommandText = "INSERT INTO [Test]([Name],[Value]) VALUES ('测试1','1')";
11 cmd.ExecuteNonQuery();
12 cmd.CommandText = "INSERT INTO [Test]([Name],[Value]) VALUES ('测试2','2')";
13 cmd.ExecuteNonQuery();
14
15 tx.Commit();
16 }
17 catch (Exception ex)
18 {
19 tx.Rollback();
20 throw new Exception(ex.Message, ex);
21 }
22 finally
23 {
24 conn.Close();
25 }
26
2 conn.Open();
3 SqlTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted);
4 SqlCommand cmd = new SqlCommand();
5 cmd.Connection = conn;
6 cmd.Transaction = tx;
7
8 try
9 {
10 cmd.CommandText = "INSERT INTO [Test]([Name],[Value]) VALUES ('测试1','1')";
11 cmd.ExecuteNonQuery();
12 cmd.CommandText = "INSERT INTO [Test]([Name],[Value]) VALUES ('测试2','2')";
13 cmd.ExecuteNonQuery();
14
15 tx.Commit();
16 }
17 catch (Exception ex)
18 {
19 tx.Rollback();
20 throw new Exception(ex.Message, ex);
21 }
22 finally
23 {
24 conn.Close();
25 }
26
3、TransactionScope事务
在.NET 2.0中新添加了一个名为System.Transactions的命名空间,其提供了一个“轻量级”的、易于使用的事务框架,通过这个框架可以大大简化事务的操作。
这个框架提供了如下优点:
(1)在简单(不涉及分布式)事务中也可以使用声明式的事务处理方法,而不必使用Com+容器和目录注册。
(2)用户根本不需要考虑是简单事务还是分布式事务。它实现一种所谓自动提升事务机制(Promotable Transaction),会自动根据事务中涉及的对象资源判断使用何种事务管理器。
TransactionScope事务类,它可以使代码块成为事务性代码。并自动提升为分布式事务
优点:实现简单,同时能够自动提升为分布式事务
例:
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
1 TransactionOptions option = new TransactionOptions();
2 option.IsolationLevel = IsolationLevel.ReadCommitted;
3
4 using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required,option))
5 {
6 using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
7 {
8 conn.Open();
9 SqlCommand cmd = new SqlCommand(conn);
10 cmd.CommandText = "INSERT INTO [Test]([Name],[Value]) VALUES ('测试1','1')";
11 cmd.ExecuteNonQuery();
12 cmd.CommandText = "INSERT INTO [Test]([Name],[Value]) VALUES ('测试2','2')";
13 cmd.ExecuteNonQuery();
14 }
15 ts.Complete();
16 }
17
2 option.IsolationLevel = IsolationLevel.ReadCommitted;
3
4 using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required,option))
5 {
6 using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
7 {
8 conn.Open();
9 SqlCommand cmd = new SqlCommand(conn);
10 cmd.CommandText = "INSERT INTO [Test]([Name],[Value]) VALUES ('测试1','1')";
11 cmd.ExecuteNonQuery();
12 cmd.CommandText = "INSERT INTO [Test]([Name],[Value]) VALUES ('测试2','2')";
13 cmd.ExecuteNonQuery();
14 }
15 ts.Complete();
16 }
17