Example of transaction of ADO. Net in C # Environment

To save convenience, you can specify multiple;-separated statements in the commandtext attribute of a command object. In this case, if there is no transaction, all the statements will be executed. If there is a statement error, data inconsistency will occur.

Of course, we can also write stored procedures. If SQL Server's database system has built-in stored procedure statements without transactions, some of the statements in multiple statements become invalid, which leads to data inconsistency: you can try/catch/begintransaction In the stored procedure.

The connection object begintransaction starts the transaction, and then assigns the transaction value to the transaction attribute of the command object, that is, the transaction is mounted. Even if there is no commit or rollback, if an error occurs during execution, the transaction is automatically rolled back or committed successfully.

Begintransaction can specify the isolation level. Readxxx does not lock the database, although the externalProgramData can still be read. However, if there is an update statement in the transaction, the database is locked and external programs cannot continue to read data.

Use transactions in the stored procedure as much as possible to avoid using ADO transactions, because ADO transactions may cause the database to be locked for a long time; transactions in the stored procedures of the database are often not suspended for a long time.

The following is a simple example for your reference:

///   <Summary>
/// Ado transaction processing: a single command object executes multiple statements:
/// All subsequent statements that cause exceptions will be executed. If there is no transaction, change it to 15.
///   </Summary>
Private   Static   Void Transbyado ()
{
String Connect =   " Data Source =. \ sqlexpress; initial catalog = northwind; Integrated Security = true; " ;
// Old value: unitprice = 18
String Update =   " Update northwind. DBO. Products set unitprice = 10 where productid = 1; " ;
Update + =   " Update products set unitprice = unitprice-18 where productid = 1; " ; // Failed due to check Constraints
Update + =   " Update products set unitprice = 15 where productid = 1 " ;

Sqltransaction tranproducts= Null;

Using(Sqlconnection cnnorthwind= NewSqlconnection (CONNECT ))
{
Try
{

Cnnorthwind. open ();
Tranproducts = Cnnorthwind. begintransaction (); // -- 1

Sqlcommand cmdupdate =   New Sqlcommand (Update, cnnorthwind );
Cmdupdate. Transaction = Tranproducts; // -2
Cmdupdate. executenonquery ();
// If no commit () or rollback () exists, the transaction still takes effect and the result is 18
Tranproducts. Commit (); // -- 3
}
Catch (Sqlexception ex)
{
Tranproducts. rollback (); // -- 4
// Throw ex;
Console. writeline (Ex );
}
Finally
{
Cnnorthwind. Close ();
}
}

}

///   <Summary>
/// If some statements fail to be executed in the stored procedure, data inconsistency may occur without transaction.
// Create procedure transtest
// As
// Begin
// Update products set unitprice = 10 where productid = 1
// Update products set unitprice = unitprice-18 where productid = 1
// Update products set unitprice = 15 where productid = 1
// End
// Go
///   </Summary>
Private   Static   Void Transbystoreprocedure ()
{
String Connect =   " Data Source =. \ sqlexpress; initial catalog = northwind; Integrated Security = true; " ;
Using (Sqlconnection cnnorthwind =   New Sqlconnection (CONNECT ))
{

Sqltransaction tranproducts= Null;

Try
{
Cnnorthwind. open ();
Tranproducts = Cnnorthwind. begintransaction (); // ----- 1

Sqlcommand named Product =   New Sqlcommand ( " Transtest " , Cnnorthwind );
Using product. commandtype = Commandtype. storedprocedure;
Invalid product. Transaction = Tranproducts; // ---- 2
Using product. executenonquery ();
// If no commit () or rollback () exists, the transaction still takes effect and the result is 18
Tranproducts. Commit (); // ---- 3
}
Catch (Sqlexception ex)
{
Tranproducts. rollback (); // ---- 4
Console. writeline (ex. tostring ());
// Throw ex;
}
Finally
{
Cnnorthwind. Dispose ();
}
}
}

 

///   <Summary>
/// Begintransaction (isolationlevel) defines the isolation level of transactions
/// Readcommitted
/// Readuncommitted: Dirty read, no share lock published, no exclusive lock
///   </Summary>
Private   Static   Void Transdebug ()
{
String Connect =   " Data Source =. \ sqlexpress; initial catalog = northwind; Integrated Security = true; " ;
Using (Sqlconnection cnnorthwind =   New Sqlconnection (CONNECT ))
{
Sqlcommand initialize Products =   Null ;
Sqltransaction trandebug =   Null ;
Try
{
Cnnorthwind. open ();
Trandebug = Cnnorthwind. begintransaction (isolationlevel. readuncommitted ); // Readable and uncommitted data
Cmdproducts =   New Sqlcommand ( " Select * from products where productid = 1 " , Cnnorthwind );
Invalid products. Transaction = Trandebug;
Using Products. executenonquery ();

// Click the next breakpoint in the following line to check whether the data record can be modified in msms and try to modify it: the data can be modified.
Using Products. commandtext =   " Update products set unitprice = UnitPrice-18 where productid = 1 " ;
Using Products. executenonquery ();
// The data is locked due to the above update and cannot be viewed or modified in the msms environment.
Using Products. commandtext =   " Select * from products where productid = 1 " ;
Using Products. executenonquery ();
// The data cannot be read because the previous update has locked the record.
Throw   New Exception ( " Rollback () " );
Trandebug. Commit ();
}
Catch (Sqlexception ex)
{
Trandebug. rollback ();
Console. Write (ex. Message );
// Throw;
}
Finally
{
Cnnorthwind. Dispose ();
}
}
}