DataReader & Connection

[Heike]

In my application 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.

[bungpeng]

If Not DataReader Is Nothing Then

DataReader.Close()

End If

DataReader = Nothing[/Quote]

 

No need to close database connection, you just need to close your DataReader

[Heike]

Yes of course that would be the way.

 

But: I have multiple threads. In one thread a DataReader is opened. Now it may be that this thread is aborted and though the DataReader isn't closed.

 

Then I can get the error.

[Derek Stone]

You should not be using multiple threads with multiple readers on one connection. You're just asking for concurrency problems. I'd strongly rethink your strategy.

[sharpcoder]

I hope I'm not interrupting anything here.

(Did you get an answer to your question Heike?)

 

Bungpeng said that there is no need to close database connection but is that always the case?

I've got a file using the following pseudo code:

 

- open connection

- execute sql-string and fetch some information

- close connection

- manipulate information

- open connection

- execute nonquery sql-string

- execute another nonquery sql-string

- close connection

 

But maybe this is the wrong way to go. I figured that it would be best to, at least, close and later reopen the connection when comprehensivly manipulating data (not to load the DBMS too much) from the database (this is done in about 15 lines of code). But maybe not?

Is it better to always follow this approach?

- open connection

execute different sql-strings, manipulate data, etc

- close connection

 

Thank you.

[samsmithnz]

The answer to your question sharpcoder:

 

It Depends.

 

How long does the manipulatation take? You should take into account

 

1. the database (SQL/ Access?): access takes a long time to open/create a connection too

2. the application (Desktop/ Web): You may want to close connections asap on some distributed applications

3. the time it takes to process: If you have an application that opens a connection and then has to open it again 2 or 3 seconds later... I'd personally leave it open. But it depends on whether or not its part of the same process (Is there human intervention in the middle? If so close the connection)

[sharpcoder]

The answer to your question sharpcoder:

 

It Depends.

 

How long does the manipulatation take? You should take into account

 

1. the database (SQL/ Access?): access takes a long time to open/create a connection too

2. the application (Desktop/ Web): You may want to close connections asap on some distributed applications

3. the time it takes to process: If you have an application that opens a connection and then has to open it again 2 or 3 seconds later... I'd personally leave it open. But it depends on whether or not its part of the same process (Is there human intervention in the middle? If so close the connection)

 

1. MySQL

2. Web application

3. Well... definately no 2 or 3 seconds.

15 lines of code - no loops, no advanced calculations...

It seems like I should keep it open. Thanks for the advice.