C # Transaction Processing

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
{< br> 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. "");
}< br> catch (exception e)
{< br> try
{< br> mytrans. rollback ();
}< br> catch (oledbexception ex)
{< br> If (mytrans. connection! = NULL)
{< br> console. writeline ("" an exception of type "" + ex. getType () +
"" was encountered while attempting to roll back the transaction. "");
}< BR >}

console. writeline ("" an exception of type "" + E. getType () +
"was encountered while inserting the data. "");
console. writeline ("" neither record was written to database. "");
}< br> finally
{< br> myconnection. close ();
}< BR >}

oledbtransaction. Commit method
submits database transactions.
Public Virtual void commit ();
oledbtransaction. rollback method
Roll back a transaction from the suspended 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?
specify the transaction lock behavior of the connection. When a transaction is executed, the. NET Framework data provides the Program to use 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, an isolationlevel member
keep the shared lock when reading data to avoid dirty reads. However, you can change the data before the transaction ends, as a result, you cannot read or phantom data repeatedly.
oledbconnection. createcommand method
creates and returns an oledbcommand object associated with oledbconnection.
Public oledbcommand createcommand ();
oledbcommand. Connection attribute
obtains or sets the 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.

In addition, the following is a transaction implemented in LINQ:

If (CTX. connection! = NULL) CTX. Connection. open (); Dbtransaction TRAN = CTX. Connection. begintransaction (); CTX. Transaction = Tran; Try { Createtest (New tbtest {id = 3, name = ""}); Createtest (New tbtest {id = 2, name = ""}); // because Id = 2 already exists, the program jumps to catch {rollback} due to an error }, therefore, id = 3 is not added! Tran. Commit (); Setbind (); } Catch { Tran. rollback (); }

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)
{< br> 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
{< br> 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. "");
}< br> catch (exception e)
{< br> try
{< br> mytrans. rollback ();
}< br> catch (oledbexception ex)
{< br> If (mytrans. connection! = NULL)
{< br> console. writeline ("" an exception of type "" + ex. getType () +
"" was encountered while attempting to roll back the transaction. "");
}< BR >}

console. writeline ("" an exception of type "" + E. getType () +
"was encountered while inserting the data. "");
console. writeline ("" neither record was written to database. "");
}< br> finally
{< br> myconnection. close ();
}< BR >}

oledbtransaction. Commit method
submits database transactions.
Public Virtual void commit ();
oledbtransaction. rollback method
Roll back a transaction from the suspended 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?
specify 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, an isolationlevel member
keep the shared lock when reading data to avoid dirty reads. However, you can change the data before the transaction ends, as a result, you cannot read or phantom data repeatedly.
oledbconnection. createcommand method
creates and returns an oledbcommand object associated with oledbconnection.
Public oledbcommand createcommand ();
oledbcommand. Connection attribute
obtains or sets the 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.

In addition, the following is a transaction implemented in LINQ:

If (CTX. connection! = NULL) CTX. Connection. open (); Dbtransaction TRAN = CTX. Connection. begintransaction (); CTX. Transaction = Tran; Try { Createtest (New tbtest {id = 3, name = ""}); Createtest (New tbtest {id = 2, name = ""}); // because Id = 2 already exists, the program jumps to catch {rollback} due to an error }, therefore, id = 3 is not added! Tran. Commit (); Setbind (); } Catch { Tran. rollback (); }