. Net Transaction Processing Method

Transaction processing is a common problem during data processing. The following three methods are commonly used:
Method 1: directly write data to SQL
Use begin trans, commit trans, and rollback trans in the Stored Procedure

 

Code
Begin Trans
Declare   @ Orderdetailserror   Int , @ Procunterror   Int
Delete   From   [ Order Details ]   Where Productid = 42
Select   @ Orderdetailserror   = @ Error
Delete   From Products Where Productid = 42
Select   @ Procunterror = @ Error
If ( @ Orderdetailserror   = 0   And   @ Procunterror = 0 )
Commit Trans
Else
Rollback Trans

Advantages:
All transaction logic is included in a separate call
Has the best performance to run a transaction
Independent from applicationsProgram
Restrictions:
The transaction context only exists in database calls.
DatabaseCodeRelated to the Database System
Method 2: using ADO. net
The advantage of using ADO. NET is that you can manage transactions in the middle layer. Of course, you can also choose to implement it at the data layer.
The sqlconnection and oledbconnection objects have a begintransaction method, which can return sqltransaction
Or oledbtransaction object. In addition, this object has the commit and rollback methods to manage transactions.

 

Code
Sqlconnection =   New Sqlconnection ( " Workstation id = weixiaoping; packet size = 4096; user id = sa; initial catalog = northwind; persist Security info = false " );
Sqlconnection. open ();
Sqltransaction mytrans = Sqlconnection. begintransaction ();
Sqlcommand sqlinsertcommand =   New Sqlcommand ();
Sqlinsertcommand. Connection = Sqlconnection
Sqlinsertcommand. Transaction = Mytrans;
Try {
Sqlinsertcommand. commandtext = " Insert into tbtree (context, parentid) values ('beijing', 1) " ;
Sqlinsertcommand. executenonquery ();
Sqlinsertcommand. commandtext = " Insert into tbtree (context, parentid) values ('shanghai', 1) " ;
Sqlinsertcommand. executenonquery ();
Mytrans. Commit ();
} Catch (Exception ex)
{
Mytrans. rollback ();
}
Finally
{
Sqlconnection. Close ();
}

Advantages:
Simplicity
Almost as fast as data transactions
The proprietary code of different databases is hidden.
Disadvantages:
Transactions cannot be connected across multiple databases
Transactions are executed on the database connection layer. Therefore, you need to maintain a database connection during the transaction process.
ADO. net distributed transactions can also span multiple databases. However, if one of the SQL Server databases is used, the SQL Server is used to connect to the server to connect to another database, but it is not allowed between DB2 and orcal.
The preceding two transactions are commonly used transaction processing methods.