C# Reader Disposal Review

Question

I am using the Enteprise Library to query my database. When I run a query, I am relying on stored procedures. Currently, I am using code that looks like the following:

Database database = DatabaseFactory.CreateDatabase();
DbCommand command = database.GetStoredProcCommand("MyStoredProcedureName");

database.AddInParameter(command, "filter", DbType.String, filter);

Result result = null;
using (IDataReader reader = database.ExecuteReader(command))
{
  if (reader.Read())
    result = new Result(reader);
}
return result;

How can I be sure that my reader is being closed? I have noticed that my application sometimes fails to load on subsequent loads. I suspect that something is being left open. But I can't figure out how to track it down.

Based on the code shown above, shouldn't teh reader get closed and disposed because of the 'using'?

Thank you!

score 2 · Answer 1 · answered Apr 27 '11 at 23:52

2

The reader is being disposed and closed, but the DbCommand is not. You should put that one in a using statement as well.

answered Apr 27 '11 at 23:52

Chris Tavares

29,165
4
46
63

score 2 · Accepted Answer · answered Apr 27 '11 at 23:53

The reader is guaranteed to be closed, since it is wrapped in using. However, the DbCommand also implements IDisposable and there's no using/dispose call for it.

Database database = DatabaseFactory.CreateDatabase();
using(DbCommand command = database.GetStoredProcCommand("MyStoredProcedureName"))
{
    //snip (no change)
}
return result;

The reader doesn't represent the underlying connection, just the buffer for reading the response.

Hans Passant · Answer 3 · 2011-04-28T00:10:48.927

You took care of disposing the reader. You didn't take care of the disposing the DbCommand and the Database objects. Not sure what they are, pretty sure they implement IDisposable too though. They always do. Nest your using statements in the prettiest way you know how.

One of the less obvious ones are:

using (var obj = new Foo())
using (var obj2 = new Bar()) {
    // etc..
}

which is very nicely supported by the C# IDE, it does the indenting just right. Or just be explicit about it, the hallmark of a programmer that writes code that makes it obvious to anybody: "wow, dude knows his stuff without the tools". That's good.

score 0 · Answer 4 · answered Apr 27 '11 at 23:53

Since you're using the IDataReader in a using block, it is guaranteed to be disposed (and therefore closed) when it goes out of scope. As another poster mentioned, don't forget to put your DbCommand in a using block as well so that it is properly disposed.

score 0 · Answer 5 · answered Apr 27 '11 at 23:57

Yes, the using block ensures that the reader will be disposed.

However, you should dispose your command and connection objects also. If you don't, they won't be closed until the garbage collector comes around to removing them, so the database connection will remain open. If you leave a lot of connections open, the database will eventually refuse new connections.

C# Reader Disposal Review

5 Answers5