DataReader & Connection

[Heike]

In my application (developed in VB.net) I have to execute several SQL statements. Therefore in the beginning I open a connection and in the end (when finishing my application) I close it.

Now, in some cases, I get the error message "There is already an open DataReader associated with this connection which must be closed first."

My question is: how can I determine if there is an open DataReader? I don't want to open and close my connection each time I execute an SQL statement because I think that would be bad to my performance.

Thanks in advance.

[nswan]

opening and closing connections isn't as bad for performance as it used to be with old ado. This is down to connection pooling. The main theory of ado.net is about being disconnected from the data source so i would say it's not a bad thing opening a connection, and then closing it whenever you've finished using it.

If you are using a dataReader to bind to something like a datagrid then you can set the command behaviour.

myDataReader = myCommand.executeReader(commandBehaviour.closeConnection)

which closes the connection after it's finished using the datareader. I'll look into how to tell if there is a datareader associated with a connection for you.

[Sheppe]

No flame is intended here, but rather than determining if a datareader is open at any given time, use good coding practice and make sure that you close a datareader when you're done with it. You can also employ techniques as mentioned above.

[Heike]

Yeah, I think you're right.

But: I use different threads. In one thread there might be a DataReader being opened. In some cases this thread will be aborted and that may prevent the DataReader being closed.

Then I get the described error.

[Carnifex]

There can only be one open data reader open with any one connection.

As far as I'm aware there is no way of knowing if a datareader is open on a given connection. (unless you catch it as an exception in a try/catch block, but this is messy and also hurts performance)

I'd recommend you do a bit of reading on concurrency, and look into Monitors/Semaphores for handling object lock issues in a multi-thread environment. It may just be easy to have a shared datareader reference that they all use. And you just check if the shared datareader is open at that point.