We have a scenario where we want to use on demand paging for ExtJS grid using some smart AJAX techniques. One method could be to use default functionality of ExtJS grid with ExtJS PagingToolbar in its bottom bar; and ExtJS store associated with this grid is using httpProxy to fetch data. This httpProxy requires a url of aspx page only and it will fetch data from this page. This seems quite easy but there are some issues with this technique. We dont want the Page_Load method to be called every time creating a complete page on server side. Other way is to use WebMethods which are quite efficient. But ExtJS has no proxy for Page Methods. In this tutorial we will be creating a PageMethodProxy extended from DataProxy of ExtJS and will use it with JsonStore of ExtJS. Lets start:

An Illustration of On-Demand Paging Using EXTJS Grid and ASP.NET

Lets start by creating a PageMethod which takes page index and number of required results on a page as parameter and returns an object of ProductList type.

using System;
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;
using System.Text;
using System.Collections.Generic;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    /// <summary>
    /// This method will be called from the client side using PageMethods
    /// and its primary responsibility is to fetch records from the database
    /// using that startRowIndex and MaximumRows allowed
    /// </summary>
    /// <param name="PageNumber">Page Number of the grid</param>
    /// <param name="MaximumRows">Page Size</param>
    /// <returns>Rows in the form of a list</returns>
    [System.Web.Services.WebMethod]
    public static ProductList GetProducts(int PageNumber,int MaximumRows)
    {
        //calculating the start row index
        int Start = PageNumber + 1;
        //calculating the end row index
        int End = PageNumber + MaximumRows;
        ProductList productList = new ProductList();
        //setting up sql connection to execute the query
        SqlConnection sqlConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");
        sqlConnection.Open();
        //setting up the command object with a parameterized query and sql connection
        using (SqlCommand command = new SqlCommand(GetQuery(), sqlConnection))
        {
            //Creating Sql Start Paramter
            SqlParameter StartParameter = new SqlParameter();
            StartParameter.DbType = DbType.Int32;
            StartParameter.ParameterName = "startRowIndex";
            StartParameter.Value = Start;
            //Creating Sql End Parameter
            SqlParameter EndParameter = new SqlParameter();
            EndParameter.DbType = DbType.Int32;
            EndParameter.ParameterName = "endRowIndex";
            EndParameter.Value = End;
            //Adding Start Parameter to the command object
            command.Parameters.Add(StartParameter);
            //Adding End Parameter to the command object
            command.Parameters.Add(EndParameter);
            //Executing Reader
            using (IDataReader reader = command.ExecuteReader())
            {
                //Creating a list of objects from the fetched rows
                while (reader.Read())
                {
                    productList.lstProducts.Add(ConstructProduct(reader));
                }
            }
        }
        //closing the connection
        sqlConnection.Close();
        //Assigning total row count for the given query so that it can
        //be used by the grid to set up total number of pages
        productList.RowCount = GetRowCount();
        return productList;
    }
    /// <summary>
    /// Builds the Actual Parameterized Query which handles On-Demand Paging using
    /// ROW_NUMBER() feature given by SQL Server 2005
    /// </summary>
    /// <returns>Parameterized Query</returns>
    private static String GetQuery()
    {

        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.Append(" SELECT * FROM ( ");
        stringBuilder.Append(" SELECT	ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS row_num,ProductID,[Name],ProductNumber,SafetyStockLevel,ReorderPoint ");
        stringBuilder.Append(" FROM	Product ");
        stringBuilder.Append(" ) AS TempTable WHERE row_num>=@startRowIndex AND row_num<=@endRowIndex ");
        return stringBuilder.ToString();
    }
    /// <summary>
    /// Builds the Query such that it return the count of the
    /// total number of account the query will select without
    /// paging
    /// </summary>
    /// <returns>Query to return total number of records</returns>
    private static String GetRowCountQuery()
    {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.Append(" SELECT COUNT(*) FROM ( ");
        stringBuilder.Append(" SELECT	ProductID,[Name],ProductNumber,SafetyStockLevel,ReorderPoint ");
        stringBuilder.Append(" FROM	Product ");
        stringBuilder.Append(" ) AS TempTable");
        return stringBuilder.ToString();
    }
    /// <summary>
    /// Executes the Actual Query without paging in order to
    /// fetch total number of records
    /// </summary>
    /// <returns>Total Number of records</returns>
    private static int GetRowCount()
    {
        int rowCount;
        SqlConnection sqlConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");
        sqlConnection.Open();
        using (SqlCommand command = new SqlCommand(GetRowCountQuery(), sqlConnection))
        {
            rowCount = Convert.ToInt32(command.ExecuteScalar());
        }
        sqlConnection.Close();
        return rowCount;

    }
    /// <summary>
    /// Constructs Product Entity from the IDataReaer
    /// </summary>
    /// <param name="reader">Reader (Already Executed)</param>
    /// <returns>Product Entity</returns>
    private static Product ConstructProduct(IDataReader reader)
    {
        Product product = new Product();

        int i = reader.GetOrdinal("ProductID");
        if (!reader.IsDBNull(i))
            product.ProductID = reader.GetInt32(i);
        i = reader.GetOrdinal("Name");
        if (!reader.IsDBNull(i))
            product.Name = reader.GetString(i);
        i = reader.GetOrdinal("ProductNumber");
        if (!reader.IsDBNull(i))
            product.ProductNumber = reader.GetString(i);
        i = reader.GetOrdinal("SafetyStockLevel");
        if (!reader.IsDBNull(i))
            product.SafetyStockLevel = Convert.ToInt32(i);
        i = reader.GetOrdinal("ReorderPoint");
        if (!reader.IsDBNull(i))
            product.ReorderPoint = Convert.ToInt32(i);

        return product;
    }
}

Now we include our magical PageMethodProxy on our page.

Ext.data.PageMethodProxy = function(config){
	Ext.data.PageMethodProxy.superclass.constructor.call(this);
	Ext.apply(this, config);
};

Ext.data.PageMethodProxy.TRANS_ID = 1000;
Ext.data.PageMethodProxy.arr_trans = Array();

Ext.extend(Ext.data.PageMethodProxy, Ext.data.DataProxy, {
    load : function(params, reader, callback, scope, arg) {
        if(this.fireEvent("beforeload", this, params) !== false){

            var p = Ext.apply(params, this.extraParams);

            var transId = ++Ext.data.PageMethodProxy.TRANS_ID;
            var trans = {
                id : transId,
                params : params,
                arg : arg,
                callback : callback,
                scope : scope,
                reader : reader
            };

            eval("PageMethods." + this.pageMethod + "(p.start,p.limit,this.handleResponse)");

            Ext.data.PageMethodProxy.arr_trans[Ext.data.PageMethodProxy.TRANS_ID] = trans;
        }
        else{
            callback.call(scope||this, null, arg, false);
        }

    },	

    isLoading : function(){
        return this.trans ? true : false;
    },

    abort : function(){
        if(this.isLoading()){
            this.destroyTrans(this.trans);
        }
    },

    handleResponse : function(o){
        var trans = Ext.data.PageMethodProxy.arr_trans[Ext.data.PageMethodProxy.TRANS_ID];
        var result;
        try {
            result = trans.reader.readRecords(o);
        }catch(e){
            this.fireEvent("loadexception", this, o, trans.arg, e);
            trans.callback.call(trans.scope||window, null, trans.arg, false);
            return;
        }
        this.trans = false;
        trans.callback.call(trans.scope||window, result, trans.arg, true);
    }
});

You should have following three Javascripts on your page.

<script type="text/javascript" src="extjs/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="extjs/ext-all-debug.js"></script>
<script type="text/javascript" src="extjs/pagemethods-proxy.js"></script>

We start by making an object of our PageMethodProxy in Ext.onReady

Ext.onReady(function(){
    // lets make an object of pagemethod proxy first
    var pmProxy = new Ext.data.PageMethodProxy({
	    pageMethod: 'GetProducts'
    });
});

Now we make a JsonStore which uses the object of this proxy and specifies properties of Json.

Ext.onReady(function(){
    // lets make an object of pagemethod proxy first
    var pmProxy = new Ext.data.PageMethodProxy({
	    pageMethod: 'GetProducts'
    });

    // create the Data Store
    var store = new Ext.data.JsonStore({
        root: 'lstProducts',
        totalProperty: 'RowCount',
        idProperty: 'ProductID',
	    fields: [
            'Name','ProductID', 'ProductNumber', 'ReorderPoint','SafetyStockLevel'
        ],
        proxy:pmProxy
    });
});

Make a PagingToolbar object to be used in grid.

Ext.onReady(function(){
    // lets make an object of pagemethod proxy first
    var pmProxy = new Ext.data.PageMethodProxy({
	    pageMethod: 'GetProducts'
    });

    // create the Data Store
    var store = new Ext.data.JsonStore({
        root: 'lstProducts',
        totalProperty: 'RowCount',
        idProperty: 'ProductID',
	    fields: [
            'Name','ProductID', 'ProductNumber', 'ReorderPoint','SafetyStockLevel'
        ],
        proxy:pmProxy
    });

    // create paging bar
    var pagingBar = new Ext.PagingToolbar({
        pageSize: 10,
        store: store,
        emptyMsg: "No topics to display"
    });
});

At the end create a grid placeholder div and create the grid object.

Ext.onReady(function(){
    // lets make an object of pagemethod proxy first
    var pmProxy = new Ext.data.PageMethodProxy({
	    pageMethod: 'GetProducts'
    });

    // create the Data Store
    var store = new Ext.data.JsonStore({
        root: 'lstProducts',
        totalProperty: 'RowCount',
        idProperty: 'ProductID',
	    fields: [
            'Name','ProductID', 'ProductNumber', 'ReorderPoint','SafetyStockLevel'
        ],
        proxy:pmProxy
    });

    // create paging bar
    var pagingBar = new Ext.PagingToolbar({
        pageSize: 10,
        store: store,
        emptyMsg: "No topics to display"
    });

    // create the grid
    var grid = new Ext.grid.GridPanel({
        el:'grid-products',
        width:405,
        height:300,
        title:'Adventure Works Products',
        store: store,
        stripeRows: true,
        loadMask: true,
        enableHdMenu: false,

        // grid columns
        columns:[{
    	    header: "id",
    	    id: 'ProductID',
            dataIndex: 'ProductID',
            hidden:true
        },{
            header: "Product Name",
            dataIndex: 'Name',
            width: 200
        },{
            header: "Product Number",
            dataIndex: 'ProductNumber',
            width: 100
        },{
            header: "Safety Stock Level",
            dataIndex: 'SafetyStockLevel',
            width: 100
        }],
        // put paging bar on the bottom
        bbar: pagingBar
    });

    grid.render();

    // set parameters for initial loading
    store.load({
	    params:{
		    start:0,
		    limit:10
	    }
    });
});

• And thats all
• Cheers, don’t forget to leave comments/feedback
• Download Source Code Here