In this article I will explain how to dynamically load data from SQL Server Database on page scroll of browser scrollbar using jQuery in ASP.Net
Database
For this article I am using Customers Table of Microsoft’s Northwind SQL Server database. You can download the Northwind database using the link below
HTML Markup
I have used ASP.Net Repeater control in order to bind the first set of records when the page loads. Once page is loaded and user scrolls the browser jQuery comes into action and it appends the data to this ASP.Net Repeater control.
<table>
<tr><td>
<div id="dvCustomers">
<asp:Repeater ID="rptCustomers" runat="server">
<ItemTemplate>
<table cellpadding="2" cellspacing="0" border="1" style="width: 200px; height: 100px;
border: dashed 2px #04AFEF; background-color: #B0E2F5">
<tr>
<td>
<b><u><span class="name">
<%# Eval("ContactName") %></span></u></b>
</td>
</tr>
<tr>
<td>
<b>City: </b><span class="city"><%# Eval("City") %></span><br />
<b>Postal Code: </b><span class="postal"><%# Eval("PostalCode") %></span><br />
<b>Country: </b><span class="country"><%# Eval("Country")%></span><br />
<b>Phone: </b><span class="phone"><%# Eval("Phone")%></span><br />
<b>Fax: </b><span class="fax"><%# Eval("Fax")%></span><br />
</td>
</tr>
</table>
<br />
</ItemTemplate>
</asp:Repeater>
</div>
</td>
<td valign="bottom">
<img id="loader" alt="" src="loading.gif" style="display: none" />
</td>
</tr>
</table>
Implement Pagination in SQL Server Stored Procedure
Since we need to get data on demand i.e. in parts we need to have pagination capability in our stored procedure so that we can fetch records based on page index or page number. This stored procedure returns Total Page Count as OUPUT parameter; this count will help notify jQuery that it should stop making AJAX calls as the all the data has been fetched.
USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetCustomersPageWise]
@PageIndex INT = 1
,@PageSize INT = 10
,@PageCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [CustomerID] ASC
)AS RowNumber
,[CustomerID]
,[CompanyName]
,[ContactName]
,[City]
,[Country]
,[PostalCode]
,[Phone]
,[Fax]
INTO #Results
FROM [Customers]
DECLARE @RecordCount INT
SELECT @RecordCount = COUNT(*) FROM #Results
SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
PRINT @PageCount
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
Binding first set of records to Repeater
Below code binds the first set of records to the ASP.Net Repeater control in Page Load event. This is very necessary as jQuery needs some HTML content which it can replicate the data fetched via AJAX
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
rptCustomers.DataSource = GetCustomersData(1);
rptCustomers.DataBind();
}
}
public static DataSet GetCustomersData(int pageIndex)
{
string query = "[GetCustomersPageWise]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", 10);
cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
return GetData(cmd);
}
private static DataSet GetData(SqlCommand cmd)
{
string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds, "Customers");
DataTable dt = new DataTable("PageCount");
dt.Columns.Add("PageCount");
dt.Rows.Add();
dt.Rows[0][0] = cmd.Parameters["@PageCount"].Value;
ds.Tables.Add(dt);
return ds;
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
rptCustomers.DataSource = GetCustomersData(1)
rptCustomers.DataBind()
End If
End Sub
Public Shared Function GetCustomersData(ByVal pageIndex As Integer) As DataSet
Dim query As String = "[GetCustomersPageWise]"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", 10)
cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
Return GetData(cmd)
End Function
Private Shared Function GetData(ByVal cmd As SqlCommand) As DataSet
Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim con As SqlConnection = New SqlConnection(strConnString)
Dim sda As SqlDataAdapter = New SqlDataAdapter
cmd.Connection = con
sda.SelectCommand = cmd
Dim ds As DataSet = New DataSet
sda.Fill(ds, "Customers")
Dim dt As DataTable = New DataTable("PageCount")
dt.Columns.Add("PageCount")
dt.Rows.Add()
dt.Rows(0)(0) = cmd.Parameters("@PageCount").Value
ds.Tables.Add(dt)
Return ds
End Function
WebMethod to handle AJAX calls from jQuery
The following web method handles the jQuery AJAX calls when page is scrolled down. It simply fetches the records from the database and the returns the XML.
C#
[WebMethod]
public static string GetCustomers(int pageIndex)
{
return GetCustomersData(pageIndex).GetXml();
}
VB.Net
<WebMethod()> _
Public Shared Function GetCustomers(ByVal pageIndex As Integer) As String
Return GetCustomersData(pageIndex).GetXml
End Function
Client side implementation to make AJAX call on page scroll
In the below code snippet, in the window scroll event of jQuery I am calling the GetRecords JavaScript method which makes an AJAX call to the server and fetches the customer records from SQL Server Database page wise using the current page index.
Every time data is fetched the page index is incremented and when the page index equals the total page count, it disables the process of making jQuery AJAX calls to server.
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
var pageIndex = 1;
var pageCount;
$(window).scroll(function () {
if ($(window).scrollTop() == $(document).height() - $(window).height()) {
GetRecords();
}
});
function GetRecords() {
pageIndex++;
if (pageIndex == 2 || pageIndex <= pageCount) {
$("#loader").show();
$.ajax({
type: "POST",
url: "Default.aspx/GetCustomers",
data: '{pageIndex: ' + pageIndex + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
},
error: function (response) {
alert(response.d);
}
});
}
}
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
pageCount = parseInt(xml.find("PageCount").eq(0).find("PageCount").text());
var customers = xml.find("Customers");
customers.each(function () {
var customer = $(this);
var table = $("#dvCustomers table").eq(0).clone(true);
$(".name", table).html(customer.find("ContactName").text());
$(".city", table).html(customer.find("City").text());
$(".postal", table).html(customer.find("PostalCode").text());
$(".country", table).html(customer.find("Country").text());
$(".phone", table).html(customer.find("Phone").text());
$(".fax", table).html(customer.find("Fax").text());
$("#dvCustomers").append(table).append("<br />");
});
$("#loader").hide();
}
</script>
Demo
Downloads
