Thursday, November 23, 2006

Automating Database Connection Management

One of the most frustrating problems in software development is this database connection exceeding due to non closed db connection usage. Things getting worse especially when we do not discover this problem at the development/testing phase (as we may not be continuously running the development/testing systems for very long periods). But frustrating enough the problem is going to appear in the production site.

What can we do about this? One way is to thoroughly test the application for non closed connections. Yes this requires developer concentration and can be time consuming if you do not use proper tools. More effective solution is to use a database access platform that can automatically take care of closing the opened database connections. But what if our simple project requires use of direct database access technologies (such as pure JDBC or pure ADO.NET)?

Let’s look at a small code fragment which we can use to employ a very simple data access framework to take care of this problem. In short we are going to write a base database action class which can provide and manage database connection to its sub classes. We use sub classing to write data access code (data actions) we need. Below is an example for the base action class written on VB.NET.

Public MustInherit Class ActionBase(Of T)

Protected MustOverride Function Body(ByVal conn As DbConnection) As T

Public Function Execute() As T

' Create a database connection

Dim conn As DbConnection = ConnectionProvider.GetConnection()

' Call the body method by providing the connection


' Close the connection


End Function

End Class

All the sub classes need to implement the abstract method “Body(ByVal conn As DbConnection)”. As a method parameter, a connection instance will be available to the implementing method and that can be used by the sub class method to perform data access logic. Above base class is written as a generic class and subclasses should provide the template class name which should be the expected return object type of the sub class. For an example, a data access action that retrieve a ‘User’ instance will like follows:

Friend Class RetrieveUserAction Inherits ActionBase(Of User)

Private _userId As String

Public Sub New(ByVal userId As String)

Me._userId = userId

End Sub

Protected Overrides Function Body(ByVal conn As DbConnection) As User

Dim user as User = new User()

' Use connection to fetch user info and populate ‘user’ instance

Return user

End Function

End Class

As you can see we write sub classes to execute database operation on the database. Developers will not have to look for database connections. It will be provided in the “Body()”method parameter. Also the developers are free from the burden of closing the connections as framework itself will take care of it.

Let’s look at how one can make use of the above written class to retrieve the user with id=12.

Dim action AS new RetrieveUserAction(12)

Dim user As User = Action.Execute()

Even though this simple framework is pretty useful in managing connection, this has some limitations also. Developers are not able to spawn threads and pass the connection in to it for further processing. Also the results will be always disconnected, you cant pass resultsets to upper layers for presessing (but I think this enforces a good practice).

Also one major thing to consider is whether actually database resources getting released by closing the connections. In Oracle with JDBC connection close will not release database resources and you need to close the ‘Statement’ instance to release database resources. In such situations we can ammend the framework to provide us statement objects instead of providing connections.

No comments: