SqlTransaction mytran = null;//声明事务
try
{
mytran = this.Conn.BeginTransaction();//开启事务
SqlCommand sqlxiaofei = new SqlCommand();
string sxiaofei = "sql语句'";
sqlxiaofei = new SqlCommand(sxiaofei, this.Conn);
sqlxiaofei.Transaction = mytran;
int m = sqlxiaofei.ExecuteNonQuery();
if (m > 0)
{
sxiaofei = “另一个sql语句"
sqlxiaofei = new SqlCommand(sxiaofei, this.Conn);
sqlxiaofei.Transaction = mytran;
int i = sqlxiaofei.ExecuteNonQuery();
if (i > 0)
{
//成功信息
mytran.Commit();//成功提交事务
}
else
{
//失败信息
}
}
else
{
//失败信息
}
}
catch (SqlException ex)
{
mytran.Rollback();//回滚事务
HttpContext.Current.Response.Write("事务操作出错,已回滚。系统信息:" + ex.Message);
HttpContext.Current.Response.End();
}
在存储过程中编写正确的事务处理代码
1. 在事务语句最前面加上set xact_abort on
set xact_abort on begin tran update statement 1 ... update statement 2 ... delete statement 3 ... commit tran go
当 xact_abort 选项为 on 时,sql server 在遇到错误时会终止执行并 rollback 整个事务。
2. 在每个单独的DML语句执行后,立即判断执行状态,并做相应处理。
begin tran update statement 1 ... if @@error <> 0 begin rollback tran goto labend end delete statement 2 ... if @@error <> 0 begin rollback tran goto labend end commit tran labend: go
3. 在SQL Server 2005中,可利用 try...catch 异常处理机制
begin tran begin try update statement 1 ... delete statement 2 ... end try begin catch if @@trancount > 0 rollback tran end catch if @@trancount > 0 commit tran go
下面是个简单的存储过程,演示事务处理过程。
create procedure dbo.pr_tran_inproc as begin set nocount on begin tran update statement 1 ... if @@error <> 0 begin rollback tran return -1 end delete statement 2 ... if @@error <> 0 begin rollback tran return -1 end commit tran return 0 end go