DataReader question

  • Thread starter Thread starter Gita George
  • Start date Start date
G

Gita George

How can I find the number of records returned by a Sql Server SELECT
statement?

Thanks ...
 
With a DataReader, you have to get to the end of the read before you can get
a count. You can fudge this by adding another SQL Statement that returns
count in front of the recordset you are going to bind.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
The problem with this (Count(*)) approach is that it causes twice the
processing--once to count and another query to fetch the rows. Why do you
need to know before the rows arrive? If you use a DataAdapter Fill, you'll
know right away.

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.
__________________________________
 
A DataReader provides ReadOnly ForwardOnly CONNECTED access to your data.
Therefore you need to read all the records into it before it could know how
many it has. You could set up your own counter and increment it manually as
you loop through the records, then you'd only need one loop and one pass
through the data.

A DataAdapter.Fill will populate a DataSet for you and give you a COPY of
the original data all in the one step.
 
Ah... no. While a DataReader must be connected while returning the rowset,
it's not any more "connected" than the DataAdapter Fill (which uses a
DataReader behind the scenes). Both return a copy of the row on the server.
Even a static server-side cursor returns a copy of the row on the server.
Yes, you can get a count in the manner described, but I expect he/she wanted
to know how many rows qualified for the WHERE.



--
____________________________________
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.
__________________________________
 
The problem here is the DataReader. I agree that a Fill(), which is a
DataAdapter routine to fill a DataTable in a DataSet is a much better option
in most cases. But, that was not the question asked.

Overall, I would recommend everyone get used to DataSets, which drives a
co-worker nuts. I say this, as Whidbey is going to force people, more or
less, to adopt this methodology. And, since it fits well under the bar, and
is easy to maintain, it is a good idea.

But, the question was about the DataReader. As the DataReader is firehose
cursor, there is no way to know without a dual query. The method I suggested
(tieing two commands) is the lightest way to do this.

Consider an analogy of a gas station:

If you pull up to the gas station and the someone asks you "how much gas did
you purchase," you would think he was crazy, as you had not pumped yet. You
would not know until you dispensed the gas. On the other side, you could
prepay the clerk and you would know, but that is another action.

A DataReader is much like the gas transaction. Unless you query records
first, you do not know. With a DataSet, however, the transaction is complete
as soon as you call fill, although it may not seem so, as you have not bound
the data. This is due to the difference between connected (DataReader) and
disconnected (DataSet) data objects.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
Technically, behind the scenes, you are correct. But, from the view of the
person programming, they are very different. When you work with the
DataReader, you can only get a count of records at the end (at least an
accurate count, as I see no value of a COUNT without the WHERE). In the
DataSet, by the time you query, the fast forward cursor fill has already
been completed (thus, giving a COUNT with the WHERE). This is what is of
value.

Now, if he simply wants an arbitrary count, which is the technicality we are
arguing here, you are 100% on the money. I do not, however, see how this
technicality helps Gita with the problem at hand. I may be incorrect in
assuming that Gita is looking for something more than an arbitrary number,
so the number of rows, with the WHERE applied, would be the proper
assumption. If someone can show me a real world application where the
arbitrary number works, I would be glad to conceed, as I feel this is a
rather inane argument.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
Back
Top