Transactions used in Stored Procedures and. Net

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.