Getting Number Of Rows returned from query

C

Chris Tremblay

I am trying to figure out how to go about retrieving the number of results
returned from my queries in SQL server from VB.NET without using a the
Select Count(*) query. The method that I was using was the following:

Take the query that I am executing, copy the query and turn it into a count
query, run the count query, then execute the original query.

The reason for this is so that I can implememt public paging on my website.
The problem with this method is that it is putting to much stress on my SQL
Server. Is there a way that I can find the number of rows returned. The way
I figure, if you use an SQLdatareader, it knows when it has reached the end
of the result list, therefore, you should be able to have some access to the
number of results without reading through each record.
 
K

Karl

Chris,
You won't be able to do it via the SQLDataReader without reading through the
records first (in which case you can simply increment a counter, or use the
solution I briefly outline in #2)

1 - Consider using a datatable. You'll easily be able to access the number
of rows via datatable.Rows.Count. Additionally, if your SQL Server is
struggling, you'll be able to cache the datatable and reduce the load (I
realize that you are probably doing a search in which case the results
aren't very cache friendly, but hey , I thought I'd throw it out there
anyways).

2 - You can access an output parameter after doing a dr.close, which could
be the @@RowCount SQL server automatically generates. I'd go into more
details, but since you need to have the datareader closed, I'll assume that
you'll have already read through it and could simply have used a
counter...so this probably is a no go.

Karl
 
G

Guest

You could use a counter variable like so...

SQLDataReader dr = new SQLDataReader();
int counter = 0;
// other database code goes here
while ( dr.Read() )
{
counter +=;
}
Response.Write ( "Number of records is: " + counter );

Hope this helps
 
G

Greg Burns

Unless I missing something this won't work.

If he is doing paging, he is probably only returning in the datareader a
subset (1 page) of the total records.

Greg
 
H

http://www.visual-basic-data-mining.net/forum

After you run the query, run the statement SELECT @@ROWCOUNT
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top