Three types of transactions with ASP.

Transaction processing is often encountered in data processing problems, often used in the following three kinds of summary summarized as follows:
Method 1: Write directly to SQL
Using BEGIN trans in stored procedures, COMMIT trans, ROLLBACK trans implementation
Begin Trans
declare @orderDetailsError int, @procuntError int
Delete FROM [order Details] where productid=42
Select @orderDetailsError[email protected]@error
Delete from products where productid=42
Select @[email Protected] @error
if (@orderDetailsError =0 and @procuntError =0)
COMMIT TRANS
Else
ROLLBACK TRANS
Advantages:
All transaction logic is contained in a separate call
Has the best performance to run a transaction
Independent of application
Limit:
Transaction context only exists in database call
Database code is related to database system
Method 2: Use the ADO implementation
Using the ADO implementation, the advantage of using this approach is that you can manage transactions in the middle tier, but you can also choose to implement them in the data tier.
The SqlConnection and OleDbConnection objects have a BeginTransaction method that can return sqltransaction
or a OleDbTransaction object. And this object has a Commit and Rollback method to manage the transaction
SqlConnection 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 of
It's almost as fast as the data business.
Database-Independent, proprietary code for different databases is hidden
Disadvantages:
Transactions cannot span multiple database connections
Transaction execution is on the database connection layer, so a database connection needs to be maintained during the transaction
Ado. NET distributed transactions can also span multiple databases, but one of the SQL Server databases can be connected to other databases by using a SQL Server connection server, but not between DB2 and orcal.
These two kinds of transactions are frequently used transaction processing methods.
Method 3 COM + transactions (distributed transactions)
The. Net Framework relies on mts/com+ services to support automated transactions. COM + uses Microsoft distributed Transaction Coordinator (DTC) as the transaction manager and transaction Coordinator to run transactions in a distributed environment.
This enables. Net applications to run in combination with different operations across multiple resources (for example, inserting an order into a SQL Server database, writing a message to a Microsoft Message Queuing (MSMQ) queue, and retrieving data from an Oracle database)
The transaction.
COM + transaction classes must inherit System.EnterpriseServices.ServicedComponent, in fact the web Service is inherited System.EnterpriseServices.ServicedComponent, so Web service also supports
COM + transactions.
To define a COM + transaction-processing class
[Transaction (transactionoption.required)]
public class DataAccess:System.EnterpriseServices.ServicedComponent
{

}
TransactionOption enumeration type supports 5 COM + values (disabled,notsupported,required,requiresnew,supported)
Disabled ignores any transactions in the current context.
NotSupported creates a component in context using a non-controlled transaction.
Required shares a transaction if the transaction exists, and creates a new transaction if necessary.
RequiresNew creates a component with a new transaction, regardless of the state of the current context.
Supported if the transaction exists, the transaction is shared.
In general, components in COM + require required or supported. RequiresNew is useful when a component is used for recording or accounting, because the component should be isolated from the commit or rollback of other transactions in the activity.
Derived classes can overload any property of the base class. If DataAccess chooses required, derived classes can still overload and specify RequiresNew or other values.

COM + transactions are handled manually and automatically, and automatic processing is done by adding [AutoComplete] to the method that needs to be handled automatically, depending on the method's normal or thrown exception, to commit or rollback.
Manual processing is called the Enablecommit,setcomplete,setabort method 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 transaction-aware resources, such as SQL Server databases, only DTC or COM + transactions can be used, and there is no other choice. DTC coordinates all resource managers involved in a distributed transaction,
Also manages transaction-related operations.
The disadvantage of this approach is that performance is degraded due to the presence of DTC and COM interoperability overhead.
The class for COM + transactions must be strongly named.

Article references from:

Three types of transactions with ASP.