Paging in ASP.Net/VB.Net using DataReader

Although I posted a long article comparing between many methods of Paging in ASP and ASP.Net, I wanted to highlight more on this method of paging using DataReader cause you won't see out there very often.. although it is simpler than SQL Paging methods and it is slightly faster than usual DataSet paging method..
It was inspired by the article [How do I page through a Recordset?] which Shows & compares between paging methods for classic ASP.

The function do paging by fetching the Top PageSize * PageIndex Records and then it loops to move the start record of current Page index..

' Paging Params (1-based)
Dim PageSize As Integer = 10
Dim PageIndex As Integer = 3
' Create+Open Conn on SQL Server 2005 Express
Dim Conn As New SqlConnection("Data Source=127.0.0.1\sqlexpress,1433;Initial Catalog=DB;Persist Security Info=False;User ID=user;Password=pass;Network Library=dbmssocn")
Conn.Open()
'Create SQL Command
Dim Cmd As New SqlCommand()
Cmd.Connection = Conn
'Execute Count Query To calculate PageCount
Cmd.CommandText = "SELECT COUNT(*) FROM users where status=1"
Dim Count As Integer = Cmd.ExecuteScalar()
Dim PageCount As Integer = Math.Ceiling(Count / PageSize)
'Query Records
'If you forget to add 'Top PageIndex * PageSize', Performance degrades badly in large tables
Cmd.CommandText = "SELECT Top " & (PageIndex * PageSize) & " * FROM users where status=1"
Dim rdr As SqlDataReader = Cmd.ExecuteReader()

'Move Reader to start record
For I As Integer = 0 To ((PageIndex - 1) * PageSize) - 1
  If Not rdr.Read Then Exit For
Next
'Loop the desired Records
'if you didn't Add Top clause you will notice that all records in table are fetched!!
While rdr.Read()
  'Do Something
End While
rdr.Close()
rdr = Nothing
Conn.Close()
Conn = Nothing
Cmd = Nothing

*If you forget to add Top (PageIndex * PageSize), Performance degrades badly in large tables

Tags: ADO.Net , ASP.Net , Paging , SQL , VB.Net