Strange Datareader error

[Johnny Jörgensen]

I've got an error that I simply cannot locate:

I've got a form in which I use a datareader object to read information from
a db. After the read, I close the reader and I dispose of both the reader
and the command object (but don't close the connection which is public in
the solution).

The first time i open my form from a parent form, there is no problem.
Everything works fine. I then close down my form, and I dispose of the form.

Then I open a new instance of the form using the new keyword, and then I get
the error when the command object tries to execute the datareader:

There is already an open DataReader associated with this Command which must
be closed first.

Why the heck? I'm 100% sure that the datareader is closed after use, and
I've even disposed of both the reader object, the command object and the
form itself.

What's going on?

From MSDN I can see that I'm not the first person with that problem, but I
cannot find any solution to it. There is a workaround posted (also on MSDN)
simply saying: Close your connection and reopen it. But I cannot do that,
because that connection is used elsewhere in the solution....

Any input would be appreciated,
Johnny J.

 

[Guest]

The idea of having some sort of "global" open connection is a design flaw.
Best practices coding tecnique dictates that you should open a connection
just prior to use, perform your work, and close the connection, allowing it
to go back to the ADO.NET connection pool, which is designed specifically for
this purpose.
Peter

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

I've got an error that I simply cannot locate:

I've got a form in which I use a datareader object to read information
from a db. After the read, I close the reader and I dispose of both the
reader and the command object (but don't close the connection which is
public in the solution).

This is a BAD design, a Connection should be open & closed on each
operation, open it as later as possible, close it as soon as possible.

You could make the connection string public in the solutioh though.
Not only that you can have a class that encapsulate the access to the DB.
The only problem with this approach (that I know no solution to) is that a
DataReader needs an open connection, so it's responsability of the calling
code to dispose it.
You can "help" to close the connection by using SqlCommand.ExecuteReader(
CommandBehavior.CloseConnection);

An alternaty is to use a dataset instead of a datareader, in this way the
connection is never open outside the "data access" class.

 

[sloan]

As others have said.

Open LATE
Close EARLY

Get it back to the pool.

//Close your connection and reopen it. But I cannot do that,

because that connection is used elsewhere in the solution....

//
Not a good idea.



Typical "using an IDataReader code" looks like

someCollection coll = new SomeCollection();

try
{
while (dataReader.Read())
{
if (!(dataReader.IsDBNull(0)))
{

coll.Add( //create an object using the dataReader
values // );
}
}


return coll;
}
//no catch here... see
http://blogs.msdn.com/brada/archive/2004/12/03/274718.aspx
finally
{
if (null!=dataReader)
{
try
{
dataReader.Close();
}
catch
{
//hmm
}
}
}