Do I need to close DataReader explicitly?

[Guest]

If I close the connection, would the reader already open on the connection be
automatically closed without explicitly closing the reader? I have an
ASP.NET app that on a daily basis generates 'there is already an open
DataReader ...' exception. I search the code to see where I am calling a
DataReader explicitly. I found this piece of code:

using(SqlConnection conn = new SqlConnection...)
{
using(SqlCommand cmd = new SqlCommand...)
{
....
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while(rdr.Read())
{
...
}
rdr.Close();
....
}
}

If an exception occurs after acquiring a reader but before closing the
reader explicitly, the connection object should be properly closed and
disposed since I am using 'using' statement. I am assuming that the active
reader on the connection should be closed with the connection as well right?
or Do I still need to catch the exception and close the reader explicitly by
rdr.Close();
?

Thanks.

 

[W.G. Ryan - MVP]

CloseConnection doesn't close the connection automatically, it closes the
connection when the DataReader is closed.
http://msdn.microsoft.com/library/d.../frlrfsystemdatacommandbehaviorclasstopic.asp

You're right about the connection and 'using' statement, however I would
recommend explicitly closing the connection in a finally block (and yes, you
will need to close your reader) just so that your intention is clear. This
recommendation is solely for code readability and while many may argue this
makes code less readable, it does show intent much better and for that
reason, I recommend doing it. This of course is a matter of personal
preference.

 

[Cor Ligthert [MVP]]

Bill,

The last who would arguing in that recommendation from you is probably me.
I find data retrieval statements without a catch a gruel.

Just my 2 eurocents

Cor

 

[Guest]

Code readibilty aside, from the operational point of view, if an exception
occurs in the code that I posted, the connection will be closed and along
with it the reader. I do not need to explicitly close the reader, right? The
connection, the command, and the reader objects should be properly closed and
disposed of and should not have any ill effects.

This would rule out the possibility of this piece of code returning
connections back to the pool with an open reader which would later cause the
' already an open DataReader' exception.

 

[Miha Markic [MVP C#]]

I would put reader in a try/finally or using block, too.