to get record count of SQL Query...?

Raheem MA

Hi All, I want to get the record count for a SQL Query other than count(*), because dont want to manipulate the query. I want to get the record count for any given query. Is there any method available in .net? Or is there any method available in SQLBulkCopy Class as I'm using it? For example given query is "Select x,y from z where x=l", I need to get the record count for that. Any ideas? Thanks in Advance :) Shah_tech :)

mark_w_

execute the query and look at how many rows are returned?

Nirandas

Try this:

Select count(*) from z where x=l;

Would return the number of rows which meets the where clause.

Nirandas, a developer from India. http://www.nirandas.com

Raheem MA

But, I dont want to even modify the given query. And I dont want to execute the query and get the count using loops because my query will return arount 1crore records. I just want to get the record count directly without modifying the existing query. any more ideas? Thanks in advance :) Shah_tech :)

Wendelius

Hi, Without any more info about the problem (what database, connection type etc.), it's hard to give any exact answer, but some ideas might be useful: 1. If you execute a select statement using SQLCommand (or corresponding class) and then get the row count from returned SQLDataReader class using property ReccordsAffected. You get the actual count of returned rows without any loop. 2. If you don't want to manipulate the original query, you can still construct a nested query. For example:

string originalQuery = "SELECT x,y FROM z WHERE x=l";
string countQuery = "SELECT COUNT(*) FROM (" + originalQuery ")";
SQLCommand countCommand = new SQLCommand;

countCommand.CommandText = countQuery;
...

This would result in a query which executes the original query, creates a result set which is then counted. So this means it returns only one row and one column, the record count. This solution varies between different databases, because all databases cannot handle nested result sets. 3. Create a stored procedure into the database which actually executes the statement and returns the row count. Something like this semi-code

PROCEDURE RowCount(IN statement, OUT rows)
BEGIN
EXECUTE statement;
rows = SQL%ROWCOUNT;
END;

4. If possible, ask the datatabase optimizer how many rows it thinks may be returned (using procedure). This implementation is fully database-dependent. Hope this helps, Mika