jqGrid MVC Html Helper

• Download Mvc.HtmlHelper.jqGrid.zip - 937.9 KB

Introduction

JQGRID HTML helper is a sever side code wrapper that generates javascript for jqGrid used by MVC 3 applications. The HTML helper provides all the jqGrid features such ajax json data binding, jsonp, editing, filtering, and customization.

JQGRID plugin is a full feature open source html grid control. Using jqGrid in MVC application is a little tidious manual hand coding. HTML helper simply this process and provide a consistent interface.

Background

JQGRID html helper provides an easy consistent way to render JQGRID JAVASCRIPT that constructs a grid in the web page. The helper is specifically designed for MVC 3 web page. The data binding is any data type defined by JQGRID documentation. It is preferable to use JSON data type and AJAX back to the controller to retrieve the data. During document ready, the html helper created java script function will retrieve JSON data and render the grid content. The html help automatically creates 2 <DIV> elements on the page, one for the grid and the other one is for the pager. The JSON data should contain metadata information to properly render the grid with paging capability.

jQGRID HTML supports subgrid, custom subgrid and custom tree grid. Subgrid is a recursive method that render the a grid within the parent grid. A custom subgrid when expanded, a partial view is retrieved from the controller. A custom tree grid also retrieves partial views including the final level.

Using the code

In the view, create the basic grid by adding the HTML helper namespace, and then add the grid control, add the columns, set the ajax URL call and create the controller method.

@using Mvc.HtmlHelpers
@(
    Html.jqGrid("AccountList")
 
    // columns
    .addColumn(new Column("AccountNumber").setLabel("AccountNumber").setWidth(100).setSortable(true))
    .addColumn(new Column("AccountName").setLabel("AccountName").setWidth(250).setSortable(true).setEditable(true))
    .addColumn(new Column("AccountDate").setLabel("Date").setWidth(70).setSortable(true))
    .addColumn(new Column("AccountType").setLabel("Type").setWidth(80).setSortable(true))
    .addColumn(new Column("AccountBalance").setLabel("Balance").setWidth(80).setSortable(true))
 
    // settings
    .setCaption("Account")
    .setRequestType(RequestType.get)
    .setUrl("~/Home/GetAccountList/")
    .setAutoWidth(true)
    .setHeight(400)
    .setRowNum(10)
    .setRowList(new int[]{10,15,20,50})
    .setViewRecords(true)
    .setSortName("AccountNumber")        
    .setSortOrder(SortOrder.asc)
    .setPager("pagerAccountList")
    .setPgButtons(true)
       
    // render the html
    .Render()
)

Create the view with ajax callback URL to the controller. The JSON result contains metadata such as total number of pages (total), current page (page), total number of records (records) ans rows (data content).

[AcceptVerbs(HttpVerbs.Get)]
public ActionResult GetAccountList(GridSettings gridSettings)
{
    // retrieve the sorted data
    List<Models.Account> accounts = Models.Account.GetAccounts();

    // create json data
    int pageIndex = gridSettings.pageIndex;
    int pageSize = gridSettings.pageSize;
    int totalRecords = accounts.Count;
    int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

    int startRow = (pageIndex - 1) * pageSize;
    int endRow = startRow + pageSize;

    var jsonData = new
    {
        total = totalPages,
        page = pageIndex,
        records = totalRecords,
        rows =
        (
            from Models.Account a in accounts
            where a.Row >= startRow && a.Row <= endRow
            select new
            {
                id = a.AccountNumber,
                cell = new string[] 
                {                            
                    a.AccountNumber, 
                    a.AccountName, 
                    a.AccountDate, 
                    a.AccountType, 
                    a.AccountBalance
                }
            }
        ).ToArray()
    };

    return Json(jsonData, JsonRequestBehavior.AllowGet);
}

Custom subgrid is a grid with a subgrid that has callback to the controller to retrieve partial view.

@using Mvc.HtmlHelpers
@(
    Html.jqGrid("CustomGrid")
                        
    // columns
        .addColumn(new Column("AccountNumber").setLabel("AccountNumber").setWidth(100).setSortable(true))
        .addColumn(new Column("AccountName").setLabel("AccountName").setWidth(250).setSortable(true).setEditable(true))
        .addColumn(new Column("AccountDate").setLabel("Date").setWidth(70).setSortable(true))
        .addColumn(new Column("AccountType").setLabel("Type").setWidth(80).setSortable(true))
        .addColumn(new Column("AccountBalance").setLabel("Balance").setWidth(80).setSortable(true).setAlign(Align.right))
 
    // settings
    .setUrl("~/Home/GetAccountList/")
    .setHeight(600)
    .setRowNum(20)
    .setRowList(new int[] { 20, 50, 100, 200, 300 })
    .setPager("CustomGridAccountPager")
    .setPrint(true)
    .setLoadText("")
 
    // error handler
    .onLoadError("Error loading Account List")
    
    // events
    .onBeforeRequest(@"
        var grid = $('#CustomGridAccount');
        grid.jqGrid('setLabel', 'AccountBalance', '', {'text-align':'right'});
    ")
 
     // subgrid
    .setCustomGrid(
        new jqGrid("CustomGridDetails")
        .setUrl("~/Home/CustomGridDetails?rowId='+row_id+'")
        .onLoadError("CustomGrid Account Details")
        .setSubGridOptions("{plusicon : 'ui-icon-circle-plus', minusicon : 'ui-icon-circle-minus'}")
    )
                        
    // render the html
    .Render()
)

@using jqGrid.Sample.Models
@model List<AccountDetail>
@{
    Layout = null;  
}
@foreach (AccountDetail detail in Model)
{
    <tr class="subGridRow">
        <td>
        </td>
        <td colspan="7">
            <a href="#" style="color: #0000FF">@detail.CLIENT_NAME</a>
        </td>
    </tr>
    <tr>
        <td>
        </td>
        <td colspan="7">
            <input type="checkbox" />
             <b>Portfolio: @detail.PORTFOLIO_NAME</b>
        </td>
    </tr>
    foreach (PortfolioDetail pd in detail.PortfolioDetails)
    {
        <tr>
            <td>
            </td>
            <td colspan="3">
                <table width="100%">
                    <tr>
                        <td>
                            <input type="checkbox" />
                        </td>
                        <td style="vertical-align: top; white-space: nowrap;">@pd.ACCOUNT_NAME</td>
                        <td style="vertical-align: top; width: 50">@pd.ACCOUNT_TYPE
                        </td>
                        <td style="vertical-align: top; width: 50">@pd.PROGRAM
                        </td>
                        <td>
                            <div style="white-space: nowrap; text-overflow: ellipsis; width: 15em; overflow: hidden;">@pd.INVESTMENT</div>
                        </td>
                    </tr>
                </table>
            </td>
            <td valign="top" align="right">
                <a href="#" style="vertical-align: bottom; padding-right: 7px">@pd.TOTAL_ASSETS.ToString("c0")</a>
            </td>
            <td valign="top" align="right">
                <a href="#" style="vertical-align: bottom; padding-right: 7px">@pd.CASH.ToString("c0")</a>
            </td>
            <td valign="top" align="right">
                <a href="#" style="vertical-align: bottom; padding-right: 7px">@pd.MSG_FEES.ToString("c0")</a>
            </td>
            <td valign="top" align="right">
                <a href="#" style="vertical-align: bottom; padding-right: 7px">@string.Format("{0:0.00%}", @pd.MSG_FEE_PCT / 100)</a>
            </td>
        </tr>
    }
    <tr class="subGridRow@(ViewData["RowId"])">
        <td>
        </td>
        <td colspan="7">
            <div style="padding-left: 32px">
                <div style="float: left; padding-bottom: 3px; padding-top: 3px">@Html.DropDownList(
                      "ddlListItems", new SelectList(new[] { new AccountDetail() { CLIENT_NAME = "Test" } }))</div>
                <div style="float:left">
                    <span class="btnStyleLeft"></span>
                    <div class="btnStyleCenter">
                        <div style="margin-top: 5px! important">
                            GO
                        </div>
                    </div>
                    <span class="btnStyleRight"></span>
                </div>
            </div>
        </td>
    </tr>    
}
<tr class="subGridRow@(ViewData["RowId"])">
    <td>
    </td>
    <td colspan="7">
        <img width="12" height="12" src="@(Url.Content("~/Images/yield.png"))" /> Ineligible
        security purchase
    </td>
</tr>

Custom tree has three parts, the main grid, the level subgrid, and the detail level grid. These are all partial views.

@using Mvc.HtmlHelpers
@(
    Html.jqGrid("CustomTree")
    // columns
    .addColumn(new Column("Name").setLabel("Organization").setWidth(350))
    .addColumn(new Column("AssetAllocation").setLabel("Asset Allocation"))
    .addColumn(new Column("ClientReview").setLabel("Client Review"))
    .addColumn(new Column("IneligibleActivity").setLabel("Ineligible Activity"))
    .addColumn(new Column("InvestmentGuideline").setLabel("Investment Guideline"))
    .addColumn(new Column("BillableAssets").setLabel("Billable Assets"))
    .addColumn(new Column("NonEarnedPcs").setLabel("Non-Earned PCs"))
 
    // settings
    .setRequestType(RequestType.get)
    .setUrl("~/Home/CustomTree")
    .setHeight(600)
 
    .setPager("CustomTreePager")
    .setLoadText("")
 
    .setCustomGrid(
        new jqGrid("CustomTreeLevel")
        .setUrl("~/Home/CustomTreeLevel?rowId='+row_id+'")
        .setSubGridOptions("{plusicon : 'ui-icon-plus', minusicon : 'ui-icon-minus', openicon:'ui-icon-carat-1-sw'}")
    )
 
    
    // render grid
    .Render()
)
<script type="text/javascript">
    function toggleExpCol(elementId, row_id) {
        var iconElement = $('#' + elementId);
 
        if (iconElement.hasClass('ui-icon-plus')) {
            iconElement.removeClass('ui-icon-plus');
            iconElement.addClass('ui-icon-minus');
 
            $.ajax(
            {
                type: 'get',
                contentType: 'application/json; charset=utf-8',
                url: "@Url.Content("~/Home/CustomTreeLevel?rowId=")" + row_id + '',
                success: function (data, textStatus) {
                    var newTr = $(data);
                    $(newTr).each(function (i) {
                        $(this).attr('isExpanded', false);
                        $(this).attr('parent', row_id);
                    });
                    $($('#CustomTree tr#' + row_id)).attr('isExpanded', true);
                    $($('#CustomTree tr#' + row_id)).after(newTr);
                }
            });
        }
        else {
            iconElement.removeClass('ui-icon-minus');
            iconElement.addClass('ui-icon-plus');
            var grid = $("#CustomTree").jqGrid();
 
            var getChildrenNode = function (row_id) {
                var result = [];
                var children = $(grid).find('tr[parent=' + row_id + ']');
                $(children).each(function (i) {
                    if ($(this).attr("isExpanded") == "true") {
                        var chl = getChildrenNode(this.id);
                        $(chl).each(function (i) {
                            result.push(this);
                        });
                    }
                    result.push(this);
                });
                return result;
            };
 
            var childern = getChildrenNode(row_id);
            $.each(childern, function (index, value) { $(value).remove(); });
        }
    }
</script>

Custom tree level a partial view controlled with parent ID attribute. The onclick function is on the main view that toggle expand and collapse.

@using Mvc.HtmlHelpers
@using jqGrid.Sample.Models
@model List<Organization>
@{
    Layout = null;
    string rowId = (string)ViewData["RowId"];
    int level = Convert.ToInt32(rowId.Split('_')[0]) + 1;
    int width = (int)(12 * level * 1.5);
    int left = width - 18;
}
@foreach (Organization org in Model)
{
    <tr id="@(org.Id)" class="ui-widget-content" role="row">
        <td></td>
        <td title="@(org.Name)" style="width: @(width)px;" role="gridcell">
            <table>
                <tr>
                    <td class="treeclick" align="right">
                        <div style="width: @(width)px;">
                            <div id="div_@(org.Id)" class="ui-icon ui-icon-plus" style="left: @(left)px;"/>
                            <script type="text/javascript">
                                $("#div_@(org.Id)").click(function () {
                                    toggleExpCol("div_@(org.Id)", "@(org.Id)");
                                });
                            </script>
                        </div>
                    </td>
                    <td title="@(org.Name)" style="white-space: nowrap" role="gridcell">@org.Name
                    </td>
                </tr>
            </table>
        </td>
        <td role="gridcell">@org.AssetAllocation</td>
        <td role="gridcell">@org.ClientReview</td>
        <td role="gridcell">@org.IneligibleActivity</td>
        <td role="gridcell">@org.InvestmentGuideline</td>
        <td role="gridcell">@org.BillableAssets</td>
        <td role="gridcell">@org.NonEarnedPcs</td>
    </tr>
}

 Updated: GridSettings.cs 

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Text;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;


namespace Mvc.HtmlHelpers
{
    public enum OPERATION
    {
        none,
        add,
        del,
        edit,
        excel
    }

    [ModelBinder(typeof(GridModelBinder))]
    public class GridSettings
    {
        public int pageIndex { get; set; }
        public int pageSize { get; set; }
        public string sortColumn { get; set; }
        public string sortOrder { get; set; }
        public bool isSearch { get; set; }
        public string id { get; set; }
        public string param { get; set; }
        public string editOper { get; set; }
        public string addOper { get; set; }
        public string delOper { get; set; }
        public Filter where { get; set; }
        public OPERATION operation { get; set; }
    }

    public class GridModelBinder : IModelBinder
    {

        public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
        {
            HttpRequestBase request = controllerContext.HttpContext.Request;
            return new GridSettings()
            {
                isSearch = bool.Parse(request["_search"] ?? "false"),
                pageIndex = int.Parse(request["page"] ?? "1"),
                pageSize = int.Parse(request["rows"] ?? "10"),
                sortColumn = request["sidx"] ?? "",
                sortOrder = request["sord"] ?? "asc",
                id = request["id"] ?? "",
                param = request["oper"] ?? "",
                editOper = request["edit"] ?? "",
                addOper = request["add"] ?? "",
                delOper = request["del"] ?? "",
                where = Filter.Create(request["filters"] ?? ""),
                operation = (OPERATION)System.Enum.Parse(typeof(OPERATION), request["oper"] ?? "none")
            };
        }

    }

    [DataContract]
    public class Filter
    {
        [DataMember]
        public string groupOp { get; set; }
        [DataMember]
        public Rule[] rules { get; set; }

        public static Filter Create(string jsonData)
        {
            try
            {
                var serializer = new DataContractJsonSerializer(typeof(Filter));
                System.IO.StringReader reader = new System.IO.StringReader(jsonData);
                System.IO.MemoryStream ms = new System.IO.MemoryStream(Encoding.Default.GetBytes(jsonData));
                return serializer.ReadObject(ms) as Filter;
            }
            catch
            {
                return null;
            }
        }
    }

    [DataContract]
    public class Rule
    {
        [DataMember]
        public string field { get; set; }
        [DataMember]
        public string op { get; set; }
        [DataMember]
        public string data { get; set; }
    }
}

To support (CUD) Add, Edit, and Delete, Grid setting is updated to handle operations such as add, edit, del and other operations. On the view, there are methods such 

.setEditUrl("~/Home/SaveGridUpdate/")
.setNavAdd(true)
.setNavDel(true)
.setNavEdit(true)

Set navigation methods (.setNav) is to enable add,edit and delete buttons on the paging row.  Controller get a callback for saving CUD when the row is edited. Use grid settings operation to check which task to for add, edit or del operations. 

public void SaveGridUpdate(GridSettings gridSettings)
{
    string id = gridSettings.id;
    switch(gridSettings.operation)
    {
        case OPERATION.add:
        {
            // do create
            break;
        }
        case OPERATION.edit:
        {
            // do update
            break;
        }
        case OPERATION.del:
        {
            // do delete
            break;
        }
    }
}

GridSettings.id is the row id that was modified, there is also a way to retrieve all the updated fields from the Request object. Do set a debug break point on the save callback method to determine how to retrieve these fields. 

Points of Interest  

http://www.trirand.com/jqgridwiki/doku.php
jqgrid wiki has plenty of documentation for all the options available.

The HTML helper contains most used features however not all features are implemented here. You are welcome to extend this helper.

Fixes by Richard

1. Fixed a bug with the "datefmt" setter logic (the string value was originally “datafmt”).
2. Added methods for "cellEdit" and "cellsubmit" for selective editing of cell data.
3. Added select, textbox, datetime, and checkbox grid fields to the sample project for performing data updating in the grid. There are now fields for each of those data types in the grid and all of those fields are editable.
4. Added submit/post logic for returning edited data to the server via a button added to the “_Grid” page.
5. Added alternate row zebra striping.
6. Re-enabled the Site.css file for a better look and feel.
7. Changed the modeling around to better represent the way that data would be created – (i.e., from back-end controller functionality), as well as adding additional hidden fields to the model that are passed back and forth.
8. Downloaded the latest version of jqGrid (4.4.1) and implemented it in the project in an unsuccessful attempt to solve the problem discussed below.

Everything works nicely, except there is a serious problem with the cell editing functionality, which I am currently researching. Here are the specifics:

If you click an editable cell, it will enter edit mode, and the cell contents can be changed. However, if you then click on another cell and fail to press the enter key first (whether you have made changes to the cell contents or not), the cell contents are cleared. The original value then cannot be restored and new values cannot be entered. It is even worse on a checkbox field. If you are on any line except the first one in the grid, this problem will happen regardless of what you do.

Furthermore, when the submit process to the server is launched by clicking the button, the contents of the hidden field used to contain the json grid data is screwed up after it is set by the JS button logic, and will contain html markup data (including “<” characters), causing ASP.Net to barf and display an “unsafe data submission” error page.

It seems ridiculous to me that the enter key needs to be pressed after making a change, or even before leaving an editable field. This looks like a jqGrid bug. Please check it out and let me know if I did anything wrong.