What You Should Dispose When Using ADO.NET

Everyone that has been using ADO.NET to get the data from the database does more or less the same thing. First you create a Connection object, then a Command, then you execute the command to get the DataReader. With reader, you iterate through the collection of records using a while loop. A sample code looks as follows:

string connectionString = "connectionString";
using (SqlConnection connection = new SqlConnection(connectionString))
{
   string commandText = "commandText";
   using (SqlCommand command = new SqlCommand(commandText, connection))
   {
      using (SqlDataReader reader = command.ExecuteReader())
      {
         while (reader.Read())
         {
            //read the data
         }
      }
   }
}

I have noticed that most people use the using statement only for the connection objects because that is what you will find in most of the books and tutorials. There is however one very important thing to remember and that is a simple rule that you should dispose all disposable object as soon as possible. When you look at the ADO.NET objects you will see the connection object is not the only one that can (and should) be disposed. The command object and also the datareader object are also IDisposable so as far as the rule goes you should dispose them. As for the command object I have seen few examples where it is disposed, but I have seen only one where the datareader is disposed.

So just to remind you: dispose your disposable objects!

Add to del.icio.us