C # Use, examples, and precautions of transactions

I. Introduction to transactions
. NET Framework developer Guide
A transaction is an operation that combines logical units of work. Although errors may occur in the system, the transaction controls and maintains the consistency and integrity of each operation in the transaction.
For example, in a bank application that transfers funds from one account to another, one account credited a certain amount to one database table, at the same time, another account debited the same amount to another database table. Because the computer may fail due to power outages or network interruptions, the rows in one table may be updated, but the rows in the other table may not be updated. If the database supports transactions, you can combine database operations into a transaction to prevent database inconsistencies due to these events. If a point in the transaction fails, all updates can be rolled back to the status before the start of the transaction. If no fault occurs, commit the transaction to complete the update.
In ADO. NET, you can use the Connection and Transaction objects to control transactions. You can use Connection. BeginTransaction to start local transactions. Once a Transaction starts, you can use the Transaction attribute of the Command object to register the Command in the Transaction. Then, you can use the Transaction object to submit or roll back the modifications made in the data source based on the Transaction component's success or failure.
You can also use Connection. EnlistDistributedTransaction to register in an existing distributed transaction. Registering in existing distributed transactions ensures that code modifications to the data source are also committed or rolled back when the entire distributed transaction is committed or rolled back.
The following example creates an OleDbConnection and an OleDbTransaction. It also demonstrates how to use the BeginTransaction, Commit, and Rollback methods.

 

Code

Public void RunOleDbTransaction (string myConnString)
{
OleDbConnection myConnection = new OleDbConnection (myConnString );
MyConnection. Open ();
OleDbCommand myCommand = myConnection. CreateCommand ();
OleDbTransaction myTrans;

// Start a local transaction
MyTrans = myConnection. BeginTransaction (IsolationLevel. ReadCommitted );
// Assign transaction object for a pending local transaction
MyCommand. Connection = myConnection;
MyCommand. Transaction = myTrans;
Try
{
MyCommand. CommandText = \ "Insert into Region (RegionID, RegionDescription) VALUES (100, \ 'description \')\";
MyCommand. ExecuteNonQuery ();
MyCommand. CommandText = \ "Insert into Region (RegionID, RegionDescription) VALUES (101, \ 'description \')\";
MyCommand. ExecuteNonQuery ();
MyTrans. Commit ();
Console. WriteLine (\ "Both records are written to database .\");
}
Catch (Exception e)
{
Try
{
MyTrans. Rollback ();
}
Catch (OleDbException ex)
{
If (myTrans. Connection! = Null)
{
Console. WriteLine (\ "An exception of type \" + ex. GetType () +
\ "Was encountered while attempting to roll back the transaction .\");
}
}

Console. WriteLine (\ "An exception of type \" + e. GetType () +
\ "Was encountered while inserting the data .\");
Console. WriteLine (\ "Neither record was written to database .\");
}
Finally
{
MyConnection. Close ();
}
}

 

OleDbTransaction. Commit Method
Commit database transactions.
Public virtual void Commit ();
OleDbTransaction. Rollback method
Roll back a transaction from the pending state.
Public virtual void Rollback ();
OleDbConnection. BeginTransaction Method
Start database transactions.
Public OleDbTransaction BeginTransaction ();
Start database transactions with the current IsolationLevel value.
Public OleDbTransaction BeginTransaction (IsolationLevel );
IsolationLevel enumeration?
Specifies the transaction lock behavior of the connection. When a transaction is executed, the. NET Framework data provider uses the IsolationLevel value. IsolationLevel remains valid before explicit changes, but can be changed at any time. The new value is used for execution rather than analysis. If it is changed during the transaction, the server's expected behavior is to apply a new lock level to all other statements.
ReadCommitted, IsolationLevel Member
Keep the shared lock when reading data to avoid dirty reading, but you can change the data before the transaction ends, resulting in non-repeated reading or phantom data.
OleDbConnection. CreateCommand Method
Create and return an OleDbCommand object associated with OleDbConnection.
Public OleDbCommand CreateCommand ();
OleDbCommand. Connection attributes
Obtain or set OleDbConnection used by this instance of OleDbCommand.
Public OleDbConnection Connection {get; set ;}

How to Implement transactions in. NET (1)
 
How to Implement the transaction mechanism in. NET? You can use either of the following methods: write directly to SQL, or use ADO. NET. The following is an introduction in sequence:
Method 1: directly write data to SQL
Use begin trans, commit trans, and rollback trans to implement:
For example
BEGIN TRANS
DECLARE @ orderDetailsError int, @ productError int
Delete from \ "Order Details \" WHERE ProductID = 42
SELECT @ orderDetailsError = @ ERROR
Delete from Products WHERE ProductID = 42
SELECT @ productError = @ ERROR
IF @ orderDetailsError = 0 AND @ productError = 0
COMMIT TRANS
ELSE
ROLLBACK TRANS
This method is relatively simple. For more information, see SQL server help.

Method 2: use ADO. NET. The advantage of this method is that you can manage transactions on the middle layer, and you can also choose to implement it on the data layer.
The SqlConnection and OleDbConnection objects have a BeginTransaction method, which can return SqlTransaction or OleDbTransaction objects. In addition, this object has the Commit and Rollback methods to manage transactions. The specific example is as follows:

CnNorthwind. Open ()
Dim trans As SqlTransaction = cnNorthwind. BeginTransaction ()
Dim cmDel As New SqlCommand ()
CmDel. Connection = cnNorthwind
CmDel. Transaction = trans

Try
CmDel. CommandText = _
\ "DELETE [Order Details] WHERE ProductID = 42 \"
CmDel. ExecuteNonQuery ()
CmDel. CommandText = \ "DELETE Products WHERE ProductID = 42 \"
CmDel. ExecuteNonQuery ()
Trans. Commit ()

Catch Xcp As Exception
Trans. Rollback ()
Finally
CnNorthwind. Close ()
End Try

OK. The above example can achieve the same effect as method 1.

Concurrency problems:

If it is not locked and multiple users access a database at the same time, problems may occur when their transactions use the same data at the same time. Concurrency problems include loss or overwriting of updates, unconfirmed correlations (dirty reads), inconsistent analyses (non-repeated reads), and Phantom reads. But how can we avoid dirty reading during data reading?
 
Ii. Transaction instance

Using (SqlTransaction trans = conn. BeginTransaction ())
{
Try
{
// Insert information cyclically
For (int count = 0; count <applyInfo. Length; count ++)
{
// Declare parameters and assign values
SqlParameter [] parms =
{Database. MakeInParam (\ "@ Stu_ID \", System. Data. SqlDbType. VarChar, 11, applyInfocount]. StuID ),
Database. makeInParam \ "@ Bank_Name \", System. data. sqlDbType. varChar, 50, applyInfo [count]. bankName), Database. makeInParam \ "@ Apply_Loan_Money \", System. data. sqlDbType. money, 8, applyInfo [count]. applyLoanMoney), Database. makeInParam (\ "@ Apply_Loan_Year \", System. data. sqlDbType. varChar, 20, applyInfo [count]. applyLoanYear), Database. makeInParam \ "@ Apply_Year \", System. data. sqlDbType. char, 6, applyInfo [count]. applyYear), Database. makeInParam (\ "@ Apply_Length \", System. data. sqlDbType. int, 4, applyInfo [count]. applyLength), Database. makeInParam (\ "@ Apply_Pass \", System. data. sqlDbType. char, 1, applyInfo [count]. applyPass ),
Database. MakeInParam (\ "@ Apply_Remark \", System. Data. SqlDbType. VarChar, 100, applyInfo [count]. ApplyRemark)
};
// Execute the add operation
SqlHelper. ExecuteNonQuery (trans, CommandType. StoredProcedure, \ "ApplyInfo_Create \", parms );
}
// If no error occurs, the transaction is committed.
Trans. Commit ();
Return true;
}
Catch (Exception ex)
{
// Rollback if an error occurs
Trans. Rollback ();
Throw ex;
}
}

 

Iv. Notes
The transaction must be defined after the connection is opened and before the transaction is committed
When using a transaction, you must add the transaction to sqlCommand.