DataSet paging vs Datareader paging

[Patrick.O.Ige]

I have been paging with DataSet using "DataGridPageCh angedEventArgs" 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.EventArg s) 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=North wind;integrated security=true;"
'Dim ConnectionStrin g As String =
"server=(local) ;database=pubs; integrated security=true;"
'Open a connection
Dim objConnection As SqlConnection
objConnection = New SqlConnection(c onnString)
objConnection.O pen()

'Specify the SQL string
'Dim strSQL As String = "Select ParentID,FileCa tegory,FileCate goryID
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(strS QL, objConnection)

'Get a datareader
Dim objDataReader As SqlDataReader
objDataReader =
objCommand.Exec uteReader(Comma ndBehavior.Clos eConnection)

'Do the DataBinding
dgResults.DataS ource = objDataReader
dgResults.DataB ind()

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

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

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*********@di scussions.micro soft.com> wrote:

I have been paging with DataSet using "DataGridPageCh angedEventArgs" 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.Thi s 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.EventAr gs) 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=Nort hwind;integrate d security=true;"
'Dim ConnectionStrin g As String =
"server=(local );database=pubs ;integrated security=true;"
'Open a connection
Dim objConnection As SqlConnection
objConnection = New SqlConnection(c onnString)
objConnection.O pen()

'Specify the SQL string
'Dim strSQL As String = "Select ParentID,FileCa tegory,FileCate goryID
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(strS QL, objConnection)

'Get a datareader
Dim objDataReader As SqlDataReader
objDataReader =
objCommand.Exe cuteReader(Comm andBehavior.Clo seConnection)

'Do the DataBinding
dgResults.DataS ource = objDataReader
dgResults.DataB ind()

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

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

End Sub