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.

DataReader not reading

Why won't this dataReader read?

Dim objCon2 As New SqlConnection()
objCon2.ConnectionString = "a standard connection string"
objCon2.Open()

Dim objCommand As SqlCommand
objCommand = New SqlCommand(strSQL, objCon2)
Dim objReader As SqlDataReader
objReader = objCommand.ExecuteReader()

Label1.Text = objReader("email")

strSQL is a select command which I've checked (using SQL Query analyzer) does return data. I know the connection string is valid (and I presume if it wasn't that it'd fail on objCon2.open, which it doesn't).

So why oh why do I get this error on the last line (and yes, there is an "email" field in the contents of the reader)

System.InvalidOperationException: Invalid attempt to read when no data is present.You have to move the "read head" to the start of a record by calling objReader.Read(). You do this each time you want to move to the next record. Since in your case there is presumably only one record you only need to call it once.

objReader = objCommand.ExecuteReader()
if objReader.Read()
 Label1.Text = objReader("email")
End If
' be sure to close and the data reader afterwards
objReader.Close()