Jack Altiere

Creating a grid of data is a common task when building a dynamic website.  I recently started looking for a good grid component that would play nice with both JQuery and ASP.NET MVC.  The solution I ended up with is jqGrid.    I’m going to walk through a simple example of setting up a grid and populating it via JSON with an MVC controller.  I’ll also show how to add, edit and delete rows to the grid.

Setup

First, I created a simple User table and generated my Linq to SQL classes.  I just want to show a simple grid of users, showing ID, First Name, Last Name, Email address and Birthday.  When we are done, the finished grid will look something like this:

One thing that I like about this grid is that it supports jQuery UI right out of the box.  After downloading jQuery UI and jqGrid and following the installation instructions, just reference the relevent .css and .js files to get started, which I did in a master page:

<link href="../../Content/jquery-ui-1.8.custom.css" rel="Stylesheet" type="text/css" />
<link href="../../Content/ui.jqgrid.css" rel="Stylesheet" type="text/css" />
<script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.min.js" type="text/javascript"></script>
<script src="../../Scripts/jquery-ui-1.8.custom.min.js" type="text/javascript" ></script>
<script src="../../Scripts/grid.locale-en.js" type="text/javascript"></script>
<script src="../../Scripts/jquery.jqGrid.min.js" type="text/javascript"></script>
<script src="../../Scripts/grid.custom.js" type="text/javascript"></script>

HTML

The first thing I want to do is set up my grid on the client site.  This requires two parts.  The first is the HTML landing spot for the grid, pager and search box, which looks like this:

<div id="search"></div>
<table id="grid" class="scroll" cellpadding="0" cellspacing="0"></table>
<div id="pager" class="scroll" style="text-align:center;"></div>

Javascript 

The next part is the Javascript that takes care of configuring our grid and hooking it up to the HTML elements on the page.

    $(document).ready(function() {
        jQuery("#grid").jqGrid({
            url: '/Home/SampleGridData/',
            datatype: 'json',
            mtype: 'POST',
            colNames: ['Id', 'First Name', 'Last Name', 'Email', 'Birthday'],
            colModel: [
              { name: 'UserID', index: 'UserID', width: 60, align: 'center', editable: true, editrules: { edithidden: false} },
              { name: 'FirstName', index: 'FirstName', width: 200, align: 'center', sortable: true, editable: true, edittype: 'text', editrules: { required: true} },
              { name: 'LastName', index: 'LastName', width: 200, align: 'center', sortable: true, editable: true, edittype: 'text', editrules: { required: true} },
              { name: 'Email', index: 'Email', width: 200, align: 'center', sortable: true, editable: true, edittype: 'text', editrules: { required: true, email: true} },
              { name: 'Birthday', index: 'Birthday', width: 200, align: 'center', sortable: true, editable: true, editrules: { required: true }
            ],
            pager: jQuery('#pager'),
            rowNum: 10,
            rowList: [5, 10, 20, 50],
            sortname: 'UserID',
            sortorder: "asc",
            viewrecords: true,
            imgpath: '/Content/Images',
            caption: 'JqGrid Sample',
            scrollOffset: 0
        });

        jQuery("#grid").jqGrid('navGrid', '#pager',
            { edit: true, add: true, del: true, search: false },
            { url: "/Home/EditSampleGrid", closeAfterEdit: true, beforeShowForm: function(formid) { $("#tr_UserID", formid).hide(); $("#Birthday").datepicker(); } },
            { url: "/Home/AddSampleGrid", closeAfterAdd: true, beforeShowForm: function(formid) { $("#tr_UserID", formid).hide(); $("#Birthday").datepicker(); } },
            { url: "/Home/DeleteSampleGrid" }, {});

        $("#search").filterGrid("#grid", {
            gridModel: false,
            filterModel: [{
                label: 'Search',
                name: 'search',
                stype: 'text'
            }]
       });
 });

This really isn’t as bad as it looks. Once you start getting into jqGrid, I suggest spending some time getting familiar with the wiki, I spent a lot of time there learning how the plugin works.  I will explain some of the important pieces.  First, the url above is set to /Home/SampleGridData.  This means that I have a method in my home controller called SampleGridData that is supplying me with the data to fill the grid.  The format that jqGrid expects is pretty particuliar, you can see from the documentation what is expected. 

Grid Configuration

There are a lot of options available to configure in the grid.  I want to quickly touch on some of the options I used in this sample.  A lot of your configuration is going to happen in the colModel section.  Here you can set up the width of your fields, the data types, etc.  More importantly, you can set up which columns are hidden, and what the validation rules are for your columns.  I didn’t show it, but you can also set up custom field validation as well as custom data formatting rules.  I used the editrules section for each column to mark any fields as required that I wanted, and I also took advantage of being able to set up my Email field as type ‘email’’.  This allows the grid to check to see if the input is in the correct format.  You can also make any column sortable by just applying the sortable: true parameter.

C#

This method is what I’m using to initially load the grid data.  As you can see, the code is fairly straight forward:

public ActionResult SampleGridData(string sidx, string sord, int page, int rows, string search)
{
    var db = new BlogSamplesDataContext();

    var pageIndex = Convert.ToInt32(page) - 1;
    var pageSize = rows;
    var totalRecords = db.Users.Count();
    var totalPages = (int)Math.Ceiling(totalRecords / (float)pageSize);

    // This is possible because I'm using the LINQ Dynamic Query Library
    var users = db.Users
            .OrderBy(sidx + " " + sord)
            .Skip(pageIndex * pageSize)
            .Take(pageSize).AsQueryable();

    var jsonData = new
    {
        total = totalPages,
        page = page,
        records = totalRecords,
        rows = (
            from User u in users
            select new {
                i = u.UserID,
                cell = new[] { u.UserID.ToString(), u.FirstName, u.LastName, u.Email, u.Birthday.Value.ToShortDateString() }
        }).ToArray()
    };

    return Json(jsonData);
}

For the sake of the example I’m doing everything in the controller rather than implementing a repository, which I would normally do.  One thing to point out, I did not choose the parameter names in this method. (with the exception of search, but we’ll get to that later)  This is the data that jqGrid is going to post to my controller to handle sorting, paging, filtering, etc.

You may notice above that my OrderBy statement is taking a string parameter.  This is possible through the LINQ Dynamic Query Library, which allows you to use string based expressions rather than type safe Lambda expressions in your queries.  Using this library allows me to use the sorting and ordering columns that are passed in from the grid directly.  Make sure that your client indexes match your database column names or this will not work! 

Adding A Record

Now that the grid is successfully loading data, the next step is adding records from the grid.  This actually isn’t very hard using jqGrid.   If you press the Add icon in the bottom left of the grid, you will be presented with a dialog with all the fields set up, like this one:

This form is constructed using the properties set up in the ‘colModel’ section of the grid call.  Every field set up with the property of editable: true will be displayed on this form.   If you notice, the ID field is not showing up in this example, although it was marked as an editable field in my grid setup.  The reason for this is that I wanted to send the UserID to the controller, but I didn’t want the user to be able to edit it.  To accomplish this, I added a little bit of jQuery to my grid setup and hid the column.  At the same time, I leveraged jQuery UI and made my Birthday field a datepicker.  I handled both of these things using the beforeShowForm event available in the grid.

{ url: "/Home/EditSampleGrid", closeAfterEdit: true, beforeShowForm: function(formid) { $("#tr_UserID", formid).hide(); $("#Birthday").datepicker(); } },
{ url: "/Home/AddSampleGrid", closeAfterAdd: true, beforeShowForm: function(formid) { $("#tr_UserID", formid).hide(); $("#Birthday").datepicker(); } },

The grid assigns ID’s in this way according to your column indexes.  Since my ID field is named UserID, I can access it’s row with the selector of $(“#tr_UserID”) and my birthday textbox with the selector of $(“#Birthday”).  As you can see from the client code, I’m setting up a method in my Home controller called AddSampleGrid to process my grid data.

public ActionResult AddSampleGrid(string FirstName, string LastName, string Email, string Birthday)
{
    try
    {
        var db = new BlogSamplesDataContext();

        DateTime bDay;
        if (!DateTime.TryParse(Birthday, out bDay))
            bDay = DateTime.MinValue;

        var user = new User
                       {
                           FirstName = FirstName,
                           LastName = LastName,
                           Email = Email,
                           Birthday = bDay
                       };

        db.Users.InsertOnSubmit(user);
        db.SubmitChanges();

        return Json(true);
    }
    catch (Exception)
    {
        // Do some error logging stuff, handle exception, etc.
        return Json(false);
    }
}

 

The grid will pass the parameters according to their name on the colModel, so catching them and setting them up is a breeze.

Editing a Record

Editing a record is basically the same process as adding, the only difference is that you are sending the ID along with the rest of the data.  You could easily combine the two operations into the same controller method if you want, although I normally separate them.  I’m using the same trick to hide the ID field from the edit form, but the key here is that it’s actually being sent to the controller so we can use it to figure out which record is being edited.

The dialog for editing records is nice in jqGrid.  It allows you to edit the highlighted row, and you can also use the navigation errors on the bottom left of the dialog to cycle between records.

The controller method for editing a record is similar to the add method, we just have to load the correct user and edit the fields as shown here.

public ActionResult EditSampleGrid(int UserID, string FirstName, string LastName, string Email, string Birthday)
{
    try
    {
        var db = new BlogSamplesDataContext();

        DateTime bDay;
        if (!DateTime.TryParse(Birthday, out bDay))
            bDay = DateTime.MinValue;

        var query = from u in db.Users
                    where u.UserID.Equals(UserID)
                    select u;

        var user = query.First();
        user.FirstName = FirstName;
        user.LastName = LastName;
        user.Email = Email;
        user.Birthday = bDay;

        db.SubmitChanges();

        return Json(true);
    }
    catch (Exception)
    {
        // Do some error logging stuff, handle exception, etc.
        return Json(false);
    }
}

If you notice, I’m passing the birthday as a string.  This is intentional, I just find that this makes dealing with dates much easier because you don’t have to convert between a Javascript date and a .NET date.

Deleting a Record

Deleting a record is trivial with jqGrid.  The grid will pass a parameter called id, and you can use that to delete the record in question in your controller.

public ActionResult DeleteSampleGrid(int id)
{
    try
    {
        var db = new BlogSamplesDataContext();
        var query = from u in db.Users
                    where u.UserID.Equals(id)
                    select u;

        var user = query.First();

        db.Users.DeleteOnSubmit(user);
        db.SubmitChanges();

        return Json(true);
    }
    catch (Exception)
    {
        // Do some error logging stuff, handle exception, etc.
        return Json(false);
    }
}

Filtering

The last part I want to talk about is implementing a grid filter.  Out of the box, the grid supports a filter that allows you to search specific fields.  You can see an example of this here:

While this is nice, I generally like to implement a more generic filter.   I want the user to just have to type in what they are looking for, and I’ll search all the relevant fields.  This is pretty easy, and it’s what I was using the search parameter for in the original SampleGridData method.  With just a quick check to see if I have a search string and a minor modification to the data call, I can quickly filter the grid on any field I want to.

if (!string.IsNullOrEmpty(search))
{
    // This is possible because I'm using the LINQ Dynamic Query Library
    var users = (from u in db.Users
             where u.FirstName.Contains(search)
                       || u.LastName.Contains(search)
                       || u.Email.Contains(search)
             select u).OrderBy(sidx + " " + sord)
                      .Skip(pageIndex*pageSize)
                      .Take(pageSize)
                      .AsQueryable();

    var totalRecords = users.Count();
    var totalPages = (int)Math.Ceiling(totalRecords / (float)pageSize);
}

If this is the way you want to go, be sure to turn off the default search parameter.  You can toggle all of the icons on the bottom left through these grid options:

jQuery("#grid").jqGrid('navGrid', '#pager',
{ edit: true, add: true, del: true, search: false },

Final Thoughts

All in all, I feel that jqGrid is a nice plugin that allows you to quickly and easily set up grid data on your site.  If you are already using jQuery UI, the fact that this grid integrates well with it is just icing on the cake.  I’ve only scratched the surface with what is possible using this plugin, so be sure to check out the product wiki.  The good news is that this product is well documented, and there are a lot of examples available to get you up and going.

I’ve had a few projects where I needed to update information on a site at a regular interval, and it’s always a better user experience if you do it asynchronously.    I always like to come up with a functional example, so I’m going to write a quick MVC page that displays all the images in a directory.  The catch is that I want add any pictures to the page that are added to this directory without having to refresh the page.   Another thing I’d like to do is use the data capabilities in JQuery to store additional information about each image.

First I wanted to create a class to hold the additional data so that I can make a strongly typed view with it.  This is what I came up with:

[Serializable]
public class UploadedImage
{
    public int Id { get; set; }
    public string ImageName { get; set; }
    public string ImagePath { get; set; }
    public int Width { get; set; }
    public int Height { get; set; }
}

After creating my class, I created my strongly typed view to accept a List<UploadedImage>.  The next step was to add the controller method to display the view.  In this method, I wanted to load the initial list of images present in the directory and pass them to the view.

public ActionResult Timer()
{
    var photos = new List<UploadedImage>();
    var dir = new DirectoryInfo(ConfigurationParser.GetAppSettingString("PhotoDirectory"));
    var counter = 1;
    foreach (var file in dir.GetFiles())
    {
        var img = Image.FromFile(file.FullName);
        var photo = new UploadedImage
                        {
                            Id = counter,
                            ImageName = file.Name,
                            ImagePath = string.Format("../../Content/Images/Photos/{0}", file.Name),
                            Height = img.Height,
                            Width = img.Width
                        };
        photos.Add(photo);
        counter ++;
    }

    ViewData.Model = photos;
    return View();
}

This is pretty self explanatory, I just loaded the photos from the directory, gathered some information about them, and assigned each an arbitrary ID.  The last step was to put them all in a list and pass them to the view.

Since the focus of this exercise is JQuery, the bulk of our work is going to take place in the view.  I’m not doing anything complicated to display the images.  All I”m doing is using an unordered list to display all of the thumbnails, and creating a div that I will use to display additional information about the image in a pop-up when it is clicked.

<h2>JQuery Delegate Example</h2>

    <ul class="thumbnails">
        <% foreach (var item in Model) { %>
        <li><a href="#"><img id="img<%= item.Id %>" src="<%=Html.Encode(item.ImagePath) %>" alt="" /></a></li>
        <% } %>
    </ul>

    <div id="popupContact">
    <a id="popupContactClose">x</a>
    <span id="imgName"></span>
    <p id="contactArea">  

    </p>
</div>
<div id="backgroundPopup"></div>  

That leaves us with the client code.  The first obstacle we have to overcome is that we need a way to monitor the photo directory to figure out when new images are added.  I ended up using a JQuery plugin I found called doTimeout that works really well.  This sets up the framework for the polling operation, to close the loop I created a web service to pass back the list of images in the directory.

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public List<UploadedImage> LoadImages()
{
    var photos = new List<UploadedImage>();
    var dir = new DirectoryInfo(ConfigurationParser.GetAppSettingString("PhotoDirectory"));
    foreach (var file in dir.GetFiles())
    {
        var img = Image.FromFile(file.FullName);
        var photo = new UploadedImage
        {
            ImageName = file.Name,
            ImagePath = string.Format("../../Content/Images/Photos/{0}", file.Name),
            Height = img.Height,
            Width = img.Width
        };
        photos.Add(photo);
    }

    return photos;
}

This method is in my .asmx web service.  The important thing to point out is that I specified that the response was going to be in JSON.  I can just return my List<UploadedImage> type, and it will be automatically be put into JSON where my client code can access it.

Before I worry about setting up the timer, I want to be able to attach data from the controller to my JSON objects.  My reasoning for this is because I don’t want to have to use unnecessary web service calls to retrieve this information if I already have it.  The reason I want to collect this data is that I want to display it in the pop up when someone clicks on an image.  To do this, I am going to use the jQuery.data() method.  When I wrote this code, it felt a little clunky, so if anyone has an idea for a better way to accomplish this leave me a comment.

var images = new Array();

$(document).ready(function() {
    // This is messy, I wonder if there is a better way to do this?
    <% foreach (var item in Model) { %>
        images[images.length] = "<%= item.ImageName %>";
        $("#img<%= item.Id %>").data('info', {
                Id: <%= item.Id %>,
                Name: "<%= item.ImageName %>",
                Width: <%= item.Width %>,
                Height: <%= item.Height %>
        });
    <% } %>
});

In addition to setting my data information, I’m populating an array that I had set up to keep track of what images are already displayed.  It’s easy to go off down a rabbit hole when you are coming up with an example, so rather than do it the right way and only bringing back images that were uploaded since the last time I checked, I’m just pulling  them all back for simplicity’s sake and adding ones that aren’t there yet.  The other thing I added that isn’t shown here is some code to display my pop up.  

The next thing to do is to set up the loop and poll the directory for new images, and then display any images that were added.

// Load the photos every 10 seconds.
$.doTimeout('tmrPhotoSearch', 10000, function() {
    $.ajax({
        type: "POST",
        contentType: "application/json; charset=utf-8",
        url: "../DataService.asmx/LoadImages",
        data: "{}",
        dataType: "json",
        success: function(result) {
            ProcessImages(result.d);
        }
    });
    return true;
});

function ProcessImages(result)
{
    var max = 0;
    var data;
    $(".thumbnails a img").each(function() {
        data = $(this).data('info');
        if (data) {
            if (data.Id > max)
                max = data.Id
        }
    });
    max++;

    var item = '';
    var id = 0;
    $.each(result, function(key, value) { 

        if (jQuery.inArray(value.ImageName, images) == -1) {
            item = "<li><a href='#'><img id='img" + max + "' src='" + value.ImagePath + "' alt='' /></a></li>";
            $(".thumbnails").append(item);

            images[images.length] = value.ImageName;

            id = "#img" + max;
            $(id).data('info', {
                    Id: max,
                    Name: value.ImageName,
                    Width: value.Width,
                    Height: value.Height
            }); 

            max++;
        }
    });
}  

One thing about the web service call I should point out, be sure to include an empty data parameter if you don’t have any parameters.  If you don’t, the content-length header won’t be generated and you could run into problems.  Notice that my service is looking for a JSON result back, and on success it’s calling my ProcessImages method, passing in the web service response.   The first part of my processing method is hokey, it’s just figuring out what the max ID is that already exists and adding 1 to it.  I would definitely not do it this way for a real app, and I would certainly have more error handling built in.  With JQuery, I’m able to loop through the results directly with the .each method since my JSON has already been translated.  All I do is check each image, and add any that are not already present dynamically to the DOM at the end of the list with the append method.  I also update my array to put the new image in there so it’s not added more than 1 time, and I add all the meta data using the .data method like before.

The last bit of logic is to set up my event for displaying the pop up when an image is clicked.  I added this to my $(document).ready function to attach the click event.  I’m also retrieving the information from the JQuery cache about the clicked image and showing it in the popup.

$(".thumbnails a img").click(function() {
    var data = $(this).data('info');
    var html = "<b>" + data.Name + "</b><br/><br/>"
             + "<i>ID: " + data.Id + "</i><br/>"
             + "<i>Height: " + data.Height + "</i><br/>"
             + "<i>Width: " + data.Width + "</i><br/>";
    $("#imgName").html(html);
    loadPopup();
});

After putting a few sample images in my photo folder and running this, you can see that it grabs the 3 images that are there and shows them.

We can also see that clicking on one of these images successfully brings up our popup window with the attributes we put in the JQuery cache.

The next thing to do is make sure that the timer is running.  The best way to do that in my opinion is with Firebug.  Firebug is a tremendous tool, and a great way to learn JQuery quickly. Using the “Net” tab, I can watch my asynchronous requests being sent off to the web service.

Now I’m going to keep the page up and drop an image into the directory.   You can see below that the image was added successfully to the page, but we have a problem.

When we click the new image, our pop up doesn’t work.  Although it took a little while to get here, this is actually the point of my example.  The problem is with our click event on the images.  When the event was wired up, the 4th image didn’t exist yet, so it obviously wasn’t selected by the JQuery selector statement, therefore no event gets attached to it. 

The solution to this problem is the JQuery delegate method.  Basically what this is doing is attaching the event to a parent DOM element, so any future elements that fit the selector that you add automatically get the event attached.  To get this to work, all we have to do is change our click event to look like this:

$(".thumbnails").delegate("a img", "click", function() {
    var data = $(this).data('info');
    var html = "<b>" + data.Name + "</b><br/><br/>"
             + "<i>ID: " + data.Id + "</i><br/>"
             + "<i>Height: " + data.Height + "</i><br/>"
             + "<i>Width: " + data.Width + "</i><br/>";
    $("#imgName").html(html);
    loadPopup();
});

After this change, I removed the 4th image from the directory and ran the page again. This time when I added the 4th image and clicked it, the popup appeared like we expected it to.

There are a few things I’d like to point out before wrapping up.  First, the delegate method is only available in JQuery 1.4.2 and later.  I think the version of JQuery that came with my MVC install was 1.4.1, so keep this in mind if you have a problem.  In fact, probably the best way to get JQuery is through a content delivery network. (I use the minified version specified here) The other thing that I’d like to point out is that the same capability is available in the JQuery .live method.  This method was introduced in version 1.3 of JQuery.  The difference is that the delegate method allows you to bind to a specific DOM element, where the live method binds to the root of the DOM tree.  This makes the delegate method faster, since it doesn’t have to bubble up as far.