Jose Guay

Most of the time, we create programs that use DataReaders (weather they are on ASP.NET or Winforms). They’re fast, easy to use but sometimes, they fill out our connections pool and withour proper care, they can cause problems to our users.

There are some blog posts around this issue and many questions in the forums, so I’ll try to make it as simple as possible so you’ll find it attractive to use, because in the end, there is no magic here, just a nice tip.

The solution: Use the DataReader within the scope of a using clause. How? Take a look at this sample:

Regular Programming
In general, programmers tend to use DataReaders like this:

SqlConnection connection = new SqlConnection(“connection string”);
SqlCommand cmd = new SqlCommand(“SELECT * FROM SomeTable”, connection);
SqlDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
      while (reader.Read())
      {
              //do something
      }
}
reader.Close();
reader.Dispose();

While this would work and do excatly what you need it to do, it does not ensure that the reader will be actualy closed and disposed. Why? it’s by design. DataReaders will be kept open until they got all the data from the source and even then, they depend on the command object that should be opened with the CommandBehavior.CloseConnection parameter.

So, anyway. Here is my proposed solution to overcome this problems.

using (SqlDataReader reader = cmd.ExecuteReader())
{
       if (reader != null)
       {
              while (reader.Read())
              {
                        //do something
              }
              reader.Close();
              reader.Dispose();
       }
}

Why this works better? Because the DataReader lives only within the scope of the using clause. Once the flow leaves the using clause the datareader is forced to deallocate from memory thus closing/disposing it properly.

Hope this will help you get the most of DataReaders because, yes, they are the FASTEST way to get data from any datasource.

Enjoy!!