DataReader and ExecuteNonQuery

[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.

[Cor Ligthert [MVP]]

Sephen

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

I hope this helps,

Cor

"Stephen Costanzo" <sx********@hotmail.com> schreef in bericht
news:Ov**************@TK2MSFTNGP10.phx.gbl...

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.

[Chris]

Stephen Costanzo wrote:

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.

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

[Stephen Costanzo]

Thanks for the help.

"Chris" <[e-mail]> wrote in message
news:u1**************@TK2MSFTNGP15.phx.gbl...

Stephen Costanzo wrote:

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.

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

[Stephen Costanzo]

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 Ligthert [MVP]" <no************@planet.nl> wrote in message
news:u8**************@TK2MSFTNGP12.phx.gbl...

Sephen

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

I hope this helps,

Cor

"Stephen Costanzo" <sx********@hotmail.com> schreef in bericht
news:Ov**************@TK2MSFTNGP10.phx.gbl...

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.

[Cor Ligthert [MVP]]

Stephen,

Probably is this the information you are looking for.
Have a look at Mars(datareaders) and connection pooling.

http://msdn2.microsoft.com/en-us/library/ex6y04yf

I hope this helps

Cor

"Stephen Costanzo" <sx********@hotmail.com> schreef in bericht
news:ef**************@TK2MSFTNGP09.phx.gbl...

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 Ligthert [MVP]" <no************@planet.nl> wrote in message
news:u8**************@TK2MSFTNGP12.phx.gbl...

Sephen

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

I hope this helps,

Cor

"Stephen Costanzo" <sx********@hotmail.com> schreef in bericht
news:Ov**************@TK2MSFTNGP10.phx.gbl...

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.

[Stephen Costanzo]

very cool.

Thanks

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:ek****************@TK2MSFTNGP14.phx.gbl...

Stephen,

Probably is this the information you are looking for.
Have a look at Mars(datareaders) and connection pooling.

http://msdn2.microsoft.com/en-us/library/ex6y04yf

I hope this helps

Cor

"Stephen Costanzo" <sx********@hotmail.com> schreef in bericht
news:ef**************@TK2MSFTNGP09.phx.gbl...

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 Ligthert [MVP]" <no************@planet.nl> wrote in message
news:u8**************@TK2MSFTNGP12.phx.gbl...

Sephen

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

I hope this helps,

Cor

"Stephen Costanzo" <sx********@hotmail.com> schreef in bericht
news:Ov**************@TK2MSFTNGP10.phx.gbl...
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.

[Kerry Moorman]

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
"Cor Ligthert [MVP]" wrote:

Sephen

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

I hope this helps,

Cor

[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
"Stephen Costanzo" wrote:

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.