newbie, converting from ado to ado.net --> datareader help needed.

[Peter Row]

Hi,

I am converted an existing vb6 web app to vb.net and have run into a bit of
a problem with data access via ADO.NET.

I am doing a full text search against a SQL Server 2000 DB, the SP that I
call to do this returns multiple resultsets, therefore
from the documentation I found that I would have to use the SqlDataReader in
order to move from one resultset to another.

However here lies the problem. After the SP is called in the vb6 version I
used the recordsets "RecordCount" method to
create a large enough array to do some additional processing (don't ask).
But I can no longer do this with the datareader because you
read one row at a time.

So my problem is how do I find out how many records are in the current
resultset when using a datareader?

As you can imagine the search SP is not trivial and repeating the query
twice just so I can find out the recordcounts
is not acceptable.

My first guess at a solution would be to use an ArrayList and then just call
it's .Add method then I wouldn't need to worry
about array size.

Any other suggestions welcome.

Regards,
Peter

 

[Miha Markic]

However here lies the problem. After the SP is called in the vb6 version I
used the recordsets "RecordCount" method to
create a large enough array to do some additional processing (don't ask).
But I can no longer do this with the datareader because you
read one row at a time.

So my problem is how do I find out how many records are in the current
resultset when using a datareader?

As you can imagine the search SP is not trivial and repeating the query
twice just so I can find out the recordcounts
is not acceptable.

My first guess at a solution would be to use an ArrayList and then just call
it's .Add method then I wouldn't need to worry
about array size.

ArrayList will do the job just fine.
You can later cast it to array if you want to.

 

[William \(Bill\) Vaughn]

Nope, you don't need to use the DataReader to get multiple resultsets. Use
the DataAdapter Fill method and point to an empty DataSet. When it's done,
you will have "n" tables--one for each resultset that contained rows. The
Ds.Tables(n).Rows.Count property has the number of rows read into each
DataTable.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Peter Row]

Hi,

Excellent! Thanks for the heads-up on that Bill.

Regards,
Pete