1

In C# using ado.net, how to know just the number of rows that may be returned by a SQL Server select stored procedure without returning the result set and without changing the stored procedure?

I don't want to read the data at all, I only want the number of rows because the load can consume a lot of memory.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yaron Amar
  • 181
  • 1
  • 14

2 Answers2

1

if u just don't want to load results, create another sql procedure that just returns select Count(*) from etc...

Nika G.
  • 2,374
  • 1
  • 17
  • 18
  • please give an example how to do that with sql server 2005 and MySP which have parameters @myPrm1,@myPrm2. thanks. – Yaron Amar Jun 29 '11 at 11:50
  • @Yaron copy the existing SP and replace all of the fields in the select list with a single Count(*) – Ben Robinson Jun 29 '11 at 11:56
  • but i wrote in my question "without changing the store procedure". – Yaron Amar Jun 29 '11 at 12:05
  • u dont have to change the existing SP, just create another with diff name (something like **ur_sp_name + _rowcount**). leave the logic as in original SP, just replace fields in select with Count(*) (as Ben Robinson suggested) – Nika G. Jun 29 '11 at 12:12
  • thanks, but it's something to be done generally, because i need that feature for all the store procedures in my project that do fetch data. – Yaron Amar Jun 29 '11 at 12:20
1

I'd originally thought that .ExecuteNonQuery() would do it. But since it doesn't work for SELECT statements, the DataReader will probably be your best (fastest) bet.

int count = 0;
using (var dr = new SqlDataReader(cmd)) {
   while (dr.Read()) count++;
}
Dave Markle
  • 95,573
  • 20
  • 147
  • 170