Multiple DataReaders on one Connection, is that possible? SQL Server2000

  • Thread starter Thread starter Lars Siden
  • Start date Start date
L

Lars Siden

Hi,

The subject says it all. Using Sybase I could configure my driver to allow
multiple open cursors on one connection object(sacrificing speed), do I have
that option on SQL Server 2000 as well?

Basically I'd like to run this code:

dbCmd1 = new SqlCommand("Select person_id from activities", dbConn );
dbCmd2 = new SqlCommand("Select address_id from address", dbConn );
dbConn.Open();
dbReader1 = dbCmd1.ExecuteReader();
dbReader2 = dbCmd2.ExecuteReader();
....do some stuff
dbReader1.Close();
dbReader2.Close();
dbConn.Close();

All input is appreciated!

Best regards,

Lars Siden
 
Hi Lars,

No, it is not possible yet.
Why don't you create two connections?
Remember, that connections are/can be pooled.
 
Any thoughts on the fact that IDataRecord (included with IDataReader)
GetData returns an IDataReader? How would you read the remote
structured data and then advance to the next row in the first reader?

I guess I should admit that I'm trying to implement a provider for a
DBMS that supports SQL3 types.

-Chris
 
Hi Lars,

Based on my understanding, you need to open multiple SqlDataReaders on one
connection simultaneously. However, that is impossible.

According to MSDN, while the SqlDataReader is in use, the associated
SqlConnection is busy serving the SqlDataReader, and no other operations
can be performed on the SqlConnection other than closing it. This is the
case until the Close method of the SqlDataReader is called. If you open
another SqlDataReader from the same SqlConnection when one is already open,
an InvalidOperationException with error message "There is already an open
DataReader associated with this Connection which must be closed first."
will be thrown. This is by design.

So if you need to open multiple SqlDataReaders, please try to open them
using another SqlConnection. As Miha mentioned in his post, connections
are/can be pooled. Please also refer to the following link for more
information:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqldatareaderclasstopic.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hello Kevin,

Thanks for your informative answer!

The problem I got is that we have some old c++/com+ code ( a batch program )
that is made to work with Sybase. Now we want to migrate the code as simple
as possible to:

1. If possible within timelimit, c# ado.net 2. else, c++/com+ using SQL
Server 2000

As far as I understand, the problem with having 2 connections is the
transaction control.

If we just could wipe all old code and make a new c# program, that wouldn't
be any problem, because then I could replace one of the datareaders with a
DataSet that I fill first, then loop through the Dataset making changes with
the single datareader.

It looks like the soloution will be to use a Vector-Array in C++ ( emulating
the Dataset ).

Best regards,

Lars Siden





"Kevin Yu [MSFT]" <[email protected]> skrev i meddelandet
Hi Lars,

Based on my understanding, you need to open multiple SqlDataReaders on

connection simultaneously. However, that is impossible.

According to MSDN, while the SqlDataReader is in use, the associated
Click to expand...
SqlConnection is busy serving the SqlDataReader, and no other
Click to expand...
operations can be performed on the SqlConnection other than closing
Click to expand...
it. This is the case until the Close method of the SqlDataReader is
Click to expand...
called. If you open another SqlDataReader from the same SqlConnection
Click to expand...
when one is already
open,

an InvalidOperationException with error message "There is already an
Click to expand...
open DataReader associated with this Connection which must be closed first."

will be thrown. This is by design.

So if you need to open multiple SqlDataReaders, please try to open
Click to expand...
them using another SqlConnection. As Miha mentioned in his post,
Click to expand...
 
Hi Lars,

We can use an Vector-Array to represent the rows to emulate the DataSet in
C++. This can be done if you just want to go through to browse the records
returned from the database. However, there are also some problem doing so:

1. Schema information cannot be obtained.
2. Updating the database is not as convenient as the DataSet.
3. An Vector-Array can be only applied to a single table without
relationship with other tables.

Hence, my suggestion is to use the DataSet, becuase it is much more
powerful than a single Vector-Array. Or could you just use another
connection to open a DataReader?

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top