indomitablehef.com » Blog Archive » Server side Paging and Sorting in .NET with jqGrid

Home

About

in·dom·i·ta·ble
adj. Incapable of being overcome, subdued, or vanquished; unconquerable.

1st
OCT

Server side Paging and Sorting in .NET with jqGrid

Posted by scott.walters | Filed under Uncategorized

One of the tasks on our radar has been replacing the comparatively heavy, slower server side grids on our pages with a lighter weight client side solution. Many of our users only have cell modem connectivity so we need to be careful about response time and bandwidth issues.We tried jqGrid as a potential candidate for this, and it worked out well on single page grids where paging and sorting weren’t required. Before we could commit to using jqGrid as our permanent grid solution though, we had to figure out how to do paging and sorting with it.

Since many grids require fields for the user to enter filtering criteria, we also had to verify that the data from these fields would be included in the posts to the server and figure out how to access them.

To demonstrate these techniques, I coded a small, self contained sample which can be downloaded here. Here’s a screenshot of the final page. Click to enlarge.

The sample form has a select for the color filter and a div to put the jqGrid placeholder objects for the grid body and pager areas.

<form id="form1" runat="server">
    Choose Color: 
    <select id = "colorSelect">
        <option value="All">All</option>
        <option value="Red">Red</option>
        <option value="Green">Green</option>
        <option value="Blue">Blue</option>
    </select>
    <br/>
    <br />
    <div>
        <table id="grid" class="scroll" cellpadding="0" cellspacing="0"></table>
        <div id="gridpager"></div> 
    </div>
</form>

This is the easy part. The rest of the markup page will take more explaining. All the script code to create the grid and set the property values for it is contained in a single call to jqGrid. The grid initialization data is passed as a JSON data structure. In the sample, this call is made inside the document ready function like this

 
$("#grid").jqGrid({
    datatype: function(postdata) {
        jQuery.ajax({
            url: '<%= ResolveUrl("~/GridSearchService.asmx/GetGridData") %>',
            data: $.toJSON(postdata),
            type: 'POST',
            dataType: "json",
            contentType: "application/json; charset=utf-8",
            complete: function(servicedata, stat) {
                if (stat == "success") {
                    var serviceJson = $.parseJSON(servicedata.responseText, true);
                    var thegrid = jQuery("#grid")[0];
                    thegrid.addJSONData(serviceJson.d);
                }
            }
        });
    },
    jsonReader: { repeatitems: false },
    mtype: 'POST',
    rowNum: 10,
    postData: { "colorToSearch": "All" },
    rowList: [10, 20, 30],
    height: 250,
    width: 500,
    colNames: ['id', 'Name', 'Color', 'Amount'],
    colModel: [
        { name: 'id', index: 'id', width: 60, sorttype: "int", hidden: true },
        { name: 'name', index: 'name', width: 100 },
        { name: 'color', index: 'color', width: 80, align: "left", sorttype: "float" },
        { name: 'amount', index: 'amount', width: 80, align: "right", sorttype: "float" }
    ],
    pager: '#gridpager',
    viewrecords: true,
    caption: "JqGridSearchAndPage Sample"
});

The jqGrid method is called with a jQuery selector for the div that will contain the grid. The first field is datatype. In the sample, this lets you specify the code that gets executed when the grid calls the server for data, which it does as soon as the grid is initialized, and for every page and sort request thereafter. There are also baked-in values for datatype like “json” and “xml”, but I couldn’t figure out how to get them to do what I needed, so the function won out.

When this function is called, it will get an object as a parameter, which will have properties containing the current values for grid state items like current page, sort field and page size. The sample function contains an ajax call to invoke a web service method, passing the grid state values as parameters. If this succeeds, the JSON it returns is parsed and added to the grid. For those of you in a hurry, the signature of the method on the server that receives this call is

public GridJsonData GetGridData(bool _search, long nd, int rows, int page,
string sidx, string sord, string colorToSearch)

The next field, jsonReader, is used to set a the repeatItems property, which has a drastic effect on how the grid interprets the returned JSON data structure. This is documented exhaustively in the jqGrid docs.

The only other field that isn’t obvious is postData. This contains a JSON data structure that gets appended to the postdata parameter passed to the data fetching function defined in datatype. This is where the values for the color filter go in the sample. It’s value when the grid is initialized is “All”, but we will change it when we select a different color.

The only other interesting part in the client code is the change event handler on the color select. This sets the new value for postData and triggers the grid reload.

$('#colorSelect').change(function() {
    var color = $('#colorSelect').val();
    $('#grid').setGridParam({ postData: { "colorToSearch": color} }).trigger("reloadGrid");
});

Now on to the server. There’s no code behind for the markup page. All the action is in the web service. For simplicity, all the code for the web service is in one file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Web;
using System.Web.Script.Services;
using System.Web.Services;
 
namespace JqGridSearchAndPage
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    [ScriptService]
    public class GridSearchService : System.Web.Services.WebService
    {
        private static List<GridRow> GridData;
        private static Random rnd = new Random();
        static GridSearchService()
        {
            GridData = new List<GridRow>();
 
            //add 100 red, 200 green, 300 blue
            //set id=rownum, name=rownum, amount=random (1-1000) / 100, tax = amount * .05
            for (int ndx = 0; ndx < 600; ndx++)
            {
                string clr;
                if (ndx < 100)
                    clr = "red";
                else if (ndx < 300)
                    clr = "green";
                else
                    clr = "blue";
 
                GridData.Add(new GridRow()
                                 {
                                     id = ndx,
                                     name = ndx,
                                     amount = ((double)rnd.Next(1, 1000))/10,
                                     color = clr
                                 });
            }
        }
 
        [WebMethod]
        [ScriptMethod(UseHttpGet = false, ResponseFormat = ResponseFormat.Json)]
        public GridJsonData GetGridData(bool _search, long nd, int rows, int page,
            string sidx, string sord, string colorToSearch)
        {
            var gdq = GridData.AsQueryable();
 
            //apply color filter
            if (colorToSearch != "All")
                gdq = gdq.Where(gd => gd.color.ToLower() == colorToSearch.ToLower());
 
            //get count
            int totalcnt = gdq.Select(gr => gr.id).Count();
 
            //apply sort
            gdq = ApplySort(gdq, sidx, sord);
 
            //get data
            var dataList = gdq.Select(gr => gr).Skip((page - 1) * rows).Take(rows).ToList();
 
            var tgd = new GridJsonData
                          {
                              page = page,
                              records = totalcnt,
                              total = totalcnt / rows,
                              rows = dataList.ToArray()
                          };
            return tgd;
        }
 
        private IQueryable<GridRow> ApplySort(IQueryable<GridRow> gdq, string sidx, string sord)
        {
            if (string.IsNullOrEmpty(sidx))
                return gdq;
 
            IQueryable<GridRow> gdqout = gdq;
 
            if (sord == "asc")
            {
                if (sidx == "name")
                    gdqout = gdqout.OrderBy(gr => gr.name);
                if (sidx == "color")
                    gdqout = gdqout.OrderBy(gr => gr.color);
                if (sidx == "amount")
                    gdqout = gdqout.OrderBy(gr => gr.amount);
            }
            else
            {
                if (sidx == "name")
                    gdqout = gdqout.OrderByDescending(gr => gr.name);
                if (sidx == "color")
                    gdqout = gdqout.OrderByDescending(gr => gr.color);
                if (sidx == "amount")
                    gdqout = gdqout.OrderByDescending(gr => gr.amount);
            }
 
            return gdqout;
        }
    }
 
    public class GridJsonData
    {
        public int page { get; set; }
        public int total { get; set; }
        public int records { get; set; }
        public GridRow[] rows { get; set; }
    }
    public class GridRow
    {
        public int id { get; set; }
        public int name;
        public string color;
        public double amount;
    }
 
}

The GridSearchService class has a static initializer that generates some grid data to simulate data returned from a database. GetGridData uses the parameter values passed to it to return the correct page from the simulated database data as a JSON array. For the JSON/C# mapping to work properly, the field names must match the parameter names on the method. This is case sensitive so look carefully. The same thing is true for the output JSON. It’s fields names have to match the names the client is expecting. For the row names, these are the values specified in the colModel field in the jqGrid call.

Once I understood the jqGrid approach to this, I had to admit that it’s pretty clean, although I did have to spend some time in Firebug and the jqGrid docs to figure it all out. Eventually, I’ll also have to figure out how to do hierarchical grids and inline editing, and will possibly do a post on that too.

Reader's Comments

Rumen Stankov | October 1st, 2009 at 10:13 am
Just to let you know, we (the guys behind jqGrid) started a beta project for jqGrid integration with ASP.NET. The approach we’ve taken is to create a server-side component, pretty much similar to what you are using to with asp:GridView (similar programming/event model and APIs)

Demo can be found here:
http://www.trirand.net/demo.aspx

Download bits and samples are also available.

Cheers,
Rumen Stankov
jqGrid

Server side Paging and Sorting in .NET with jqGrid

Reader's Comments

Leave a Reply

Post Meta

Categories

Archives

Links