Multiple Datareaders On One Connect

[Bernie Hunt]

OK, I promise no more questions after this one today!

My application is reading data out of an ODBC database for creating
reports. I'm using Datareaders because they are lighter weight and I only
need to read through the data once.

My problem is that as I run through the records in the data reader, I
need to open a second set of records depending on what I find in the
first record, example.

Open recordset1
Loop through recordset1
process some fields
Open recordset2
Loop through recordset2
process some fields
move to next record in recordset2
Loop
Close recordset2
move to next record in recordset1
Loop
Close recordset2

Can I have two Datareaders open on the same Dataconnection? I haven't
been able to find any reference to this. So I suspect it violates the
intended use of Datareaders, meaning short term usage. The code gives me
an error that the connection is already open, which is it. I can't close
the connection because the first recordset is still in process.

Can this be done with Datareaders or do I need to go to a Dataset? I'd
like to stay with Datareaders for now.

Thanks,
Bernie

 

[Marina Levit [MVP]]

In 1.1, only one datareader can be open per connection. So here, you would
either need to not use them, or open up a second connection.

I remember reading that in 2.0 you can have more then one datareader per
connection, but I haven't used this myself yet.

 

[Sahil Malik [MVP C#]]

Bernie,

Since you say "Recordset" I'm confused - are you talking about ADO Classic?
But for now I'll assume that you're talking about ADO.NET

So to answer your Q - yes you can have two datareaders open on the same
ODBCConnection - BUT - it's all farce. Under the scenes ODBC will open two
seperate connections for you. To make this work, you just have to add a
keyword (Multiple Connections=true .. I think, check in my book for the
exact keyword, or MSDN online).

For multiple reasons this is not recommended however. So you're almost
better off creating a new ODBCCOnnection.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

 

[Bernie Hunt]

Since you say "Recordset" I'm confused - are you talking about ADO

Classic?

Sorry, I was trying to use a generic term so I didn't cap the recordset. I
should have said Set of Records to make it more generic.

I'll go with the multiple connections.

Thanks,
Bernie

 

[Cor Ligthert [MVP]]

Bernie,

Sorry, I was trying to use a generic term so I didn't cap the recordset. I
should have said Set of Records to make it more generic.

ResultSet

Cor