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
 
source : http://aspsnippets.com/Articles/Load-data-while-Scrolling-Page-down-with-jQuery-AJAX-and-ASPNet.aspx