Datareader not referencing connection object

Please see following code :

SqlConnection conn=new SqlConnection(@."something...;");
SqlCommand comm=new SqlCommand("Select TOP 10 * FROM TableReaderTest WITH (HOLDLOCK) ",conn);

conn.Open();
SqlDataReader rd;
conn=null;

try
{
rd = comm.ExecuteReader();
rd.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

This Code works fine. I have set conn=null, still datareader is able to read the data. Why?

Thank you.

You set conn to null, you are setting thereference to null. Since your SqlCommand has it's own reference to the connection, the connection is still around. You should use a Finally block for the .Close() to ensure it is called.|||

Thank you for answering.

Yes it is bad code that I have set it null.

Please look following code:

rd=comm.ExecuteReader(<<behaviour close connection>>);
do someting.. donot close rd
comm.CommandText="another query"
rd=comm.ExecuteReader(<<behaviour close connection>>);
rd.close()

Is there First SQL Connection is still open?
Thank you.

|||

The only provider that will even allow such a thing is SQL Server 2005, otherwise you will get an error when you attempt to execute the second ExecuteReader.

Regardless of what it actually does, I wouldn't recommend assuming anything. It's ambiguous on what it SHOULD do, so don't rely on what it really does, and don't code that.

|||

Hi Motley,
Yes, we do not code like this way. But this is already done by other developers, and we are reviewing it.

Please have a look to following code (Assume try/catch is properly placed) :

privatevoid Form1_Load(object sender, System.EventArgs e)
{
SqlDataReader dr1=GetDataReader("Select 1"); //Line1
SqlDataReader dr2=GetDataReader("Select 2"); //Line2
dr1=GetDataReader("Select 3"); //Line3
dr1.Close(); //Line4
dr2.Close(); //Line5
}

private SqlDataReader GetDataReader(string qry)
{
SqlConnection conn=new SqlConnection(@."Server=localhost;uid=user;pwd=password;database=northwind;;");
SqlCommand comm=new SqlCommand(qry,conn);
conn.Open();
SqlDataReader rd;
rd = comm.ExecuteReader(CommandBehavior.CloseConnection);
comm=null;
conn=null;
return rd;
}

This works withSQL2000 also. Each time new instance of sqlDatareader is created and reference is returned.
At Line3, we are assigning new datareader to dr1. Will it close the dr1 previously opened (for "Select 1") automatically?
If 2 datareaders are not allowed open at the same time, how it is working?
At Line4 we are closing the dr1, but I think there are 2 different connections opened for dr1 and this will lose the last one. Right?

Thank you,

|||In this example, you are using a new connection for each data reader, and so you can get an additional datareader and assign it to dr1. However, in this case, dr1 will be closed (and in this example, the underlying connection) whenever the data reader object is garbage collected, which is a terrible idea.|||What doug said is correct.