Best Practices?

  • Thread starter Thread starter VBM
  • Start date Start date
V

VBM

I want to use the single connection and command object, Can anybody tell me
the best practice for the follwoing code....I get an error "There is already
an open DataReader associated with this Connection which must be closed
first. "

IDataReader result = myCommand.ExecuteReader();
myCommand.Parameters.Clear();
myCommand.CommandText = "SomeStoreProcName";

while(result.Read())
{

//Read or do someother oprations
//Set parameters for SomeStoreProcName

IDataReader rAccess = myCommand.ExecuteReader();
//read rAccess or do some oprations on it

rAccess.Close();
rAccess.Dispose();
}

result.close();
result.Dispose();
 
You are either going to have to close the first connectino and or reader or
use a second connection.

Since you can't close the connection/reader and use it in a while loop, I
think you'll need another connection.

BTW, if you are using the same connectionstring, don't be afraid of having
another connection. You'd be better off with 10 connections with the same
connectionstring that are opened and closed a second later, than one
connection that stays open for a few minutes. The connection object itself
isn't very large, and opening and closing connections isn't so costly that
having two instead of one is even going to noticeable except in the most
extreme cases.
HTH,

Bill
 
VBM said:
I want to use the single connection and command object, Can anybody tell me
the best practice for the follwoing code....I get an error "There is already
an open DataReader associated with this Connection which must be closed
first. "
Click to expand...

You need to use a DataTable instead of a DataReader. Once you fill the
datatable, the connection will be available for use with other commands.

David
 
Back
Top