Posted by Irfan in 
We will continue with the example in previous post:
On-Demand Paging using ExtJS Grid with Client-Centric ASP.NET AJAX (WebMethods)
First of all we will add config option sortable:true to all columns we want to be sortable
So, the grid code will look like this:
// 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,
sortable:true
},{
header: "Product Number",
dataIndex: 'ProductNumber',
width: 100,
sortable:true
},{
header: "Safety Stock Level",
dataIndex: 'SafetyStockLevel',
width: 100
}],
// put paging bar on the bottom
bbar: pagingBar
});
Now, you can sort the grid on client side. But this is sorting within a page only. We want to sort the grid for all products which can be achieved by server side sorting only.
Lets make it request the server for sorting. We have to add remoteSort:true to our JsonStore. So the JsonStore code will look like this:
// create the Data Store
var store = new Ext.data.JsonStore({
root: 'lstProducts',
totalProperty: 'RowCount',
idProperty: 'ProductID',
fields: [
'Name','ProductID', 'ProductNumber', 'ReorderPoint','SafetyStockLevel'
],
remoteSort:true,
proxy:pmProxy
});
Now, all of sorting information will go with params to PageMethodProxy as:
sorthaving the id of the columndirfor direction of sorting (it can have valuesASCorDESC)
We have to change the PageMethodProxy a little bit to forward this sorting information to WebMethod. After this our PageMethodProxy code will look like this:
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
};
if(typeof p.sort == "undefined")
p.sort = "";
if(typeof p.dir == "undefined")
p.dir = "";
eval("PageMethods." + this.pageMethod + "(p.start,p.limit,p.sort,p.dir,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);
}
});
Notice that we have added check for undefined sorting parameters. This is the case when no sorting is applied. We have to handle this on client side.
Now finally add the sorting code to the WebMethod which will handle the sorting parameters. The Web Method code will look like this now
[System.Web.Services.WebMethod]
public static ProductList GetProducts(int PageNumber,int MaximumRows,string SortColumnName, string SortDirection)
{
//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(SortColumnName,SortDirection), 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;
}
private static String GetQuery(String ColumnName,String SortDirection)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" SELECT * FROM ( ");
if(!ColumnName.Trim().Equals(String.Empty) && !SortDirection.Trim().Equals(String.Empty))
stringBuilder.Append(" SELECT ROW_NUMBER() OVER (ORDER BY "+ColumnName.Trim().ToUpper()+" "+ SortDirection.Trim().ToUpper()+") AS row_num,ProductID,[Name],ProductNumber,SafetyStockLevel,ReorderPoint ");
else
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();
}
And thats all. We have successfully added Server side sorting to our existing code. This will work with paging too.
You can download the complete project here:.toString() - ExtJS Serverside Grid Sorting
