Paging on Page Scroll in jQuery

In this article we will see how to fetch records from a database in chunks for page scrolling.

Introduction

In this article we will create a web application in which we get data from the in a scrollable page using jQuery and get simple pagination on page scrolling.

Create Stored Procedure

USE [Northwind]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--This procedure will get data from Customer Table using parameters StartIndex and EndIndex

CREATE PROC [dbo].[usp_GetCustomersByPaging]

@StartIndex INT

,@EndIndex INT

AS

BEGIN

      SELECT Row,CompanyName, ContactName,ContactTitle FROM

      (

            SELECT ROW_NUMBER() OVER(ORDER BY CustomerID DESC) AS Row,

                  CompanyName, ContactName,ContactTitle

            FROM Customers

      ) AS Customers

      WHERE Row BETWEEN @StartIndex AND @EndIndex

END

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

 

<!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>Pagination on scroll</title>

    <script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.js"></script>

    <script type="text/javascript">

 

        $(document).ready(function () {

            $(window).scroll(function () {

                if ($(window).scrollTop() == $(document).height() - $(window).height()) {

                    var startInd = parseInt($("#hdfStartIndex").val());

                    var endInd = parseInt($("#hdfEndIndex").val())

 

                    $("#hdfStartIndex").val(startInd + 10);

                    $("#hdfEndIndex").val(endInd + 10);

                    bindData();

                }

            });

        });

 

        function bindData() {

            var startInd = $("#hdfStartIndex").val();

            var endInd = $("#hdfEndIndex").val();

            $.ajax({

                type: "POST",

                contentType: "application/json; charset=utf-8",

                url: "scrollpaging.aspx/BindDatatable",

                data: "{startIndex: " + startInd + "," + "endIndex:" + endInd + "}",

                dataType: "json",

                success: function (data) {

                    for (var i = 0; i < data.d.length; i++) {

                        $("#gvDetails").append("<tr><td><img src='images.jpg' alt='image' width='100px' height='100px'/> </td> <td>" + data.d[i].CompanyName + "</td><td>" + data.d[i].ContactName + "</td><td>" + data.d[i].ContactTitle + "</td></tr>");

 

                    }

                },

                error: function (result) {

                    alert("Error");

                }

            });

        }

    </script>

 

    <style type="text/css">

        table, th, td

        {

            border: 1px solid black;

            border-collapse: collapse;

        }

    </style>

</head>

<body onload="bindData();">

    <form id="form1" runat="server">

 

    <div>

        <input type="hidden" value="1" id="hdfStartIndex" />

        <input type="hidden" value="10" id="hdfEndIndex" />

        <div>

            <asp:GridView ID="gvDetails" runat="server" Width="90%">

                <HeaderStyle BackColor="#DC5807" Font-Bold="true" ForeColor="White" />

            </asp:GridView>

        </div>

    </div>

    </form>

</body>

</html>

Default.aspx.cs
 

using System;

using System.Data;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Web.Services;

 

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindColumnToGridview();

        }

    }

 

 

    private void BindColumnToGridview()

    {

        DataTable dt = new DataTable();

        dt.Columns.Add("Image");

        dt.Columns.Add("CompanyName");

        dt.Columns.Add("ContactName");

        dt.Columns.Add("ContactTitle");

        dt.Rows.Add();

        gvDetails.DataSource = dt;

        gvDetails.DataBind();

        gvDetails.Rows[0].Visible = false;

    }

 

    [WebMethod]

    public static CustomerDetails[] BindDatatable(string startIndex, string endIndex)

    {

        DataTable dt = new DataTable();

        List<CustomerDetails> details = new List<CustomerDetails>();

 

        using (SqlConnection con = new SqlConnection("Data Source=CHETUIWK432\\SQL2008; Initial Catalog=Northwind;Integrated Security=true"))

        {

            using (SqlCommand cmd = new SqlCommand())

            {

                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandText = "usp_GetCustomersByPaging";

                cmd.Parameters.AddWithValue("@StartIndex", startIndex);

                cmd.Parameters.AddWithValue("@EndIndex", endIndex);

                try

                {

                    con.Open();

                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    da.Fill(dt);

 

                    foreach (DataRow dtrow in dt.Rows)

                    {

                        CustomerDetails customers = new CustomerDetails();

                        customers.CompanyName = dtrow["CompanyName"].ToString();

                        customers.ContactName = dtrow["ContactName"].ToString();

                        customers.ContactTitle = dtrow["ContactTitle"].ToString();

                        details.Add(customers);

                    }

                }

                catch { }

            }

        }

        return details.ToArray();

    }

    public class CustomerDetails

    {

        public string CompanyName { get; set; }

        public string ContactName { get; set; }

        public string ContactTitle { get; set; }

    }

}

Up Next
    Ebook Download
    View all
    Learn
    View all