Explicitly close DataReader if I close the underlying connection?"

[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

 

[Carl Daniel [VC++ MVP]]

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

 

[MarkusJNZ]

Thanks for your help

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