Using jqGrid with JsonResult in ASP.NET MVC 3

I have been working on an ASP.NET MVC 3 project with a C# backend, and I wanted to get a grid going that had grouping capabilities. I came across jqGrid and decided to give it a try. I discovered a blog post which got me close, but it still took a bit of doing. It turned out to be a pain, but I managed to get basic data display working (I'll try to update this post when I get grouping working). I decided to post the code because it wasn't straightforward, and all of the other blog & forum posts on the topic didn't quite have a complete solution. The code has a bit more detail than is necessary for this post, but I hope it is useful.

Here is my HTML...
My javascript looks like this...

There are a couple things to note.
  • Line 3: The url attribute. It took a bit of trial & error to figure out this url format for my controller method. "Asset" is the controller (my actual controller name is "AssetController"), and "GetGridContents" is the method name.
  • Line 4: "mtype: 'POST'".
  • Lines 6 - 13: The jsonReader attribute. Before adding this my webservice would return valid Json (checked using JsonLint), but the grid would remain empty with no error.
  • Lines 25 & 27: the formatter & formatoptions attributes were required to convert the date format output by the JsonResult serialization. I found that information here.

Those were the three things that weren't obvious from the jqGrid examples.
LoadGrid = function () {
    jQuery("#AssetTable").jqGrid({
        url: 'Asset/GetGridContents/',
        mtype: 'POST',
        datatype: "json",
        jsonReader: {
            root: "rows", //array containing actual data
            page: "page", //current page
            total: "total", //total pages for the query
            records: "records", //total number of records
            repeatitems: false,
            id: "ID" //index of the column with the PK in it
        },
        colNames: ['ID', 'Name', 'Description', 'Model', 'Make', 'Serial Number', 'Warehouse', 'Asset Type', 'Create User', 'Create Date', 'Update User', 'Update Date'],
        colModel: [
         { name: 'ID', index: 'ID', width: 35, sorttype: "int" },
            { name: 'Name', index: 'Name', width: 100 },
            { name: 'Description', index: 'Description', width: 100 },
            { name: 'ModelName', index: 'ModelName', width: 55 },
            { name: 'MakeName', index: 'MakeName', width: 55 },
            { name: 'SerialNumber', index: 'SerialNumber', width: 55 },
            { name: 'WarehouseName', index: 'WarehouseName', width: 60 },
            { name: 'AssetTypeID', index: 'AssetTypeID', width: 55 },
            { name: 'CreateUser', index: 'CreateUser', width: 100 },
            { name: 'CreateDate', index: 'CreateDate', width: 100, sorttype: "date", formatter: "date", formatoptions: { newformat: "m/d/Y"} },
            { name: 'UpdateUser', index: 'UpdateUser', width: 100 },
            { name: 'UpdateDate', index: 'UpdateDate', width: 100, sorttype: "date", formatter: "date", formatoptions: { newformat: "m/d/Y"} }
        ],
        rowNum: 10,
        rowList: [10, 20, 30],
        pager: '#AssetTablePager',
        sortname: 'Name',
        viewrecords: true,
        sortorder: "desc",
        caption: "Assets"
    });
    jQuery("#AssetTable").jqGrid('navGrid', '#AssetTablePager', { edit: true, add: true, del: true });
}

$(document).ready(function () {
    LoadGrid();
});
Here is the webservice code...

A few things to note here as well.
  • Line 3: Obviously "Assets" is one of my models. I'm creating a viewmodel to send to the grid.
  • Line 4: It yelled at me if I didn't have an orderby in my linq.
  • Line 24: The .SortBy method I use on the assets collection uses an extension by David Fowl. I found it here.
  • Line 25: I'm using the PagedList library written by Troy Goode (available via NuGet).
  • Lines 27 - 35: I'm building the object to return & returning it as JsonResult takes care of the serialization.
public JsonResult GetGridContents(int? page, int? rows, string sidx, string sord)
{
    var assets = from a in db.Assets
                    orderby a.AssetType.Name
                    select new
                    {
                        ID = a.ID,
                        Name = a.AssetType.Name,
                        Description = a.Description,
                        ModelID = a.ModelID,
                        ModelName = a.Model.Name,
                        MakeID = a.Model.MakeID,
                        MakeName = a.Model.Make.Name,
                        SerialNumber = a.SerialNumber,
                        WarehouseID = a.WarehouseID,
                        WarehouseName = a.Warehouse.Name,
                        AssetTypeID = a.AssetTypeID,
                        CreateUser = a.CreateUser,
                        CreateDate = a.CreateDate,
                        UpdateUser = a.UpdateUser,
                        UpdateDate = a.UpdateDate
                    };

    var sortedAssets = assets.SortBy(sidx, sord.Contains("DESC"));
    var pagedAssets = sortedAssets.ToPagedList(page ?? 1, rows ?? 20);

    var results = new
    {
        total = pagedAssets.PageCount, //number of pages
        page = pagedAssets.PageNumber, //current page
        records = assets.Count(), //total items
        rows = pagedAssets
    };

    return Json(results);
}

Comments

  1. I've been having issues completely understanding the concepts needed for the implementation of the data transfer. I come from studying strictly HTML and CSS, moving now into js and c#. My current need is to populate a grid list from a SQL Server db dynamically. I've seen plenty of examples and documentation, but not a good explanation of why and how it does what it does. Any suggestions for my research? -serenityconstant@yahoo.com/serenityconstant@gmail.com

    ReplyDelete
    Replies
    1. Hi, here is complete working code of jqgrid with asp.net. you can download from here :

      http://oceancloudy.com/blog/jqgrid-with-asp-net-in-csharp/

      Delete

Post a Comment

Popular posts from this blog

Fixing Conan Lock Issues

Setting up Jenkins, GoogleTest, & Mercurial (with a local repository)

Making a standard ASP.NET listbox do multiselect without holding Ctrl