Asp.Net Ajax Web Continuous Pagination Thus Making Endless Scroll

Brief Intro

Nowadays, a web page contains continuous pagination to deal with retrieving lot of data.
This continuous pagination means data views per page doesnt requires to reload a whole page to get next / previous page. It partially reload and attached next data on same page.
So it makes seemingly endless scroll.

This blog post will give you an example on how to do this continuos paging using Asp .Net Ajax capabilities.

Database & Query

I use SQL Server 2008 Northwind Database as sample. Spesifically use Orders, OrderDetails, Products, Employees and Customers tables.

I will make a list of sales transaction along with purchased products, customers and salesman or employees. Total value also being presented.

Tables Design

Below are list of design of tables I used. These list converted into create table statement.

Orders Table:

CREATE TABLE [dbo].[Orders](
	[OrderID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [nchar](5) NULL,
	[EmployeeID] [int] NULL,
	[OrderDate] [datetime] NULL,
	[RequiredDate] [datetime] NULL,
	[ShippedDate] [datetime] NULL,
	[ShipVia] [int] NULL,
	[Freight] [money] NULL,
	[ShipName] [nvarchar](40) NULL,
	[ShipAddress] [nvarchar](60) NULL,
	[ShipCity] [nvarchar](15) NULL,
	[ShipRegion] [nvarchar](15) NULL,
	[ShipPostalCode] [nvarchar](10) NULL,
	[ShipCountry] [nvarchar](15) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Order Details Table:

CREATE TABLE [dbo].[Order Details](
	[OrderID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[Quantity] [smallint] NOT NULL,
	[Discount] [real] NOT NULL,
 CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC,
	[ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Products Table:

CREATE TABLE [dbo].[Products](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[ProductName] [nvarchar](40) NOT NULL,
	[SupplierID] [int] NULL,
	[CategoryID] [int] NULL,
	[QuantityPerUnit] [nvarchar](20) NULL,
	[UnitPrice] [money] NULL,
	[UnitsInStock] [smallint] NULL,
	[UnitsOnOrder] [smallint] NULL,
	[ReorderLevel] [smallint] NULL,
	[Discontinued] [bit] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Customers Table:

CREATE TABLE [dbo].[Customers](
	[CustomerID] [nchar](5) NOT NULL,
	[CompanyName] [nvarchar](40) NOT NULL,
	[ContactName] [nvarchar](30) NULL,
	[ContactTitle] [nvarchar](30) NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[Phone] [nvarchar](24) NULL,
	[Fax] [nvarchar](24) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Employees Table:

CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL,
	[TitleOfCourtesy] [nvarchar](25) NULL,
	[BirthDate] [datetime] NULL,
	[HireDate] [datetime] NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[HomePhone] [nvarchar](24) NULL,
	[Extension] [nvarchar](4) NULL,
	[Photo] [image] NULL,
	[Notes] [ntext] NULL,
	[ReportsTo] [int] NULL,
	[PhotoPath] [nvarchar](255) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Sales Transaction Query

The sql query of sales transaction contains Order and its details line.

select Orders.OrderID, convert(varchar, OrderDate, 102) as OrderDate, ProductName, Quantity, OD.UnitPrice, Discount,CompanyName as Customer, FirstName + ' ' + LastName as Salesman
from Orders, [Order Details] as OD, Products, Customers, Employees
where Orders.OrderID = OD.OrderID And OD.ProductID = Products.ProductID
and Orders.CustomerID = Customers.CustomerID and Orders.EmployeeID = Employees.EmployeeID order by OrderDate desc

Above query will returns a lot of records due to all sales detail lines also retrievied and thus we need pagination.

Since I will be using pagination then the query must support that purpose, which means sql query must return only specific records at time according to what page number that user currently browse.

This can be done by applying row number() sql function. This function returns unique record numbers so that we can specifiy what number as start and also its finish in order to do pagination.
To do pagination more make sense, I have to view order details completely per order header in a page. This means pagination will not cut order details records due to it is considered as last record in that page number.

So that I have to retrieve start OrderID and then finish OrderID in each page. This can be done by below query:

select min(OrderID) as min_OID, max(OrderID) as max_OID from (
select row_number() over(order by OrderDate desc) as rowno, Orders.OrderID 
from Orders ) as tbl where rowno between <<start_row_num>> and <<finish_row_num>>

If I want to show 10 Sales Orders per page then <> and <> should follow accordingly. In example on page 1, <> is 1 and <> is 10.
page 2 <> is 11 and <> is 20. Above query will returns minimum Order ID and maximum Order ID for particular page.

Sales Orders With pagination query will become:

select Orders.OrderID, convert(varchar, OrderDate, 102) as OrderDate, ProductName, Quantity, OD.UnitPrice, Discount,CompanyName as Customer, FirstName + ' ' + LastName as Salesman
from Orders, [Order Details] as OD, Products, Customers, Employees 
where Orders.OrderID = OD.OrderID And OD.ProductID = Products.ProductID 
and Orders.CustomerID = Customers.CustomerID and Orders.EmployeeID = Employees.EmployeeID 
and Orders.OrderID between <<min_OrderID>> and <<max_orderID>> order by OrderDate desc

<> is minimum OrderID which retrieved from previous query also likewise with <>.
This method will make pagination more make sense, since No Order header records having uncomplete details line in each page.

Using Asp.Net Ajax And Table Server Control

Asp .Net has its own pre-build Ajax Component which I used in this example. I use ScriptManager, UpdatePanel, UpdateProgress.

Ajax loads page partially and thus web pagination doen’t have to refresh a whole page and it makes endless scroll due to this pagination method.

Orders Data presented in Table control so that I can populate and add records on next page programmatically. All using server side script and server control. There are no mixing between server side script and client side.
FYI, Asp.Net Ajax also has client side script framework. Client side script in this case will help on cancelling ajax process if next page data is too large or some traffic problem occurs and thus user may cancel it.

Complete Source Code

Default.aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" Debug="true" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css" media="screen">
        .ttlvalue
        {
            border-bottom: 1px solid blue;
        }
        
        .header 
        {
            border: 1px solid black;
        }        
    </style>
</head>
<body >
    <form id="form1" runat="server">
    <div >
        <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true" >
        </asp:ScriptManager>
        
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
            <asp:Table ID="report_tbl" runat="server" CellPadding="3" CellSpacing="0">    
               <asp:TableHeaderRow >
                <asp:TableHeaderCell cssclass="header">OrderID</asp:TableHeaderCell>
                <asp:TableHeaderCell cssclass="header">Order Date</asp:TableHeaderCell>
                <asp:TableHeaderCell HorizontalAlign="Left" cssclass="header">Product</asp:TableHeaderCell>
                <asp:TableHeaderCell cssclass="header">Qty</asp:TableHeaderCell>
                <asp:TableHeaderCell cssclass="header">Price</asp:TableHeaderCell>
                <asp:TableHeaderCell cssclass="header">Discount</asp:TableHeaderCell>
                <asp:TableHeaderCell cssclass="header">Customer</asp:TableHeaderCell>
                <asp:TableHeaderCell cssclass="header">Salesman</asp:TableHeaderCell>
               </asp:TableHeaderRow>
            </asp:Table>
        </ContentTemplate>
        <Triggers>
            <asp:AsyncPostBackTrigger ControlID="btnshowmore" />           
        </Triggers>
        </asp:UpdatePanel>
		<asp:Button ID="btnshowmore" runat="server" Text="Show More Data" /><br>
        <asp:UpdateProgress ID="updateprogress1" runat="server">
            <ProgressTemplate>Fetching More Data..</ProgressTemplate>
        </asp:UpdateProgress>
        
    </div>
    
    </form>
</body>
</html>

There is a button control btnshowmore to load next page records. This button’s onclick event will triggers Ajax Postback event and page will re-load partially and fetch next page’s records on Table control via server side script.

Default.aspx.vb:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Partial Class _Default
    Inherits System.Web.UI.Page
    Private Shared nPage As Integer
    Private Shared tempTable As Table
    Const PageSize As Integer = 10
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            nPage = 1
            tempTable = New Table()
            GetData()
        End If
    End Sub

    Public Sub GetData()

        Dim cnnstr As New SqlConnectionStringBuilder()
        cnnstr.DataSource = ".\SQLDEV"
        cnnstr.InitialCatalog = "Northwind"
        cnnstr.IntegratedSecurity = True
        Dim conn As New SqlConnection(cnnstr.ConnectionString)
        conn.Open()

        'use paging based on orderid rank
        Dim start_no, end_no As Integer
        start_no = (nPage - 1) * PageSize + 1
        end_no = start_no + PageSize - 1
        Dim query As String = "select min(OrderID) as min_OID, max(OrderID) as max_OID from ( " & _
                "select row_number() over(order by OrderDate desc) as rowno, Orders.OrderID " & _
                "from Orders ) as tbl where rowno between " & start_no & " and " & end_no

        Dim cmd As New SqlCommand(query, conn)
        Dim dread As SqlDataReader = cmd.ExecuteReader()

        Dim min_OID As String, max_OID As String

        If dread.Read() Then
            min_OID = dread(0).ToString()
            max_OID = dread(1).ToString()
        End If
        dread.Close()
        cmd.Dispose()
        Dim dt As New DataTable()

        If min_OID <> "" And max_OID <> "" Then
            query = "select Orders.OrderID, convert(varchar, OrderDate, 102) as OrderDate, ProductName, Quantity, OD.UnitPrice, Discount,CompanyName as Customer, FirstName + ' ' + LastName as Salesman " & _
                "from Orders, [Order Details] as OD, Products, Customers, Employees " & _
                "where Orders.OrderID = OD.OrderID And OD.ProductID = Products.ProductID " & _
                "and Orders.CustomerID = Customers.CustomerID and Orders.EmployeeID = Employees.EmployeeID " & _
                "and Orders.OrderID between " & min_OID & " and " & max_OID & " order by OrderDate desc"

            cmd = New SqlCommand(query, conn)
            Dim da As New SqlDataAdapter(cmd)

            da.Fill(dt)

            da.Dispose()
            cmd.Dispose()


        End If
        conn.Close()
        Dim lstHeaderCols As New List(Of String)
        lstHeaderCols.Add("orderid")
        lstHeaderCols.Add("orderdate")
        lstHeaderCols.Add("customer")
        lstHeaderCols.Add("salesman")

        Dim tr As TableRow
        Dim tcell As TableCell
        Dim idx As Int32
        Dim OrderID As Integer
        Dim prevOrderID As Integer = 0
        Dim ordervalue As Double = 0
        Dim qty As Integer
        Dim price As Double
        Dim discount As Double

        If dt.Rows.Count > 0 Then
            For Each dr As DataRow In dt.Rows
                tr = New TableRow()
                idx = 0
                OrderID = dr("OrderID").ToString()
                For Each c In dr.ItemArray
                    tcell = New TableCell()

                    If OrderID <> prevOrderID Then

                        If idx = 0 And prevOrderID > 0 Then
                            tcell.ColumnSpan = 4
                            tcell.Text = "SubTotal"
                           
                            tcell.Style.Item("font-weight") = "bold"

                            tcell.HorizontalAlign = HorizontalAlign.Right
                            tcell.CssClass = "ttlvalue"
                            tr.Cells.Add(tcell)

                            tcell = New TableCell()
                            tcell.ColumnSpan = 4
                            tcell.Text = FormatNumber(ordervalue.ToString(), 2)
                            tcell.CssClass = "ttlvalue"
                            tr.Cells.Add(tcell)

                            tempTable.Rows.Add(tr)

                            ordervalue = 0

                            tr = New TableRow()
                            tcell = New TableCell()
                        End If
                        If idx = 4 Then c = FormatNumber(c, 2).ToString()
                        tcell.Text = c.ToString()

                    Else
                        If idx = 4 Then c = FormatNumber(c, 2).ToString()
                        If lstHeaderCols.Contains(dt.Columns(idx).ColumnName.ToString().ToLower()) Then
                            tcell.Text = ""
                        Else
                            tcell.Text = c.ToString()
                        End If
                    End If
                    tr.Cells.Add(tcell)
                    Select Case idx
                        Case 3
                            qty = Convert.ToInt32(c)
                        Case 4
                            price = Convert.ToDouble(c)
                        Case 5
                            discount = Convert.ToDouble(c)
                        Case 6
                            ordervalue = ordervalue + qty * (price - discount)
                    End Select
                    idx = idx + 1
                Next
                tempTable.Rows.Add(tr)

                prevOrderID = OrderID

            Next

            tr = New TableRow()
            tcell = New TableCell()
            tcell.ColumnSpan = 4
            tcell.Text = "SubTotal"
            tcell.Style.Item("font-weight") = "bold"
            tcell.HorizontalAlign = HorizontalAlign.Right
            tcell.CssClass = "ttlvalue"
            tr.Cells.Add(tcell)

            tcell = New TableCell()
            tcell.ColumnSpan = 4
            tcell.Text = FormatNumber(ordervalue.ToString(), 2)
            tcell.CssClass = "ttlvalue"
            tr.Cells.Add(tcell)
            tempTable.Rows.Add(tr)

            tr = New TableRow()
            tcell = New TableCell()
            For Each rw As TableRow In tempTable.Rows
                
                tr = New TableRow()
                For Each cl As TableCell In rw.Cells
                    tcell = New TableCell()
                    tcell.Text = cl.Text
                    tcell.Style.Item("font-weight") = cl.Style.Item("font-weight")

                    tcell.HorizontalAlign = cl.HorizontalAlign
                    tcell.ColumnSpan = cl.ColumnSpan
                    tcell.CssClass = cl.CssClass
                    tr.Cells.Add(tcell)
                Next
                report_tbl.Rows.Add(tr)
            Next

        End If

    End Sub
    Protected Sub btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnshowmore.Click
        nPage = nPage + 1

        GetData()
    End Sub
End Class

I have two Table controls, report_tbl and tempTable. tempTable is a temporary Table control with Shared keyword. This means its rows & columns are persistent. They won’t be cleared even postback happens.
Unlike main table control report_tbl, it is got refreshed and cleared every time postback occurs. This table control is acting as presentation to user.

When ajax processed to get next page records, tempTable will be the first that fetched with data. Since tempTable members is not refreshed then its members all copied to main Table control to view real data to user page.
All data, properties and style in tempTable are copied to user table control.

Screen shots:


Additional Note

Asp.Net Ajax makes possible to do asynchronous process via server side script. There are no mixing between server & client side script in order to load next page data.
Asp.Net Ajax client side script might be useful in another case, i.e cancelling process, validation, component controls, calling server side script from client script, etc.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s