Ajax Scrolling — an alternative to Paging.

The example below, uses JQuery scroll function to fetch additional incremental records, when user hits the bottom of the Repeater. The Repeater has a fixed external header (NOT the in-built Repeater header template), to keep the header row visible, as the user scrolls down. When the last batch of records is fetched, a flag is set at client side , which stops further AJAX calls to DB. Here are few screen shots and complete source code.

img1

img2

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ScrollRepeaterFixedHeader.aspx.cs" Inherits="ScrollRepeaterFixedHeader" %>

<!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></title>
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.5.js" type="text/javascript"></script> 

<script type="text/javascript">
var _AjaxDBCall = 0;
$(document).ready(function () {
    $("#content").scroll(function () {
        if (this.scrollHeight - $(this).scrollTop() == $(this).innerHeight()) {
            if ($("#hdCurrMaxAddressID").val() != "-1") {
                //There's still some more data left. Go get those records. 
                getData($("#hdCurrMaxAddressID").val());
            }
        }
    });
});

function getData(i) {
$.ajax({
    type: "POST",
    contentType: "application/json",
    data: "{CurrMaxAddressID:" + i + "}",
    url: "ScrollRepeaterFixedHeader.aspx/GetNextBatch",
    dataType: "json",
    success: function (data) {
        _AjaxDBCall++;
        $("#hdCurrMaxAddressID").val(data.d.MaxAddressID);
        if (data.d.MaxAddressID == "-1") {
            //All records have been fetched. No further AJAX DB calls.
            $("#StatusDiv").html($("#StatusDiv")[0].innerHTML + "<br> All data fetched from DB. No further AJAX calls");
            return;
        }
        var lastRow = $("#RptTable tr:last");
        lastRow.after(data.d.FetchedRows);
        $("#StatusDiv").html("<br> DB Ajax calls done so far : " + _AjaxDBCall);
    },
    error: function (XMLHttpRequest, textStatus, errorThrown) {
        debugger;
    }
});
} 
</script> 

</head>
<body>
<form id="form1" runat="server">
<table><tr style="width:500px; color:Red;">
<td style="display:none; text-align:center;">ID</td>
<td style="width:200px; text-align:left;">AddressLine1</td>
<td style="width:100px; text-align:left;">City</td>
<td style="width:100px; text-align:left;">Postal</td>
</tr></table>
<div id="content" style="height:300px;width:500px;overflow:auto"> 
<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate><table id="RptTable"></HeaderTemplate>
<ItemTemplate>
<tr style="width:500px;">
    <td style="display:none;"><%#Eval("AddressID")%></td>
    <td style="width:200px;"><%#Eval("AddressLine1")%></td>
    <td style="width:100px;"><%#Eval("City")%></td>
    <td style="width:100px; text-align:left;"><%#Eval("PostalCode")%></td>
    </tr>
</ItemTemplate>
    <FooterTemplate></table></FooterTemplate>
</asp:Repeater>
</div>
<div id="Div1" style="width:500px;"> 
<hr />
</div>

<asp:HiddenField runat="server" ID="hdCurrMaxAddressID" />
<div id="StatusDiv" style="border-color:Red; color:Green; font-size:large;"></div>
</form>
</body>
</html>

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Web.Services;

public partial class ScrollRepeaterFixedHeader : System.Web.UI.Page
{
public class RepeaterData
{
public int MaxAddressID { get; set; }
public string FetchedRows { get; set; }
}

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
    DataTable dt = GetData();
    Repeater1.DataSource = dt;
    Repeater1.DataBind();
    dt.DefaultView.Sort = "AddressID DESC";
    hdCurrMaxAddressID.Value = dt.DefaultView[0]["AddressID"].ToString();
}
}

private static DataTable GetData()
{
SqlDataReader reader = null;
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection())
{
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStr2"].ConnectionString;
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.CommandText = "SELECT top 20 AddressID, AddressLine1, City, PostalCode "+
                            "FROM [AdventureWorks].[Person].[Address] where AddressLine1 like '10%' ORDER BY AddressID";
        cmd.Connection = conn;
        StringBuilder sb = new StringBuilder();
        conn.Open();
        reader = cmd.ExecuteReader();
        dt.Load(reader);
        conn.Close();
    }
}
return dt;
}

private static DataTable GetBatchData(int _AddressID)
{
SqlDataReader reader = null;
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection())
{
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStr2"].ConnectionString;
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.CommandText = "SELECT top 10 AddressID, AddressLine1, City, PostalCode "+
                            "FROM [AdventureWorks].[Person].[Address] "+
                            " where AddressLine1 like '10%' and AddressID > "+_AddressID+" ORDER BY AddressID";
        cmd.Connection = conn;
        StringBuilder sb = new StringBuilder();
        conn.Open();
        reader = cmd.ExecuteReader();
        dt.Load(reader);
        conn.Close();
    }
}
return dt;
}

[WebMethod]
public static RepeaterData GetNextBatch(int CurrMaxAddressID)
{
RepeaterData _RepeaterData = new RepeaterData();
DataTable dt = GetBatchData(CurrMaxAddressID);
_RepeaterData.MaxAddressID = -1;
_RepeaterData.FetchedRows = "";
    
StringBuilder sb = new StringBuilder();
System.Data.DataRow rowProduct;
if (dt.Rows.Count > 0)
{
    dt.DefaultView.Sort = "AddressID DESC";
    _RepeaterData.MaxAddressID = int.Parse(dt.DefaultView[0]["AddressID"].ToString());

    for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
    {
        sb.Append("<tr>");
        rowProduct = dt.Rows[rowIndex];
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            if (j == 0)
            {
                sb.Append("<td style='display:none;'>" + rowProduct[j] + "</td>");
            }
            else
            {
                sb.Append("<td style='width:200px;'>" + rowProduct[j] + "</td>");
            }
        }
        sb.Append("</tr>");
    }
}
_RepeaterData.FetchedRows = sb.ToString();
return _RepeaterData;
}

    
}

Hope this helps. Thanks for reading.

Advertisements
This entry was posted in JQuery. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s