Three types of transaction processing
Transaction processing is a common problem during data processing. The following three methods are commonly used:
Method 1: directly write data to SQL
Use begin trans, commit trans, and rollback trans in the Stored Procedure
Begin Trans
Declare @ orderdetailserror int, @ procunterror int
Delete from [Order Details] Where productid = 42
Select @ orderdetailserror@ Error
Delete from products where productid = 42
Select @ procunterror = @ Error
If (@ orderdetailserror = 0 and @ procunterror = 0)
Commit Trans
Else
Rollback Trans
Advantages:
All transaction logic is included in a separate call
Has the best performance to run a transaction
Independent from applications
Restrictions:
The transaction context only exists in database calls.
Database code is related to the Database System
Method 2: using ADO. net
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 object. In addition, this object has the commit and rollback methods to manage transactions.
Sqlconnection = new sqlconnection ("workstation id = weixiaoping; packet size = 4096; user id = sa; initial catalog = northwind; persist Security info = false ");
Sqlconnection. open ();
Sqltransaction mytrans = sqlconnection. begintransaction ();
Sqlcommand sqlinsertcommand = new sqlcommand ();
Sqlinsertcommand. Connection = sqlconnection
Sqlinsertcommand. Transaction = mytrans;
Try {
Sqlinsertcommand. commandtext = "insert into tbtree (context, parentid) values ('beijing', 1 )";
Sqlinsertcommand. executenonquery ();
Sqlinsertcommand. commandtext = "insert into tbtree (context, parentid) values ('shanghai', 1 )";
Sqlinsertcommand. executenonquery ();
Mytrans. Commit ();
} Catch (exception ex)
{
Mytrans. rollback ();
}
Finally
{
Sqlconnection. Close ();
}
Advantages:
Simplicity
Almost as fast as data transactions
The proprietary code of different databases is hidden.
Disadvantages:
Transactions cannot be connected across multiple databases
Transactions are executed on the database connection layer. Therefore, you need to maintain a database connection during the transaction process.
ADO. net distributed transactions can also span multiple databases. However, if one of the SQL Server databases is used, the SQL Server is used to connect to the server to connect to another database, but it is not allowed between DB2 and orcal.
The preceding two transactions are commonly used transaction processing methods.
Method 3 COM + transaction (Distributed Transaction)
. NET Framework relies on MTS/COM + to support Automatic transactions. COM + uses Microsoft Distributed Transaction Coordinator (DTC) as the Transaction Manager and Transaction Coordinator to run transactions in a distributed environment.
In this way.. NET application programs run across multiple resources combined with different operations (for example, insert orders into the SQL Server database, write messages to the Microsoft Message Queue (MSMQ) queue, and retrieve data from the Oracle database)
.
The COM + transaction processing class must inherit system. enterpriseservices. servicedcomponent. In fact, Web Service inherits system. enterpriseservices. servicedcomponent, so Web Service also supports
COM + transaction.
Define a class for COM + Transaction Processing
[Transaction (transactionoption. Required)]
Public class dataaccess: system. enterpriseservices. servicedcomponent
{ }
The transactionoption Enumeration type supports five COM + values (disabled, notsupported, required, requiresnew, supported)
Disabled ignores any transactions in the current context.
Notsupported uses uncontrolled transactions to create components in the context.
Required shares the transaction if the transaction exists, and creates a new transaction if necessary.
Requiresnew creates a component using a new transaction, regardless of the status of the current context.
Supported shares the transaction if the transaction exists.
In general, components in COM + need required or supported. Requiresnew is useful when a component is used for recording or checking the account, because the component should be isolated from the commit or rollback of other transactions in the activity.
A derived class can overload any attribute of a base class. For example, if you select required for dataaccess, the derived class can still be reloaded and specify requiresnew or another value. COM + transactions are manually processed and automatically processed. Automatic processing is to add [AutoComplete] before the method to be automatically processed. It is determined to submit or roll back based on the normal method or thrown exception of the method.
Manual processing is to call the enablecommit, setcomplete, and setabort methods in the contextutil class.
Public String testtransaction ()
{
Try
{
Contextutil. enablecommit ();
Insertarecord1 ();
Insertarecord2 ();
Contextutil. setcomplete ();
Return "succeed! ";
}
Catch (exception ex)
{
Contextutil. setabort ();
Return "failed! ";
} }
Public void insertarecord1 ()
{
String strconn = "workstation id = weixiaoping; packet size = 4096; user id = sa; initial catalog = northwind; persist Security info = false ";
Sqlconnection conn = new sqlconnection (strconn );
Conn. open ();
Sqlcommand command = new sqlcommand ("insert into tbtree (context, parentid) values ('beijing', 1)", Conn );
Command. executenonquery ();
Conn. Close ();
}
Public void insertarecord2 ()
{
String strconn = "workstation id = weixiaoping; packet size = 4096; user id = sa; initial catalog = northwind; persist Security info = false ";
Sqlconnection conn = new sqlconnection (strconn );
Conn. open ();
Sqlcommand command = new sqlcommand ("insert into tbtree (context, parentid) values ('shanghai', 1)", Conn );
Command. executenonquery ();
Conn. Close ();
}
In systems that require transactions to run across MSMQ and other resources that can identify transactions (such as SQL Server databases), you can only use DTC or COM + transactions, but there is no other choice. DTC coordinates all resource managers involved in distributed transactions,
It also manages transaction-related operations.
The disadvantage of this approach is that the performance is reduced due to the overhead of DTC and COM interoperability.
The class for processing COM + transactions must be named strongly. |
