Click here to Skip to main content
15,860,972 members
Articles / Web Development / ASP.NET

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

Rate me:
Please Sign up or sign in to vote.
4.78/5 (68 votes)
12 Feb 2010CPOL7 min read 353.2K   10.2K   156   78
This article describes how to use jqGrid’s search toolbar with multiple filters in ASP.NET MVC.

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
  • Demo1.PNG

  • supports data editing
  • Demo2.PNG

  • 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:

XML
<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.:

JavaScript
$('#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:

JavaScript
.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:

JavaScript
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:

SearchToolbar.PNG

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:

JavaScript
_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:

C#
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
  • C#
    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
  • C#
    [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
  • C#
    [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:

C#
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:

C#
[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."

ExpressionTrees.PNG

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

  • OrderBy
  • Where

The OrderBy method is used for sorting:

C#
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:
  • C#
    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.
  • C#
    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:
  • C#
    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:
  • C#
    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>:
  • C#
    return query.Provider.CreateQuery<T>(result);

The Where method is used for filtering:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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:
  • C#
    var count = query.Count();
  • Paging:
  • C#
    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:
  • C#
    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:
  • C#
    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.

History

  • 12 February 2010 - First version of the article.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Nokia
Germany Germany
Interested in design/development of framework functionality using the best patterns and practices.

Comments and Discussions

 
GeneralRe: Fix for expensive queries in the OR operation Pin
KnutMarius14-Nov-10 22:45
KnutMarius14-Nov-10 22:45 
GeneralFixed Handling Nulls for searching Pin
abdulkaderjeelani3-Sep-10 0:23
abdulkaderjeelani3-Sep-10 0:23 
GeneralCase sensitivity [modified] Pin
Liqdfire26-Jul-10 12:25
Liqdfire26-Jul-10 12:25 
GeneralRe: Case sensitivity Pin
Liqdfire26-Jul-10 15:23
Liqdfire26-Jul-10 15:23 
GeneralRe: Case sensitivity Pin
Ilya Builuk26-Jul-10 21:01
Ilya Builuk26-Jul-10 21:01 
GeneralImproved where clause handling Pin
Devin Garner9-Jul-10 10:36
Devin Garner9-Jul-10 10:36 
GeneralRe: Improved where clause handling Pin
taber loveless20-Jul-10 7:01
taber loveless20-Jul-10 7:01 
GeneralI made a change to your code. I this is generaly beneficial Pin
ChaosSlave22-Jun-10 3:53
ChaosSlave22-Jun-10 3:53 
GeneralRe: I made a change to your code. I this is generaly beneficial Pin
Ilya Builuk22-Jun-10 19:05
Ilya Builuk22-Jun-10 19:05 
GeneralVery nice presentation Pin
das.gaurav18-May-10 22:52
das.gaurav18-May-10 22:52 
Generalthank for good content Pin
Asura02726-Apr-10 23:46
Asura02726-Apr-10 23:46 
GeneralWell done Pin
Islamegy3-Apr-10 4:31
Islamegy3-Apr-10 4:31 
GeneralWhere do I get the free jqGrid? [modified] Pin
Cebocadence26-Mar-10 10:39
Cebocadence26-Mar-10 10:39 
GeneralRe: Where do I get the free jqGrid? Pin
Ilya Builuk27-Mar-10 2:15
Ilya Builuk27-Mar-10 2:15 
GeneralIt turned commercial Pin
manticorebp23-Mar-10 3:58
manticorebp23-Mar-10 3:58 
GeneralRe: It turned commercial Pin
Ilya Builuk23-Mar-10 4:34
Ilya Builuk23-Mar-10 4:34 
GeneralRe: It turned commercial Pin
Liqdfire25-Jul-10 14:15
Liqdfire25-Jul-10 14:15 
GeneralBrilliant Pin
Brij22-Mar-10 19:43
mentorBrij22-Mar-10 19:43 
GeneralCongratulations Pin
Sky Sanders22-Mar-10 8:04
Sky Sanders22-Mar-10 8:04 
GeneralRe: Congratulations Pin
Ilya Builuk22-Mar-10 8:50
Ilya Builuk22-Mar-10 8:50 
GeneralHi Compnaion Pin
Anil Srivastava18-Mar-10 18:43
Anil Srivastava18-Mar-10 18:43 
GeneralExcellent Pin
Md. Marufuzzaman18-Mar-10 4:03
professionalMd. Marufuzzaman18-Mar-10 4:03 
GeneralThis article is nominated to "Best ASP.NET article of February 2010" [modified] Pin
Ilya Builuk12-Mar-10 8:19
Ilya Builuk12-Mar-10 8:19 
GeneralGreat One Pin
Brij12-Mar-10 4:55
mentorBrij12-Mar-10 4:55 
GeneralExcellent Pin
Marcelo Ricardo de Oliveira11-Mar-10 10:34
mvaMarcelo Ricardo de Oliveira11-Mar-10 10:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.