Using DataReader to count rows

  • Thread starter Thread starter Steve Wolfie
  • Start date Start date
S

Steve Wolfie

Hello All:

First let me thank everyone on the forum(s) for all the great (and timely)
help!!!

I am writing a help desk app in asp.net that allows people to input work
orders for IT. I used to use data adapters and datasets until someone here
helped me to realize the performance advantage of using data reader. It
works great for displaying the info, but i would like to add the ability to
pull the *number* of open and closed work orders for a particular user as
such:

SELECT * FROM workorders WHERE extension = ('extension') and STATUS =
('Open')

SELECT * FROM workorders WHERE extension = ('extension') and STATUS =
('Closed')

then i want the data reader to tell me how many rows were selected. I tried
things similar to;

******************************************
While rdr.read()
a = a + 1
End While

labelOpenWorkOrders.text = a
******************************************

and I also tried


********************************************
while rdr.read()
do
a = a + 1
loop
end while

labelOpenWorkOrders.text = a
*******************************************

It never returns the actual row count.

i also tried

labelOpenWorkOrders.text = rdr.recordsaffected -- but this is only for
insert, update, delete.


HELP!!!!

Thanks

Steve
 
The loop you have there does work. I don't know what you are doing
beforehand so that it end up not working, but that technique in general will
work.

A faster technique is to do a coutn(*) in your query, to get the number of
rows, instead of tediously going through the result set. You would see the
performance much improved.

And yet another option is to use a datatable, which is an in memory copy of
the result set, and gives you instantenous access to any row in the result
set, as well as the count.
 
HELP!!!!
Click to expand...

SqlDataReaders are great for certain things, and not so great for others -
you need to decide whether to use them based on each separate requirement.

However, you can read something like this into an SqlDataReader

SELECT COUNT(*) FROM workorders WHERE extension = ('extension') and STATUS =
('Open')

SELECT (*) FROM workorders WHERE extension = ('extension') and STATUS =
('Closed')

This will create an SqlDataReader object with two resultsets:

objDataReader.Read(); // the number of 'Open' workorders
objDataReader.NextResult();
objDataReader.Read(); // the number of 'Closed' workorders

Bear in mind, though, that this is probably not the most efficient use of
your system resources, and I mention it merely as an example of what is
possible...
 
Bear in mind, though, that this is probably not the most efficient use of
your system resources, and I mention it merely as an example of what is
possible...
Click to expand...


What do you consider might be a better use of resources? I am so confused
about there seems to be 100 ways to do the same thing!!

I thought that datareader would be best for this... we do not need to work
with the data for long, so i figured that a adapter and data set would be
overkill,

what might you suggest to be a better use of resources?

Thanks

Steve
 
In a database where the members of your rowset are likely to change, the
approach you're using won't return a valid count.
If all you need is a count, simply ask (in a single SQL query in SQL
Server):

SELECT COUNT(*) FROM workorders WHERE extension = 'extension' and STATUS
= 'Open' ;
SELECT COUNT(*) FROM workorders WHERE extension = 'extension' and STATUS
= > 'Closed'

If you need the row data and a count, yes, the best approach is to use the
DataAdapter Fill method. The Ds.Tables(0).Rows.Count returns the number of
rows in the first returned rowset.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________
 
What do you consider might be a better use of resources? I am so confused
about there seems to be 100 ways to do the same thing!!
Click to expand...

See Bill's post - I haven't really got anything to add to that...
 
Hi Steve,

The datareader is a read-only, forward-only stream of data from a database.
You mentioned that the
While reader.Read()
a = a + 1
End While
Doesn’t return actual row count (It supposes to count total records). I just
assume you take two steps in your process, retrieve data then count records.
If it’s true, of course you cannot get row count. Because the datareader is a
forward-only stream, after the first process loop, it goes to end of record
already. Hence you should put data processing and record counting in one loop:

While reader.Read()
‘ process data
a = a + 1
End While

Then you can get total records.

You can also think Bill and other people’s suggestion.


HTH

Elton Wang
(e-mail address removed)
 
Hi Steve,

I agree with Bill's advice to use SELECT COUNT(*) to get the count of
records. In addition, besides filling the result to a DataSet using
DataAdapter.Fill, we can also use SqlCommand.ExecuteScalar to get the
result, it returns the object in the first column of the first record. The
return value of ExecuteScalar is an object, we then just cast it to int.

For more information about SqlCommand.ExecuteScalar(), please check the
following link:

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

HTH.

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