Progressive loading in Gridview on Scroll down

In contrast to the Pagination patterns, the Continuous Scrolling pattern has no natural break. When using pagination patterns, a decision to only show a subset of data at a time and then let the user request more data if wanted is chosen. With the Continuous Scrolling, new data is automatically retrieved as the user has scrolled to the bottom of the page. It thus appears as if the page has no end, as more data will be loaded and inserted into the page each time the user scrolls to the bottom of page.


The minimalistic code sample explained here will show how to fetch records asynchronously on scrolling from a hypothetical Users table after the first batch is initially fetched. A Generic Handler (which is similar to an ASPX page but lacks HTML tags) is used to retrieve records asynchronously and hand it over to the parent page. So the sample basically consists of  the following 2 files which you will have to copy to a ASP.NET 2.0 Website in VS.NET 2005 or VS.NET 2008 –
FetchOnDemand.aspx
AsyncHandler.ashx

All the JavaScript magic to track when the user reaches the end of the scroll bar (in this example we simulate DIV scrolling  by restricting it’s height & setting overflow:auto style for the DIV tag) is done effortlessly by jQuery.

You can grab the external JavaScript file representing the jQuery library need for this sample from either of these URLs  –
http://code.jquery.com/jquery-latest.js
http://ajax.googleapis.com/ajax/libs/jquery/1.2.3/jquery.min.js

You can copy and place the external JavaScript file in a JS folder under the ASP.NET 2.0 website you create in VS.NET for running this code sample or you may even choose to reference it directly in the code. The jQuery library will be used in the parent page (FetchOnDemand.aspx) to trigger fetching of new records dynamically on scrolling to the bottom. The parent page communicates the unique Id related to the last row that was previously fetched to a Generic Handler (AsyncHandler.ashx). The Handler in turn returns a batch of new records asynchronously as a HTML table so that it can be injected at the end of table rendered by the GridView in the parent page.

Onto the code …


FetchOnDemand.aspx displays the first 20 records through a GridView. jQuery helps us get the last User Id from the last row of the table rendered from the GridView. We pass this as parameter to the Generic Handler’s querystring -“AsyncHandler.ashx?lastUserId=

Design View :
<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”FetchOnDemand.aspx.cs” Inherits=”FetchOnDemand” %>


<!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>Untitled Page</title>
    <style type=”text/css”>
        body
        {
            font-family: Verdana, Arial, Helvetica, sans-serif;
            font-size: 11px;
            color: #666666;
        }
        .divLeft
        {
            height: 500px;
            border: solid 1px black;
            width: 1300px;
            text-align: left;
            overflow: auto;
        }
        .divProgress
        {
            width: 100%;
            /*background-color: red;
            color: white;*/
        }
        .tblHeader
        {
            font-weight: bold;
            text-align: left;
            background-color: gray;
            color: black;
        }
        td
        {
            text-align: center;
        }
    </style>


    <script src=”js/jquery-1.2.6.min.js” type=”text/javascript”></script>


    <script type=”text/javascript”>
            //following code utilizes jQuery 1.2.6
            var prev = 0;
             $(document).ready(
             
             //DIV showing the message “Loading…” is hidden initially
            //The message will be shown when records are fetched with AJAX 
            //when user has scrolled to the bottom of the DIV scrollbar 
            function() {
                 $(“.divProgress”).hide();


                 $(window).scroll(
                
            function() {
            //triggering point is when the difference of the heights of the TABLE 
            //and DIV match the DIV’s scrollTop value
            //if ($(“#tblOrders”).height() – ($(window).scrollTop()-50) == $(window).height()) {
            if ($(window).scrollTop() == $(document).height() – $(window).height()){
            //progress bar         
            $(“.divProgress”).ajaxStart(function() {
                 $(this).show();
            });
             $(“.divProgress”).ajaxStop(function() {
                 $(this).hide();
            });


            //get last Order Id to track next fetch
            var UserIdLast = $(“#tblOrders tr:last”).children(“td:first”).html();


            //get last table row in order to append the new result set increment
            var trLast = $(“#tblOrders tr:last”);
            if (parseInt(UserIdLast, 10) > parseInt(prev, 10)) {
                prev = UserIdLast;
                 //make a async call to fetch the incremental results     
                $.post(“AsyncHandler.ashx?lastUserId=” + UserIdLast, function(data) {
                     if (data != null) {
                         //append new result set to last row
                        trLast.after(data);
                    }
                });
            }
        }
    });
    });
    </script>


</head>
<body>
    <h3>
        This is a demo to show Continous Scrolling UI Pattern</h3>
    <form id=”form1″ runat=”server”>
    <%–<div class=”divLeft”>
    </div>–%>
    <asp:GridView ID=”tblOrders” runat=”server” AutoGenerateColumns=”false” CellPadding=”2″
        Width=”100%”>
        <HeaderStyle CssClass=”tblHeader” />
        <Columns>
            <asp:BoundField DataField=”UserID” HeaderText=”UserID” InsertVisible=”False” ReadOnly=”True”
                SortExpression=”UserID”></asp:BoundField>
            <asp:BoundField DataField=”LoginName” HeaderText=”LoginName” SortExpression=”LoginName”>
            </asp:BoundField>
            <asp:BoundField DataField=”FirstName” HeaderText=”FirstName” SortExpression=”FirstName”>
            </asp:BoundField>
            <asp:BoundField DataField=”LastName” HeaderText=”LastName” SortExpression=”LastName”>
            </asp:BoundField>
            <asp:BoundField DataField=”EmailAddress” HeaderText=”EmailAddress” SortExpression=”EmailAddress”>
            </asp:BoundField>
            <asp:BoundField DataField=”TelephoneNo” HeaderText=”TelephoneNo” SortExpression=”TelephoneNo”>
                <ItemStyle Width=”50px” />
            </asp:BoundField>
        </Columns>
    </asp:GridView>
    <div class=”divProgress”>
        Loading….
    </div>
    </form>
</body>
</html>

Code View : 
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class FetchOnDemand : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection con=new SqlConnection(“ConnectionString”);
        string strSql = “Select TOP 20          UserId,LoginName,FirstName,LastName,EmailAddress,TelephoneNo From Users”;
        SqlDataAdapter dap= new SqlDataAdapter(strSql, con);
        DataTable dt=new DataTable();
        dap.Fill(dt);

        tblOrders.DataSource = dt;
        tblOrders.DataBind();
    }
}

AsyncHandler.ashx keeps the weight of the response lighter than an equivalent ASPX page. It takes a UserId value & returns the next twenty  records formatted in a HTML table so that it can be readily appended to the table in the parent view containing the bigger set of records.

 

<%@ WebHandler Language=”C#” Class=”AsyncHandler” %>


using System;
using System.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;


public class AsyncHandler : IHttpHandler
{
    
    public void ProcessRequest (HttpContext context) 
    { 
        //REMOVE BELOW LINE if you are using it in a real application 
        //It is here to simulate the delay while fetching results
        System.Threading.Thread.Sleep(2000);


        //The last OrderId is used to get the next increment
        string lastUserId = Convert.ToString(context.Request.QueryString[“lastUserId”]);


        //The PrepareDataSet method stuffs the DataSet into a HTML table 
        context.Response.Write(PrepareDataSet(lastUserId));
    }

    private string PrepareDataSet(string _UserId) 
    { 
        System.Data.SqlClient.SqlConnection conn = new SqlConnection(“ConnectionString”); 
        string strSql = “Select TOP 20 UserId,LoginName,FirstName,LastName,EmailAddress,TelephoneNo From Users Where UserId >” + _UserId;
        SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(strSql,conn); 
        DataSet ds = new System.Data.DataSet(); 
        da.Fill(ds, “Users”); 


        //The BuildRows method prepares a HTML table & stuffs the resultset into it 
        return BuildRows(ds.Tables[0]);
    }
     
    private string BuildRows(System.Data.DataTable dt) 
    { 
        StringBuilder sb = new StringBuilder();
        System.Data.DataRow dr; 
        if (dt.Rows.Count > 0) 
        { 
            for (int i = 0; i < dt.Rows.Count;i++) 
            { 
                sb.Append(“<tr class=’tblRow’>”); 
                dr = dt.Rows[i]; 
                for (int j = 0; j < dt.Columns.Count; j++) 
                { 
                    sb.Append(“<td>” + dr[j] + “</td>”); 
                } 
                sb.Append(“</tr>”); 
            } 
        } 
        return sb.ToString(); 
    } 


    public bool IsReusable 
    { 
        get 
        { 
            return false; 
        } 
    }
}

References : 
Load Content While Scrolling With jQuery
Implementing infinite scrolling with jQuery

Leave a Reply

Your email address will not be published. Required fields are marked *