Navigation

	Home Page
	Index
	All Pages

Quick Search

Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Discuss (0) History

Paging Data and the PageSelector Control

RSS

Modified on Wed, Dec 14, 2011, 2:04 PM by Administrator Categorized as ASP·NET Web Forms, Class Library, SQL Server

This page is part of the Class Library Pages collection.
Click the icon to see the index.

Overview¶

When presenting large volumes of data to a user, a common expectation is that the data will be divided into "pages" to reduce the load time. For example, when performing a search that returns thousands or even millions of rows, it would take much too long to load all the rows at once. Instead, the user would prefer to see a list of them, say 10 or 25 at a time. This article outlines a means of doing so by first modifying the stored procedure that generates the data, then by using an ASP.NET user control to allow the user to select a page of data.

Sample Screen Shot of the PageSelector control

Sample Implementation¶

Database Code¶

(1) Add two parameters to your stored procedure.

{copytext|params}

    ,@PageNumber    bigint
    ,@LinesPerPage  bigint

(2) Include the following code before your SQL statement

{copytext|calcs}

declare
     @MinRow bigint
    ,@MaxRow bigint

select
     @MaxRow = @PageNumber * @LinesPerPage
    ,@MinRow = @MaxRow - @LinesPerPage + 1

(3) Modify your SQL statement to include a RowNumber column, which is based upon the row_number() over (order by ...) construct and adding a condition to the WHERE based on RowNumber.

Old SQL Statement	New SQL Statement
select * from ...	with MyData as ( select * ,RowNumber = row_number() over (order by ...) from ... ) select * ,MaxRow = (select max(RowNumber) from MyData) from MyData where RowNumber between @MinRow and @MaxRow

Old SQL Statement

New SQL Statement

select
    *
from
    ...

with MyData as (
    select 
         *
        ,RowNumber = row_number() over (order by ...)

    from
        ...
    )
select
     *
    ,MaxRow = (select max(RowNumber) from MyData)
from
    MyData
where
    RowNumber between @MinRow and @MaxRow

Web.Config Code¶

1. To use this control, follow the instructions in this Consuming Custom Controls in ASP.NET article.

2. Add an entry under appSettings called LinesPerPage. Set this value to the number of search results per data page.

ASPX Markup¶

<abc:PageSelector runat="server" ID="uxPageSelector" 
    OnPageNumberChanged="uxPageSelector_PageNumberChanged" />

Code-Behind¶

1. Copy the code below into your code-behind page

VB.NET¶

{copytext|VbCodeBehind2}

Protected Sub uxPageSelector_PageNumberChanged(ByVal sender as Object, ByVal e as EventArgs)

    uxGridView.Requery()

End Sub

Private ReadOnly Property PageNumber() As Long
    Get
        Dim result As Long = 1
        If uxPageSelector.Visible Then
            result = uxPageSelector.PageNumber
        End If
        If result <= 0 Then
            result = 1
        End If
        Return result
    End Get
End Property

Private ReadOnly Property LinesPerPage() As Long
    Get
        Dim result As Integer
        If Not Long.TryParse(ConfigurationManager.AppSettings("LinesPerPage"), result) Then
            result = 100
        End If
        Return result
    End Get
End Property

2. Adjust your database call to include the parameters for PageNumber and LinesPerPage.

3. In the code immediately after you query the database, add the following code. The variable t is the DataTable object holding your query results.

VB.NET¶

{copytext|VbCodeBehind}

If t IsNot Nothing AndAlso t.Rows.Count > 0 Then

    Dim row As MyDataSet.MyRow = CType(t.Rows(0), MyDataSet.MyRow)
    uxPageSelector.InitByRowCount(row.MaxRow, Me.LinesPerPage)

End If

C#¶

{copytext|CsCodeBehind}

if (t != null && t.Rows.Count > 0)
{
    MyDataSet.MyRow row = t.Rows[0] as MyDataSet.MyRow;
    uxPageSelector.InitByRowCount(row.MaxRow, this.LinesPerPage);
}

4. In the code where you refresh your grid (e.g., in the uxSearchButton_Click event handler), add a line: uxPageSelector.Reset() — before you requery the grid!

C#¶

{copytext|CsCodeBehind2}

protected void uxPageSelector_PageNumberChanged(object sender, EventArgs e)
{
    uxGridView.Requery()
}
private long PageNumber
{
    get 
    {
        long result = 1;
        if (uxPageSelector.Visible)
            result = uxPageSelector.PageNumber;
        if (result <= 0)
            result = 1;
        return result;
    }
}
private long LinesPerPage
{
    get
    {
        long result;
        if (!long.TryParse(ConfigurationManager.AppSettings["LinesPerPage"], out result))   
            result = 100;
        return result;
    }
}

Source Code¶

ASPX Markup¶

<%@ Control Language="VB" AutoEventWireup="false" CodeFile="PageSelector.ascx.vb" 
Inherits="AcmeBroomCompany.PageSelector" %>

Showing Page <asp:DropDownList runat="server" AutoPostBack="true" ID="uxPageDropDown" /> 
(Items <asp:Label runat="server" ID="uxItemMinLabel" /> - <asp:Label runat="server" ID="uxItemMaxLabel" /> of 
<asp:Label ID="uxItemCountLabel" runat="server"></asp:Label>)
&nbsp;
<asp:LinkButton runat="server" ID="uxFirstLinkButton" Text="<< First " />
&nbsp;
<asp:LinkButton runat="server" ID="uxPrevLinkButton" Text="< Prev " />
&nbsp;
<asp:LinkButton runat="server" ID="uxNextLinkButton" Text="Next >" />
&nbsp;
<asp:LinkButton runat="server" ID="uxLastLinkButton" Text="Last >>" />

<asp:HiddenField runat="server" ID="uxPageCountField" Value="0" />
<asp:HiddenField runat="server" ID="uxPageNumberField" Value="0" />
<asp:HiddenField runat="server" ID="uxItemsPerPageField" Value="1" />
<asp:HiddenField runat="server" ID="uxItemCountField" Value="0" />
<asp:HiddenField runat="server" ID="uxResetField" Value="N" />

VB.NET¶

Namespace AcmeBroomCompany

    Partial Class PageSelector
        Inherits System.Web.UI.UserControl
        Public Event PageNumberChanged()

        '== Constructor and Public Members ========================================================
        Public Sub New()
            Me.Visible = False
        End Sub
        Public Sub InitByRowCount(ByVal itemCount As Long, ByVal itemsPerPage As Long)

            Me.ItemsPerPage = itemsPerPage
            Me.ItemCount = itemCount

            Dim pageCount As Long = Math.Truncate(CType(itemCount / itemsPerPage, Decimal))

            If itemCount Mod itemsPerPage > 0 Then
                pageCount += 1
            End If

            If uxResetField.Value = "Y" Then
                uxResetField.Value = "N"
                SetPageNumberAndCount(1, pageCount)
            End If

        End Sub
        Public ReadOnly Property PageNumber() As Int64
            Get
                Return Convert.ToInt64(uxPageNumberField.Value)
            End Get
        End Property
        Public ReadOnly Property PageCount() As Int64
            Get
                Return Convert.ToInt64(uxPageCountField.Value)
            End Get
        End Property
        Public Sub Reset()
            uxResetField.Value = "Y"
            uxPageNumberField.Value = "1"
        End Sub

        '== Private Members =======================================================================
        Private Sub InitControls()

            Try

                If Me.PageCount <= 1 Then

                    Me.Visible = False

                Else

                    Me.Visible = True
                    Dim pageNumber As Long = Me.PageNumber
                    Dim items As ListItemCollection = uxPageDropDown.Items
                    items.Clear()

                    Dim imax As Long = PageCount
                    Dim p As Long = pageNumber

                    For i As Long = 1 To imax
                        items.Add(New ListItem(i.ToString(), i.ToString()))
                    Next

                    Try
                        uxPageDropDown.SelectedValue = pageNumber
                    Catch ex As Exception
                        Throw ex
                    End Try

                    uxNextLinkButton.Visible = (pageNumber < PageCount)
                    uxPrevLinkButton.Visible = (pageNumber > 1)
                    uxLastLinkButton.Visible = uxNextLinkButton.Visible
                    uxFirstLinkButton.Visible = uxPrevLinkButton.Visible
                    uxItemCountLabel.Text = Me.ItemCount.ToString("#,##0")
                    Dim itemMax As Long = Math.Min(pageNumber * Me.ItemsPerPage, Me.ItemCount)
                    uxItemMaxLabel.Text = itemMax.ToString("#,##0")
                    Dim itemMin As Long = (pageNumber - 1) * Me.ItemsPerPage + 1
                    uxItemMinLabel.Text = itemMin.ToString("#,##0")

                End If

            Catch ex As Exception
                Throw ex
            End Try

        End Sub
        Private Property ItemCount() As Long
            Get
                Return Convert.ToInt64(uxItemCountField.Value)
            End Get
            Set(ByVal value As Long)
                uxItemCountField.Value = value.ToString()
            End Set
        End Property
        Private Property ItemsPerPage() As Long
            Get
                Return Convert.ToInt64(uxItemsPerPageField.Value)
            End Get
            Set(ByVal value As Long)
                uxItemsPerPageField.Value = value.ToString()
            End Set
        End Property
        Private Sub SetPageCount(ByVal value As Int64)

            uxPageCountField.Value = value.ToString()
            InitControls()

        End Sub
        Private Sub SetPageNumber(ByVal value As Int64)

            uxPageNumberField.Value = value.ToString()
            InitControls()

        End Sub
        Private Sub SetPageNumberAndCount(ByVal pageNumber As Int64, ByVal pageCount As Int64)

            uxPageNumberField.Value = pageNumber.ToString()
            uxPageCountField.Value = pageCount.ToString()
            InitControls()

        End Sub

        '== Event Handlers ========================================================================
        Protected Sub uxNextLinkButton_Click(ByVal sender As Object, ByVal e As _
        EventArgs) Handles uxNextLinkButton.Click

            SetPageNumber(PageNumber + 1)
            RaiseEvent PageNumberChanged()

        End Sub
        Protected Sub uxFirstLinkButton_Click(ByVal sender As Object, ByVal e As _
        EventArgs) Handles uxFirstLinkButton.Click

            SetPageNumber(1)
            RaiseEvent PageNumberChanged()

        End Sub
        Protected Sub uxLastLinkButton_Click(ByVal sender As Object, ByVal e As _
        EventArgs) Handles uxLastLinkButton.Click

            SetPageNumber(PageCount)
            RaiseEvent PageNumberChanged()

        End Sub
        Protected Sub uxPageDropDown_SelectedIndexChanged(ByVal sender As Object, ByVal e As _
        EventArgs) Handles uxPageDropDown.SelectedIndexChanged

            Dim selectedPage As Long = Convert.ToInt64(uxPageDropDown.SelectedValue)

            If selectedPage <> PageNumber Then
                SetPageNumber(selectedPage)
                RaiseEvent PageNumberChanged()
            End If

        End Sub
        Protected Sub uxPrevLinkButton_Click(ByVal sender As Object, ByVal e As _
        EventArgs) Handles uxPrevLinkButton.Click

            SetPageNumber(PageNumber - 1)
            RaiseEvent PageNumberChanged()

        End Sub

    End Class

End Namespace

C#¶

TODO

Jasinski Technical Wiki

Navigation

Contributor Links

Other Wiki Sections

Paging Data and the PageSelector Control

Overview¶

Sample Implementation¶

Database Code¶

Web.Config Code¶

ASPX Markup¶

Code-Behind¶

VB.NET¶

VB.NET¶

C#¶

C#¶

Source Code¶

ASPX Markup¶

VB.NET¶

C#¶