0

I use a ADO DataReader to get rows from a sql server. As DataReader has no record count I need to get the record count myself. So in my case I want to place the original sql statement, which is passed to the class dynamic, in a subquery.

Example

SELECT 
      id, 
      title, 
      value 
FROM table 
WHERE id = 1234 
ORDER BY title

should be 

SELECT COUNT(*) 
   FROM (
         SELECT id, 
               title,
               value 
        FROM table 
        WHERE id = 1234 
        ORDER BY title)t

In this case we need to modify the SQL statement because the syntax demands a TOP 100 PERCENT added to work propertly:

SELECT COUNT(*) 
FROM (SELECT TOP 100 PERCENT id, 
                     title, 
                     value 
              FROM table 
              WHERE id = 1234 
              ORDER BY title)t

So I did this:

int fromIndex = this.SQL.IndexOf("SELECT", 
                     StringComparison.CurrentCultureIgnoreCase);
String countSql = "SELECT TOP 100 PERCENT" + 
                   this.SQL.Substring(fromIndex + 6);
countSql = "SELECT COUNT(*) FROM (" + countSql + ")t";

But what if the original statement already has a TOP clause like

SELECT TOP 5 id, 
             title, 
             value 
 FROM table 
 WHERE id = 1234 
 ORDER BY title

or even worse

SELECT   TOP   5  
               id, 
               title, 
               value 
     FROM table 
     WHERE id = 1234 
     ORDER BY title

So my code needs to handle this.

BTW: If someone has a far more better approach to make a record count from any SQL statement this answer is welcomed as well!

EDIT To clarify my question. The link How to get number of rows using SqlDataReader in C# explains how to do it. I read that. My question is how to handle the incoming SQL statement to add TOP 100 PERCENT clause or not.

Community
  • 1
  • 1
YvesR
  • 5,922
  • 6
  • 43
  • 70
  • 1
    add `select @@rowcount` after the first `select`, you can `.NextResult` to read it – Alex K. May 21 '15 at 14:57
  • Take a look at http://stackoverflow.com/questions/1383315/how-to-get-number-of-rows-using-sqldatareader-in-c-sharp – dansasu11 May 21 '15 at 14:59
  • I tried this answer. => run a specialized SELECT COUNT(*) before is exactly what I tried to do and as the sql statement is dynamic I have to handle this. @@rowcount did not give me the exact count in multi query/multi user system... – YvesR May 21 '15 at 16:02

2 Answers2

1

Just to clarify: the query is something that is dynamically passed in? And then you want to know how many rows were read by it? I believe as one comment suggests; you could add "select @@rowcount" to the end of the passed-in query; then back in C# use .NextResult to jump from the query results to your row count result.

johnjps111
  • 1,160
  • 9
  • 24
  • Is it 100% safe that @@rowcount represents the numbers from the sql statement regardless how many parallel statements are fired by many users? – YvesR May 21 '15 at 15:59
  • I believe so; it should get @@rowcount only for your default transaction; anything else going on should be irrelevant. – johnjps111 May 21 '15 at 16:17
0

DataReader may not have a row count, but you can deduce it by calling Read() while parsing the result set into your data model.

Also, your code is vulnerable to SQL Injection attacks. You should rather use SqlCommand and add SqlParameter objects for your dynamic value parameters in place of string concatenation.

Here is a basic example of using Read() and SqlCommand class : MSDN - SqlDataReader.Read

toadflakz
  • 7,764
  • 1
  • 27
  • 40
  • Code is not vulnerable as the sql statement passed is already set with methods like you mentioned. this.SQL is just internal use. Just count won't work either as my original SQL statement hold e.g. 1 million records but I only fetch the first 10 rows. – YvesR May 21 '15 at 15:58