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.

Guest Blogger: Scott Walters (@cpound)

Posted by indomitablehef | Filed under Uncategorized

Please join me in welcoming my friend and colleague, Scott Walters (@cpound on twitter), as a guest blogger. His first post will be on jqGrid, and I know he’s got some cool LINQ stuff he’s been working on….looking forward to reading more about that, too.

Speaking at .Net User Group on Jan 15

Posted by indomitablehef | Filed under Uncategorized

I’ll be presenting at the Nashville .Net User Group on Thursday night, January 15, 6PM at the Brentwood Library.

The topic will be “Continuous Integration with JetBrains TeamCity”:

TeamCity, the new Continuous Integration application from JetBrains (the people that brought you ReSharper) provides easily configurable continuous integration, advanced reporting and metrics, and integration with Visual Studio (and It’s free for small teams). This presentation will cover installing and configuring TeamCity and connecting it to source control, with examples using various build runners (MSBuild, NAnt, and Rake) to build .Net applications. We’ll also cover NUnit integration, artifact generation, and configuring notifications. We’ll show you how to troubleshoot broken builds, run the .Net duplicate finder build, Visual Studio integration, and how to use TeamCity as an automated deployment tool.

Tactics vs. Strategy

Posted by indomitablehef | Filed under Uncategorized

Neal Ford: Tactics vs. Strategy (SOA and the Tarpit of Irrelevancy)
First in a series on “Evolutionary SOA”…looks like great stuff.

Cecilia Laine Hefley

Posted by indomitablehef | Filed under Uncategorized

My little girl:

She was born on Saturday morning at 5:00 (6/28). 7lbs, 14 oz.

Breaking News

Posted by indomitablehef | Filed under Uncategorized

Resurrection

Posted by indomitablehef | Filed under Uncategorized

After having neglected this blog and my other blog (designindrive.com) for some time, I’ve decided to revive this one, combine the posts from design in drive, and use it to blog about whatever I feel like. In reality, I expect to be blogging mostly about software development, but I might occasionally clutter it up with personal stories or family news. I’ve learned so much in the past couple of years, and I’m learning so much now, and really enjoying my job more than I ever have. Agile Software Development, TDD, Continuous Integration, and other such buzzword-friendly topics are foremost on my mind right now, and I expect will fill the lion’s share of posts here.

One of the blogs I read regularly now is codebetter.com Today, Jeremy Miller posted his 500th post, and said this:

I originally started the blog with some fuzzy idea of writing about TDD and other Agile practices to help the other .Net developers in my then company adopt and use those practices. I don’t know if any of that stuff ever helped my company, but I’ve gotten a lot out of writing the blog posts.

When I first started blogging about software development at DesignInDrive.com, it was for the same reason…to help other .Net developers at my company adopt agile software development practices. We’re on our way now, and I’m transitioning from that purpose to simply blogging about what I’m learning, and what’s got me excited this week. Enjoy.

QCon San Francisco, 2007

Posted by indomitablehef | Filed under Uncategorized

I just returned from the QCon conference in San Francisco, which was completely AWESOME! So, hopefully I’ll be posting a lot more in the coming days/weeks about the things that I saw/heard/learned there. Stay Tuned.

My first Google Gadget - Foxmarks

Posted by indomitablehef | Filed under Uncategorized

I created my first Google Gadget this morning, to display my Foxmarks bookmarks on my “Weekly Review” tab for my Google Homepage (now called iGoogle, believe it or not). It was disgustingly easy to do.

Here’s the code:

When you first open the homepage, you have to log in to your Foxmarks account, then you get this:

Nothing to it. And since it is totally based on what the Foxmarks page renders, there’s virtually no end to what you could do with this. For example, I could just change the url and the title, upload it using Google Page Creator:

and viola! Homestar-in-a-box (with galvanized nails, of course).

For info on creating your own Google Gadgets, check out http://code.google.com/apis/gadgets/

and you can add the Foxmarks gadget to your page using the URL: http://indomitablehef.googlepages.com/Foxmarks.xml, and the Homestar Gadget using http://indomitablehef.googlepages.com/homestar.xml

Google Calendar Gets Better

Posted by indomitablehef | Filed under Uncategorized

One of the few complaints I’ve had with Google calendar was that I only got reminders from my primary calendar. I use several calendars to track my time-sensitive tasks/events/etc, and to rough out my daily plan. Now you can set reminder options for all of your calendars individually. [via GoogleOperatingSystem]