ADO.NET Database Transaction as Cross-Cutting Concern
Piyush Gupta, Tata Consultancy Services (piyush.gupta@tcsseattle.com)

---

For transaction, EDRA framework supports only automatic transaction thru COM+ transactions which may not be appropriate for all projects( unless you are looking for 2 Phase commit). Some projects may be looking for ADO.NET transactions only. In a particular application scenario Enterprise Service may not be used at all.

Our team has developed a custom handler which takes care of establishing database connections and invoking transactions so that business actions need not worry about opening/closing a database connection or starting/committing/rolling back transactions.

Actions taken by this custom handler are:
1. Open the database connection using the connection string specified in handler’s configuration in ServicesReferenceArchitecture.config. (This connection string may be overridden by handler’s configuration in the business action’s pipeline so that every business action may connect to different database).
2. If the IsTransaction flag is set in the handler’s configuration (or in the handler’s configuration in business action’s pipeline), then start the database transaction.
3. Add a database connection to the Context as an IDbConnection object so that the business actions have an abstraction from the type of database.
4. Execute the handler in try-catch-finally block.
5. If there is no exception, commit the database transaction if IsTransaction flag is set in handler’s configuration (or in handler’s configuration in business action’s pipeline).
6. If there is any exception, rollback the database transaction if IsTransaction flag is set in handler’s configuration (or in handler’s configuration in business action’s pipeline) and throw exception to higher levels.
7. In the finally block, close the database connection and remove it from Context. Removing the database connection from context is a must because the connection object is not serializable.

By implementing this handler, we ensured that database operations are implemented as cross-cutting concerns and business actions does not need worry about database connection or transaction handling issues.

---

Code for Handler's IHandlerConfiguration implementation

---

public void Initialize(System.Xml.XmlNode configuration)
{
_connectionString = configuration.Attributes"connectionstring".Value;

if(configuration.Attributes"isTransaction".Value.Equals("true"))
{
_isTransaction = true;
}
else
{
_isTransaction = false;
}
}

---

Code for Handler's IStatefulAroundHandler implementation

---

public void Execute(IContext context, NextHandler nextHandler)
{
//DBHelper is database helper class which may be replaced with
//ADO.NET connection class
DBHelper databaseHelper = new DBHelper(_connectionString);

if(true == _isTransaction)
{
databaseHelper.BeginTransaction();
}

//Add database helper to the context
contextConstants.CONTEXT_DBHELPER = databaseHelper;

try
{
//Execute next handler
nextHandler();
if(true == _isTransaction)
{
//Commit the transaction
databaseHelper.CommitTransaction();
}
}
catch
{
if(true == _isTransaction)
{
//Rollback the transaction
databaseHelper.RollbackTransaction();
}
throw;
}
finally
{
//Dispose method of database helper class will close the underlying database connection
databaseHelper.Dispose();

context.Remove(Constants.CONTEXT_DBHELPER);
}
}

---

Handler's implementation in ServicesReferenceArchitecture.config

---

<ra:handlers>
<ra:handler ra:name="DatabaseHandler" ra:type="TCS.Alliance.AMS.Services.Handlers.DatabaseHandler, TCS.Alliance.AMS.Services.Handlers">
<ra:settings connectionstring="some" isTransaction="true" />
</ra:handler>
</ra:handlers>