Explicitly close DataReader if I close the underlying connection?"

31 Oct 2006 11:27 PM

MarkusJNZ

Hi, I have a DataReader which I think may be the cause of connections
remaining open to a MSSQL database.

basically the pseudo code is below

======

SqlConnection conn = new SqlConnection("connectionString");
SqlCommand cmd = new SqlCommand("Select * from blah",conn);
conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

while(reader.Read())
{
// do something
}

conn.Close();
======

Basically, the code closes the connection object but does not close the
acutal dataReader.
Does closing the database connection *also* close the dataReader
connection and remove all references betweent the MSSQL database and
the ADO.NET objects?

Normally, I would do this, so I'm interested in the difference

======

SqlConnection conn = new SqlConnection("connectionString");
SqlCommand cmd = new SqlCommand("Select * from blah",conn);
conn.Open();

SqlDataReader reader =
cmd.ExecuteReader(CommandBehaviour.CloseConnection);

while(reader.Read())
{
// do something
}

reader.Close();
======
TIA Markus

1 Nov 2006 12:40 AM

Carl Daniel [VC++ MVP]

<Markus***@gmail.com> wrote in message
Show quote

news:1162337263.523528.286900@b28g2000cwb.googlegroups.com...
> Hi, I have a DataReader which I think may be the cause of connections
> remaining open to a MSSQL database.
>
> basically the pseudo code is below
>
> ======
>
> SqlConnection conn = new SqlConnection("connectionString");
> SqlCommand cmd = new SqlCommand("Select * from blah",conn);
> conn.Open();
>
> SqlDataReader reader = cmd.ExecuteReader();
>
> while(reader.Read())
> {
> // do something
> }
>
> conn.Close();
> ======
>
> Basically, the code closes the connection object but does not close the
> acutal dataReader.
> Does closing the database connection *also* close the dataReader
> connection and remove all references betweent the MSSQL database and
> the ADO.NET objects?

Yes, it should close the connection under the data reader, but it's still
good practice to make sure that your reader is properly cleaned up.

You should use:

SqlConnection conn = new SqlConnection("connectionString");
SqlCommand cmd = new SqlCommand("Select * from blah",conn);
conn.Open();

using (SqlDataReader reader = cmd.ExecuteReader())
{
    while(reader.Read())
    {
        // do something
    }
}
conn.Close();

....to ensure that the reader is properly disposed. If your code really is
that linear, then you should use a using block to control the lifetime of
the SqlConnection object as well. I'm assuming that's just schematic
though, and in reality the code that opens and closes the connection is
farther from the code that uses the reader.

-cd

1 Nov 2006 5:49 AM

MarkusJNZ

Thanks for your help
Markus
Carl Daniel [VC++ MVP] wrote:

Show quote

> <Markus***@gmail.com> wrote in message
> news:1162337263.523528.286900@b28g2000cwb.googlegroups.com...
> > Hi, I have a DataReader which I think may be the cause of connections
> > remaining open to a MSSQL database.
> >
> > basically the pseudo code is below
> >
> > ======
> >
> > SqlConnection conn = new SqlConnection("connectionString");
> > SqlCommand cmd = new SqlCommand("Select * from blah",conn);
> > conn.Open();
> >
> > SqlDataReader reader = cmd.ExecuteReader();
> >
> > while(reader.Read())
> > {
> > // do something
> > }
> >
> > conn.Close();
> > ======
> >
> > Basically, the code closes the connection object but does not close the
> > acutal dataReader.
> > Does closing the database connection *also* close the dataReader
> > connection and remove all references betweent the MSSQL database and
> > the ADO.NET objects?
>
> Yes, it should close the connection under the data reader, but it's still
> good practice to make sure that your reader is properly cleaned up.
>
> You should use:
>
> SqlConnection conn = new SqlConnection("connectionString");
> SqlCommand cmd = new SqlCommand("Select * from blah",conn);
> conn.Open();
>
> using (SqlDataReader reader = cmd.ExecuteReader())
> {
>     while(reader.Read())
>     {
>         // do something
>     }
> }
> conn.Close();
>
> ...to ensure that the reader is properly disposed. If your code really is
> that linear, then you should use a using block to control the lifetime of
> the SqlConnection object as well. I'm assuming that's just schematic
> though, and in reality the code that opens and closes the connection is
> farther from the code that uses the reader.
>
> -cd