Introduction
jqGrid is a freely licensed (both GPL and MIT), Ajax-enabled JavaScript plugin created by Tony Tomov of Trirand that provides web developers advanced means for displaying and manipulating tabular data. There are many good features about this plugin:
- Features such as sorting, both form-based and inline editing, search, paging and so forth are built into the framework.
- Extensive documentation is available online including a large number of active working demos with source code
- Community feedback assistance (which often includes contributions by Tony Tomov and other members of the Trirand team) is available on the Trirand web site and on user forums such as Stack Overflow and Code Project.
- A tutorial textbook is available: Instant jqGrid, by Gabriel Manricks (Packt Publishing, 2013).
- Trirand supplies a .NET assembly (Trirand.Web.Mvc.dll) that greatly simplifies the job of interfacing between server-side .NET code and client-side Javascript.
The grid also has a number of negatives.
- The Javascript clearly was written by a variety of different people who used different coding conventions and who did not always communicate well with each other or with the original author.
- Client-side Javascript and the server-side assembly imperfectly duplicate a great deal of functionality, leaving the user often baffled as how best to configure a feature.
- The source Javascript is often cryptic.
- Most of the online documentation is for the PHP developer, which leaves .NET developers such as myself in the dark.
- Most of the online .NET documentation is for WebForms.
- Most of the online MVC documentation is for MVC 3.
- Tutorials for the MVC 4 developer have mostly been lacking.
I am absolutely not a jqGrid expert; as of late September, 2014, when I wrote the first draft of this article, I had only been using the tool for about three months. Ten months passed before I was once again called to use jqGrid. The results of my two weeks of struggle to apply jqGrid in this new situation are documented in a new section that I have added to this article on Server-Side Paging.
Oddly enough, I believe that the lack of experience on my part provides me with a useful and highly distinct perspective on jqGrid. In the course of my learning how to use jqGrid, I have committed many newbie errors and have gotten stuck on numerous "gotchas" which blocked my progress for hours while I frantically searched online for a solution. In the following, I have highlighted in bold text descriptions of several of the worst pits into which I've managed to fall. It is my hope that by sharing my experiences, I can prevent others from committing the same mistakes.
The following discussion represents text and image captures from the Tutorial Demonstration program accompanying this article. The live demos from the program, run in a Visual Studio environment where one can explore the source code, are superior to any possible static presentation.
Beginning Grids
First Grid
For our first few introductory grids, we will be using local data defined in a dataArray. To create the grid, we simply insert in the html a <table> element identified by an id,
<div class="page-content">
<table id="JQGrid1">
</table>
</div>
followed by a bit of jQuery selecting the table element and making a call to jqGrid(). We pass to this method an associative array of key-value pairs identifying the datatype ("local"), the source of the data (dataArray), and a column model:
var dataArray = [
{ FirstName: "Nancy", Title: "Sales Representative" },
{ FirstName: "Andrew", Title: "Vice President, Sales" }
];
$("#JQGrid1").jqGrid({
datatype: "local",
data: dataArray,
colModel: [
{ name: "FirstName", label: "Name" },
{ name: "Title", label: "Title" }
]
});
The grid is not especially pretty, but it is fully functional. We can sort the columns, and there is space for a scrollbar at the right should we ever have more data than can fit within the default grid height, which is 150px.
Caption and Auto Height
By adding two more key-value pairs to the associative array, we add a caption to the grid, and adjust the height of the grid to automatically adjust to the amount of data. Note that the empty space reserved for the scrollbar has disappeared. This is because no matter how many rows this grid may have, it will stretch to fit the data, so a scrollbar is superfluous.
$("#JQGrid1").jqGrid({
datatype: "local",
data: dataArray,
colModel: [
{ name: "FirstName", label: "Name" },
{ name: "Title", label: "Title" }
],
height: "auto",
caption: "Employees"
});
Paging
To add paging, we start by adding a <div> element below the <table> element as a placeholder for the navigation layer:
<div class="page-content">
<table id="JQGrid1">
</table>
<div id="JQGrid1_pager"></div>
</div>
We replace the simple dataArray in our earlier examples with an associative array in Json format. The Json identifies the jsonrpc version, gives the total number of rows, and sets the data as the value of the result key.
The expanded associative array fed to the jqGrid() method identifies the <div> element used for the pager and indicates the number of rows per page (rowNum). Note that the value of the data key is no longer dataArray but dataArray.result :
var dataArray = {
"id": 1,
"jsonrpc": "2.0",
"total": 13,
"result": [
{ FirstName: "Nancy", Title: "Sales Representative" },
{ FirstName: "Andrew", Title: "Vice President, Sales" },
{ FirstName: "Janet", Title: "Sales Representative" },
{ FirstName: "Margaret", Title: "Sales Representative" },
{ FirstName: "Steven", Title: "Sales Manager" },
{ FirstName: "Michael", Title: "Sales Representative" },
{ FirstName: "Robert", Title: "Sales Representative" },
{ FirstName: "Laura", Title: "Inside Sales Coordinator" },
{ FirstName: "Anne", Title: "Sales Representative" },
{ FirstName: "Tom", Title: "Sales Representative" },
{ FirstName: "Gerald", Title: "Sales Representative" },
{ FirstName: "Sharon", Title: "Office Manager" },
{ FirstName: "Gregory", Title: "Network Engineer" }
]
};
$("#JQGrid1").jqGrid({
datatype: "local",
data: dataArray.result,
colModel: [
{ name: "FirstName", label: "Name" },
{ name: "Title", label: "Title" }
],
height: "auto",
caption: "Employees",
rowNum: 5,
pager: "#JQGrid1_pager"
});
The resulting grid displays all four standard layers described in the jqGrid documentation. Beginning from the top, these are the caption, the header, the body, and the navigation layers.
Formatting
So far in our demos, all of the data have been simple strings. In a real-world application, it may be preferable to display the values of various fields as checkboxes, hyperlinks, formatted dates, and so forth. jqGrid provides a variety of formatters that enable you to display data in a variety of forms. Below, we have added two new fields to the Json array, an "Active" field containing Boolean values, and a "PhotoPath" field containing text that is intended to be displayed as clickable hyperlinks:
var dataArray = {
"id": 1,
"jsonrpc": "2.0",
"total": 13,
"result": [
{
FirstName: "Nancy", Title: "Sales Representative", Active: "true",
PhotoPath: "http://accweb/emmployees/nancy.bmp"
},
{
FirstName: "Gregory", Title: "Network Engineer", Active: "true",
PhotoPath: "http://accweb/emmployees/gregory.bmp"
}
]
};
$("#JQGrid1").jqGrid({
datatype: "local",
data: dataArray.result,
colModel: [
{ name: "FirstName", label: "Name"},
{ name: "Title", label: "Title" },
{ name: "Active", label: "Active", formatter: "checkbox", width: "40px" },
{ name: "PhotoPath", label: "Photo", formatter: "link", width: "200px" }
],
height: "auto",
caption: "Employees",
rowNum: 5,
pager: "#JQGrid1_pager"
});
In the colModel, we have added a "checkbox" formatter to the "Active" field, and a "link" formatter to the "PhotoPath" field. These simple additions provide the jqGrid columns with the desired appearance and behavior.
A complete listing of the jqGrid formatters is available at http://www.trirand.com/jqgridwiki/doku.php?id=wiki:predefined_formatter
Searching
Adding search functionality to a grid involves several steps:
- By default, all columns are searchable.
- In the colModel, the non-searchable columns are tagged with a search:false key-value pair.
- searchoptions are declared. In the code below, "bw" means "begins with", "cn" means "contains", and "eq" means "equals".
- In the navGrid, the search icons are made visible. In the code below, the add, edit, del, and view icons are invisible, while the search and refresh buttons are visible.
$("#JQGrid1").jqGrid({
datatype: "local",
data: dataArray.result,
colModel: [
{ name: "FirstName", label: "Name", searchoptions: { "sopt": ["bw", "eq"] } },
{ name: "Title", label: "Title", searchoptions: { "sopt": ["bw", "cn", "eq"] } },
{ name: "Current", label: "Current", search:false, formatter:"checkbox", width:"40px" },
{ name: "PhotoPath", label: "Photo", search: false, formatter: "link", width:"200px" }
],
height: "auto",
caption: "Employees",
rowNum: 5,
pager: "#JQGrid1_pager"
});
$("#JQGrid1").jqGrid("navGrid", "#JQGrid1_pager",
{ add: false, edit: false, del: false, search: true, view: false, refresh: true });
Various options are available for customizing the navigation bar, which can be viewed here: http://www.trirand.com/jqgridwiki/doku.php?id=wiki:navigator
Working with Real Data
Getting Data from a Backend
jqGrid uses Json to communicate between client and web server. On the server side, in the Models folder, I have created a class EmployeeJQGridModel that serves as a container for a JQGrid object. (Depending on your application, you can add other fields to the container besides the JQGrid object for its use in a strongly-typed view.) The DataField names in the contained JQGrid object must correspond exactly in name, number and ordering with the colModel names.
public class EmployeeJQGridModel
{
public JQGrid EmployeeGrid;
public EmployeeJQGridModel()
{
EmployeeGrid = new JQGrid
{
Columns = new List<JQGridColumn>()
{
new JQGridColumn { DataField = "EmployeeID", PrimaryKey = true },
new JQGridColumn { DataField = "LastName" },
new JQGridColumn { DataField = "PhotoPath" }
}
};
}
}
$("#JQGrid1").jqGrid({
url: "/Employee/OnEmployeeDataRequested",
mtype: "GET",
datatype: "json",
page: 1,
jsonReader: { id: "EmployeeID" },
prmNames: { id: "EmployeeID" },
colNames: ["EmployeeID", "Last Name", "First Name", "Title", "Birth Date", "Hire Date",
"Address", "City", "Region", "Postal Code", "Country", "Home Phone", "Ext", "Photo"],
colModel: [
{ key: true, width: 50, name: "EmployeeID", hidden: true },
{ width: 60, name: "LastName" },
{ width: 60, name: "FirstName" },
{ width: 120, name: "Title" },
{ width: 60, name: "BirthDate", hidden: true },
{ width: 60, name: "HireDate", formatter: "date",
formatoptions: { srcformat: "m/d/Y h:i:s A", newformat: "Y-m-d" } },
{ width: 110, name: "Address" },
{ width: 50, name: "City" },
{ width: 40, name: "Region" },
{ width: 60, name: "PostalCode" },
{ width: 50, name: "Country" },
{ width: 70, name: "HomePhone" },
{ width: 30, name: "Extension" },
{ width: 200, name: "PhotoPath", formatter: "link" }
],
height: "auto",
caption: "Employees",
rowNum: 5,
pager: "#JQGrid1_pager"
});
Note that several columns have been tagged as hidden.
The jqGrid() code on the client begins rendering the grid html, and when the point is reached where the rendering code needs data from the web server, it issues a "GET" request to the indicated url ("/Employee/OnEmployeeDataRequested"). This request is received by the controller, which queries the database. Fields in the resultset that match corresponding DataFields in the JQGrid object are converted to a JsonResult by the JQGrid DataBind() method, which accepts an IQueryable object as input. Note the use of the AsQueryable() method. In this particular case, the result set is already an IQueryable, so use of this method is redundant; however, other types of collection that may be returned by a data source such as List<>s and arrays cannot be bound to the JQGrid, so routine use of AsQueryable() can save considerable headache. If you forget to convert to IQueryable, you will not have any useful warning message telling you to use an IQueryable object; the grid just won't populate.
public JsonResult OnEmployeeDataRequested()
{
var gridModel = new EmployeeJQGridModel();
var repository = new GenericEmployeeRepository();
var resultSet = repository.GetAll();
return gridModel.EmployeeGrid.DataBind(resultSet.AsQueryable());
}
You might also wish to take note of the date formatter that I used to convert from the format used in the Json string to what we in the .NET world would call "yyyy-MM-dd" format. jqGrid date format strings follow PHP standards rather than following .NET conventions. For a complete list of PHP date formatters, see http://php.net/manual/en/function.date.php
Searching Revisited
Up until this point, all the grid configuration that I have demonstrated has been via manipulation of the array of parameters in the client-side $().jqGrid() function call. If you look at the metadata definition of the JQGrid class as defined in the Trirand.Web.Mvc assembly, you will note the existence of a considerable number of properties that can be set on the server-side, as well as some useful methods that can be called. Although many of the properties appear to duplicate properties that can also be set on the client-side, the correspondence is by no means exact.
Setting up a search on data from a backend requires a combination of client-side and server-side configuration. The client-side setup has nothing surprising:
$("#JQGrid1").jqGrid({
url: "/Searching/OnEmployeeDataRequested",
mtype: "GET",
datatype: "json",
page: 1,
jsonReader: { id: "EmployeeID" },
prmNames: { id: "EmployeeID" },
colNames: ["EmployeeID", "Last Name", "First Name", "Title",
"Birth Date", "Hire Date", "Address", "City", "Region",
"Postal Code", "Country", "Home Phone", "Ext", "Photo"],
colModel: [
{ key: true, width: 50, name: "EmployeeID", hidden: true, search: false },
{
width: 60, name: "LastName",
search: true, stype: "text", searchoptions: { "sopt": ["bw", "eq"] }
},
{
width: 60, name: "FirstName",
search: true, stype: "text", searchoptions: { "sopt": ["bw", "eq"] }
},
{
width: 120, name: "Title",
search: true, stype: "text", searchoptions: { "sopt": ["bw", "cn", "eq"] }
},
{ width: 0, name: "BirthDate", hidden: true, search: false },
{
width: 60, name: "HireDate", search: false, formatter: "date",
formatoptions: { srcformat: "m/d/Y h:i:s A", newformat: "Y-m-d" }
},
{ width: 110, name: "Address", search: false },
{ width: 50, name: "City", search: false },
{ width: 40, name: "Region", search: false },
{ width: 60, name: "PostalCode", search: false },
{ width: 50, name: "Country", search: false },
{ width: 70, name: "HomePhone", search: false },
{ width: 30, name: "Extension", search: false },
{ width: 200, name: "PhotoPath", formatter: "link", search: false }
],
height: "auto",
caption: "Employees",
rowNum: 5,
pager: "#JQGrid1_pager"
}).jqGrid('navGrid', '#JQGrid1_pager',
{ edit: false, add: false, del: false, search: true, view: false, refresh: true });
In the controller, I have defined SetupEmployeeJQGrid() as a method to be called by both Employees() and OnEmployeeDataRequested(). In this method, I set the DataType property of the searchable columns to typeof(string) even though setting stype: "text" in the cshtml code seemingly should have been able to set the search type of the field. http://www.trirand.com/jqgridwiki/doku.php?id=wiki:search_config
public void SetupEmployeeJQGrid(JQGrid employeeGrid)
{
JQGridColumn lastNameColumn = employeeGrid.Columns.Find(c => c.DataField == "LastName");
lastNameColumn.DataType = typeof(string);
JQGridColumn firstNameColumn = employeeGrid.Columns.Find(c => c.DataField == "FirstName");
firstNameColumn.DataType = typeof(string);
JQGridColumn titleColumn = employeeGrid.Columns.Find(c => c.DataField == "Title");
titleColumn.DataType = typeof(string);
}
The error message that you want to avoid is:
"DataTypeNotSetException: JQGridColumn.DataType must be set in order to perform search operations."
Add/Edit/Delete
Enabling add/edit/delete functionality requires both client-side configuration and server-side coding.
Identifying the primary key column is essential to the add/edit/delete operations. For certain purposes, the default assumption is that the first column represents the primary key. This will not necessarily always be the case, so jqGrid provides alternative means for identifying the primary key. There are at least four different places whereby the primary key column is set.
1. via the PrimaryKey = true attribute in EmployeeJQGridModel
2. via the "key: true" key-value pair in the colModel array
3. as the id value associated with prmNames
4. as the id value associated with jsonReader
These primary key setters are associated with different parts of the jqGrid code, and I do not know which are important for what. I do know that, at the very least, setting both the key and the prmNames values are required for editing. For example, if the prmNames is not set, the value of the primary key will not be passed to the HttpPost-handler when you submit an edit. Adopting a "better safe than sorry" attitude, I routinely set all four values.
In the figure below:
- An editurl is identified.
- The primary key is set in the colModel ("key: true"), prmNames, and jsonReader.
- I have added "editable: true" to the editable columns.
- In the navGrid, I have set "edit: true, add: true, del: true, search: true, view: false, refresh: true".
- In the edit and add options, I have set "closeAfterEdit: true" and "closeAfterAdd: true". (The default behavior of these dialogs to remain open so that a series of changes may be rapidly made).
- In the add options, I have made visible the hidden BirthDate column.
- Note that the selector for the BirthDate column is "tr_BirthDate".
$("#JQGrid1").jqGrid({
url: "/Editing/OnEmployeeDataRequested",
editurl: '/Editing/EditEmployeeData',
mtype: "GET",
datatype: "json",
page: 1,
jsonReader: { id: "EmployeeID" },
prmNames: { id: "EmployeeID" },
colNames: ["EmployeeID", "Last Name", "First Name", "Title",
"Birth Date", "Hire Date", "Address", "City", "Region",
"Postal Code", "Country", "Home Phone", "Ext", "Photo"],
colModel: [
{ key: true, width: 50, name: "EmployeeID", hidden: true, search: false },
{ editable: true, width: 60, name: "LastName", search: true, stype: "text",
searchoptions: { "sopt": ["bw", "eq"] } },
{ editable: true, width: 60, name: "FirstName", search: true, stype: "text",
searchoptions: { "sopt": ["bw", "eq"] } },
{ editable: true, width: 120, name: "Title", search: true, stype: "text",
searchoptions: { "sopt": ["bw", "cn", "eq"] } },
{ editable: true, width: 200, name: "PhotoPath", formatter: "link", search: false }
],
height: "auto",
caption: "Employees",
rowNum: 5,
pager: "#JQGrid1_pager"
}).jqGrid('navGrid', '#JQGrid1_pager',
{ edit: true, add: true, del: true, search: true, view: false, refresh: true },
{ closeAfterEdit: true
},
{ beforeShowForm: function (form) {
$("#tr_BirthDate", form).show();
},
closeAfterAdd: true
},
{ } );
In the controller, we define an action method corresponding to the editurl referenced in the grid definition.
- The action method is prefixed by an [HttpPost] attribute.
- The parameter list includes a string oper. The primary key, EmployeeID, is here interpreted as a nullable int, but it can optionally be interpreted as a string. Additional parameters correspond to all of the other editable columns; the non-editable columns are not represented.
- The oper parameter may have the values "add", "edit", or "del".
- The primary key will be null on an "add" operation.
- It doesn't seem to make too much difference what you return. The Content() method simply wraps the true/false strings in a ContentResult.
[HttpPost]
public ActionResult EditEmployeeData(string oper, int? EmployeeID, string LastName, string FirstName,
string Title, string BirthDate, string HireDate, string Address, string City, string Region,
string PostalCode, string Country, string HomePhone, string Extension, string PhotoPath)
{
var repository = new GenericEmployeeRepository();
if (oper == "add")
{
var employee = new Employee()
{
LastName = LastName,
FirstName = FirstName,
Title = Title,
BirthDate = DateTime.Parse(BirthDate),
HireDate = DateTime.Parse(HireDate),
Address = Address,
City = City,
Region = Region,
PostalCode = PostalCode,
Country = Country,
HomePhone = HomePhone,
Extension = Extension,
PhotoPath = PhotoPath,
};
repository.Insert(employee);
return Content("true");
}
else if (oper == "del")
{
var employee = repository.GetById((int)EmployeeID);
repository.Delete(employee);
return Content("true");
}
else if (oper == "edit")
{
var employee = repository.GetById((int)EmployeeID);
employee.LastName = LastName;
employee.FirstName = FirstName;
employee.Title = Title;
employee.HireDate = DateTime.Parse(HireDate);
employee.Address = Address;
employee.City = City;
employee.Region = Region;
employee.PostalCode = PostalCode;
employee.Country = Country;
employee.HomePhone = HomePhone;
employee.Extension = Extension;
employee.PhotoPath = PhotoPath;
repository.Update(employee);
return Content("true");
}
return Content("false");
}
Client Side Validation
jqGrid offers a variety of options for performing client-side validation. These options are set by configuring the editrules property. You can designate a field as required, specify that it must be a number, or an integer, or that it adhere to the requirements of being a proper email address, or the url of a website, or have a valid time or date format. In addition, you can specify a numeric range with minValue and maxValue, and if none of the pre-defined validators are appropriate, you can create a custom validator. In the illustration below:
- I have specified required on most of the columns.
- The date validator automatically checks that entries follow "Y-m-d" format
- I have created two custom functions to check that (a) phone numbers follow either USA or UK format, and (b) the links to employee photos are without gross syntax errors and may be in bmp, jpg, or png format.
- In addition, I modified the Country column to use a dropdown list which is populated via an ajax call to the back end.
colNames: ["EmployeeID", "Last Name", "First Name", "Title",
"Birth Date", "Hire Date", "Address", "City", "Region",
"Postal Code", "Country", "Home Phone", "Ext", "Photo"],
colModel: [
{ key: true, width: 50, name: "EmployeeID", hidden: true, search: false },
{ editable: true, width: 60, name: "LastName", search: true, stype: "text",
searchoptions: { "sopt": ["bw", "eq"] }, editrules: { required: true } },
{ editable: true, width: 60, name: "FirstName", search: true, stype: "text",
searchoptions: { "sopt": ["bw", "eq"] }, editrules: { required: true } },
{ editable: true, width: 120, name: "Title", search: true, stype: "text",
searchoptions: { "sopt": ["bw", "cn", "eq"] }, editrules: { required: true } },
{ editable: true, width: 0, name: "BirthDate", hidden: true, search: false, formatter: "date",
formatoptions:{ srcformat:"m/d/Y h:i:s A", newformat:"Y-m-d" }, editrules:{ date:true } },
{ editable: true, width: 60, name: "HireDate", search: false, formatter: "date",
formatoptions:{ srcformat:"m/d/Y h:i:s A", newformat:"Y-m-d" }, editrules:{ date:true } },
{ editable: true, width: 110, name: "Address", search: false, editrules: { required: true } },
{ editable: true, width: 50, name: "City", search: false, editrules: { required: true } },
{ editable: true, width: 40, name: "Region", search: false },
{ editable: true, width: 60, name: "PostalCode", search:false, editrules:{ required:true } },
{ editable: true, width: 50, name: "Country", search: false, edittype: "select",
editoptions: { dataUrl: "/Editing/CountriesSelectList" } },
{ editable: true, width: 70, name: "HomePhone", search: false,
editrules:{ required:true, custom:true, custom_func:JQGridDemo.usaUkPhoneNumberCheck } },
{ editable: true, width: 30, name: "Extension", search: false },
{ editable: true, width: 200, name: "PhotoPath", formatter: "link", search: false,
editrules: { custom: true, custom_func: JQGridDemo.allowedImageLinkCheck } }
],
A custom validator function accepts two parameters (the cell value and the column name) and returns an array, the first element of which is a Boolean value indicating whether the validation passed or not, and element is an error message string which is displayed if the Boolean is false:
var JQGridDemo = JQGridDemo || {
allowedImageLinkCheck: function (value, colname) {
var pattern = /((ht|f)tp(s?)\:\/\/){1}\S+\.(png|bmp|jpg)/;
if (pattern.test(value)) {
return [true, ""];
}
else {
return [false, "Invalid image link"];
}
},
usaUkPhoneNumberCheck: function (value, colname) {
var pattern = /^([0-9]( |-)?)?(\(?[0-9]{2,3}\)?|[0-9]{2,3})( |-)?[0-9]{3}( |-)?[0-9]{4}$/;
if (pattern.test(value)) {
return [true, ""];
}
else {
return [false, "Neither USA nor UK phone #"];
}
},
For further information on validation, see http://www.trirand.com/jqgridwiki/doku.php?id=wiki:common_rules
Note on Edit Select List Ordering
If you are using a hard-coded select list to limit edit entries in a grid cell, then you should be aware that there are two means of initializing the select list that have distinctly different effects:
- You can initialize the select list with an associative array.
- You can initialize the select list with a string.
Suppose that you have an alphabetical list of the original U.S. colonies keyed by their order of ratification of the Constitution. Since you intend the colonies to be displayed alphabetically, the order of the keys is jumbled:
stateArray : {
0: "Select State", 5: "Connecticut", 1: "Delaware", 4: "Georgia", 7: "Maryland",
6: "Massachusetts", 9: "New Hampshire", 3: "New Jersey", 11: "New York",
12: "North Carolina", 2: "Pennsylvania", 13: "Rhode Island", 8: "South Carolina",
10: "Virginia"
},
stateString: "0:Select State;5:Connecticut;1:Delaware;4:Georgia;7:Maryland;6:Massachusetts;" +
"9:New Hampshire;3:New Jersey;11:New York;12:North Carolina;2:Pennsylvania;" +
"13:Rhode Island;8:South Carolina;10:Virginia",
customFormatter: function (value) {
return JQGridDemo.stateArray[value];
}
The BirthPlace edit dropdown is initialized with the associative array, while the DeathPlace edit dropdown is initialized with the string:
{
editable: true, width: 150, name: "BirthPlace", search: false, stype: "select",
edittype: "select", label: "Birth Place",
editrules: { required: true }, editoptions: { value: JQGridDemo.stateArray },
formatter: JQGridDemo.customFormatter
},
{
editable: true, width: 150, name: "DeathPlace", search: false, stype: "select",
edittype: "select", label: "Death Place",
editrules: { required: true }, editoptions: { value: JQGridDemo.stateString },
formatter: JQGridDemo.customFormatter
},
The BirthPlace edit dropdown of the Founding Fathers grid ignores your ordering, but instead orders the list by the state's order of ratification of the Constitution. On the other hand, the DeathPlace edit dropdown will be in the order in which you created the list:
Server Side Validation
The general recommendation, for security reasons, is to perform both client-side and server-side validation. While it is somewhat more difficult for a hacker to bypass client-side validation with jqGrid than with, say, a webforms GridView control, inasmuch as turning off Javascript will totally disable jqGrid, to an experienced hacker this is but a minor detail.
On the server side, the simplest action to perform when an error is encountered in validation is to throw an exception. However, this leads to several ancillary issues:
- How is the user to be informed that a validation error has occurred, and how much information should be revealed to the user?
- Unexpected exceptions arising during processing generally need to be logged for tracking purposes. However, there is usually no need to log exceptions thrown as a result of validation errors.
An unhandled exception occurring on a development workstation generally results in a special view being sent to the user, a "yellow screen of death" (HTTP 500 - internal server error) with stack trace information that should never be revealed to an end user. On a remote machine, the default action in response to an unhandled exception is to send the user a sanitized version of the yellow screen of death that supplies little information other than the fact that a runtime error has occurred.
In response to a validation exception, users should not be redirected to another screen, but instead should receive a succinct explanation of how to correct their input. In response to a general exception, users may or may not be directed to another screen, where in addition to a succinct explanation of what went wrong, they should be given sufficient information, such as a log reference number and a timestamp, which will aid IT Support Members in diagnosing the issue.
Exceptions on Submitted Edits
The easiest way to explain the code that I have used to implement server-side validation is to trace through an invalid edit.
We begin with the following innocuous edit. The user has supplied the extension "x3333", not knowing that the validation rules require that the extension be all numeric:
The posted message is handled by the EditEmployeeData() action method. Note that the method is decorated with a [JQGridPostExceptionHandler] attribute. Within the method, I have written a Regex check for the extension format:
[HttpPost]
[JQGridPostExceptionHandler]
public ActionResult EditEmployeeData(string oper, int? EmployeeID, string LastName,
string FirstName, string Title, string BirthDate, string HireDate, string Address,
string City, string Region, string PostalCode, string Country, string HomePhone,
string Extension, string PhotoPath)
{
var repository = new GenericEmployeeRepository();
if (oper == "edit")
{
if (Extension != null && !Regex.IsMatch(Extension, @"(^$|^[0-9]{3,4}$)"))
throw new ValidationException("Phone extension is non-numeric or wrong length");
Since the Regex does not match, I throw a ValidationException:
public class ValidationException : Exception
{
public ValidationException(string message) : base(message)
{
}
}
The exception is handled by the OnException() method of the filter attribute.
public class JQGridPostExceptionHandlerAttribute : FilterAttribute, IExceptionFilter
{
public void OnException(ExceptionContext filterContext)
{
if (!filterContext.ExceptionHandled)
{
string message = filterContext.Exception.Message;
if (filterContext.Exception is ValidationException)
{
}
else
{
string stackTrace = filterContext.Exception.StackTrace;
}
HttpContext.Current.Session["CustomErrorMessage"] = message;
throw filterContext.Exception;
}
}
}
Since the exception type is that of a ValidationException, nothing will be logged.
Note on the use of session variables. A session remains active as long as the browser continues to make requests with the same SessionID value within a specified timeout. http://msdn.microsoft.com/en-us/library/ms178581%28v=vs.90%29.aspx This appears to be true regardless whether a controller exits normally or via an exception.
ASP.NET generates a random SessionID, which is stored in a non-expiring session cookie on the browser. A new SessionID is created for each page request until the first time that the session object is accessed, after which the SessionID becomes static. http://msdn.microsoft.com/en-us/library/system.web.sessionstate.httpsessionstate.sessionid(v=vs.110).aspx
In the code shown above, if the assignment to Session["CustomErrorMessage"] represents the first time that the session object has ever been accessed, the throwing of the exception means that the browser will not know what SessionID accesses the session variable. In our demo code, the symptom will be a "Failed to retrieve states" error. To avoid this issue, it is necessary, early in the session life cycle, to create a SessionID which is static for the entire session. We do this by implementing the Session_Start() method in Global.asax and storing data in the session object, as shown below:
public class MvcApplication : System.Web.HttpApplication
{
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
WebApiConfig.Register(GlobalConfiguration.Configuration);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
}
void Session_Start(object sender, EventArgs e)
{
var context = System.Web.HttpContext.Current;
context.Session["MakeSessionIDStaticForEntireSession"] = "";
}
}
The unhandled exception results in transmission of a sanitized yellow screen of death (HTTP/1.1 500 Internal Server Error) to the remote browser. Within the browser, the error message is handled by the following code in the jqGrid definition, which specifies a custom error handler. The function (data) parameter receives the sanitized yellow screen of death HTML, which has nothing of interest to us and is ignored:
}).jqGrid('navGrid', '#JQGrid1_pager', { edit: true, add: true, del: true, search: true, view: false, refresh: true },
{ closeAfterEdit: true,
errorTextFormat: function (data) {
JQGridDemo.displayCustomErrorMessage();
}
},
{ beforeShowForm: function (form) {
$("#tr_BirthDate", form).show();
},
closeAfterAdd: true,
errorTextFormat: function (data) {
JQGridDemo.displayCustomErrorMessage();
}
},
{ errorTextFormat: function (data) {
JQGridDemo.displayCustomErrorMessage();
}
});
The custom error handler issues an Ajax call to the controller:
displayCustomErrorMessage: function () {
url = document.URL;
if (url[url.length - 1] == '/') {
url = url.substring(0, url.length - 1);
}
url = url.substring(0, url.substring(0, url.lastIndexOf('/')).lastIndexOf('/'));
url = url + "/Home/GetCustomErrorMessage";
$.ajax({
cache: false,
type: "GET",
url: url,
dataType: 'json',
data: {},
success: function (data) {
alert(data);
},
error: function (xhr, ajaxOptions, thrownError) {
alert('Failed to retrieve states.');
}
}); },
The controller action GetCustomErrorMessage() fetches the custom error message and returns its content to the browser:
public string GetCustomErrorMessage()
{
var js = new JavaScriptSerializer();
return js.Serialize(Session["CustomErrorMessage"]);
}
The browser then calls an alert() to show the error message to the user:
In a production application, one would probably not want to use a simplistic alert() dialog to inform the end-user of a validation error, but would use Javascript to control the visibility and content of hidden message placeholders on the screen.
General Controller Exceptions
How other controller exceptions are handled is illustrated by clicking on the bad link at the top of the tutorial demonstration page, which sends "GET /ServerValidation/ValidationAndExceptions?act=bad HTTP/1.1" to the server. The server receives the bad query string and throws an exception:
public ActionResult ValidationAndExceptions(string act)
{
if (act == "bad") throw new Exception("You clicked on a bad link!");
var gridModel = new EmployeeJQGridModel();
SetupEmployeeJQGrid(gridModel.EmployeeGrid);
return View(gridModel);
}
In the GlobalFilterCollection, I had replaced the default HandleErrorAttribute with a customized ControllerExceptionHandlerAttribute:
public class FilterConfig
{
public static void RegisterGlobalFilters(GlobalFilterCollection filters)
{
filters.Add(new ControllerExceptionHandlerAttribute());
}
}
The thrown exception is handled by the OnException() method of the filter attribute.
public class ControllerExceptionHandlerAttribute : FilterAttribute, IExceptionFilter
{
public void OnException(ExceptionContext filterContext)
{
if (!filterContext.ExceptionHandled)
{
string message = filterContext.Exception.Message;
string stackTrace = filterContext.Exception.StackTrace;
HttpContext.Current.Session["CustomErrorMessage"] = message;
filterContext.Result = new ViewResult
{
ViewName = "Error",
ViewData = new ViewDataDictionary<string>(message)
};
filterContext.ExceptionHandled = true;
}
}
}
The filter attribute logs the exception and redirects to the Error view:
Please note that in a development environment, the redirect to the Error view does not necessarily work. While preparing this article, I spent several hours trying to understand why a base class Exception thrown from the ValidationAndExceptions action redirected to the Error view, but a connection error thrown from OnEmployeeDataRequested refused to redirect. This turns out to be a glitch in the development web server. http://stackoverflow.com/questions/13845318/asp-net-mvc-4-exception-handling-not-working
What happens is that when jqGrid makes a request to the url for grid data with which to render the grid, it expects the server to return a json string with the grid row information. If an exception is thrown from a development server, and the page does not redirect, jqGrid will receive, not the expected json string, but the HTML markup of the Error redirect screen. Naturally, the jqGrid code hasn't the foggiest idea what to do with this HTML markup.
A workaround is available which will be discussed in the next lesson.
The subject of exception handling in MVC is a large one. Since this is an article about jqGrid and not one about MVC exception handling, I would highly recommend that you read articles on this subject by authors whose knowledge of this subject considerably exceed my own:
http://www.prideparrot.com/blog/archive/2012/5/exception_handling_in_asp_net_mvc
http://www.codeproject.com/Articles/731913/Exception-Handling-in-MVC
Other Topics
Harnessing Events
Almost any action that jqGrid performs or is sensitive to has an attached event, which you can use as a trigger to perform your own custom actions. Dozens of events are described in the following documentation: http://www.trirand.com/jqgridwiki/doku.php?id=wiki:events You can monitor row selection, row double clicks, resizing, showing, hiding, editing, and more. At appropriate points in grid rendering, you can use events to call your own custom Javascript to change the grid's appearance.
I illustrate here two simple uses of events.
Controlling Grid Visibility
If the result of a query is an empty resultset, do we want the caption, header and navigation layers of the grid to still be visible on the page, like the grin of a Cheshire Cat, or do we want the grid to disappear entirely? I've had some users desire the one behavior, and some desire the other. In the code below, I wait until the grid is complete, then get the number of rows retrieved in the query to the server. If the total number of rows is zero, I hide() the <div></div> that encloses the table.
{ width: 50, name: "Country" },
{ width: 70, name: "HomePhone" },
{ width: 30, name: "Extension" },
{ width: 200, name: "PhotoPath", formatter: "link" }
],
gridComplete: function () {
var JQGrid2count = jQuery("#JQGrid2").jqGrid('getGridParam', 'records');
if (JQGrid2count > 0) $("#divJQGrid2").show();
else $("#divJQGrid2").hide();
},
height: "auto",
caption: "Employees Grid 2",
rowNum: 5,
pager: "#JQGrid2_pager",
loadError: JQGridDemo.jqGrid_aspnet_loadErrorHandler,
});
The code to handle dropdown changes, by the way, is very straightforward, except for one caveat. Upon a dropdown change, I use an ajax call to send the current dropdown selection back to the server to save in a Session variable. Upon a successful response, I reset the grid to page 1 and trigger the grid to be reloaded. Resetting the grid to page 1 is very important, since if the user happens to be on, say, page 10 of a grid and the grid suddenly shrinks to a single page of data, the user will be "stuck" on a nonexistent page until he refreshes the grid. The user will be very unhappy with your sloppy coding.
$("#ddlCountry").change(function () {
var selectedCountry = $(this).val();
$.ajax({
cache: false,
type: "GET",
url: "@(Url.Action("OnSelectedCountryChanged", "Events"))",
dataType: 'json',
data: { "country": selectedCountry },
success: function (data) {
$("#JQGrid1").setGridParam({ page: 1 }).trigger("reloadGrid");
$("#JQGrid2").setGridParam({ page: 1 }).trigger("reloadGrid");
},
error: function (xhr, ajaxOptions, thrownError) {
JQGridDemo.displayCustomErrorMessage();
}
})
});
Displaying Grid Load Errors
As explained in the last section, if an exception occurs in response to jqGrid's ajax request for grid data, a development server will return, not the expected json string, but HTML markup, which leaves the jqGrid code totally baffled. The jqGrid code will raise a loadError event, which we can exploit to provide the developer a sensible error message. We provide a handler for the event:
height: "auto",
caption: "Employees Grid 1",
rowNum: 5,
pager: "#JQGrid1_pager",
loadError: JQGridDemo.jqGrid_aspnet_loadErrorHandler,
});
The handler code extracts the response text from the ajax XMLHttpRequest object, and replaces the inner html of the current document body with the response text:
jqGrid_aspnet_loadErrorHandler: function (xht, st, handler) {
jQuery(document.body).css('font-size', '100%');
jQuery(document.body).html(xht.responseText);
},
The resulting screen shows the error message, which in this case is "The underlying provider failed on Open."
Master-Detail Tables
Master-detail grids are relatively simple to set up. Essentially, all that one needs to do is to add a handler function to the onSelectRow event. The primary key from the selected row is passed to the handler. One sets this key as a Query Parameter in an ajax url, which is used to retrieve the detail rows for populating the target grid. Set the target grid to page 1, set the caption of the target grid, and trigger it to reload:
{ width: 75, name: "Phone" },
{ width: 75, name: "Fax" }
],
caption: "Customers",
pager: "#JQGrid1_pager",
onSelectRow: function (id) {
if (lastID !== null) {
var lastRowData = jQuery(this).getRowData(lastID);
if (lastRowData["HasOrders"] == "-") {
$("#JQGrid1").jqGrid("setCell", lastID, "HasOrders", "+");
}
}
var rowData = jQuery(this).getRowData(id);
var hasOrders = rowData["HasOrders"];
var company = rowData["CompanyName"];
if (hasOrders == "+") {
$("#JQGrid1").jqGrid("setCell", id, "HasOrders", "-");
}
jQuery("#JQGrid2")
.setGridParam({url: "/MasterDetail/OnOrderDataRequested?id=" + id, page: 1 })
.setCaption("Orders for " + company + "")
.trigger('reloadGrid');
lastID = id;
},
Other logic alters the appearance of the +/- column that shows which rows have details to expand.
Exporting
Exporting your grids to an Excel-compatible file is extremely simple. If you have done all the work to display the grid, you have done almost all the work necessary to export to an Excel-compatible format.
As illustrated below, the code for the export method looks almost exactly the same as the code for the action method that returns a JsonResult for jqGrid rendering. The only difference is that instead of performing a DataBind() to the JQGrid, one calls the ExportToExcel() method. No other work is needed. One does not have to worry of the details of inserting the exported results into the response output stream, nor does one need to concern oneself about specifying the correct content type, content dispositions or anything else except giving the file an appropriate name.
public JsonResult OnEmployeeDataRequested()
{
var gridModel = new EmployeeJQGridModel();
var repository = new GenericEmployeeRepository();
var resultSet = repository.GetAll();
return gridModel.EmployeeGrid.DataBind(resultSet.AsQueryable());
}
public void ExportEmployeesToExcel()
{
var gridModel = new EmployeeJQGridModel();
var repository = new GenericEmployeeRepository();
var resultSet = repository.GetAll();
gridModel.EmployeeGrid.ExportSettings.ExportDataRange = ExportDataRange.All;
gridModel.EmployeeGrid.ExportToExcel(resultSet.AsQueryable(), "employees_export.xls");
}
Since the file is not actually an xls file, but rather consists of html markup, one gets a warning message when one opens the file with Excel.
Inline Editing
Up until now, all of our edits have been via Form editing, which involves creating a form, on the fly, to enter or edit grid data. Inline editing allows one to update the cell content in particular row, while Cell editing enables editing individual cells.
To enable Inline editing, one explicitly turns off the edit, add, save, and cancel buttons in the navGrid option, while explicitly turning on the same buttons in the inlineNav option:
height: "auto",
caption: "Employees",
rowNum: 5,
pager: "#JQGrid1_pager",
addParams: { useFormatter: false },
loadError: JQGridDemo.jqGrid_aspnet_loadErrorHandler
});
$("#JQGrid1").jqGrid('navGrid', '#JQGrid1_pager', {
edit: false, add: false, save: false, cancel: false,
search: true, refresh: true, del: true, view: true
});
$("#JQGrid1").jqGrid('inlineNav', '#JQGrid1_pager', {
edit: true, add: true, save: true, cancel: true
});
Unlike the case with Form editing, the visibility of the add and edit columns in Inline editing should necessarily be the same. One gets into logic issues if one creates a hidden required field which will appear only during an add operation:
{
editable:true, width:55, name:"BirthDate", search:false, formatter: "date",
formatoptions: { srcformat: "m/d/Y h:i:s A", newformat: "Y-m-d" }, editrules: { date: true }
},
Although the visibility of add and edit columns in Inline editing are necessarily the same, it is possible to toggle their editability. In a future version of this article, I will discuss how to toggle the editability of columns in Inline editing.
Memory Caching
The grid above has been configured to display five rows at a time. When one examines the network traffic used to fill the grid, one sees, in fact, that five rows of data are in fact passed back from server, regardless of how may records may have been retrieved in the database query.
This is an efficient use of network bandwidth, but it may not necessarily be efficient in terms of the back end. For example, if the original query required retrieving, say, 100000 records, this same query would be repeated every time one advanced by a single page, or if one sorted on a column.
Caching is a technique of storing data in memory that is expensive to create or retrieve. If the data does not need to be absolutely, totally up to date, subsequent requests for the data do not need to fetch data from the database; rather, the request can get data from the cache. By using the cache, one can improve the performance of one's application.
ASP.NET provides two types of caching for improving Web application performance. The one to be discussed here is application data caching. The data cache enables one to programmatically store arbitrary objects in server memory so that one's application can avoid spending the time and resources it takes to recreate them each time they are needed.
Caching is implemented by the Cache class. Instances of the cache are private to each instance of the application. Items are placed and retrieved in the Cache using key/value pairs. In this, the Cache may at first seem similar to Session variables; however the Cache class offers powerful features that enables customization of cache characteristics and lifespan. More importantly, under conditions of low memory, the operating system has the ability to evict seldom-used or low-priority items to free memory.
One can configure the Cache object to give certain items priority over other items when it performs scavenging. One can also establish an expiration policy for an item. For example, the item can be given an absolute expiration time, or it can be given a sliding expiration. External dependencies can also be specified, so that the cache item can be evicted depending on whether its data source has changed; this may be an external file, a database table, or another cached item.
Below, the cache key is set as the fully qualified controller name, "JQGridDemo.Controllers.CachingController", and the result of the database query is set into the cache with an absolute expiration of 60 seconds from its creation:
public JsonResult OnEmployeeDataRequested()
{
var gridModel = new EmployeeJQGridModel();
var repository = new GenericEmployeeRepository();
SetupEmployeeJQGrid(gridModel.EmployeeGrid);
var cache = MemoryCache.Default;
var resultSet = cache[this.ToString()] as IQueryable<Employee>;
if (resultSet == null)
{
CacheItemPolicy policy = new CacheItemPolicy();
policy.AbsoluteExpiration = DateTimeOffset.Now.AddSeconds(60);
resultSet = repository.GetAll();
cache.Set(this.ToString(), resultSet, policy);
}
return gridModel.EmployeeGrid.DataBind(resultSet.AsQueryable());
}
The cache is evicted, not just on timeout, but any time an edit is made to the table(s) that are the source for the displayed data. Although not shown here, the cache should also be evicted if a change is made to the view of the data, i.e. if the "where" clause parameters of the "select" query are changed.
[HttpPost]
[JQGridPostExceptionHandler]
public ActionResult EditEmployeeData(string oper, int? EmployeeID, string LastName, string FirstName,
string Title, string BirthDate, string HireDate, string Address, string City, string Region,
string PostalCode, string Country, string HomePhone, string Extension, string PhotoPath)
{
var cache = MemoryCache.Default;
cache.Remove(this.ToString());
An Evolving Tool
Since its first development, jqGrid has undergone frequent revision to (1) fix bugs, (2) add feature enhancements, and to improve both the (3) end-user experience and the (4) developer experience.
These improvements unfortunately have side effects, such that when one uses a search engine to try to figure out how to fix a bug or to implement a feature, one gets frequent hits to solutions that may once upon a time have worked, but which no longer work properly in the current version of the code. A case in point is when I tried to provide the detail screen in a master-detail view the following behavior:
I wanted the jqGrid to adjust its height automatically to a given number of rows; but when a certain height is reached, its height would not increase anymore, but the grid would acquire a vertical scrollbar.
Searching the internet brought up the following 2011 solution by a well-known expert on jqGrid:
http://stackoverflow.com/questions/5895801/jqgrid-with-automatic-height-but-has-a-max-height-scrollbars
Unfortunately, the above solution didn't work for me:
The developer's best friend may be a good search engine, but a web UI developer's second-best friend is certainly a suite of web development tools such as Firebug or the Chrome Developer Tools. Using such a tool, I was able to ascertain that the culprit was a height default which apparently was not present in 2011, or which was else implemented differently:
I was able to use the web development tool to work out an alternative to the originally suggested solution:
The result was a detail grid that functioned according to my expectations.
Web development tools are provided by all of the major browsers. Familiarity with one or another of the available tool suites is an extremely valuable skill when working with jqGrid. If one is confronted with a bug or wishes a desired behavior that searching on the Internet or raising a question on a developer forum does not answer, one can frequently work out a solution by directly manipulating the html markup.
Server-Side Paging
The JQGrid base class defined in the Trirand.Web.Mvc namespace automatically provides, out of the box, client side paging capabilities.
For various reasons to be discussed shortly, it may be deemed preferable to implement paging on the server side. Although well-optimized for client-side paging, the JQGrid class has issues if presented with resultsets that have been paged on the server side.
We find that need to take one of the two following actions:
- We can implement workarounds for the features of the JQGrid class that are incompatible with server-side paging.
- We can abandon the use of the JQGrid class entirely, instead building our JsonResults manually.
The second option is entirely feasible and not at all difficult, since the structure of jqGrid's JsonResponse object has been well-documented online as well as in books.
For the purposes of this tutorial, however, we will focus on the first option.
Option 1: Workarounds Adapting the JQGrid Class to Server-Side Paging
In the grids shown above, please note the following features that up to now I had not yet incorporated into any of the other grids in this article:
- The CompanyName is the default sorted column, sorted in ascending order.
- A dropdown menu shows the number of rows in the grid, and allows changing the number of rows.
- The record text in the lower right of the grid has format "View {0} - {1} of {2}". This is the default format set in the grid.locale file in the i18n folder.
The first two options are set in the jqGrid options list:
$("#JQGrid1").jqGrid({
url: "/MasterDetail/OnCustomerDataRequested",
mtype: "GET",
datatype: "json",
page: 1,
jsonReader: { id: "CustomerID" },
prmNames: { id: "CustomerID" },
sortname: "CompanyName",
sortorder: "asc",
colNames: [" ", "Cust ID", "Company Name", "Contact Name", "Contact Title",
"Address", "City", "Region", "Postal Code", "Country", "Phone", "Fax"],
viewrecords: true, rowNum: 5,
rowList: [5, 10, 20, 50, 100],
pager: "#JQGrid1_pager",
loadError: JQGridDemo.jqGrid_aspnet_loadErrorHandler
});
jQuery('#JQGrid1').bindKeys();
Events that occur in the built-in client-side paging
When one clicks on the pager arrow, clicks on the head of a sortable column, or changes the number of rows to be displayed, the browser sends back, in the GET querystring, the page number to be retrieved ("page"), the number of rows per page ("rows"), the sort column ("sidx"), and the sort direction ("sord").
The result set returned by the repository call is a collection of CustomerModel objects:
The JQGrid DataBind method reads the Request QueryString for the page number, the number of rows, the sort column and the sort direction. In the illustrated case, DataBind sorts the IQueryable CustomerModel collection by CustomerName, skips the first 15 rows, and takes the next 5 rows to populate the JsonResult object.
The "Data" property of the JsonResult is a JsonResponse object that includes, among other information, the page number, the total number of records that were in the IQueryable model, a computed total number of pages based on the page size and the total number of records, and the collection of rows that is being returned to the browser.
The repository call was inefficient. Out of 91 rows, only five rows were returned to the browser. We had earlier discussed server side caching strategies that improved the efficiency of client-side paging by avoiding repeated unnecessary repository calls when paging, sorting, or changing the table size.
However, there are various situations where caching may be considered unacceptable:
- Caching is unacceptable if the database records are rapidly changing and the view must be as up-to-date as possible.
- Caching is unacceptable if the repository calls return massive resultsets, only a few records of which are likely to be accessed.
- The issues associated with massive resultsets are exascerbated if the database is being accessed through a service. There may be bandwidth issues between the database service and the web server.
To address such issues, paging may be implemented on the server side. We find that we must work around various features of the JQGrid class before it will be compatible with server-side paging.
Events that occur in server-side paging
The browser sends back, in the GET querystring, exactly the same sort of information as was sent back in client-side paging:
There is considerably more work necessary to be done on the controller side than with client-side paging. The controller needs to read the page, rows, sidx and sord from the Request QueryString, and passes these values on to the repository. Two key lines have been commented out for the purposes of this tutorial.
public JsonResult OnCustomerDataRequested(int page, int rows, string sidx, string sord)
{
int totalRecordsCount;
var gridModel = new CustomerJQGridModel();
var repository = new CustomerOrdersRepository();
var resultSet = repository.GetPageOfCustomers(page, rows, sidx, sord, out totalRecordsCount);
JsonResult jsonResult = gridModel.CustomerGrid.DataBind(resultSet.AsQueryable());
return jsonResult;
}
In the case illustrated here, the repository query on the database sorts by the CompanyName column, skips 15 rows and takes 5 rows to return a resultset of 5 rows.
We now encounter a problem. The JQGrid DataBind method ALSO reads the page, rows, sidx and sord from the Request QueryString. It sorts the 5 rows of the resultset and then skips 15 rows, passing the end of the data and returning no records. Neither can it compute the correct total number of pages to send back to the browser.
To fix this issue, we need to trick the JQGrid DataBind method.
- We set the value of the "page" element of the Request QueryString collection to "1", so that the DataBind method does not skip.
- We modify the JsonResponse so to have the correct values for page, records, and total.
The Request QueryString is read-only and the JsonResponse class is private. We get around these issues through the use of reflection. We define two extension methods on the Controller, SetRequestQueryStringPageToOne() and ModifyJsonResultData():
public static class ControllerJQGridHelper
{
public static void SetRequestQueryStringPageToOne(this Controller c)
{
var type = c.Request.QueryString.GetType();
PropertyInfo prop = type.GetProperty("IsReadOnly",
BindingFlags.NonPublic | BindingFlags.Instance);
prop.SetValue(c.Request.QueryString, false, null);
c.Request.QueryString.Set("page", "1");
prop.SetValue(c.Request.QueryString, true, null);
}
public static void ModifyJsonResultData(this Controller c, Object jsonResponse,
int currentPage, int rowsPerPage, int totalRecordsCount)
{
var type = jsonResponse.GetType();
if (type.Name != "JsonResponse") return;
PropertyInfo pageProperty = type.GetProperty("page");
pageProperty.SetValue(jsonResponse, currentPage, null);
PropertyInfo recordsProperty = type.GetProperty("records");
recordsProperty.SetValue(jsonResponse, totalRecordsCount, null);
PropertyInfo totalProperty = type.GetProperty("total");
int totalpages = (totalRecordsCount + rowsPerPage - 1) / rowsPerPage;
totalProperty.SetValue(jsonResponse, totalpages, null);
}
}
The use of these methods is illustrated here. We uncomment the two lines previously mentioned:
public JsonResult OnCustomerDataRequested(int page, int rows, string sidx, string sord)
{
int totalRecordsCount;
var gridModel = new CustomerJQGridModel();
var repository = new CustomerOrdersRepository();
this.SetRequestQueryStringPageToOne();
var resultSet = repository.GetPageOfCustomers(page, rows, sidx, sord, out totalRecordsCount);
JsonResult jsonResult = gridModel.CustomerGrid.DataBind(resultSet.AsQueryable());
this.ModifyJsonResultData(jsonResult.Data, page, rows, totalRecordsCount);
return jsonResult;
}
Since page has been set to "1", DataBind does not skip and so successfully populates the JsonResponse. However, the JsonResponse still has incorrect property values:
The call to ModifyJsonResultData() corrects these values:
Option 2: Building the JsonResponse Object Completely From Scratch
The official documentation describing the structure of the Json result expected by the jqGrid JavaScript may be found here:
http://www.trirand.com/jqgridwiki/doku.php?id=wiki:retrieving_data
jqGrid expects the following default format for JSON data.
In brief, one creates a view model of the grid, then serializes the model into a Json representation of the model which is what is actually returned to the browser.
Using the code
- Please look up, read and respect the free licensing terms that Trirand has published on their web site.
- This MVC 4 application was developed in Visual Studio 2013 using the .NET 4.5 Framework, Entity Framework 6.1.1, and the Razor engine. I have tried to make installation as painless as possible, but it is possible that I have overlooked something. I have included the necessary jQuery and jqGrid files.
- In the Web.config, check that the binding redirect is set to point to the version of System.Web.Mvc that you are using. Check the Web.config file for any other version mismatches.
- Download and install the Northwind sample database, and set the data source of the "NorthwindEntities" connection string appropriately.
- The version of SQL Server or SQL Server Express should be at least 2008. Otherwise you can get "The version of SQL Server in use does not support datatype datetime2" while attempting to add new records into tables that have a datetime field. See this Stack Overflow article.
- At this point, you are ready to build. NuGet is configured to (1) download the NuGet.exe file, then (2) to check the dependencies listed in packages.config and to download the dependencies into the packages folder.
- While I was setting up this demo, I found that the Entity Framework 5.0 "database first" tools were a bit buggy. Among other annoyances, the Employee.cs file kept disappearing every time that I regenerated the Northwind.tt template. I have since upgraded to version 6.1.1, and here's hoping that you don't experience the same annoyances that I did.
History
2015-08-30 Replaced most code images with formatted code blockis.
2015-08-16 Added new section on Server-Side Paging. Also linted the JavaScript and modified some figures.
2014-09-24 Extracted text and images from demo source code to produce this article.
