Skip to content

Using jqGrid with ASP.NET MVC: Search and Formatting

This is the fourth post in a series on using jqGrid with ASP.NET MVC. Today, we’re going to examine search and formatting results. I will implement a search feature and fix the problem with formatting date columns which I observed in the last post. I’ve updated the demo solution with these new features, and also fixed a bug reported by Andrey last time. If you’re new to the series, you might want to start at the beginning.

First, let’s fix the date display. The reason the date appeared so oddly is that there is no official support for Date types in JSON, even though JavaScript has a date type. Microsoft, therefore, invented their own way of encoding dates for their JSON serializer, which jqGrid does not understand. There are two possible ways to fix the problem. The first is to keep sending the data to the grid in the Microsoft format, and write a custom formatter for the grid to parse that into a human-readable string. The second is to change the data we send to the grid. Both ways work, but I’m going to demonstrate the second method, mostly because I find C#’s date handling features superior to (and less confusing than) JavaScript’s.

In order to change the date "column" in the results of the query to a formatted string, we simply need to project into an anonymous type. However, this would break the sort, because a text sort on a formatted date will not be in the correct order. Hence, it is necessary to apply the sort before projecting. So I add an OrderBy to the query, and pass null as the orderBy argument to the ToJqGridData method.

public ActionResult GridDemoData(int page, int rows, string search, string sidx, string sord)
{
    var repository = new Repository();
    var model = from entity in repository.SelectAll().OrderBy(sidx + " " + sord)
                select new
                {
                    Id = entity.Id,
                    IntProperty = entity.IntProperty,
                    StringProperty = entity.StringProperty,
                    DateProperty = entity.DateProperty.ToString(
                        System.Globalization.CultureInfo.CurrentUICulture)
                };
    return Json(model.ToJqGridData(page, rows, null, search,
        new[] { "IntProperty", "StringProperty", "DateProperty" }));
}

Note that in this revised solution I’ve updated the mock repository to return more sensible dates.

Important update: This code works correctly with LINQ to Objects, as with my demo repository, but will not work with LINQ to SQL. I have addressed this issue in a subsequent post.

Now let’s add a search feature. One of the problems in making a "demo" search is that search is a highly application-specific feature, both in terms of implementation and in terms of user experience. JqGrid’s search feature presumes that you want a search which looks something like this:

Visually, there are lots of variations on this theme available, but in terms of the use case they all presume that the user will specify which fields she would like to search. However, I prefer a "Google-style" interface with just a search box and an implementation that can (hopefully) figure out what the user actually wants. So that’s what I’m going to demonstrate. The needs of your application may be different. The important thing to take away from this demonstration is that the jqGrid search feature can be co-opted to fit your design.

I want the search box to appear above the grid at all times, rather than requiring the user to press a toolbutton to see it. So first I need to add an empty div to the markup in order to indicate where the search box will be positioned, relative to the grid:

    <h2>GridDemo</h2>
    <div id="search"></div>
    <table id="grid" class="scroll" cellpadding="0" cellspacing="0"></table>
    <div id="pager" class="scroll" style="text-align:center;"></div>

Next, I add a small bit of JavaScript to tell jqGrid to replace this div with a search control:

    search.filterGrid("#" + grid.attr("id"), {
        gridModel: false,
        filterModel: [{
            label: 'Search',
            name: 'search',
            stype: 'text'
        }]
    });

The filterModel tells the grid to show a search control for one column, called "Search." Note that the entities I’m returning do not have a property called "search." That’s OK. When the user types something in the search box and presses enter, the grid will add a query string parameter to its request for the data of the form "search=WhatTheUserTyped". That’s what I want, and the fact that there is no "search" column in the data does not adversely affect the grid in any way.

The first argument to filterGrid is a little odd. I’m required to pass a jQuery selector as a string rather than being able to pass a jQuery object directly. Since this is all inside a method which accepts jQuery objects as arguments (rather than hard-coding the IDs of the DOM objects in the method, for unit-testability), I have to "decode" the jQuery object into a selector string. But note that I did not need to do that when setting up the pager or the grid itself. Like I said before, the grid’s API is consistently inconsistent.

As you can see in the code at the beginning of this post, the GridDemoData action just passes the search query string parameter through to the ToJqGridData method unchanged. Inside that method, I use Dynamic LINQ to alter the IQueryable to implement the search. I’ve cleaned up this method substantially since I first posted the code. Here’s the revised method:

/// <summary>
/// Adds a Where to a Queryable list of entity instances.  In other words, filter the list
/// based on the search parameters passed.
/// </summary>
/// <typeparam name="T">Entity type contained within the list</typeparam>
/// <param name="baseList">Unfiltered list</param>
/// <param name="searchQuery">Whatever the user typed into the search box</param>
/// <param name="searchColumns">List of entity properties which should be included in the
/// search.  If any property in an entity instance begins with the search query, it will
/// be included in the result.</param>
/// <returns>Filtered list.  Note that the query will not actually be executed until the
/// IQueryable is enumerated.</returns>
private static IQueryable<T> ListAddSearchQuery<T>(
    IQueryable<T> baseList,
    string searchQuery,
    IEnumerable<string> searchColumns)
{
    if ((String.IsNullOrEmpty(searchQuery)) | (searchColumns == null)) return baseList;
    const string strpredicateFormat = "{0}.ToString().StartsWith(@0)";
    var searchExpression = new System.Text.StringBuilder();
    string orPart = String.Empty;
    foreach (string column in searchColumns)
    {
        searchExpression.Append(orPart);
        searchExpression.AppendFormat(strpredicateFormat, column, searchQuery);
        orPart = " OR ";
    }
    var filteredList = baseList.Where(searchExpression.ToString(), searchQuery);
    return filteredList;
}

Importantly, this method is simply adding to the expression represented by the IQueryable. It does not actually implement the search. So any search methodology which understands LINQ can deal with its results. At least, theoretically. In practice, LINQ providers vary greatly in terms of which LINQ features they support. So although this method works fine with LINQ to Objects (as used in the rest of the demo solution) I cannot guarantee that it will work with every LINQ provider in the world. You have to try, and revise it to fit the capabilities of the provider you’re using.

Putting all these pieces together, I now have a search feature which does what I want:


Be sure to download the updated solution in order to get the bug fixes and new features.

As a special, no-extra-charge bonus, the updated solution also includes a demonstration of using the grid’s tree view mode with client-side data from a JavaScript function. This has nothing to do with ASP.NET MVC; it’s just an example of how to use a grid feature which I wrote in response to a request in the jqGrid support forums.

That’s all for today. In the next post in the series, I’ll begin to demonstrate the grid’s editing features, and how to use them in an ASP.NET MVC application. But I will also take requests. If there’s a grid feature you’d like to see demonstrated query question regarding using the grid in ASP.NET MVC, please feel free to make a request in comments, and I will answer it as best I can.

{ 18 } Comments

  1. spencer | April 28, 2009 at 8:11 pm | Permalink

    Very good series. I would like to have jqGrid MVC application with features of editing, add, delete and details with Mater/details.

  2. Graeme | May 3, 2009 at 12:10 am | Permalink

    Finding your blogs very helpful - superbly written - just found out about jqgrid last night and have integrated your code into my project.

    One thing that didn’t work for me though was the dates fix above…

    adding

    .ToString(System.Globalization.CultureInfo.CurrentUICulture)

    onto the end of my DateTime values makes everything render Ok but when I try doing a search, it fails. Debugging shows the error is

    Method ‘System.String ToString(System.IFormatProvider)’ has no supported translation to SQL.

    at the TotalItemCount = source.Count(); line of the PagedList.cs file.

    Any ideas?

    Thanks

  3. d | May 11, 2009 at 8:20 pm | Permalink

    thanks a lot!!!!!!!

  4. CarmelFrank | May 27, 2009 at 1:36 pm | Permalink

    Nice article. I ground through integrating jqGrid with MVC a few months ago as well.

    I’m getting javascript errors in the TreeDemo view (/Home/TreeDemo). I see one line of text which reads:
    Name level parent isLeaf expanded

    Anybody else see the same thing?

  5. jd | August 6, 2009 at 1:53 pm | Permalink

    Hi,
    I was going through your code and did not understand this section in dynamic.cs
    foreach (DynamicProperty p in properties) {
    hashCode ^= p.Name.GetHashCode() ^ p.Type.GetHashCode();

    What does ^= and ^ do?

  6. alex | August 8, 2009 at 1:34 am | Permalink

    hi sir,
    i’m very interested in your work and it is very good, but i have a problem.
    i just create a new View which is GridLinq and also in a controller,i just copy the way you use it but it doesn’t work,off course your sample is working.
    what is the cause of this error?

    The parameters dictionary contains a null entry for parameter ‘page’ of non-nullable type ‘System.Int32′ for method ‘System.Web.Mvc.ActionResult GridLinq(Int32, Int32, System.String, System.String, System.String)’ in ‘GridDemo.Controllers.HomeController’. To make a parameter optional its type should be either a reference type or a Nullable type.
    Parameter name: parameters

    thanks
    alex

  7. Matt | September 9, 2009 at 6:40 am | Permalink

    Nice set of articles thanks. It doesnt have editing etc, at least not yet, but for a more automated approach to making JqGrids in .Net MVC with search and sort check out mvccrud.codeplex.com. Can always just use parts of it and do other stuff it doesnt support on your own.

  8. hedayat | October 30, 2009 at 7:31 am | Permalink

    How can i add few contorls in filtermode and send their data to the Controller?

    i think this must be something like this:
    ———————–
    {
    label: ‘Search2′,
    name: ’search2′,
    stype: ‘text’
    }

    but, what a bout their data? I can not catch their data in the controller.

  9. Henke | December 15, 2009 at 6:05 am | Permalink

    Thank you so much, helps me alot even though I’m a php programmer. =)

  10. Shauna Hogan | January 26, 2010 at 10:08 am | Permalink

    Excellent tutorial- thanks for sharing.

    I just wanted to add some information on clearing the search field prior to a grid reload…
    From your example, clicking the "Reload" button retains the search parameter in the url. Adding the following JavaScript prior to the reload clears the filtergrid’s search field, and reload the grid data without the search filter:

    //clear the search values
    var sg = $(’#search’)[0];
    sg.clearSearch();
    //reload grid…

  11. Eran | January 31, 2010 at 1:32 pm | Permalink

    anyone got it working with nhibernate.linq provider?

  12. Steve Vinge | September 4, 2010 at 11:53 am | Permalink

    Thanks.

    FYI, When upgrading your demo to VS2010, I had to add:
    mtype: ‘POST’
    to the jqGrid stmt in the Home.GridDemo.js file for it to load data.

  13. Steve Vinge | September 4, 2010 at 12:02 pm | Permalink

    OR
    Json(pageModel, JsonRequestBehavior.AllowGet)

  14. Rooban | September 29, 2010 at 1:15 am | Permalink

    Hi, this is great series. thanks. but i have one question: how to display search filter not like "google-style". i want some thing similar to the first image where in user can search with more than one criteria. how to enable it?

    Waiting for your response

  15. eric | January 18, 2011 at 2:15 pm | Permalink

    Security Exception:

    I’m getting this error:
    System.Security.SecurityException: Request for the permission of type ‘System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ failed.

    Any clue. I didn’t make any changes to anything in the project. I cleaned and rebuilt and this continued to occur

  16. eric | January 18, 2011 at 3:29 pm | Permalink

    Actually it’s something even more simple.

    The project was loaded on a shared drive and this will occur for LOCALHOST for testing.

    Solution: Move the folder to the local drive.

    Sheesh. wish I could get my 2 hours back.

    thanks

  17. Patrick M-A | April 28, 2011 at 3:56 am | Permalink

    Thank you for this , i have i have been searching for ways to elegantly search all fields .Perfect.

    Just one question tho, why in the ListAddSearchQuery are u building a string using StartWith(), when surely Contains() is better suited for a generic search function. Is there performance penalties ?

  18. Vikas | June 8, 2011 at 8:40 am | Permalink

    Here is the javascript function for add , edit search ,and delete

    GridDemo.Home.GridDemo = {

    setupGrid: function (grid, pager, search) {
    grid.jqGrid({
    datatype: 'json',
    mtype: 'GET',
    viewrecords: true,
    colNames: ['Id','first_name', 'Last Name','age','dob','Address', 'State','sex' ,'Country', 'Subject'],
    colModel: [
    { name: 'Id', index: 'Id', width: 75, editable: false, editoptions: { readonly: true, size: 10} },
    { name: 'first_name', index: 'first_name', width: 100, editable: true, editoptions: { size: 10}, editrules: { required: true}},
    { name: 'last_name', index: 'last_name', width: 100, editable: true, editoptions: { size: 10 }, editrules: { required: true} },
    { name: 'age', index: 'age', width: 70, editable: true, editoptions: { size: 10 }, editrules: { required: true} },
    { name: new Date('dob'), index: new Date('dob'), width: 100, editable: true, editoptions: { size: 10 }, editrules: { required: true} },
    { name: 'address', index: 'address', width: 100, editable: true, editoptions: { size: 10}, editrules: { required: true}},
    { name: 'state', index: 'state', width: 70, editable: true, editoptions: { size: 10 }, editrules: { required: true} },
    { name: 'sex', index: 'sex', width: 70, editable: true, editoptions: { size: 10 }, editrules: { required: true} },
    { name: 'country', index: 'country', width: 70, editable: true, editoptions: { size: 10}, editrules: { required: true}},
    { name: 'subject', index: 'subject', width: 70, editable: true, editoptions: { size: 10}, editrules: { required: true}}
    ],
    pager: pager,
    sortname: ‘first_name’,
    rowNum: 10,
    rowList: [10, 20, 50],
    sortorder: "asc",
    url: "GridStudentDate"
    }).navGrid(pager, { edit: true, add: true, del: true, search: false }, { url: "Create" }, { url: "Create" }, { url: "Delete" });

    search.filterGrid("#" + grid.attr("id"), {
    gridModel: false,
    filterModel: [{
    label: 'Search',
    name: 'search',
    stype: 'text'
    }]
    });
    }
    };

    =======================
    Now these are the Server Side Method to call these function –

    public ActionResult GridStudentDate(int page, int rows, string search, string sidx, string sord)
    {
    sidx = "first_name";
    StudentDBEntities DB = new StudentDBEntities();
    var student = from r in DB.studentDetails
    select new Student
    {
    Id=r.id,
    first_name = r.first_name,
    last_name = r.last_name,
    age=r.age,
    dob=r.dob,
    address = r.address,
    state = r.state,
    sex=r.sex,
    country = r.country,
    subject = r.subject
    };
    var a = Json(student.OrderBy(sidx + " " + sord).ToJqGridData(page, rows, null, search,
    new[] { "Id", "first_name", "last_name", "age", "dob", "address", "state", "sex", "country", "subject" }), JsonRequestBehavior.AllowGet);
    return Json(student.OrderBy(sidx + " " + sord).ToJqGridData(page, rows, null, search,
    new[] { "Id","first_name", "last_name","age","dob", "address", "state","sex", "country", "subject" }), JsonRequestBehavior.AllowGet);
    }

    [AcceptVerbs(HttpVerbs.Post)]
    public ActionResult Delete(int id)
    {
    StudentDBEntities DB = new StudentDBEntities();
    var stu= (from r in DB.studentDetails
    where r.id==id
    select r).First();

    if (stu != null)
    {
    DB.DeleteObject(stu);
    DB.SaveChanges();
    }
    else
    {
    Response.StatusCode = 500;
    return Content("Record not found.");
    }
    return Json(true);
    }
    [AcceptVerbs(HttpVerbs.Post)]
    public ActionResult Create(string first_name, string last_name,string age,string dob, string address, string state,string sex, string country, string subject)
    {
    StudentDBEntities DB = new StudentDBEntities();
    studentDetail std = new studentDetail();
    std.first_name = first_name;
    std.last_name = last_name;
    std.address = address;
    std.state = state;
    std.age = Convert.ToInt32(age);
    std.sex = Convert.ToInt32(sex);
    std.country = country;
    std.dob = System.DateTime.Now;
    std.country = country;
    std.country = country;
    std.subject = subject;
    DB.AddTostudentDetails(std);
    DB.SaveChanges();
    return Json(true);
    }

Post a Comment

Your email is never published nor shared. Required fields are marked *

Bad Behavior has blocked 713 access attempts in the last 7 days.

Close