Transaction Sample

This sample extends the framework code to demonstrate ADO/WFC’s support for transactions. Transactional-aware applications are an integral part of modern computing. ADO fully supports both local transactions, as well as distributed, two-phase transactions coordinated by the Microsoft® Distributed Transaction Coordinator (DTC). The following sample utilizes local transaction management capabilities surfaced through the Connection object. You can get the current transaction from the Connection by using the getActiveTransaction method.

public boolean run()
{
   Connection c = new Connection();
   c.setIsolationLevel (AdoEnums.adXactReadCommitted);
   c.setCursorLocation (AdoEnums.adUseClientBatch);
   c.open ("dsn=aDSN", "aName", "aPWD");

   Transaction t = c.getActiveTransaction();

   if (t != null)
   {
      Recordset rs = c.execute ("select * from authors");
      t.beginTrans();

      int recordCount = rs.getRecordCount();
      c.executeUpdate ("delete from authors");
      rs.requery();
      if (rs.getRecordCount() != 0)
      {
         rs.close();
         // Roll back (undo) the changes. 
         t.rollbackTrans(false);
         c.close();
         return false;
      }

      // Make the changes permanent in the data source 
      // and then refresh the results to verify the operation. 
      t.commitTrans(false);
      rs.requery();
      rs.close();
   }
   
   c.close();
   return true;
}

The IsolationLevel property, set through c.setIsolationLevel (AdoEnums.adXactReadCommitted), is important when dealing with transactionally-scoped data access and application logic. Isolation refers to the level of autonomy that a transaction has from data/state changes made in other transactions. In the case of ReadCommitted isolation (specified by the constant parameter passed to the Connection object), any access to data sources will only see “committed” data, meaning the changes that are durable and have been successfully made. This guards against “phantom writes” that may appear if lower, more relaxed isolation levels are used.

The ADO/WFC Transaction object is dispensed from the Connection object. Once a non-null reference is obtained, the standard transaction operations may be used: beginning, committing, or aborting changes. In this sample, a new transaction is created after the initial select statement is executed from the Connection, resulting in a non-transacted Recordset. Subsequent to the t.beginTrans() call, the Recordset is replenished, after the Connection object is used to execute a SQL DML statement. This statement deletes the entire authors table. The Recordset, now empty, is within a transactional scope. The following lines then verify that the Recordset is indeed within the “scope” of the running transaction:

if (rs.getRecordCount() != 0)
   {
      rs.close();
      // Roll back (undo) the changes. 
      t.rollbackTrans(false);
      c.close();
      return false;
   }

The RecordCount of the Recordset should equal zero, since the Connection deleted all of the rows. If it fails to equal zero, the entire delete operation is aborted, rolling back the state of the data source to the state it had before the transaction was started. If, on the other hand, the Recordset is empty, then the transaction has worked appropriately and the changes can be committed, as follows:

t.commitTrans(false);
rs.requery();
rs.close();

The Recordset is requeried, and should be empty, since the deletes were made permanent. The Recordset is then closed and the sample is completed. Transactions are an excellent way to write applications that ensure strange results do not creep into your control logic. ADO/WFC’s full support for both the local and distributed varieties make transactions a flexible and useful framework for building distributed business applications.