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