Ensure Proper Closure & Disposal Of A DataReader

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!!

 

5 Comments

  • Dispose() will call Close()

  • Isn't the call to Dispose no longer necessary once it is in the using statement.

    It was my understanding that using was syntatic sugar for a try/finally block that calls dispose on the object.

  • Yeah, the lone fact of the "using" statement is that at the end of that statement, whatever object you were using is disposed, gone, caput.

  • If you need to return a reader from a method make sure to give the CommandBehavior.CloseConnection parameter to ensure the underlying connection is closed.

    public static SqlDataReader GetReader()
    {
    SqlConnection connection = new SqlConnection("connection string");
    SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection);
    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    if (reader != null)
    {
    return reader;
    } else { return null ; }
    }

    using(SqlDataReader myReader = GetReader())
    {
    if(myReader != null && myReader.HasRows())
    {
    // read in the data
    }
    myReader.Close();
    }

    From MSDN on using and calling Dispose. The using statement calls the Dispose method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon as Dispose is called.

    So calling dispose is not necessary.

  • @Bivvo

    Returning a reader is calling for trouble. You have no control over the code that calls that and you could end up with full connection pools followed by errors and you won't have a clue why it's so "random."

    Here's a way around it, but avoid if possible:


    public static void Read(string query, Action predicate)
    {
    if (predicate != null)
    {
    using (SqlConnection connection = new SqlConnection("connection string"))
    {
    using (SqlCommand command = new SqlCommand(query, connection))
    {
    using (SqlDataReader reader = command.ExecuteReader())
    {
    predicate(reader);
    }
    }
    }
    }
    }

    public static void SampleOfUsingRead()
    {
    int sum = 0;

    Read("SELECT Amount FROM SomeTable", p =>
    {
    while (p.Read()) sum += Convert.ToInt32(p["Amount"]);
    });
    }


    This way you're guaranteed that the connection, command, and reader are always closed.

Comments have been disabled for this content.