Generic JqGrid Implementation in MVC

Introduction

This is my first article on Code Project. Lot of information about integration of JqGrid in ASP.NET MVC is available. We can integrate the Jqgrid with ASP.NET MVC and entity framework. Here, we introduce dynamic sorting and custom filtration using jqgrid.

Background

We face a problem in our project implementation. We created individual JQGrid implementation in each page. But it is too difficult in our entire project. So we try to generalize the jqgrid implementation.

Using the Code

Create an ASP.NET MVC Project

Initially, create an ASP.NET MVC project from Visual Studio.

Setup Database and Entity Framework

Create a table employee with the following fields - Id, EmployeeId, FirstName, LastName, DateofBirth, Designation and place. Then, insert some sample data to the Employee table.

Create an entity data model by choosing ADO.NET entity data model. Establish the connection to our sqlserver.

Choose the employee table from the window.

Installing ASP.NET MVC JqGrid from NuGet

Install the JQuery.JqGrid from nuGet package installer

Create Controller and View

Create an employee controller in controller folder.

Also create an index view for the employee controller.

Create the references for jquery-ui.css, ui.jqgrid.css, jquery-1.9.1.min.js, jqgrid.locale-en.js, jQuery.jqgrid.min.js:

Prepare Server Side

Create an enumerator GrpOpertion to handle operation values:

public enum GrpOperation
{
    AND,
    OR
}

Create an enumerator WhereOperation to handle the where clause values:

public enum WhereOperation
{
    eq,
    ne,
    lt,
    le,
    gt,
    ge,
    bw,
    bn,
    ew,
    en,
    cn,
    nc
}

Create JqGridRule Class to handle the values:

[DataContract]
    public class JqGridRule
    {
        [DataMember]
        public string field { get; set; }
        [DataMember]
        public string op { get; set; }
        [DataMember]
        public string data { get; set; }
        public WhereOperation OpEnum
        {
            get
            {
                return (WhereOperation)Enum.Parse(typeof(WhereOperation), op, true);
            }
        }
    }

Create JQGridFilter Class to collect the filtering information:

[DataContract]
    public class JqGridFilter
    {
        [DataMember]
        public string groupOp { get; set; }
        [DataMember]
        public JqGridRule[] rules { get; set; }
        public GrpOperation groupOpEnum
        {
            get
            {
                return (GrpOperation)Enum.Parse(typeof(GrpOperation), groupOp, true);
            }
        }

        private static readonly string[] FormatMapping = {
            "(it.{0} = @p{1})",                 // "eq" - equal
            "(it.{0} <> @p{1})",                // "ne" - not equal
            "(it.{0} < @p{1})",                 // "lt" - less than
            "(it.{0} <= @p{1})",                // "le" - less than or equal to
            "(it.{0} > @p{1})",                 // "gt" - greater than
            "(it.{0} >= @p{1})",                // "ge" - greater than or equal to
            "(it.{0} LIKE (@p{1}+'%'))",        // "bw" - begins with
            "(it.{0} NOT LIKE (@p{1}+'%'))",    // "bn" - does not begin with
            "(it.{0} LIKE ('%'+@p{1}))",        // "ew" - ends with
            "(it.{0} NOT LIKE ('%'+@p{1}))",    // "en" - does not end with
            "(it.{0} LIKE ('%'+@p{1}+'%'))",    // "cn" - contains
            "(it.{0} NOT LIKE ('%'+@p{1}+'%'))" //" nc" - does not contain
         };

Create a generic class JQGrid to handle the data of jqgrid details. PopulateFilter method is used to desterilize the json data.

class JqGrid<T>
    {
        public bool IsSearch;
        public string SortColumn;
        public string SortOrder;
        public int PageNo;
        public int PageSize;
        public int TotalRecords;
        public int PageCount;
        public JqGridFilter Filter;
        public IQueryable<T> List;
        public int PageIndex
        {
            get
            { return Convert.ToInt32(this.PageNo) - 1; }
        }


        public JqGrid(bool search, string sidx, string sord, int page, int rows, string filters)
        {
            this.IsSearch = Convert.ToBoolean(search);
            this.SortColumn = sidx;
            this.SortOrder = sord;
            this.PageNo = page;
            this.PageSize = rows;
            this.Filter = PopulateFilter(filters);
        }

        public static JqGridFilter PopulateFilter(string jsonData)
        {
            try
            {
                if (!String.IsNullOrEmpty(jsonData))
                {
                    var serializer = new DataContractJsonSerializer(typeof(JqGridFilter));
                    System.IO.StringReader reader = new System.IO.StringReader(jsonData);
                    System.IO.MemoryStream ms = 
                         new System.IO.MemoryStream(Encoding.UTF8.GetBytes(jsonData));
                    return serializer.ReadObject(ms) as JqGridFilter;
                }
                return null;
            }
            catch
            {
                return null;
            }
        }

Create an extension class to create the lambda extension methods. And extend the where method.

public static class Extensions
    {
        public static IQueryable<T> Where<T>(this IQueryable<T> Query, 
                                   JqGridRule[] Rules, GrpOperation groupOp)
        {
            LambdaExpression lambda;
            Expression resultCondition = null;

            ParameterExpression parameter = Expression.Parameter(Query.ElementType, "p");

            foreach (var rule in Rules)
            {
                if (string.IsNullOrEmpty(rule.field))
                    continue;

                MemberExpression memberAccess = null;
                foreach (var property in rule.field.Split('.'))
                {
                    memberAccess = 
                      MemberExpression.Property(memberAccess ?? (parameter as Expression), property);
                }

                ConstantExpression filter = 
                      Expression.Constant(StringToType(rule.data, memberAccess.Type));

                Expression e1 = memberAccess; Expression e2 = filter;
                if (IsNullableType(e1.Type) && !IsNullableType(e2.Type))
                    e2 = Expression.Convert(e2, e1.Type);
                else if (!IsNullableType(e1.Type) && IsNullableType(e2.Type))
                    e1 = Expression.Convert(e1, e2.Type);

                Expression condition = null;
                switch (rule.OpEnum)
                {
                    case WhereOperation.eq:
                        condition = Expression.Equal(e1, e2);
                        break;
                    case WhereOperation.ne:
                        condition = Expression.NotEqual(e1, e2);
                        break;
                    case WhereOperation.gt:
                        condition = Expression.GreaterThan(e1, e2);
                        break;
                    case WhereOperation.ge:
                        condition = Expression.GreaterThanOrEqual(e1, e2);
                        break;
                    case WhereOperation.lt:
                        condition = Expression.LessThan(e1, e2);
                        break;
                    case WhereOperation.le:
                        condition = Expression.LessThanOrEqual(e1, e2);
                        break;
                    case WhereOperation.cn:
                        condition = Expression.Call(memberAccess, 
                        typeof(string).GetMethod("Contains"), Expression.Constant(rule.data));
                        break;
                    case WhereOperation.bw:
                        condition = Expression.Call(memberAccess, 
                        typeof(String).GetMethod("StartsWith", new Type[] { typeof(String) }), 
                                                  Expression.Constant(rule.data));
                        break;
                    case WhereOperation.ew:
                        condition = Expression.Call(memberAccess, 
                        typeof(String).GetMethod("EndsWith", new Type[] { typeof(String) }), 
                                                  Expression.Constant(rule.data));
                        break;
                    case WhereOperation.nc:
                        condition = Expression.Not(Expression.Call
                        (memberAccess, typeof(string).GetMethod("Contains"), 
                        Expression.Constant(rule.data)));
                        break;
                    case WhereOperation.bn:
                        condition = Expression.Not(Expression.Call
                        (memberAccess, typeof(String).GetMethod("StartsWith", 
                        new Type[] { typeof(String) }), Expression.Constant(rule.data)));
                        break;
                    case WhereOperation.en:
                        condition = Expression.Not(Expression.Call
                        (memberAccess, typeof(String).GetMethod("EndsWith", 
                        new Type[] { typeof(String) }), Expression.Constant(rule.data)));
                        break;

                    default:
                        continue;
                }
                if (groupOp == GrpOperation.OR)
                {
                    resultCondition = resultCondition != null ? 
                    Expression.Or(resultCondition, condition) : condition;
                }
                else
                {
                    resultCondition = resultCondition != null ? 
                    Expression.And(resultCondition, condition) : condition;
                }
            }

            lambda = Expression.Lambda(resultCondition, parameter);

            MethodCallExpression result = Expression.Call(
                       typeof(Queryable), "Where",
                       new[] { Query.ElementType },
                       Query.Expression,
                       lambda);
            return Query.Provider.CreateQuery<T>(result);
        }

        public static object StringToType(string value, Type propertyType)
        {
            var underlyingType = Nullable.GetUnderlyingType(propertyType);
            if (underlyingType == null)
            {
                return Convert.ChangeType(value, propertyType, 
                System.Globalization.CultureInfo.InvariantCulture);
            }
            return String.IsNullOrEmpty(value) ? 
            null : Convert.ChangeType(value, underlyingType, 
            System.Globalization.CultureInfo.InvariantCulture);
        }

        static bool IsNullableType(Type t)
        {
            return t.IsGenericType && 
            t.GetGenericTypeDefinition() == typeof(Nullable<>);
        }
    }

Create GetEmployeeDetails method to filter the data from jqgrid:

public JsonResult GetEmployeeDetails
     (bool _search, string sidx, string sord, int page, int rows, string filters)
{
    JQGridEntities db = new JQGridEntities();
    JqGrid<Employee> jqg =
    new JqGrid<Employee>(_search, sidx, sord, page, rows, filters);
    IQueryable<Employee> query = db.Employees.AsQueryable();
    if (jqg.IsSearch)
    {
        query = query.Where(jqg.Filter.rules, jqg.Filter.groupOpEnum);
    }
    jqg.PageCount = query.Count();
    int pageIndex = Convert.ToInt32(page) - 1;
    int pageSize = rows;
    IEnumerable<Employee> result = query.AsEnumerable();
    if (sord.ToUpper() == "ASC" && !string.IsNullOrEmpty(sidx))
    {
        result = result.OrderBy(i => i.GetType().GetProperty(sidx).GetValue(i, null));
    }
    else if (sord.ToUpper() == "DESC" && !string.IsNullOrEmpty(sidx))
    {
        result = result.OrderByDescending
        (i => i.GetType().GetProperty(sidx).GetValue(i, null));
    }

    int totalRecords = query.Count();
    var totalPage = (int)Math.Ceiling((float)totalRecords / (float)rows);
    var jsonData = new
    {
        total = totalPage,
        page,
        records = totalRecords,
        rows = result
    };

    return Json(jsonData, JsonRequestBehavior.AllowGet);
}

Add script and Ajax call in the view:

<script>
    $('#grid').jqGrid({
        url: "/Employee/GetEmployeeDetails",
        datatype: "json",
        stype: "Get",
        colNames: ["Id", "EmployeeId", 
        "FirstName", "LastName", 
        "DatofBirth", "Designation", "Place"],
        colModel: [
            { key: true, hidden: true, name: "Id", Index: "Id" },
            { key: true, name: "EmployeeId", 
            Index: "EmployeeId", editable: true },
            { key: true, name: "FirstName", 
            Index: "FirstName", editable: true },
            { key: true, name: "LastName", 
            Index: "LastName", editable: true },
            { key: true, name: "DatofBirth", 
            Index: "DatofBirth", editable: true, formatter: 'date', 
                formatoptions: { srcformat: 'd/m/Y', newformat: 'd/m/Y' } },
            { key: true, name: "Designation", 
            Index: "Designation", editable: true },
            { key: true, name: "Place", Index: "Place", editable: true }],
        pager: jQuery("#pager"),
        rowNum: 10,
        rowList: [10, 20, 30, 40],
        height: "100%",
        viewrecords: true,
        caption: "TodoList",
        emptyrecords: "No Record To Display",
        jsonReader: {
            root: "rows",
            page: "page",
            total: "total",
            records: "records",
            repeatitems: false,
            id: "0"
        },
    }).navGrid('#pager', { del: false, add: false, edit: false }, {}, {}, {}, {
        multipleSearch: true, closeAfterSearch: true,
        onSearch: function () {
            $("#jqg1").val();
        }
    });
</script>