DataSet paging vs Datareader paging

[Patrick.O.Ige]

I have been paging with DataSet using "DataGridPageChangedEventArgs" and i
guess it works with only Dataset because default paging requires that the
DataGrid be able to determine the number of total records in the
DataSource.This is possible with the DataSet, but not with the SqlDataReader..
So what are the best ways to apply paging to SqlDataReader....
For example how can i apply paging with the code below:-

THANKS AND ALL IDEAS WELCOME

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
'If Not Page.IsPostBack Then
'DataBind()
'End If

'Create a connection string
Dim connString As String =
"server=(local);database=Northwind;integrated security=true;"
'Dim ConnectionString As String =
"server=(local);database=pubs;integrated security=true;"
'Open a connection
Dim objConnection As SqlConnection
objConnection = New SqlConnection(connString)
objConnection.Open()

'Specify the SQL string
'Dim strSQL As String = "Select ParentID,FileCategory,FileCategoryID
from treetable where ParentID =1"

Dim strSQL As String = "Select* from treetable"

Dim Str As String
'Create a command object
Dim objCommand As SqlCommand
objCommand = New SqlCommand(strSQL, objConnection)

'Get a datareader
Dim objDataReader As SqlDataReader
objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseConn ection)

'Do the DataBinding
dgResults.DataSource = objDataReader
dgResults.DataBind()

'While objDataReader.Read()
'do something
'Str = objDataReader("FileCategory")
'End While

'Close the datareader/db connection
objDataReader.Close()

End Sub

[Scott Allen]

Here is one way to do it:

How to dynamically page through a large result set in ASP.NET by using
SQL Server stored procedures in Visual C# .NET
http://support.microsoft.com/default...42&Product=asp

The approach will generally involve passing pagesize and pagenumber
parameters to a SQL procedure in order to filter the records from the
database resultset.

--
Scott
http://www.OdeToCode.com/

On Thu, 7 Oct 2004 17:29:06 -0700, "Patrick.O.Ige"
<Pa*********@discussions.microsoft.com> wrote:

I have been paging with DataSet using "DataGridPageChangedEventArgs" and i
guess it works with only Dataset because default paging requires that the
DataGrid be able to determine the number of total records in the
DataSource.This is possible with the DataSet, but not with the SqlDataReader..
So what are the best ways to apply paging to SqlDataReader....
For example how can i apply paging with the code below:-

THANKS AND ALL IDEAS WELCOME

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
'If Not Page.IsPostBack Then
'DataBind()
'End If

'Create a connection string
Dim connString As String =
"server=(local);database=Northwind;integrated security=true;"
'Dim ConnectionString As String =
"server=(local);database=pubs;integrated security=true;"
'Open a connection
Dim objConnection As SqlConnection
objConnection = New SqlConnection(connString)
objConnection.Open()

'Specify the SQL string
'Dim strSQL As String = "Select ParentID,FileCategory,FileCategoryID
from treetable where ParentID =1"

Dim strSQL As String = "Select* from treetable"

Dim Str As String
'Create a command object
Dim objCommand As SqlCommand
objCommand = New SqlCommand(strSQL, objConnection)

'Get a datareader
Dim objDataReader As SqlDataReader
objDataReader =
objCommand.ExecuteReader(CommandBehavior.CloseCon nection)

'Do the DataBinding
dgResults.DataSource = objDataReader
dgResults.DataBind()

'While objDataReader.Read()
'do something
'Str = objDataReader("FileCategory")
'End While

'Close the datareader/db connection
objDataReader.Close()

End Sub