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()
Try
conn.ConnectionString = “data source=Builder;user
id=test;password=test”
comm.CommandText = “SELECT * FROM test'”
comm.CommandType = CommandType.Text
comm.Connection = conn
conn.Open()
dr = comm.ExecuteReader()
Catch ex As SqlException
Console.Writeline(“SQL Error”)
Catch ex As Exception
Console.Writeline(“Exception”)
Finally
If Not (conn Is Nothing) Then
If (conn.State = ConnectionState.Open) Then
conn.Close()
End If
conn.Dispose()
End If
If Not (comm Is Nothing) Then
comm.Dispose()
End If
If Not (dr Is Nothing) Then
If Not (dr.IsClosed) Then
dr.Close()
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;
conn.Open();
dr = comm.ExecuteReader();
} catch (SqlException e) {
Console.WriteLine(“SQL Error”);
} catch (Exception ex) {
Console.WriteLine(“Exception”);
} finally {
if (conn != null) {
if (conn.state == ConnectionState.Open) {
conn.Close();
}
conn.Dispose();
}
if (comm. != null) {
comm.Dispose();
}
if (dr != null) {
if (!dr.IsClosed) {
dr.Close();
} } }

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!