DataReader and ExecuteNonQuery

  • Thread starter Thread starter Stephen Costanzo
  • Start date Start date
S

Stephen Costanzo

We have an application that uses a SQLDataReader to cycle through a control
table in the database and process records based upon that data. These
records include things like the directory to poll and where to move the
data.

During this process there is a timer that fires which reports that the
application is running to the database, sort of a heartbeat. It uses the
SQLCommand.ExecuteNonQuery function to execute the stored procedure. If this
operation fires against the open connection (we aren't closing the
connection) we get an error indicating that there is already a open reader
on the connection object.

My questions are as follows:
1. What are we doing wrong? Should we be doing:
open connection
execute the SQLCommand.ExecuteReader
loop through the reader
close the connection

and have a different connection object for the SQL Statement as opposed to
using the global connection object that the SQLCommand (above) is using?

2. As I am executing a NonQuery why would that conflict with an open reader?
Nothing is being returned relative to a Reader object.

3. Why can't we have multiple SQLDataReaders on a connection? We noticed
this as a problem as well. Is this a bug or "as designed"?

4. Is the best practice to open and close connections consistantly? In older
versions of VB we found that opening a connection to the same database was
very resource intensive which is why the original code (prior to porting to
..NET) never closed the database connection. Has that been addressed with the
SQLClient object?

Thanks in advance.
 
Sephen

In Net 1.x is only one open connection at the same time allowed.

I hope this helps,

Cor
 
Stephen said:
We have an application that uses a SQLDataReader to cycle through a control
table in the database and process records based upon that data. These
records include things like the directory to poll and where to move the
data.

During this process there is a timer that fires which reports that the
application is running to the database, sort of a heartbeat. It uses the
SQLCommand.ExecuteNonQuery function to execute the stored procedure. If this
operation fires against the open connection (we aren't closing the
connection) we get an error indicating that there is already a open reader
on the connection object.

My questions are as follows:
1. What are we doing wrong? Should we be doing:
open connection
execute the SQLCommand.ExecuteReader
loop through the reader
close the connection

and have a different connection object for the SQL Statement as opposed to
using the global connection object that the SQLCommand (above) is using?

2. As I am executing a NonQuery why would that conflict with an open reader?
Nothing is being returned relative to a Reader object.

3. Why can't we have multiple SQLDataReaders on a connection? We noticed
this as a problem as well. Is this a bug or "as designed"?

4. Is the best practice to open and close connections consistantly? In older
versions of VB we found that opening a connection to the same database was
very resource intensive which is why the original code (prior to porting to
.NET) never closed the database connection. Has that been addressed with the
SQLClient object?

Thanks in advance.
Click to expand...

1. Open Connection -> Execute Reader -> Loop Reader -> Close Reader
You don't need to close the connection, just close the reader. Then you
will be able to do other calls on the connection.

2. The reader is tied to the connection and has the connection locked,
that's where the conflict is.

3. This is by design. A reader doesn't bring all the data down at one
time, only every time do you the reader.read() so the connection must
remain open (and tied up for other calls) to keep the place you are in
your reader.

4. I wouldn't open and close consistantly.

Chris
 
Thanks for the help.

Chris said:
1. Open Connection -> Execute Reader -> Loop Reader -> Close Reader
You don't need to close the connection, just close the reader. Then you
will be able to do other calls on the connection.

2. The reader is tied to the connection and has the connection locked,
that's where the conflict is.

3. This is by design. A reader doesn't bring all the data down at one
time, only every time do you the reader.read() so the connection must
remain open (and tied up for other calls) to keep the place you are in
your reader.

4. I wouldn't open and close consistantly.

Chris
Click to expand...
 
So in Net 2.x will there be the ability to have more than one open
connection at the same time?

Do you know if there is something on the microsoft site? i tried several
keywords and was not getting meaningful results.

Thanks
 
Cor,

What are you talking about here? You can have as many open connections as
you want.

Perhaps you meant that you can only have one open data reader per connection?

Kerry Moorman
 
Stephen,

Using one open global connection is a design mistake in .Net.

You need to open and close connections each time you access the database.

Kerry Moorman
 
Back
Top