Recently I was working with jQGrid when I came across a major block, the solution of which I now wish to share through this tip.
In case you don’t know, jQGrid is a free jQuery component which has a lot of features and can be used to display data in tabular format like you do with GridView Control in ASP.NET [refer: http://www.trirand.com/blog/ ]
While working with jQGrid at Client end and JSON string returned by WCF service running on the Server, I found that all functionalities were working fine except sorting and paging. It is quite well-known that jQGrid supported sorting as well as paging but there was some problem in my case, so I set out to investigate.
The hidden fact : Each and everytime the new request is being made on JQGrid page, It calls the server for update, delete, add, paging, sorting etc. so just like we have separate methods/modules to handle update, delete command, we should also handle code for paging and sorting. So, how does the paging and sorting work for jQGrid ?
Simple: Each time when the next page is being requested or a column is being clicked to sort the grid, it send some httpRequest to Server, and our server side code should be well enough to handle these request.
For eg:
Page Size is obtained thorugh -> HttpContext.Current.Request[“rows”]; //No of records in a page
Page Number is obtained trhugh -> HttpContext.Current.Request[“page”]; // page number
Sort Column Header is obtained through -> HttpContext.Current.Request[“sidx”]; //column of grid to sort
and Sort Order is obtained thorugh -> HttpContext.Current.Request[“sord”]; //ascending/descending
(Remember Sort Coulmn will be the name given to column model defined in jQGrid that may or may not be equivalent to DataBaseColumn).
To handle sorting, execute the database query to fill poulate the jQGrid with additonal sorting coulmns and sorting order. which returns the results as collection of DataContract( http://msdn.microsoft.com/en-us/library/ms733127.aspx) defined in WCF service.
Now to handle paging, i wrote following commands at my WCF end:
Public GridData(required params,1,2...)
{
//some initial code
List lstrawData = ...//Get the actual results here (rows after sorting)
int pageSize = Convert.ToInt32(HttpContext.Current.Request["rows"]);
int page = Convert.ToInt32(HttpContext.Current.Request["page"]);
int rowsCount = lstrawData.Count;
int innerRows = rowsCount - ((page - 1) * pageSize);
//SKIP() and Take() are keywords used in LINQ to get paged data
List pagedList = lstrawData.Skip((page - 1) * pageSize).Take(pageSize).ToList();
//GridData is another DataContract defined in WCF service which is being rturnde and bound to jQgrid
GridData gridData = new GridData();
gridData.page = page;
gridData.records = rowsCount;
gridData.total = (rowsCount % pageSize) == 0 ? rowsCount % pageSize : rowsCount / pageSize + 1;
gridData.rows = pagedList;
return gridData ; //this contains the actual paged data with page, pagesize, totalNumber of rows details.
}