Using Local Transactions

In my last post I talked a bit about how the DataFactory class we're using is structured. In the past I've written about how we implement transactional behavior across business actions by utilizing the EDAF Context and Enterprise Services through the EDAF transaction handler.

Of course within a business action we also need to implement transactional behavior, for example, when an action makes calls to multiple stored procedures that need to be implemented in a single unit of work. To do this we could use simply configure the transaction handler in the pipeline for the business action. However, doing so incurs additional overhead because of the precense of the handler and the need to start a new MSDTC transaction.

To implement a lighter weight approach we've added support for local transactions to our DataFactory. The DataFactory exposes a BeginTransaction method that opens a database connection and return an object that implements the IDbTransaction interface. The methods of the factory are then overloaded to accept the transaction object and therefore can use it across multiple calls.

So the pattern for using local transactions is....

// get the data factory to use
// (a property of our BusinessObjectBase class)
DataFactory df = this.GetDataFactory(FactoryType.Write);

IDbTransaction tx = null;
IDbConnection connection=null;
int ret = 0;

try
{
  // create a transaction to use
  tx = df.BeginTransaction(IsolationLevel.ReadCommitted);
  // grab the connection since the Rollback wipes out
  // the property of the transaction object
  connection = tx.Connection;
              
  HybridDictionary parms = new HybridDictionary();
  parms.Add("partner_country_code",
    req.CCQOrder.PartnerCountryCode);
  // etc.

  df.ExecuteNonQuery("InsertCCQNeed",parms,out ret,tx);

  // call a second sproc
  HybridDictionary ccqParms = new HybridDictionary();
  ccqParms.Add("partner_country_code",
    req.CCQOrder.PartnerCountryCode);

  df.ExecuteNonQuery("InsertCCQ",ccqParms,out ret,tx);

  // commit the transaction
  tx.Commit();

}
catch (System.Exception ex)
{
  if (tx != null)
  {
    if (tx.Connection.State == ConnectionState.Open)
      tx.Rollback();
  }
}
finally
{
  if (connection != null)
  {
    // release to the pool
    if (connection.State == ConnectionState.Open)
      connection.Close();
  }
}