Transactions used in Stored Procedures and. Net

1. SQL Server Stored Procedure Transaction Processing
Method 1:
-- Test table
Create table tb (
Id int not null constraint PK_sys_zj_fielddict primary key
, Aa int)

-- Transaction Processing
Begin tran
Insert into tb values (1, 1)
If @ error <> 0 goto lb_rollback
Insert into tb values (1, 1)
If @ error <> 0 goto lb_rollback
Insert into tb values (2, 1)
If @ error <> 0 goto lb_rollback
Insert into tb values (2, 1)
If @ error <> 0 goto lb_rollback
Insert into tb values (3, 1)
If @ error <> 0 goto lb_rollback
Lb_commit:
Commit tran
Goto lb_ OK
Lb_rollback:
Rollback tran

-- Display Results
Lb_ OK:
Select * from tb
Drop table tb

Method 2:
-- Create a test table
Create table tb (
Id int not null constraint PK_sys_zj_fielddict primary key
, Aa int)

-- Set options
SET XACT_ABORT on

-- Transaction Processing
Begin tran
Insert into tb values (1, 1)
Insert into tb values (1, 1)
Insert into tb values (2, 1)
Commit tran

-- Display Results
/* -------- Note
If this is the case, the subsequent statements will not be executed. If you want to execute the following statements, you must add GO to the end of the sentence, which is only supported by the query analysis analyzer, therefore, in the stored procedure, make sure that there are no other statements after commit tran. Otherwise, other statements will not be executed when an error occurs.
-----------*/
Select * from tb
Drop table tb

2. Use transactions in. Net
SqlConnection myConnection = new SqlConnection ("Data Source = localhost; Initial Catalog = Northwind; Integrated Security = SSPI ;");
MyConnection. Open ();

SqlTransaction myTrans = myConnection. BeginTransaction (); // use New to generate a transaction
SqlCommand myCommand = new SqlCommand ();
MyCommand. Transaction = myTrans;

Try
{
MyCommand. CommandText = "Update Address set location = '23 rain street 'where userid = '000000 '";
MyCommand. ExecuteNonQuery ();

MyCommand. CommandText = "Update table2 set dd = '23 rain street 'where userid = '000000 '";
MyCommand. ExecuteNonQuery ();

MyTrans. Commit ();
Console. WriteLine ("Record is udated .");
}
Catch (Exception e)
{
MyTrans. Rollback ();
Console. WriteLine (e. ToString ());
Console. WriteLine ("Sorry, Record can not be updated .");
}
Finally
{
MyConnection. Close ();
}

Note: In SqlServer, each SQL statement is executed as a transaction. Therefore, no matter whether it is stored or used in. net code, it is unnecessary to use transaction processing to execute a single SQL statement.