(原创)在web开发中的三个层次使用事务
很多数据库操作需要进行事务,Asp.net下面进行事务大致有3个层次:
(1)存储过程层次的事务
(2)Ado.Net层次的事务
(3)Asp.Net页面层次的事务
下面分别举例:
首先建立trantest表,字段id(int),test(char)
为id设置主键(利用主键是不允许重复的特性进行事务测试)
假设数据库内存在记录id=1,test='test'
(1)
CREATE PROCEDURE Tran1
as
begin tran
set xact_abort on
Insert Into trantest (id,test)values(1,'test')
Insert Into trantest (id,test)values(2,'test')
commit tran
GO
set xact_abort on 表示遇到错误立即回滚
当然你也可以这么写
CREATE PROCEDURE tran1
as
begin tran
insert into trantest(id,test)values(1,'test')
if(@@error<>0)
rollback tran
else
begin
insert into trantest(id,test)values(2,'test')
if(@@error<>0)
rollback tran
else
commit tran
end
GO
(2)
SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
SqlCommand cmd1=new SqlCommand("Insert Into trantest (id,test)values(1,'test')",conn);
SqlCommand cmd2=new SqlCommand("Insert Into trantest (id,test)values(1,'test')",conn);
conn.Open();
SqlTransaction tran=conn.BeginTransaction();
cmd1.Transaction=tran;
cmd2.Transaction=tran;
try
{
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tran.Commit();
}
catch(SqlException except)
{
tran.Rollback();
Response.Write(except.Message);
}
finally
{
conn.Close();
}
(3)
添加引用System.EnterpriseServices.dll
using System.EnterpriseServices;
随便建立一个按钮,在按钮中进行如下操作:
try
{
work1();
work2();
ContextUtil.SetComplete();
}
catch(System.Exception except)
{
ContextUtil.SetAbort();
Response.Write(except.Message);
}
然后在页面中添加2个操作,模拟一下在逻辑层调用不同类中的操作的情况
private void work1()
{
SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
SqlCommand cmd1=new SqlCommand("Insert Into trantest (id,test)values(1,'test')",conn);
conn.Open();
cmd1.ExecuteNonQuery();
conn.Close();
}
private void work2()
{
SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
SqlCommand cmd2=new SqlCommand("Insert Into trantest (id,test)values(2,'test')",conn);
conn.Open();
cmd2.ExecuteNonQuery();
conn.Close();
}
修改前台页面在<%Page后面添加 Transaction="Required" 即可
(1)存储过程层次的事务
(2)Ado.Net层次的事务
(3)Asp.Net页面层次的事务
下面分别举例:
首先建立trantest表,字段id(int),test(char)
为id设置主键(利用主键是不允许重复的特性进行事务测试)
假设数据库内存在记录id=1,test='test'
(1)
CREATE PROCEDURE Tran1
as
begin tran
set xact_abort on
Insert Into trantest (id,test)values(1,'test')
Insert Into trantest (id,test)values(2,'test')
commit tran
GO
set xact_abort on 表示遇到错误立即回滚
当然你也可以这么写
CREATE PROCEDURE tran1
as
begin tran
insert into trantest(id,test)values(1,'test')
if(@@error<>0)
rollback tran
else
begin
insert into trantest(id,test)values(2,'test')
if(@@error<>0)
rollback tran
else
commit tran
end
GO
(2)
SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
SqlCommand cmd1=new SqlCommand("Insert Into trantest (id,test)values(1,'test')",conn);
SqlCommand cmd2=new SqlCommand("Insert Into trantest (id,test)values(1,'test')",conn);
conn.Open();
SqlTransaction tran=conn.BeginTransaction();
cmd1.Transaction=tran;
cmd2.Transaction=tran;
try
{
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tran.Commit();
}
catch(SqlException except)
{
tran.Rollback();
Response.Write(except.Message);
}
finally
{
conn.Close();
}
(3)
添加引用System.EnterpriseServices.dll
using System.EnterpriseServices;
随便建立一个按钮,在按钮中进行如下操作:
try
{
work1();
work2();
ContextUtil.SetComplete();
}
catch(System.Exception except)
{
ContextUtil.SetAbort();
Response.Write(except.Message);
}
然后在页面中添加2个操作,模拟一下在逻辑层调用不同类中的操作的情况
private void work1()
{
SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
SqlCommand cmd1=new SqlCommand("Insert Into trantest (id,test)values(1,'test')",conn);
conn.Open();
cmd1.ExecuteNonQuery();
conn.Close();
}
private void work2()
{
SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
SqlCommand cmd2=new SqlCommand("Insert Into trantest (id,test)values(2,'test')",conn);
conn.Open();
cmd2.ExecuteNonQuery();
conn.Close();
}
修改前台页面在<%Page后面添加 Transaction="Required" 即可
欢迎大家阅读我的极客时间专栏《Java业务开发常见错误100例》【全面避坑+最佳实践=健壮代码】
![](https://img2020.cnblogs.com/blog/103060/202004/103060-20200402094642952-518549746.jpg)