UPDATE: 1/20/2009 - Implementing the jqGrid can be quite painful when you get into advanced configurations. My initial impression of jqGrid was based on the overall look and feel of the control. In the eyes of the end user, this control is amazing. However, for those who write the code to feed the grid, it can be a nightmare.
When I discovered the jQuery Grid I had very high expectations. So I thought I would put it through some tests to see if it could handle my requirements. The requirements are: paging, sorting, inline controls and filtering.
So, If you want to use jqGrid in your ASP.NET MVC app then just follow these steps:
- Download the jQuery Grid Plugin from trirand.com. The download is customizable. For the purposes of this demo you will only need the Grid base component.
- In the <HEAD> section of your HTML page put in the following js includes:
<script type="text/javascript" src="/Scripts/i18n/grid.locale-en.js"></script>
<script type="text/javascript" src="/Scripts/jquery.jqGrid.min.js"></script>
- Optionally, you may want to include additional css references if you are using a jQuery theme. For example if you are using the overcast theme then you would also include these lines in the <HEAD> section of your HTML:
<link rel="stylesheet" type="text/css" href="/Scripts/themes/overcast/ui.jqgrid.css"/>
<link rel="stylesheet" type="text/css" href="/Scripts/themes/overcast/ui.all.css"/>
- In the <BODY> of your HTML page you need to include this jqGrid initialization code:
<script type="text/javascript">
$(document).ready(function() {
$("#list").jqGrid({
url: '/Instance/AssignmentsJson',
datatype: "json",
mtype: 'GET',
colNames: ['Instance', 'Version', 'Primary DBA', 'Secondary DBA'],
colModel: [
{ name: 'InstanceName', index: 'InstanceName', width: 300, formatter: 'showlink',
formatoptions: { baseLinkUrl: '/Instance/Details', idName: 'name'}
},
{ name: 'Version', index: 'Version', width: 80 },
{ name: 'PrimaryDBA', index: 'PrimaryDBA', width: 200 },
{ name: 'SecondaryDBA', index: 'SecondaryDBA', width: 200 }
],
jsonReader: {
repeatitems: false,
id: "0"
},
rowNum: 50,
rowList: [10, 20, 30, 50],
pager: jQuery('#pager'),
viewrecords: true,
sortname: '0',
sortorder: "asc",
width: 800,
height: 500
});
});
</script>
So what exactly does this code do? I know, it can be a little overwhelming the first time you look at it. So I will break down the important parameters for you:
- url - The url where the grid data is coming from. I will talk more about this in the upcoming steps.
- datatype - I am using Json, which is Javascript object notation. However, the JqGrid also supports other formats such as XML.
- mtype - Post of Get, since we are populating the grid we are going to use GET.
- colNames - The user friendly column names.
- colModel - This parameter is used to format each column and also to specify additional options, such as putting a drop down, date picker or other components in each cell. There should be a 1 to 1 mapping between colNames and colModel.
- jsonReader - When using jqGrid with ASP.NET you will probably need to override the default jsonReader settings. Specifically, you will need to set the repeatitems parameter to false. Also in my example, I have a primary key which is a string. Therefore I tell the JqGrid that the first column returned in each row is the primary key. I could either use the column name or the column index. In my example I used "0" which is the index of the column.
- Also, in the <BODY> of your HTML page you should include the following two lines:
<table id="list" cellpadding="0" cellspacing="0"></table>
<div id="pager"></div>
Note: The id's of the table and div correspond to the parameters in the javascript code above
- Ok, so now we have all the HTML and Javascript wired up. Now we just need to implement an Action inside our controller to return the Json data to populate the grid. The jqGrid normally expects data in this format:
{
total: "xxx",
page: "yyy",
records: "zzz",
rows : [
{id:"1", cell:["cell11", "cell12", "cell13"]},
{id:"2", cell:["cell21", "cell22", "cell23"]},
...
]
}
In the Json string above, there is header data, such as the total, page and records followed by an array of row data. In order to adhere to this format we will need to do some work when we return our data from the controller:
public JsonResult AssignmentsJson( int? page, int? rows, string sidx, string sord ) {
var query = from x in db.Instances
where
x.RemovedFromServiceDate == null
&& (x.PrimaryDBA != null || x.SecondaryDBA != null)
select new
{
InstanceName = x.InstanceName,
Version = x.DBVersion,
PrimaryDBA = x.Contact.FullName,
SecondaryDBA = x.Contact1.FullName
};
//sort the data
string sortExpression = (sidx ?? "InstanceName" ) + " " + (sord ?? "asc" );
query = query.OrderBy(sortExpression);
//page the data
var data = query.ToPagedList( page ?? 0, rows ?? MySession.PageSize);
//return it in a format acceptable to the JqGrid
var ret = new
{
total = data.PageCount-1, //number of pages
page = data.PageIndex, //current page
records = data.TotalItemCount, //total items
rows = data
};
//put the data in the JSON format
return Json(ret);
}
Note: I am using the PagedList class from Rob Conery in my code. This allows me to easily implement paging with Linq. Also notice that the parameters are named sidx, sord, page and rows respectively. These parameter names are the default names expected by the JqGrid component. If the parameter names do no match, then your grid will not sort or page properly.
The important things to note are the return type (JsonResult) and the structure created by the ret variable. We generated an anonymous class that included the header elements and the row data as an array. If you view this URL in your browser it should return the following:
{"total":8,"page":0,"records":208,
"rows":[{"InstanceName":"instance1",
"Version":"10.2.0.4.0",
"PrimaryDBA":"Smith, Jim",
"SecondaryDBA":"Brown, Michael"}...]}
- If you followed all the steps correctly you should end up with a grid that looks something like this (sorry, had to blur some of the data for security reasons):
The JqGrid gives you a rich user experience but it is painful to customize and integrate into your application. The initial "cool factor" behind the look and feel of the grid made me want to use it in all of my applications. However, a few days later I ended up pulling it out in favor of standard HTML tables because I wanted to do some advanced things that were just way too painful to implement with jqGrid. Don't get me wrong, jqGrid is cool and the user experience is great but the steps to implement it made me cringe which eventually led me to pulling the plug on using the control all together.
Additional Resources:
