Author: Craig Stuntz
Mere hours after I posted the first in a planned series of posts on using jqGrid in ASP.NET MVC applications, Phil Haack, a rather-more-widely-read-ASP.NET-MVC-blogger, wrote a long post on, er, exactly the same thing. Who, me, bitter? Naahhh… 🙂
But it turns out that we’re using the grid in a different way, and I think the difference is important. So rather than just referring you to Phil’s post and skipping the rest of the series, I think I still have some important information to contribute. In particular, I’m using the grid in a way which allows me to write an extension method for IQueryable<T> which returns data suitable for the grid, without having to know anything about the type T. If you look closely at the code in Phil’s post, you will see that populating the cell array requires custom code (or use of reflection) every time you want to return data to a grid.
It’s important to me that the method should work without knowing anything about the type of the data in the list because I don’t want to have to write repetitious code in every action method which supplies data to a grid to shape the data according to the grid’s needs, and because I generally use anonymous types for JSON serialization. JSON cannot handle object graphs with cycles (i.e., a “circle” of objects which all reference each other). The .NET JSON serializer will throw an exception if you pass it an object which contains a circular reference, or refers to another object which does. Since this is largely data-dependent and, worse, can be occasionally masked by lazy loading and the like, the safest way to serialize to JSON is to pass it data which provably can never contain a cycle. Anonymous types work really well for this. I’ll probably elaborate on this point in a future post. For the time being, the important thing is that I need to be able to take any IQueryable and turn it into data suitable for the grid, without having to think about what type that list might contain.
In a nutshell, this is what I want to be able to do:
public JsonResult ListGridData(int page, int rows, string search, string sidx, string sord)
{
var model = repository.SelectAll().ToJqGridData(page, rows, sidx + " " + sord, search,
new[] { "Column1", "Column2", "Column3" })
return Json(model);
}
In this example, repository.SelectAll() returns an IQueryable<TSomething>. I’ve written a method, ToJqGridData, which can transform that IQueryable into data suitable for the grid without having to know what TSomething is. (What the heck is that string array? Don’t worry about that just yet; I’ll cover it in a little bit.) The argument names aren’t what I would have picked, but they come from the grid. There is probably a way to change them, but I’ve never bothered to look.
The key to this is a grid property called jsonReader.repeatitems. I’m not sure why it’s called repeatitems, but here’s what it does. When repeatitems is true (the default), data is returned to the grid in a “cell array,” like in Phil’s post. That is, an individual row of data in the JSON looks like this:
{id:"1", cell:["cell11", "cell12", "cell13"]}
When repeatitems is false, the data looks like a regular object in JSON format:
{id:"45678",name:"Speakers",note:"note",stock:"false",ship:"4"}
Interestingly, it turns out that this is the format the .NET JSON serializer already uses. So I don’t have to write any code to get this portion of the data into the right shape. I just hand an object to the JSON serializer and let it do its thing. The only things I need to do to make the data work for the grid is to handle paging, sorting, search, and provide certain other bits of data, like the number of rows.
But, as you may have noticed, I’m lazy. Why write code when someone else has already done it? Troy Goode enhanced it. So I’m going to use their work instead of re-writing paging. It turns out this type already computes most of what the grid needs to know. And Microsoft has this thing called Dynamic LINQ which can deal with ordering and filtering by property names, mostly. All I have to do is tie this stuff together.
I wrote a type to hold the data which the grid needs. You can actually use an anonymous type for this; the JavaScript grid obviously doesn’t care if the data it receives comes from an anonymous type or not, but because I am writing extension methods to IQueryable, it was convenient for me to have a non-anonymous type so that the extension methods could build on each other. This type represents one page of data requested by the grid:
namespace GridDemo.Models
{
/// <summary>
/// This type is designed to conform to the structure required by the JqGrid JavaScript component.
/// It has all of the properties required by the grid. When this type is serialized to JSON, the resulting
/// JSON will be in the structure expected by the grid when it fetches pages of data via AJAX calls.
/// </summary>
[SuppressMessage("Microsoft.Naming", "CA1709:IdentifiersShouldBeCasedCorrectly", MessageId = "Jq",
Justification = "JqGrid is the correct name of the JavaScript component this type is designed to support.")]
[SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId="Jq",
Justification = "JqGrid is the correct name of the JavaScript component this type is designed to support.")]
public class JqGridData
{
/// <summary>
/// The number of pages which should be displayed in the paging controls at the bottom of the grid.
/// </summary>
public int Total { get; set; }
/// <summary>
/// The current page number which should be highlighted in the paging controls at the bottom of the grid.
/// </summary>
public int Page { get; set; }
/// <summary>
/// The total number of records in the entire data set, not just the portion returned in Rows.
/// </summary>
public int Records { get; set; }
/// <summary>
/// The data that will actually be displayed in the grid.
/// </summary>
public IEnumerable Rows { get; set; }
/// <summary>
/// Arbitrary data to be returned to the grid along with the row data. Leave null if not using. Must be serializable to JSON!
/// </summary>
public object UserData { get; set; }
}
}
Note that I’ve used C# capitalization for the property names. This is not what the grid expects. But I have to tell the grid that I’m using a non-default value for repeatitems anyway, so it’s nice to follow C# conventions.
We have lots of grids in our application, so rather than configuring each grid individually, we set defaults for the grid in one place. In a JavaScript file referenced by the Site.Master, this method is called in the jQuery ready event:
setDefaults: function() {
$.jgrid.defaults = $.extend($.jgrid.defaults, {
datatype: 'json',
height: 'auto',
imgpath: '/Content/jqGrid/themes/basic/images',
jsonReader: {
root: "Rows",
page: "Page",
total: "Total",
records: "Records",
repeatitems: false,
userdata: "UserData",
id: "Id"
},
loadui: "block",
mtype: 'GET',
multiboxonly: true,
rowNum: 20,
rowList: [10, 20, 50],
url: "ListGridData.json",
viewrecords: true
});
}
In addition to setting repeatitems, telling the grid to expect the capitalization I mentioned above, this code also sets the grid theme and other options we want to be consistent throughout the application. One important property set in the jsonReader property is id. The value supplied is the name of a column in the returned data which contains a unique ID for the row. You can supply any name you want to, and the type of the property can be just about anything serializable, but it is important that the data you return to the grid contains some unique ID. All of the types in our Entity Framework model contain a unique ID called “Id“, so we set this in our defaults for the grid. If you don’t have a single operating name for any data you might supply to the grid, you will have to set this value on the individual grids, when you write the JavaScript for the view.
Now I can write a method which takes a PagedList and transforms it into jqGrid data:
/// <summary>
/// Converts a paged list into a format suitable for the JqGrid component, when
/// serialized to JSON. Use this method when returning data that the JqGrid component will
/// fetch via AJAX. Take the result of this method, and then serialize to JSON. This method
/// will also apply paging to the data.
/// </summary>
/// <typeparam name="T">The type of the element in baseList. Note that this type should be
/// an anonymous type or a simple, named type with no possibility of a cycle in the object
/// graph. The default JSON serializer will throw an exception if the object graph it is
/// serializing contains cycles.</typeparam>
/// <param name="list">The list of records to display in the grid.</param>
/// <param name="userData">Arbitrary data to be returned to the grid along with the row data.
/// Leave null if not using. Must be serializable to JSON!</param>
/// <returns>A structure containing all of the fields required by the JqGrid. You can then call
/// a JSON serializer, passing this result.</returns>
[SuppressMessage("Microsoft.Naming", "CA1709:IdentifiersShouldBeCasedCorrectly", MessageId = "Jq",
Justification = "JqGrid is the correct name of the JavaScript component this type is designed to support.")]
[SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId = "Jq",
Justification = "JqGrid is the correct name of the grid component we use.")]
public static JqGridData ToJqGridData<T>(this PagedList<T> list, object userData)
{
return new JqGridData()
{
Page = list.PageIndex,
Records = list.TotalItemCount,
Rows = from record in list
select record,
Total = list.PageCount,
UserData = userData
};
}
That’s getting somewhere, but it’s not yet enough to let me write the action method I showed above. For that, I need to wrap up the PagedList inside the action so that I can pass the arguments from the grid directly, without having to construct a new PagedList inside of every action method which supplies data to a grid. So I build on the method above:
/// <summary>
/// Converts a queryable expression into a format suitable for the JqGrid component, when
/// serialized to JSON. Use this method when returning data that the JqGrid component will
/// fetch via AJAX. Take the result of this method, and then serialize to JSON. This method
/// will also apply paging to the data.
/// </summary>
/// <typeparam name="T">The type of the element in baseList. Note that this type should be
/// an anonymous type or a simple, named type with no possibility of a cycle in the object
/// graph. The default JSON serializer will throw an exception if the object graph it is
/// serializing contains cycles.</typeparam>
/// <param name="baseList">The list of records to display in the grid.</param>
/// <param name="currentPage">A 1-based index indicating which page the grid is about to display.</param>
/// <param name="rowsPerPage">The maximum number of rows which the grid can display at the moment.</param>
/// <param name="orderBy">A string expression containing a column name and an optional ASC or
/// DESC. You can, separate multiple column names as with SQL.</param>
/// <param name="searchQuery">The value which the user typed into the search box, if any. Can be
/// null/empty.</param>
/// <param name="searchColumns">An array of strings containing the names of properties in the
/// element type of baseList which should be considered when searching the data for searchQuery.</param>
/// <param name="userData">Arbitrary data to be returned to the grid along with the row data. Leave
/// null if not using. Must be serializable to JSON!</param>
/// <returns>A structure containing all of the fields required by the JqGrid. You can then call
/// a JSON serializer, passing this result.</returns>
[SuppressMessage("Microsoft.Naming", "CA1709:IdentifiersShouldBeCasedCorrectly", MessageId = "Jq",
Justification = "JqGrid is the correct name of the JavaScript component this type is designed to support.")]
[SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId = "Jq",
Justification = "JqGrid is the correct name of the grid component we use.")]
public static JqGridData ToJqGridData<T>(this IQueryable<T> baseList,
int currentPage,
int rowsPerPage,
string orderBy,
string searchQuery,
IEnumerable<string> searchColumns,
object userData)
{
var filteredList = ListAddSearchQuery(baseList, searchQuery, searchColumns);
var pagedModel = new PagedList<T>(filteredList.OrderBy(orderBy), currentPage, rowsPerPage);
return pagedModel.ToJqGridData(userData);
}Now you see that string array (IEnumerable, actually) again. JqGrid supports sorting and searching, and so do I. Like Phil, I suggest using Microsoft Dynamic LINQ in order to take the field names that jqGrid passes and turn them into a sorted, filtered, result set. ListAddSearchQuery is a (longish) method which takes a search string and a list of columns to search and turns them into a Dynamic LINQ expression and applies it to the base list. Unfortunately, search is kind of application-specific. In some applications you might want to search different columns in different ways. Some applications might want “Google-style” searching. It kind of depends upon what you’re doing. So although I will supply the source code for this method when I make the demo project available, I cannot promise that it will be useful for your application. Feel free to substitute your own method here.
It turns out both methods (the one which takes a PagedList and the one which takes an IQueryable) are useful. Generally, I use the method which takes an IQueryable. But every once in a while I want to do something to the data I’m about to supply to the grid before the grid sees it, but after paging, like transform a single page of results in some way. In this case, the action builds up a PagedList, uses that to reduce to one page of data, modifies the results in whatever way is appropriate, and then calls the PagedList extension. I’ve also written additional overloads to both methods which do not have a userData argument, since it is very seldomly used. userData, as the name suggests, is an additional bit of data you can return to the grid with your result set for use in grid event handlers when loading a fresh page of data. Since it’s of type object, you can put as much data in there as you need to, so long as it’s serializable to JSON. In practice, though, we rarely use this feature of the grid.
With these methods, it is easy to write actions as concise as the code I showed at the beginning of this post. Just get a list from somewhere and call ToJqGridData, passing information about which page to display, how to sort, etc.
In a future post, I will tie this all together into a demo application.