System.Transactions Programming Models
The .NET Framework System.Transactions namespace offers two programming models to the .NET application programmer to create a transaction. The .NET Data Provider supports both models:
• The explicit programming model allows the programmer to create, enlist into, and control the transaction manually.
• The implicit programming model allows .NET to automatically perform these operations. The implicit programming model is recommended as a best practice since there are fewer chances for programming errors and it frees the programmer from the details of managing enlistment in the System.Transactions.Transaction.
Implicit Automatic Enlistment using TransactionScope
The System.Transactions.TransactionScope class allows a .NET application to establish a transaction context. When a TransactionScope is instantiated, a current transaction context is established by .NET. Resource managers such as Ingres by default enlist in this ambient transaction. If an IngresConnection.Open() is issued by the application within the scope of this current transaction, and if the ConnectionString contains Enlist=yes (the default), then the IngresConnection automatically enlists the IngresConnection into the transaction.
Within the scope of the TransactionScope, the application calls the TransactionScope.Complete() method to indicate that the database unit of work should be committed. If the method is not called, the work is rolled back. When the TransactionScope is Disposed, updates to the Ingres database are committed or rolled back as directed by the .NET Transaction Manager. The Transaction Manager examines whether TransactionScope.Complete() method was called and issues the appropriate commit or rollback statements to Ingres.
Note: When an Ingres connection is enlisted in the .NET transaction, the commit or rollback to Ingres to commit/rollback the database changes occur when the TransactionScope is disposed, not when the IngresConnection is closed or disposed. Even though a IngresConection.Close() method has been called and the IngresConection instance has been disposed, the Ingres session remains active until the .NET TransactionScope is disposed and the .NET Transaction Manager issues the commit/rollback to the Ingres session.
The .NET application programmer can prevent automatic enlistment of the IngresConnection in the transaction context if the IngresConnection.ConnectionString includes the Enlist=No keyword/value pair.
The advantage to coding a "using" statement and TransactionScope is that if any of the database operations throws an exception, flow of control jumps out of the "using (TransactionScope)" block and a rollback of the transaction automatically occurs. The ease of programming and reliability of rollback or commit within the TransactionScope makes this model of enlistment a better programming practice.
TransactionScope Example
This example shows the enlistment of an Ingres database session in a distributed transaction managed by a using TransactionScope block. When the IngresConnection.Open( ) method is issued, the Ingres connection will enlist in the distributed transaction represented by the Transaction object within the TransactionScope. After the update, the TransactionScope is marked Complete(), and the updates to the Ingres database will be committed when the TransactionScope object is disposed.
static void TestEnlistTransactionImplicitSample(
string connstring)
{
using (TransactionScope scope = new TransactionScope())
{
using (IngresConnection conn1 =
new IngresConnection(connstring))
{
conn1.Open();
IngresCommand cmd = conn1.CreateCommand();
cmd.CommandText =
"update authors set au_id = '409-56-7008' " +
"where au_id = '409-56-7008'";
cmd.ExecuteNonQuery();
scope.Complete();
} // end using (IngresConnection)
} // end using (TransactionScope)
}
Explicit Enlistment by EnlistTransaction() Method
A .NET application can disable automatic transaction enlistment and manually enlist the Ingres connection in the transaction if desired. The application programmer can prevent automatic enlistment of the IngresConnection in the current transaction context if the IngresConnection.ConnectionString includes the Enlist=no or Enlist=false keyword/value pair. Later, the application can manually enlist the Ingres connection in the transaction by calling the IngresConnection.EnlistTransaction method. The .NET Transaction Manager will issue a commit to the Ingres transaction if the .NET System.Transactions.Transaction is marked complete before the Transaction object is disposed, else the Ingres transaction will be rollbacked.
Ingres Enlistment in a .NET Transaction
Whether the enlistment is automatic or manual, when an Ingres connection is enlisted in a .NET transaction, the .NET Data Provider participates as a resource manager within the transaction. The data provider works with the Microsoft Distributed Transaction Coordinator (MSDTC), Ingres DBMS Server, and the Ingres XA Distributed Transaction Processing (DTP) subsystem to allow the Ingres connection to participate in the distributed transaction on Windows with other Ingres or non-Ingres participants. The participants are polled in a vote to commit in a two-phase commit protocol (2PC). If the vote to commit is unanimous, all participants are directed to commit; otherwise, they are directed to roll back the database updates as an atomic unit of work.