Customize Listing and Paging in your ASP.NET Page

• Download source code - 97.2 KB

Introduction

This example shows how to use the SQL Server 2005 ROW_NUMBER function for paging, and gives a better look by customizing the HTML in the code-behind.

Using the code

First, we have to select the records to list. In normal paging techniques, we take all the records into a dataset and then using a PagedDataSource to show the appropriate page. The main disadvantage of this technique is that we are storing all the records in memory to just show a few records in one page. Each time you click the page number, it will select all the records. To overcome this headache, we can use the ROW_NUMBER function of SQL Server 2005 effectively.

The snippet below shows the Stored Procedure to select the specific records; we are passing two parameters:

• @intCurrentPage int - This parameter is used to select the records of a page.
• @intRecordPerPage int - This parameter is used to get the number of records to show in one page.

For example, if we pass 1 as @intCurrentPage and 10 as @intRecordPerPage, then the result will be records from 1 to 10. This Stored Procedure connects to the AdventureWorks database and selects records from the Production.Product table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Johnson
-- Create date: 24-12-2009
-- Description: To get specific records from product table 
--       of AdventureWorks database.
-- =============================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[uspFetchCustomer]
@intCurrentPage int,
@intRecordPerPage int
AS
BEGIN
    SET NOCOUNT ON;

    --***Find the starting record id and ending record id ****
    declare @intStartRec int
    set @intStartRec=(@intCurrentPage*@intRecordPerPage)-@intRecordPerPage
    declare @intEndRec int
    set @intEndRec=(@intCurrentPage*@intRecordPerPage)+1    
    --********************************************************

    Begin
        --***Select the records from @intStartRec to @intEndRec ***
        Select * from
        (
            select ROW_NUMBER() OVER (ORDER BY ProductID) as SlNo,ProductID,
            [Name],ProductNumber,Color
            from Production.Product
        ) Temp
        Where SlNo>@intStartRec and slno<@intEndRec    
        --***********************************************************

        --***Get the total number of records***************
        select COUNT(ProductID) as TotalRecords
        from Production.Product
        --************************************************
    End
END

The output of this Stored Procedure

Execute the Stored Procedure by passing: @intCurrentPage=1, @intRecordPerPage=10.

First Result Set
SlNo    ProductID   Name                        ProductNumber       StandardCost
1        1        Adjustable Race                AR-5381                0.00
2        2        Bearing Ball                BA-8327                0.00
3        3        BB Ball Bearing                BE-2349                0.00
4        4        Headset Ball Bearings        BE-2908                0.00
5        316        Blade                        BL-2036                0.00
6        317        LL Crankarm                        CA-5965                0.00
7        318        ML Crankarm                        CA-6738                0.00
8        319        HL Crankarm                        CA-7457                0.00
9        320        Chainring Bolts                CB-2903                0.00
10        321        Chainring Nut                CN-6137                0.00


TotalRecords
504

The first result set is the records of the first page. The second result set is the total number of records.

Execute this Stored Procedure in the AdventureWorks database and create a connection string for connecting to AdventureWorks.

connectionString ="Data Source=[SERVER NAME];
   Initial Catalog=AdventureWorks;User ID=[USERNAME]; Password=[PASSWORD]" />

Using the result in the code-behind

Now we have the Stored Procedure ready in the database. Start implementing our listing and paging methods. To show the listing, we are not using any data controls, we just write directly to the ASPX page.

For this, first we have to declare two strings in the code-behind:

Protected strList As String     ' to set the HTML of the listing
Protected strPages As String    ' to set the HTML of paging

The string variable strList holds the HTML for the records, and the string strPages will hold the HTML for paging.

Fill the DataReader

First, we have to get the page index. We can store the page index in a hidden text field (txtHidPageIndex); by default, the first page will be shown. Using this hidden field, we can store the page index clicked by the user, and can access the hidden field in the server side to know the page number. We need a server control to listen to the clicking of the page. I am adding a link button control (lnkPaging) to the page to initiate the clicking and to do some server side action.

Write a JavaScript function for accepting the page number and set that page number to the hidden field. After that, do a post back of the link button so that we can perform some server side action. The Click event of the link button can be initiated by the doPostBack method of ASP.NET.

function doPaging(intPageIndex)
{
    document.getElementById('txtHidPageIndex').value=intPageIndex;
    __doPostBack('lnkPaging','');
}

The code-behind of the link button (lnkPaging) looks like this:

Protected Sub lnkPaging_Click(ByVal sender As Object, _
          ByVal e As System.EventArgs) Handles lnkPaging.Click
    DoSearch(Val(txtHidPageIndex.Value))
End Sub

This snippet below shows how to fill the reader with records of this page:

Private Sub DoSearch(Optional ByVal intPageIndex As Integer = 1)

    Dim intRecordPerPage As Integer = 10
    Dim objCon As New SqlConnection(_
      ConfigurationManager.ConnectionStrings("ConString").ConnectionString)
    Dim objCmd As New SqlCommand()
    objCmd.Connection = objCon
    Dim objReader As SqlDataReader
    objCmd.CommandType = CommandType.StoredProcedure
    objCmd.CommandText = "uspFetchProduct"
    objCmd.Parameters.Add(New SqlParameter("@intCurrentPage", intPageIndex))
    objCmd.Parameters.Add(New SqlParameter("@intRecordPerPage", intRecordPerPage))
    objCon.Open()
    objReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)

I assigned 10 records to show in one page. If you want the user to customize the records per page, you can add an option in the settings of your website and take the value to set as the record per page. Now, we have the result in the reader. Create a StringBuilder to store the HTML created.

Dim sbHTML As New StringBuilder
sbHTML.Append("YOUR HEADER INFORMATION")
While objReader.Read
sbHTML.Append("THE LISTING COLUMNS ")

After filling the HTML for the listing, we can assign it to the protected variable strList.

strList = sbHTML.ToString

Paging for the records

Now comes the main thing: how to do the paging. For paging, first we need the total number of records. Take this number from the reader.

objReader.NextResult()
Dim intTotalRecords As Integer = 0
If objReader.Read Then
    intTotalRecords = Val(objReader(0))
End If

Now, the variable intTotalRecords contains the total number of records. The second task is to find the number of pages. This is shown in the snippet below:

'(100/10) mod=0 and  (105/10) mod=5
Dim intReminder As Integer = intTotalRecords Mod intRecordPerPage
'(100/10) =10 and 105\10 =10
Dim intTotalPages As Integer = intTotalRecords \ intRecordPerPage
If intReminder > 0 Then
'(100/10) so 10 pages, if (105/10) means 11 pages
    intTotalPages += 1
End If

Now we have to find the starting record and the ending record for each page. We are assuming that the page numbers shown in the list contains five pages. So the user will get the First and Previous buttons, and then buttons for the next five pages, and then the Next and Last buttons.

Dim intPagesShowing As Integer = 5
' 5 pages will be showing in the list (1|2|3|4|5 then show next button)

Dim intStartPage As Integer
Dim intEndPage As Integer
intStartPage = IIf(intPageIndex <= intPagesShowing, 1, intPageIndex)

If intTotalPages > intPagesShowing And intTotalPages > intStartPage Then
    intEndPage = IIf((intStartPage + intPagesShowing - 1) > intTotalPages, _
                      intTotalPages, (intStartPage + intPagesShowing - 1))
Else
    intEndPage = intTotalPages
End If

Now we can hide or disable the First, Previous, Next, and Last buttons by checking the page index and the number of pages remaining:

Dim bShowFirst As Boolean = False
Dim bShowPrevious As Boolean = False
Dim bShowNext As Boolean = False
Dim bShowLast As Boolean = False

If intPageIndex > 1 Then
    bShowPrevious = True
    bShowFirst = True
End If

If intEndPage < intTotalPages Then
    bShowNext = True
    bShowLast = True
End If

Now we can add page numbers, which is from the start page to the last page:

For iPage As Integer = intStartPage To intEndPage
    sbHTML.Append(iPage)
Next

After filling the HTML for paging, store the StringBuilder value in the protected string strPages. Paging will show only if there is more than one page.

If intTotalPages > 1 Then
    strPages = sbHTML.ToString
End If

Points of interest

When using this method, we don't need any data controls. We are creating and managing the HTML for listing and paging. This technique will give you maximum flexibility in your page.