Summary of transaction processing methods in ADO. NET

This article summarizes some common program code for transaction processing in asp.net. The transaction processing is generally in the database, and the following instances are all basic database transaction processing, create a transaction processing object on the database connection, and then call the transaction processing object to submit the transaction or roll back the transaction. For more information, see.

A transaction is a mechanism that ensures that multiple SQL statements are treated as a single work order.
To process. Transactions have the following roles:
* Consistency: the query and update operations at the same time do not conflict with each other.
The user will not see the changed but not submitted data.
* Recoverability: Once a system failure occurs, the database automatically recovers completely.
.

Ado. Net transaction processing.
In ADO. NET, you can use the Connection and Transaction objects to control transactions. To execute a transaction, perform the following operations:
• Call the BeginTransaction method of the Connection object to mark the start of the transaction.
• Assign the Transaction object to the Transaction attribute of the Command to be executed.
• Execute the required commands.
• Call the Commit method of the Transaction object to complete the Transaction or call the Rollback method to cancel the Transaction.
Of course, the ADO. NET transaction processing has advantages and disadvantages. It depends on the specific situation.

The Code is as follows:

Copy code

Using System; Using System. Collections. Generic; Using System. Linq; Using System. Web; Using System. Web. UI; Using System. Web. UI. WebControls; Using System. Data; Using System. Data. SqlClient; Using System. Transactions; Namespace dotNET { Public partial class transaction processing: System. Web. UI. Page { String conn = "Data Source =.; Initial Catalog = test; Integrated Security = True "; SqlConnection con; Protected void Page_Load (object sender, EventArgs e) { } Protected void button#click (object sender, EventArgs e) { Con = new SqlConnection (conn ); Con. Open (); // Start a transaction. SqlTransaction myTran = con. BeginTransaction (); SqlCommand myCom = new SqlCommand (); MyCom. Connection = con; MyCom. Transaction = myTran; Try { MyCom. CommandText = "insert into fenye (value) values ('zz ')"; // This line is not inserted into the database MyCom. ExecuteNonQuery (); MyCom. CommandText = "insert into fenye2 (value) values ('zz2 ')"; MyCom. ExecuteNonQuery (); MyTran. Commit (); Response. Write ("executed successfully! 1 "); } Catch (Exception Ex) { MyTran. Rollback (); // Create an error message and return an exception Response. Write (Ex. ToString ()); Response. Write ("failed to Write data to the database "); } Finally { Con. Close (); } } Protected void Button2_Click (object sender, EventArgs e) { Using (TransactionScope tsCope = new TransactionScope ()) { Using (con = new SqlConnection (conn )) { SqlCommand cmd = new SqlCommand ("insert into fenye (value) values ('zz ')", con ); Con. Open (); Cmd. ExecuteNonQuery (); } Using (con = new SqlConnection (conn )) { SqlCommand cmd = new SqlCommand ("insert into fenye2 (value) values ('zz2')", con ); Con. Open (); Cmd. ExecuteNonQuery (); } Response. Write ("execution successful! 2 "); TsCope. Complete (); } } } }

Code 2

The Code is as follows:

Copy code

Private void button#click (object sender, System. EventArgs e) { SqlConnection conn = new SqlConnection ("Data Source = 192.168.2.200; uid = sa; password =; database = HaierHR "); Conn. Open (); // Enable transaction SqlTransaction tran = conn. BeginTransaction (); SqlCommand cmd = new SqlCommand (); Cmd. Connection = conn; Cmd. Transaction = tran; Try { Cmd. CommandText = "UPDATE HRRollMain Set TotalMember = TotalMember-100 WHERE RollID = '20140901 '"; Cmd. ExecuteNonQuery (); Cmd. CommandText = "UPDATE HRRollSum Set TotalSumMember = TotalSumMember + 100 WHERE RollSumID = '000000 '"; Cmd. ExecuteNonQuery (); Tran. Commit (); MessageBox. Show ("transaction committed successfully! "); } Catch (Exception ex) { Tran. Rollback (); MessageBox. Show ("Error! "+ Ex. Message ); } }

Note:

• Advantages:
-Simplicity
-Almost as fast as data transactions
-The proprietary code of different databases is hidden independently of databases.
• Disadvantages:
-Transactions cannot span multiple database connections.
-The transaction runs on the database connection layer. Therefore, you must maintain a database connection during the transaction process.