DataSet paging vs Datareader paging

Discussion in 'ASP .Net' started by =?Utf-8?B?UGF0cmljay5PLklnZQ==?=, Oct 8, 2004.

  1. 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.CloseConnection)

    '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
     
    =?Utf-8?B?UGF0cmljay5PLklnZQ==?=, Oct 8, 2004
    #1
    1. Advertising

  2. =?Utf-8?B?UGF0cmljay5PLklnZQ==?=

    Scott Allen Guest

    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.aspx?scid=kb;en-us;829142&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"
    <> 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.CloseConnection)
    >
    > '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, Oct 8, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bihn

    datareader vs. dataset

    Bihn, Mar 9, 2005, in forum: ASP .Net
    Replies:
    14
    Views:
    3,503
  2. Shapper
    Replies:
    2
    Views:
    583
    Shapper
    Apr 22, 2005
  3. Shapper
    Replies:
    2
    Views:
    438
    Shapper
    Apr 25, 2005
  4. Patreek

    Datareader, dataset, else?

    Patreek, Sep 21, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    621
    Patreek
    Sep 21, 2005
  5. Paul Aspinall

    Paging..... DataSet, DataReader or DataView??

    Paul Aspinall, Jul 19, 2003, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    247
    Paul Aspinall
    Jul 19, 2003
Loading...

Share This Page