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 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.
Share This | Email this page to a friend
Tagged .NET, asp.net mvc, C#, javascript, jqGrid, jQuery
