While much has been written about how easy ADO.NET makes it to access data from a variety of data sources, there are things to which you must pay attention to maintain good application performance. One of these is the proper termination and cleanup of ADO.NET objects to be certain that the resources they consume are freed in a timely manner.

Garbage collection
The .NET Framework follows Java’s lead by freeing the developer from the laborious task of tracking memory usage and knowing when to release or free up memory. This feature is known as automatic garbage collection. While garbage collection gives you much needed freedom, it doesn’t totally remove your responsibility for the resources your application consumes.

For instance, the garbage collector knows nothing of the actual objects in memory and is sometimes not fully aware of how to properly free a resource held by an object. To properly perform the garbage-collection process, you must write code to clean up and free any resources you use. This is achieved through the use of methods such as Close, Dispose, and Finalize. Let’s take a closer look at using these methods with ADO.NET objects.

Communicating with a database
In the sample code for this article, I’m using SQL Server and VB.NET. As a result, I’m using the SQL Server-specific objects found in the System.Data.SqlClient namespace, but the same concepts apply for the other ADO.NET data providers. The most common classes you’ll use when accessing SQL Server data are:

  • ·        SqlCommand
  • ·        SqlConnection
  • ·        SqlDataAdapter
  • ·        SqlDataReader
  • ·        SqlParameter

The VB.NET code found in Listing A establishes a connection with the Northwind database included with SQL Server. It accesses the list of names from the Customers table.

In Listing A, I establish the database connection and read data from the Customers table, if data is returned from the query. After I’ve finished reading the data, the database connection and other objects are no longer needed. The resources these objects hold should be freed, and most important, the database connection should be closed. The number of database connections is often restricted according to the database platform installation, so it’s critical to make sure you close your connections in a timely fashion.

To clean up your ADO.NET object and the resources it consumes, you’ll take advantage of both the Close and Dispose methods found on those objects. These methods should be utilized only if the object in question has been properly created and opened. In addition, the cleanup code should be contained in a Try… Catch… Finally block to ensure that errors are properly handled.

Let’s take a look at a selection of ADO.NET classes specific to SQL Server and what methods are utilized to release system resources:

  • ·        SqlConnection—Once a connection has been established by way of the Open method, the Close method releases the database connection, and Dispose makes the system resources used available for garbage collection. One note, the connection must actually be open before you call the Close method. You can verify that a connection is open by examining the State property of the connection class.
  • ·        SqlCommand—The command class has no Close method, but it does contain a Dispose method to make system resources available for garbage collection.
  • ·        SqlDataReader—The data reader class includes a Close method to release the connection it consumes.
  • ·        SqlDataAdapter—The data adapter class includes a Dispose method to return resources to the system.

This is a small sampling of the ADO.NET classes, but it does provide a peek at how to properly release resources consumed by the objects. The code in Listing B expands the example in Listing A to properly handle object cleanup. The cleanup code is placed inside a Finally code block to ensure that the code is executed whether or not an exception occurs.

Notice that I verify the existence of the objects by comparing them to Nothing before attempting to access methods on the objects. In addition, I make sure that the connection and data reader objects are actually open before trying to close them. This way I know that no errors will be encountered in the Finally block. The code in Listing B takes full advantage of the ADO.NET classes, and it returns the resources to the system as soon as possible.

Clean up your mess
The process of development combined with unit and system testing should reveal any problems with ADO.NET code. Also, database administrators will notice any problems by monitoring the number of open database connections, which will increase over time if connections aren’t properly terminated in code. If you continually leave your connections open, you can guarantee a visit from an angry database administrator.