How to Find the Number of Rows in an SQL Data Reader

Written by robmcclanahan
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Find the Number of Rows in an SQL Data Reader
(deep in database image by .shock from Fotolia.com)

In the .Net framework, the SQL Data Reader is a lightweight forward-only stream from SQL Server. This means that after reading row 1 of the data set and advancing to row 2 you will not be able to return to row 1. This is a limitation for developers, but the trade-off is that the SQL Data Reader is very efficient and lightweight.

Skill level:
Easy

Other People Are Reading

Things you need

  • SQL Server
  • Website

Show MoreHide

Instructions

  1. 1

    Query the database and return an SQLDataReader object from the query.

  2. 2

    Iterate the SQLDataReader to get the total number of rows. The SQLDataReader is forward-only, so you will also need to consume the data in this loop. Here is a code sample for iterating the SQLDataReader:

    Dim i As Integer

            While sqlDataReader.Read
    
                'Consume data here if needed
    
                i += 1
    
            End While
    
    
    
            Response.Write("Total Rows: " & i)
    
  3. 3

    Close the SQLDataReader when you are done with it by calling sqlDataReader.Close()

Tips and warnings

  • If you only need a count you can execute a query with just "count(1)" in the select clause, then execute the query as Command.ExecuteScalar to return the count.

Don't Miss

References

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.