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

}

}