Data Management

Protect database resources with clean .NET code

Developers are keen to open and utilize a database connection, but it is common to overlook the process of closing and returning the resources utilized to the system. Tony Patton examines how this can be accomplished.

In a previous article, I wrote about the importance of observing certain rules for object cleanup in .NET code. One of the worst examples I've experienced is with database interaction. I once fixed an application whose performance slowly degraded (after a restart) as database connections were consumed. The problem lay in the fact that the database objects were not being properly disposed.

The days before .NET

While many developers may not remember, there was a time when the .NET Framework did not exist. In those days, a responsible programmer would explicitly destruct object instances when finished with them. Destructing the instance involves invoking the object's destructor, which proceeds to free up any specific resources used by the object, such as blocks of memory, other objects, and OS resources, and also frees the memory occupied by the object's instance data.

Programmers knew they had to return resources to the system to ensure application stability, but platforms like Java and the .NET Framework arrived with the acclimation that resource cleanup was a thing of the past. They include built-in garbage collectors to handle the task.

Garbage collection processes only those objects ready for garbage collection. The system tracks objects and determines those objects that are no longer in use, but this process can be enhanced by signaling which objects are no longer being used. This entails calling an object's dispose method, which is used for all tasks associated with freeing resources held by an object, or preparing an object for reuse. It tells the CLR that the associated object is ready for garbage collection, and it releases all external resources used by the object (such as window handles, database connections, etc.).

Problems results when the system doesn't know how to dispose of an object, so the developer must intervene and force it. This is especially true when dealing with database connections. As I monitored a recent application, I watched the database connections pile up as the application ran—these resources were never returned to the system. Consequently, the application had to be restarted every few days.

Restarting an application is never acceptable, and this is especially true with Web-based applications (ASP.NET). The basic function of the system was providing a front-end for viewing data from a SQL Server backend. The data is displayed in DataGrid objects.

Fixing the problem

Developers are keen to open and utilize a database connection, but it is common to overlook the process of closing and returning the resources utilized to the system. Now I'll examine how this can be accomplished.

The .NET Framework simplifies the process of tracking objects, including knowing when to release or free up memory, with the garbage collector. Sadly, it doesn't totally remove the developer from the process.

The garbage collector knows nothing of the actual objects in memory. Consequently, the garbage collector is not fully aware of how to dismantle a resource. To properly perform the garbage collection process, code is written to properly clean up a resource. This is achieved through the use of methods like close, dispose, and even finalize.

Let's take a closer look at using these methods with ADO.NET objects connecting to a SQL Server database. We begin with a list of common objects utilized with a description of its available methods for object cleanup:

  • SqlConnection: Includes the close method for closing the connection to the database, and a dispose method for returning the associated resources to the system.
  • SqlCommand: Contains a dispose method for returning resources to the system.
  • SqlDataAdapter: Its dispose method returns the object's resources to the system.
  • SqlDataReader: Includes a close method for closing the object.
  • ConnectionState: This object is used to work with the SqlConnection object's state. It has the following possible values: Broken, Closed, Connecting, Executing, Fetching, and Open.

The necessary dispose methods should be called when finished working with the objects. For connection-related objects, you should first close it before disposing. With this approach, the ConnectionState class may be combined with the connection's state property to determine if the connection is open. You should use a try/catch/finally block when dealing with database connections/objects. The objects are initialized within the try section and closed/disposed within the finalized section. Also, the objects must be declared (but not instantiated) outside of the try/catch/finally block so it may be accessed within the finally block. The following VB.NET code shows this approach:

Dim conn As SqlConnection
Dim comm As SqlCommand
Dim dr As SqlDataReader
conn = New SqlConnection()
comm = New SqlCommand()
conn.ConnectionString = "data source=Builder;user id=test;password=test"
comm.CommandText = "SELECT * FROM test'"
comm.CommandType = CommandType.Text
comm.Connection = conn
dr = comm.ExecuteReader()
Catch ex As SqlException
Console.Writeline("SQL Error")
Catch ex As Exception
If Not (conn Is Nothing) Then
If (conn.State = ConnectionState.Open) Then
End If
End If
If Not (comm Is Nothing) Then
End If
If Not (dr Is Nothing) Then
If Not (dr.IsClosed) Then
End If
End If
End Try

The C# equivalent follows:

SqlConnection conn = null;
SqlCommand comm = null;
SqlDataReader dr = null;
try {

conn = new SqlConnection();
comm. = new SqlCommand();
conn.ConnectionString = "data source=test;user id=test;password=test";
comm.CommandText = "SELECT * FROM test";
comm.CommandType = CommandType.Text;
comm.Connection = conn;
dr = comm.ExecuteReader();
} catch (SqlException e) {
Console.WriteLine("SQL Error");
} catch (Exception ex) {
} finally {
if (conn != null) {
if (conn.state == ConnectionState.Open) {
if (comm. != null) {
if (dr != null) {
if (!dr.IsClosed) {
} } }

Using this approach, the database resources are properly utilized and returned to the system, thus no negative impact on performance and no necessary weekly restart.

Finish what you start

It's hard to find an application that doesn't utilize a backend database in some way, so proper interaction with the database server is imperative for a stable application. You must always close and dispose of objects when finished with them.

This article concentrates on SQL Server, but it's applicable to other database systems; the methods are available in their corresponding classes.

TechRepublic's free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!

About Tony Patton

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

Editor's Picks

Free Newsletters, In your Inbox