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]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

CREATE PROC [dbo].[usp_GetCustomersByPaging]

@StartIndex INT

,@EndIndex INT

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">

<title>Pagination on scroll</title>

$(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>

table, th, td

{

border: 1px solid black;

border-collapse: collapse;

}

</style>

</head>

<div>

<div>

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

</asp:GridView>

</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; }

}

3 Comments

Join the conversation! Your thoughts help the community grow.

bashiruddin Sheikh10y ago
Please sagged ME......>
bashiruddin Sheikh10y ago
Please reply me my datatable 300 records 1sd show 50 and Scroll after 50 records ..........
bashiruddin Sheikh10y ago
Please tell me Datatable to data Paging on Page Scroll in jQuery as.net???