Using jqGrid’s search toolbar with multiple filters in ASP.NET MVC

Prize winner in Competition "Best ASP.NET article of February 2010"

Download sample - 350 KB

MvcGrid_Sample

Introduction

Very often, we have a requirement to display data in tabular form and manipulate it. In classic ASP.NET, built-in controls are available. It's always better to use custom scripts to solve this problem in ASP.NET MVC. jqGrid is one of such solutions.

Background

jqGrid

jqGrid is a plugin for jQuery, which allows you to display data in tabular form with greater functionality. Its features include:

supports tables within tables

supports data editing

supports tree-like structure to display data
supports themes on the jQuery UI
records searching, filtering of each column, sorting by columns, page navigation, etc.

License

"The jqGrid is released under the GPL and MIT licenses. This license policy makes the software available to everyone for free (as in free beer), and you can use it for commercial or Open Source projects, without any restriction (the freedom above). Of course, the creation of this software has taken some time, and its maintenance will take even more. All the time we use for enhancing jqGrid is time you save on your own projects. Consider all the time you have saved by simply downloading the source file and trying to give it a price: this is the amount of money you should consider sending us to continue our good work."

Official site: trirand.com. Version 3.6.3. is available currently. Note: I use an older version. It has some bugs in IE, but in Firefox it works alright.

Using the code

Using jqGrid

Consider which files we must include to use this plug-in:

<link href="../../Scripts/jqGrid/themes/smoothness/jquery-ui-1.7.2.custom.css" 
  rel="stylesheet" type="text/css" />
<link href="../../Scripts/jqGrid/themes/ui.jqGrid.css" 
  rel="stylesheet" type="text/css" />
<script language="javascript" type="text/javascript" 
  src="<%= Url.Content("/Scripts/jquery-1.3.2.js") %>"></script>
<script language="javascript" type="text/javascript" 
  src="<%= Url.Content("/Scripts/jquery-ui-1.7.2.custom.min.js") %>">

It's obvious that we should include jQuery and JQuery UI scripts, the jqGrid plug-in, and the jqGridHomeIndex.js file which contains the grid declaration. Besides, there are some auxiliary files in the directory \Scripts\jqGrid. We must set the jqGrid settings to help the plug-in to configure itself properly for our needs. In it, we define the headers, columns, format of the data used, the size of the table, etc.:

$('#grid').jqGrid({
    colNames: ['Online', 'Computer', 'IP', 'User'],
    colModel: [
                { name: 'IsOnline', width: 100, index: 'IsOnline', 
                  searchoptions: { sopt: ['eq', 'ne']} },
                { name: 'Name', index: 'Name', 
                  searchoptions: { sopt: ['eq', 'ne', 'cn']} },
                { name: 'IP', index: 'IP', 
                  searchoptions: { sopt: ['eq', 'ne', 'cn']} },
                { name: 'User', index: 'User', 
                  searchoptions: { sopt: ['eq', 'ne', 'cn']} }
              ],
    sortname: 'Name',
    rowNum: 10,
    rowList: [10, 20, 50],
    sortorder: "asc",
    datatype: 'json',
    caption: 'Result of scanning',
    viewrecords: true,
    mtype: 'GET',
    jsonReader: {
        root: "rows",
        page: "page",
        total: "total",
        records: "records",
        repeatitems: false,
        userdata: "userdata"
    },
    url: "/Home/GetData"
})

You can find a lot of information about this plug-in in the Internet, so I won't talk about its settings.

Search

jqGrid has the ability to set filters for searching the necessary data only. For this, in its navigator panel, the following options for searching should be provided:

.navGrid('#pager', { view: false, del: false, add: false, edit: false },
   {}, // default settings for edit
   {}, // default settings for add
   {}, // delete
   {closeOnEscape: true, multipleSearch: true, 
         closeAfterSearch: true }, // search options
   {}
 );

multipleSearch - allows search by multiple criteria
closeOnEscape - allows to close the search toolbar by pressing the Escape key
closeAfterSearch - allows to close the search toolbar after search

But, we must also determine which operations are available for searching on a particular column of the table. It's done in the colModel section in the searchoptions param:

colModel: [
{ name: 'IsOnline', width: 100, index: 'IsOnline', 
  searchoptions: { sopt: ['eq', 'ne']} },
{ name: 'Name', index: 'Name', searchoptions: { sopt: ['eq', 'ne', 'cn']} },
{ name: 'IP', index: 'IP', searchoptions: { sopt: ['eq', 'ne', 'cn']} },
{ name: 'User', index: 'User', searchoptions: { sopt: ['eq', 'ne', 'cn']} 
}],

A set of operations is defined for each field here:

eq - equal
ne - not equal
cn - contains

A pop-up search window will be available after we set these settings:

The drop down list match is used to determine how these settings are linked to each other - with the help of the AND or the OR operator. "+" and "-" are used to add or delete new filtering options. The server will send an asynchronous request with the following parameters after we press the Find button:

_search = true
filters {
"groupOp":"AND",
"rules":[
{"field":"IsOnline","op":"eq","data":"True"},
{"field":"Name","op":"cn","data":"asdasd"}
   ]
}
nd = 1265873327560
page = 1
rows = 10
sidx = Name
sord = asc

_search - determines if filtering is used
filters - if filtering is used, it provides information in JSON-format on its parameters:

groupOp - operator which applies to a group of rules, Rules (AND and OR)
rules - a set of rules, where:

field - a field where filtering is done
op - an operation which the user selected
data - a filter which the user entered

page - page number
rows - page size
sidx - a field where sorting is executed
sord - sorting direction (asc or desc)

The request is sent by a URL which is set in the jqGrid:

mtype: 'GET',
url: "/Home/GetData"

Now the server must process the request and return the relevant data.

jqGrid processing

The method which will handle this request is declared as follows:

public JsonResult GetData(GridSettings grid)

As shown, it is served with a strongly typed object GridSettings, and in response, it returns JSON-data. In order to bring the object to the method , we use the ASP.NET MVC feature, ModelBinder, which is provided to allow Action methods to take complex types as their parameters.

First of all, we define several types, which will contain data from the jqGrid:

GridSettings - stores the jqGrid structure

public class GridSettings
{
    public bool IsSearch { get; set; }
    public int PageSize { get; set; }
    public int PageIndex { get; set; }
    public string SortColumn { get; set; }
    public string SortOrder { get; set; }

    public Filter Where { get; set; }
}

Filter - a filter which is defined by the user in the search toolbar

[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;
        }
    }
}

The factoring method Create allows to create an object of this class from the serialized JSON data.

Rule - represents a rule from the filter

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

In fact, these types are identical to the corresponding structures in jqGrid. Next, we create a class GridModelBinder, which inherits from IModelBinder and overrides the BindModel method. Inside this method, we expect the HttpContext.Request object will contain a query string from jqGrid:

public class GridModelBinder : IModelBinder
{
    public object BindModel(ControllerContext controllerContext, 
                            ModelBindingContext bindingContext)
    {
        try
        {
            var 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",
                Where = Filter.Create(request["filters"] ?? "")
            };
        }
        catch
        {
            return null;
        }
    }
}

In order to call this method when accessing Home/GetData, you must add the following attribute to the GridSettings class:

[ModelBinder(typeof(GridModelBinder))]
public class GridSettings

Now a grid object in the GetData method of the Home controller will be initialized correctly.

Filtering and sorting on the server

After we have learned how to get typed form data from the jqGrid, it would be nice to handle and apply them to the collection. Reflection and expression trees will help us here. Here is some information on expression trees from MSDN:

"Expression trees represent language-level code in the form of data. The data is stored in a tree-shaped structure. Each node in the expression tree represents an expression, for example, a method call or a binary operation such as x < y. The following illustration shows an example of an expression and its representation in the form of an expression tree. The different parts of the expression are color coded to match the corresponding expression tree node in the expression tree. The different types of the expression tree nodes are also shown."

In the LinqExtensions class, I wrote two methods that use this approach:

OrderBy
Where

The OrderBy method is used for sorting:

public static IQueryable<T> OrderBy<T>(
   this IQueryable<T> query, string sortColumn, string direction)

This is the extension method of the IQueryable<T> object, with these parameters:

sortColumn - the name of the column which is sorted
direction - direction of sorting

Let us now consider what is done in this method:

Define sort order:

string methodName = string.Format("OrderBy{0}",
  direction.ToLower() == "asc" ? "" : "descending");

Then, we construct the lambda-expression as p => p.Name (or System.Linq.Expressions.Expression<func<t,tkey>>). We create the parameter p, whose type is defined in ElementType.

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

So we get a member of the class, which will be sorted. This code takes into account that a class can be a nested class. In this case, it's expected that they will be separated by a dot:

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

The Lambda-expression is completed by the creation of the object which represents calling a method:

LambdaExpression orderByLambda = Expression.Lambda(memberAccess, parameter);
MethodCallExpression result = Expression.Call(
                      typeof(Queryable),
                      methodName,
                      new[] { query.ElementType, memberAccess.Type },
                      query.Expression,
                      Expression.Quote(orderByLambda));

Return IQuerable<T>:

return query.Provider.CreateQuery<T>(result);

The Where method is used for filtering:

public static IQueryable<T> Where<T>(this IQueryable<T> query,
              string column, object value, WhereOperation operation)

column - name column
value - filtering value
operation - operation used for filtration

The enumeration WhereOperation is declared as follows:

public enum WhereOperation
{
    [StringValue("eq")]
    Equal,
    [StringValue("ne")]
    NotEqual,
    [StringValue("cn")]
    Contains
}

Here, I use the StringValue attribute, which allows to set the string value of the transfer line, which is very convenient in our case. More information about the StringValue attribute can be found here: StringValueAttribute by Matt Simner.

Depending on the type of operation, we must construct an appropriate lambda-expression. For this, we can use the predefined Equal and NotEqual methods of the Expression class, or use Reflection to access the desired method of another class, as we do in the case of the Contains operation:

Expression condition = null;
LambdaExpression lambda = null;
switch (operation)
{
    //equal ==
    case WhereOperation.Equal:
        condition = Expression.Equal(memberAccess, filter);
        break;
    //not equal !=
    case WhereOperation.NotEqual:
        condition = Expression.NotEqual(memberAccess, filter);
        break;
    //string.Contains()
    case WhereOperation.Contains:
        condition = Expression.Call(memberAccess,
            typeof(string).GetMethod("Contains"),
            Expression.Constant(value));
        break;
}
lambda = Expression.Lambda(condition, parameter);

By setting these methods, we can take advantage of them. During filtering, we must go through the entire collection of rules with a call to the Where method. Here is how we filter with the usage of the AND operator:

foreach (var rule in grid.Where.rules)
   query = query.Where<computer>(rule.field, rule.data,
             (WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));

Filtering using the OR operator:

var temp = (new List<computer>()).AsQueryable();
foreach (var rule in grid.Where.rules)
{
    var t = query.Where<computer>(
    rule.field, rule.data,
    (WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op));
    temp = temp.Concat<computer>(t);
}
//remove repeating records
query = temp.Distinct<computer>();

Sorting is simpler:

query = query.OrderBy<computer>(grid.SortColumn, grid.SortOrder);

Return data jQuery

Now, when we have a collection of the required data, we can send it to the client. Let's see how this is done:

Counting the number of records which satisfy the conditions:

var count = query.Count();

Paging:

var data = 
  query.Skip((grid.PageIndex - 1) *grid.PageSize).Take(grid.PageSize).ToArray();

Convert to the data format which jqGrid expects. Here, an anonymous class can help us very much:

var result = new
{
    total = (int)Math.Ceiling((double)count / grid.PageSize),
    page = grid.PageIndex,
    records = count,
    rows = (from host in data
            select new
            {
                IsOnline = host.IsOnline.ToString(),
                Name = host.Name,
                IP = host.IP,
                User = host.User,
            }).ToArray()
};

Serialize in JSON format and send to the client:

return Json(result, JsonRequestBehavior.AllowGet);

Data source

We can use the database as a data source. To do this, we need to use ORM tools such as NHibernate (you must use the LinqToNHibernate extension) and LinqToSql, which provides the IQueryable interface.