Scrolling Data Pagination Using AJAX (Ext JS), JSON (Jayrock), and LINQ

• Download VB.NET source code - 7.01 MB
• Download C# source code - 6.86 MB

• View online scrolling pagination demo

Introduction

Tired of continuously clicking Next Page, Next Page, Next Page etc., to find the data you are looking for? I always thought this was just something you had to deal with when viewing data on the Internet, that was until I took a look at the new Microsoft live image search. Instead of forcing the user to click Next Page to paginate the data, the page catches the scroll event and fetches the next page of data and adds it to the output asynchronously. In this project, I will expand upon my previous project, Using LINQ to paginate your ObjectDataSource, replacing the standard grid with a scrolling paginated grid. This example works by first creating an Ext JS grid, making a call to a custom JSON RPC handler to retrieve data, and by adding an event listener to the scrolling event of our grid. This article will focus primarily on the AJAX portion of this project to read more on the underlying data pagination technique. Please take a look at the previous version of this project.

Topics covered in this project

• Singleton collections
• Querying objects using LINQ
• Using Skip() and Take() with LINQ
• Custom data pagination
• Creating a custom JSON RPC handler using Jayrock
• Using the Ext JS with ASP.NET and JSON RPC
• Working with JavaScript events and asynchronous JavaScript

Background

This project is an example of using LINQ to query a Singleton collection. This basically states that the collection persists in memory at the application level of your IIS process. This is achieved by using static or shared instances, or by using .NET web caching, or other third party cache mechanisms such as zcache or ncache. If you do not want to persist your data in memory, then rownumber is another way for you to achieve data pagination. In place of the class that the JSON RPC handler accesses to retrieve its data, you can replace the reference with a database query or object which loads directly from a database.

Using the code

The download Zip file contains the Visual Studio 2008 project and the source files necessary to run the online demo as well as a CSV containing a Zip code database and a Stored Procedure used to load the data referenced inside the code. You will need to update the connection string located in the web.config file to point to your data source after you have imported the Zip code data and ran the Stored Procedure script. In addition, this Zip contains the Ext JS library and the Jayrock DLLs required to run this project.

Points of interest

Before you get started, you will need a mechanism for retrieving the data asynchronously as your user scrolls through the list of data. I chose to use a generic handler which returns a JSON serialized string for this task to maximize performance and throughput.

C#

using System;
using System.Web;
using Jayrock.Json;
using Jayrock.JsonRpc;
using Jayrock.JsonRpc.Web;
using ZipCodeObjects;
using System.Collections.Generic;

/// <summary> 
/// GetZips 
/// </summary> 
/// <remarks> 
/// Json Handler for zip code information. 
/// </remarks> 
public class GetZipCodes : JsonRpcHandler
{

    /// <summary> 
    /// GetZipCodesCount 
    /// </summary> 
    /// <returns></returns> 
    /// <remarks> 
    /// get count of all zip codes. 
    /// </remarks> 
    [JsonRpcMethod("GetZipCodesCount")]
    public int GetZipCodesCount()
    {
        return ZipCodeCollection.SelectCount();
    }

    /// <summary> 
    /// GetZipCodes 
    /// </summary> 
    /// <param name="ResultsPerPage"></param> 
    /// <param name="PageNumber"></param> 
    /// <returns></returns> 
    /// <remarks> 
    /// Get paginated zip codes data. 
    /// </remarks> 
    [JsonRpcMethod("GetZipCodesList")]
    public IEnumerable<ZipCode> GetZipCodesList(int ResultsPerPage, int PageNumber)
    {
        return ZipCodeCollection.GetZipCodes(ResultsPerPage, PageNumber);
    }
}

VB.NET

Imports System
Imports System.Web
Imports Jayrock.Json
Imports Jayrock.JsonRpc
Imports Jayrock.JsonRpc.Web
Imports LinqListGridBind.ZipCodeObjects

''' <summary>
''' GetZips
''' </summary>
''' <remarks>
''' Json Handler for zip code information.
''' </remarks>
Public Class GetZipCodes
    Inherits JsonRpcHandler

    ''' <summary>
    ''' GetZipCodesCount
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks>
    ''' get count of all zip codes.
    ''' </remarks>
    <JsonRpcMethod("GetZipCodesCount")> _
    Public Function GetZipCodesCount() As Integer
        Return ZipCodeCollection.SelectCount()
    End Function

    ''' <summary>
    ''' GetZipCodes
    ''' </summary>
    ''' <param name="ResultsPerPage"></param>
    ''' <param name="PageNumber"></param>
    ''' <returns></returns>
    ''' <remarks>
    ''' Get paginated zip codes data.
    ''' </remarks>
    <JsonRpcMethod("GetZipCodesList")> _
    Public Function GetZipCodesList(ByVal ResultsPerPage As Integer, _
                                    ByVal PageNumber As Integer) _
                                    As IEnumerable(Of ZipCode)
        Return ZipCodeCollection.GetZipCodes(ResultsPerPage, PageNumber)
    End Function

End Class

This example uses no postbacks, viewstate, or session state. Data is stored in the DOM and accessed via JavaScript.

//create ext data store and grid.
var store = null;
var grid = null;

//set default scroll variables.
var currentpos = 0;
var currentpage = 1;

//set constants.
//itemsperpage: number of records that appear on each page of content.
var itemsperpage = 14;
//number of pages of content to fetch when offset reached.
var pagestofetch = 2;
//number of pixel interval to fire handler request.
var scrolloffset = 263;
var scrolloffsetinterval = 263;
    
//event fires when ext is loaded and ready to display controls.
Ext.onReady(function(){
    
    //set current count.
    var x = document.getElementById("currentcount");
    x.innerHTML = (itemsperpage*pagestofetch);
    
    //create reference to GetZipCodes.ashx
    var s = new GetZipCodes();
    
    //call GetZipCodes.ashx handler
    //to retrieve total records in collection.
    s.GetZipCodesCount(function(response) { 
        var x = document.getElementById("totalcount");
        x.innerHTML = response.result;
    });
    
    //set skip take values.
    var y = document.getElementById("skip1");
        y.innerHTML = '0';
    var z = document.getElementById("skip2");
        z.innerHTML = '0';
    var a = document.getElementById("take1");
        a.innerHTML = (itemsperpage * pagestofetch);
    var b = document.getElementById("take2");
        b.innerHTML = (itemsperpage * pagestofetch);
        
    //set ext state provider.
    Ext.state.Manager.setProvider(new Ext.state.CookieProvider());
    
    //initialize temp data store for handler response.
    var myData
    
    //call GetZipCodes.ashx to retrieve zip code data.
    s.GetZipCodesList(
    itemsperpage*pagestofetch,
    currentpage,
    function(response) { //async callback fires when handler returns data.
        //store response.
        myData = response;
        
        //create jason data store and load data.
        store = new Ext.data.JsonStore({data: myData,
                                        root: 'result',
                                        fields: ['zip', 
                                                 'city', 
                                                 'state', 
                                                 'latitude', 
                                                 'longitude', 
                                                 'timeZone', 
                                                 'dst']
                                        });
              
          
            //create the ext Grid and load data store
            grid = new Ext.grid.GridPanel({
                store: store,
                columns: [
                  {header: "Zip", width: 120, sortable: false, dataIndex: 'zip'},
                  {header: "City", width: 120, sortable: false, dataIndex: 'city'},
                  {header: "State", width: 120, sortable: false, dataIndex: 'state'},
                  {header: "Latitude", width: 120, sortable: false, dataIndex: 'latitude'},
                  {header: "Longitude", width: 120, sortable: false, dataIndex: 'longitude'},
                  {header: "Time Zone", width: 120, sortable: false, dataIndex: 'timeZone'},
                  {header: "DST", width: 120, sortable: false, dataIndex: 'dst'}
                ],
                stripeRows: true,
                height:350,
                autoExpandColumn:6,
                header: false,
                title:'Zip Code Listing'
            });
            //render grid
            grid.render('zip-grid');
            //add scroll event listener
            grid.addListener('bodyscroll',scrollListener);

        }); //end Json call listener.
          
   });  //end ext ready listener
       
//scrollListener
//fires when grid is scrolled.
function scrollListener(scrollLeft, scrollTop){
    //only handle scroll downs past highest position.
    if ( scrollTop > currentpos )
    {
        //check if we should get more data
        if ( scrollTop >  scrolloffset )
        {
            //store current grid scroll state.
            var state = grid.getView().getScrollState();
            
            //adjust scroll offset
            scrolloffset=scrollTop+scrolloffsetinterval;
            //adjust current page
            currentpage=currentpage+1;
            
            //initialize temp data store for handler response.
            var myData
            //call GetZipCodes.ashx to retrieve zip code data for next pages of data
            var s = new GetZipCodes();
            s.GetZipCodesList(
            itemsperpage*pagestofetch,
            currentpage,
            function(response) {  //async callback fires when handler returns data.
                //store response.
                myData = response;
                //append items to json store.
                store.loadData(myData,true);
                    
                //restore grid scroll state.
                grid.getView().restoreScroll(state);
                
                //set current count.
                var x = document.getElementById("currentcount");
                x.innerHTML = store.getCount();
                
                //set skip take values.
                var y = document.getElementById("skip1");
                    y.innerHTML = (((currentpage-1) * pagestofetch) * itemsperpage);
                var z = document.getElementById("skip2");
                    z.innerHTML = (((currentpage-1) * pagestofetch) * itemsperpage);
                var a = document.getElementById("take1");
                    a.innerHTML = (itemsperpage * pagestofetch);
                var b = document.getElementById("take2");
                    b.innerHTML = (itemsperpage * pagestofetch);
                
                 }); //end: async callback fires when handler returns data.
                 
        }  //end: check if we should get more data
        
        //reset current scroll position
        currentpos=scrollTop;
        
    } //end last position test.
    
} //end: scrollListener

Download the demo project for the complete source code and database. To view the LINQ and base class code online, please take a look at Using LINQ to paginate your ObjectDataSource.

History

• 4/03/2008: Posted code samples and article.
• 4/03/2008: Uploaded sample projects.