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.Exec uteNonQuery 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.Exec uteReader
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]]

Re: DataReader and ExecuteNonQuery

Sephen

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

I hope this helps,

Cor

"Stephen Costanzo" <sxcostanzo@hot mail.com> schreef in bericht
news:OvpIXIpyFH A.3952@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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.Exec uteNonQuery 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.Exec uteReader
> 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.
>
>[/color]

[Chris]

Re: DataReader and ExecuteNonQuery

Stephen Costanzo wrote:[color=blue]
> 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.Exec uteNonQuery 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.Exec uteReader
> 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.
>
>[/color]

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]

Re: DataReader and ExecuteNonQuery

Thanks for the help.

"Chris" <no@spam.com> wrote in message
news:u1PqgSpyFH A.2924@TK2MSFTN GP15.phx.gbl...[color=blue]
> Stephen Costanzo wrote:[color=green]
>> 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.Exec uteNonQuery 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.Exec uteReader
>> 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.
>>
>>[/color]
>
> 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[/color]

[Stephen Costanzo]

Re: DataReader and ExecuteNonQuery

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]" <notmyfirstname @planet.nl> wrote in message
news:u8UJjRpyFH A.1856@TK2MSFTN GP12.phx.gbl...[color=blue]
> Sephen
>
> In Net 1.x is only one open connection at the same time allowed.
>
> I hope this helps,
>
> Cor
>
> "Stephen Costanzo" <sxcostanzo@hot mail.com> schreef in bericht
> news:OvpIXIpyFH A.3952@TK2MSFTN GP10.phx.gbl...[color=green]
>> 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.Exec uteNonQuery 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.Exec uteReader
>> 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.
>>
>>[/color]
>
>[/color]

[Cor Ligthert [MVP]]

Re: DataReader and ExecuteNonQuery

Stephen,

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

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

I hope this helps

Cor

"Stephen Costanzo" <sxcostanzo@hot mail.com> schreef in bericht
news:efv4bbpyFH A.3812@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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]" <notmyfirstname @planet.nl> wrote in message
> news:u8UJjRpyFH A.1856@TK2MSFTN GP12.phx.gbl...[color=green]
>> Sephen
>>
>> In Net 1.x is only one open connection at the same time allowed.
>>
>> I hope this helps,
>>
>> Cor
>>
>> "Stephen Costanzo" <sxcostanzo@hot mail.com> schreef in bericht
>> news:OvpIXIpyFH A.3952@TK2MSFTN GP10.phx.gbl...[color=darkred]
>>> 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.Exec uteNonQuery 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.Exec uteReader
>>> 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.
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

[Stephen Costanzo]

Re: DataReader and ExecuteNonQuery

very cool.

Thanks

"Cor Ligthert [MVP]" <notmyfirstname @planet.nl> wrote in message
news:ek7p%23xpy FHA.2076@TK2MSF TNGP14.phx.gbl. ..[color=blue]
> Stephen,
>
> Probably is this the information you are looking for.
> Have a look at Mars(datareader s) and connection pooling.
>
> http://msdn2.microsoft.com/en-us/library/ex6y04yf
>
> I hope this helps
>
> Cor
>
> "Stephen Costanzo" <sxcostanzo@hot mail.com> schreef in bericht
> news:efv4bbpyFH A.3812@TK2MSFTN GP09.phx.gbl...[color=green]
>> 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]" <notmyfirstname @planet.nl> wrote in message
>> news:u8UJjRpyFH A.1856@TK2MSFTN GP12.phx.gbl...[color=darkred]
>>> Sephen
>>>
>>> In Net 1.x is only one open connection at the same time allowed.
>>>
>>> I hope this helps,
>>>
>>> Cor
>>>
>>> "Stephen Costanzo" <sxcostanzo@hot mail.com> schreef in bericht
>>> news:OvpIXIpyFH A.3952@TK2MSFTN GP10.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.Exec uteNonQuery 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.Exec uteReader
>>>> 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.
>>>>
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

[Kerry Moorman]

Re: DataReader and ExecuteNonQuery

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:
[color=blue]
> Sephen
>
> In Net 1.x is only one open connection at the same time allowed.
>
> I hope this helps,
>
> Cor
>[/color]

[Kerry Moorman]

RE: DataReader and ExecuteNonQuery

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:
[color=blue]
> 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.Exec uteNonQuery 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.Exec uteReader
> 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.
>
>
>[/color]