ADO.NET嵌套SQL事务一例
C#代码:
SqlConnection conn
=
new
SqlConnection(
"
Data Source=192.168.1.200;Initial Catalog=PBCRM;Persist Security Info=True;User ID=sa;Password=123456
"
);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand( " exec CRM_SPGenProfileRevision 10, '1', 1 " ,conn, tran);
int row = cmd.ExecuteNonQuery();
// trans.Rollback();
tran.Commit();
conn.Close();
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand( " exec CRM_SPGenProfileRevision 10, '1', 1 " ,conn, tran);
int row = cmd.ExecuteNonQuery();
// trans.Rollback();
tran.Commit();
conn.Close();
SQL存储过程代码:
ALTER
PROCEDURE
[
dbo
]
.
[
CRM_SPGenProfileRevision
]
@FormKey int ,
@Source varchar ( 20 ),
@UserKey int
AS
-- ====================================================
-- Author: Rock Niu
-- Create date: 2008-03-03
-- Description: Generate a revision for specified form
-- Test: EXEC [dbo].[CRM_SPGenProfileRevision] 945,'Ad-Hoc',18
-- ====================================================
begin
declare @currentVersion int ,
@IsCompany bit ,
@versionKey int ,
@submitKey int ,
@trans int ,
@error nvarchar ( max ),
@formType varchar ( 50 );
begin try
set @trans = @@trancount ;
if ( @trans > 0 )
begin
save tran localTran;
end
else
begin
begin tran ;
end
.
if ( @trans = 0 )
begin
commit tran ;
end
end try
begin catch
-- rollback any transction if error occurred.
set @error = error_message();
if (XACT_STATE() <> 0 )
begin
if ( @trans > 0 )
begin
rollback tran localTran;
end
else
begin
rollback tran ;
end
end
raiserror 99999 @error ;
end catch
select @currentVersion as ' NewVersion ' ;
end
@FormKey int ,
@Source varchar ( 20 ),
@UserKey int
AS
-- ====================================================
-- Author: Rock Niu
-- Create date: 2008-03-03
-- Description: Generate a revision for specified form
-- Test: EXEC [dbo].[CRM_SPGenProfileRevision] 945,'Ad-Hoc',18
-- ====================================================
begin
declare @currentVersion int ,
@IsCompany bit ,
@versionKey int ,
@submitKey int ,
@trans int ,
@error nvarchar ( max ),
@formType varchar ( 50 );
begin try
set @trans = @@trancount ;
if ( @trans > 0 )
begin
save tran localTran;
end
else
begin
begin tran ;
end
.
if ( @trans = 0 )
begin
commit tran ;
end
end try
begin catch
-- rollback any transction if error occurred.
set @error = error_message();
if (XACT_STATE() <> 0 )
begin
if ( @trans > 0 )
begin
rollback tran localTran;
end
else
begin
rollback tran ;
end
end
raiserror 99999 @error ;
end catch
select @currentVersion as ' NewVersion ' ;
end
执行结果,成功时两个事务一起commit,有错误时,两个事务一起rollback.