Identifying more than one record in DataReader..........

  • Thread starter Thread starter Gill Smith
  • Start date Start date
G

Gill Smith

I am populating the data to SQL Datareader from a stored procedure which has
query result.
I am using Data Reader HasRows to indetify whether records exist in data
reader or not.
How to find out whether more than one record exist in the record set or not.
My application flow will change if the number of records are more than one.
I am using .Net 1.1.
Thanks for your suggestions.
Gill
 
You can either batch the queries, putting SELECT COUNT(*) before the current
query you are using (you cna advance to the next queryset by using
..NextResult). This however is inefficient b/c you are firing two queries for
thesame data, one of which is an aggregate.

The second method is looping through the datareader. There's no way to know
in advance with a reader until you've walked through it.

If you must know it in advance, can't use the first method b/c performance
makes it impractical and can't wait until you walk through the reader, use a
DataTable and check its Rows.Count property to determine how many came back,

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
 
Thanks
Gish

William Ryan eMVP said:
You can either batch the queries, putting SELECT COUNT(*) before the current
query you are using (you cna advance to the next queryset by using
.NextResult). This however is inefficient b/c you are firing two queries for
thesame data, one of which is an aggregate.

The second method is looping through the datareader. There's no way to know
in advance with a reader until you've walked through it.

If you must know it in advance, can't use the first method b/c performance
makes it impractical and can't wait until you walk through the reader, use a
DataTable and check its Rows.Count property to determine how many came back,

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Click to expand...
 
Bill's answer is correct, but if I may I'd like to offer a variation on a
theme. Instead of using a DataReader and walking through the records (which
would require either two loops - one to move to the next record and an inner
loop to see if that record can be found elsewhere - or some other method of
tracking found records), run the query through a DataAdapter to populate a
DataTable in a DataSet. With that done you can walk through the records in
the DataTable just as with the reader but at each record apply filters and
use views to see if that record can be found more than once. This really
defers the work of finding the duplicates to the .NET framework instead of
putting the onus on you to do it.

I'm curious though - what kind of database design do you have that would
return duplicates, and what kind of application flow do you have when
duplicates are found ?
 
what was the thought process in removing the ado recordcount property? What is the ADO.NET equivelent?
 
The query is simple select statement which returs all the records in the
table. There is no duplicate data here. It is designed for 3rd
normalization!!!
My requiremnet is if the search results in more than one record, I would
like to stop the search here and prompt the user to use different search
window to do the search.
Did I make things clear ?
Gish
 
ADO.NET isn't ADO and there's very little in common other than the letters
ADO, a command object and a connection object. ADo.NET works in two modes,
connected mode (exemplified by Datareaders and the command.Executexxx
commands). Disconnected mode is exemplified w/ DataSets/
DataAdapters/dataViews etc.

There's not currently an equivalent of a recordset. A datareader is the
closest thing I can think of but there's more differences than similarities.
You don't know how many values are retrieved until you walk through it. If
you use disconnected objects you no longer have a connection once you get
the object back, so you will know how many records you have by using
DataTable.Rows.Count.

HTH,

Bill
--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
chris said:
what was the thought process in removing the ado recordcount property?
Click to expand...
What is the ADO.NET equivelent?
 
Gil, the bottom line is that if you must know this b/c of the application
logic, then you need to use a DataTable/DataAdapter and check the Rows.Count
property , then use this info to do whatever else you wanted, or you need to
walk through the Reader. The only way you can get there otherwise with a
reader is appending a Select COUNT(*) statement with the same where clause
as the original query which is totally inefficient and would probably
consume more resources than the DataAdapter approach. IF you opt for this
though, you can use NextResult method of the reader after you get the count
to do the dr.Read stuff.

Other than that you'll simply have to walk the reader to know the count.
Even if you use output parameters you'll need to process everything so one
way or the other, if you use the connected objects you are either going to
use two queries or you are going to have to make your decision on the fly.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
 
thank you

William Ryan eMVP said:
Gil, the bottom line is that if you must know this b/c of the application
logic, then you need to use a DataTable/DataAdapter and check the Rows.Count
property , then use this info to do whatever else you wanted, or you need to
walk through the Reader. The only way you can get there otherwise with a
reader is appending a Select COUNT(*) statement with the same where clause
as the original query which is totally inefficient and would probably
consume more resources than the DataAdapter approach. IF you opt for this
though, you can use NextResult method of the reader after you get the count
to do the dr.Read stuff.

Other than that you'll simply have to walk the reader to know the count.
Even if you use output parameters you'll need to process everything so one
way or the other, if you use the connected objects you are either going to
use two queries or you are going to have to make your decision on the fly.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
on populate records instead
Click to expand...
 
Back
Top