Using transactions at three levels in web development

Many database operations on the web require transactions, and there are roughly 3 levels of transactions under ASP.net:
(1) Transaction at the level of the stored procedure
(2) Ado.net-level transactions
(3) asp.net transaction at the page level
Here are some examples:
First create Trantest table, field ID (int), test (char)
Set primary key for ID (use primary key is not allow duplicate attribute for transaction test)
Suppose there is a record id=1,test= ' test ' in the database
(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 indicates an immediate rollback of errors encountered
Of course you can write that.
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)
Add Reference System.EnterpriseServices.dll
Using System.EnterpriseServices;

Create a button and do the following in the button:
Try
{
Work1 ();
Work2 ();
ContextUtil.SetComplete ();
}
catch (System.Exception except)
{
ContextUtil.SetAbort ();
Response.Write (except. message);
}

Then add 2 actions to the page to simulate an operation that is not in the same class in the logical layer
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 ();
}

Modify the foreground page to add transaction= "Required" after <%page