Do I need to close DataReader explicitly?

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.

CloseConnection doesn't close the connection automatically, it closes the
connection when the DataReader is closed.
http://msdn.microsoft.com/library/d...rclasstopic.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.
"Matt" <Matt@discussions.microsoft.com> wrote in message
news:5921C0DB-C9A5-479F-9FBD-321901174D6E@microsoft.com...
> 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.

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

"W.G. Ryan - MVP" <WilliamRyan@nospam.gmail.com> schreef in bericht
news:ObHaRW6kGHA.1244@TK2MSFTNGP05.phx.gbl...
> CloseConnection doesn't close the connection automatically, it closes the
> connection when the DataReader is closed.
> http://msdn.microsoft.com/library/d...rclasstopic.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.
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:5921C0DB-C9A5-479F-9FBD-321901174D6E@microsoft.com...
>> 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.
>
>

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.


"W.G. Ryan - MVP" wrote:

> CloseConnection doesn't close the connection automatically, it closes the
> connection when the DataReader is closed.
> http://msdn.microsoft.com/library/d...rclasstopic.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.
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:5921C0DB-C9A5-479F-9FBD-321901174D6E@microsoft.com...
> > 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.
>
>
>

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

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Matt" <Matt@discussions.microsoft.com> wrote in message
news:53CEA0AE-F1B5-4B5D-B069-A9933C909291@microsoft.com...
> 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.
>
>
> "W.G. Ryan - MVP" wrote:
>
>> CloseConnection doesn't close the connection automatically, it closes the
>> connection when the DataReader is closed.
>> http://msdn.microsoft.com/library/d...rclasstopic.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.
>> "Matt" <Matt@discussions.microsoft.com> wrote in message
>> news:5921C0DB-C9A5-479F-9FBD-321901174D6E@microsoft.com...
>> > 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.
>>
>>
>>