C # transaction application instance)

In C # programming, we usually perform database operations in the try statement block.Set the start and end of the transaction before and after database operations in try, and use the RollBack action in catch exception handling.This ensures that the database will be rolled back to the initial state once the database fails.

[Example 1]: Use the update command to set the tname value of the data record for tid> '123456' in the my_test table of the test database to 'aillo '. Then execute the SQL statement "create database Hello. BecauseA statement such as create database cannot be used in a transaction.Therefore, when the program runs here, an exception is thrown, reflecting the role of rollback. Run the command cmd. commandText = "create database Hello"; in this line, comment out the run, uncomment, change Aillo to another name, and then run the command to compare the running results.

Using System;
Using System. Collections. Generic;
Using System. Text;
Using System. Data. SqlClient;

Namespace test1
{
Public class Transaction
{
Public SqlConnection getCon ()
{
String constr = "server = localhost; uid = sa; pwd = 123456; database = test ";
SqlConnection con = new SqlConnection (constr );
Return con;
}
Public void UpdateWithTran ()
{
SqlConnection mycon = getCon ();
Mycon. Open ();
SqlCommand cmd = new SqlCommand ();
SqlTransaction mytran = mycon. BeginTransaction ();

// Bind the database connection and transaction object
Cmd. Connection = mycon;
Cmd. Transaction = mytran;
Try
{
Cmd. CommandText = "use test ";
Cmd. CommandText = "update my_test set tname = 'aillo' where tid> '123 '";
// Cmd. CommandText = "create database Hello ";
Cmd. ExecuteNonQuery ();
Mytran. Commit ();
}
Catch (Exception ex)
{
Mytran. Rollback ();
Console. Write ("transaction operation error, rolled back. System Information: "+ ex. Message );
}
Finally
{
Mycon. Close ();
}
}
}
Class Program
{
Static void Main (string [] args)
{
Transaction tran = new Transaction ();
Tran. UpdateWithTran ();
}
}
}

 

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 canUse 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.

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? GenerallyTwo Methods: write directly to SQL (Stored Procedure); 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: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 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.