By David Espino
Jquery UI controls are actually very useful on many web projects and jqGrid is an excellent example. This is a really handy control that can help us to render our list of objects using Ajax calls.
How to fill the jqGrid?
The jqGrid needs a json object with a particular structure. This is an example of the json object used to fill a jqGrid.
{"page":1,"total":100000,"records":1000000,"rows":[{"id":"1","cell":["1","VINET","1996/07/04","32.3800","Vins et alcools Chevalier"]}, {"id":"2","cell":["2","TOMSP","1996/07/05","11.6100","Toms Spezialitäten"]},{"id":"3","cell":["3","HANAR","1996/07/08","65.8300","Hanari Carnes”}}
As you can see there are general parameters (as page, total, records) and the list of rows, defined each row as a string array. So, if you want to fill a jqGrid from javascript code, here’s a way to achieve that.
Create Helper Clases:
First we need to create helper clases to represent the jqGrid results, the grid row, the arguments to implement paging, and the jqGrid request params.
public class GridResult
{
//Actual Page
public string page { get; set; }
//Number of pages to display
public int total { get; set; }
//Number of rows to display
public string records { get; set; }
//items
public List<GridRow> rows { get; set; }
}
public class GridRow
{
public string id { get; set; }
public string[] cell { get; set; }
}
public class PagingArguments
{
public int page { get; set; }
public int limit { get; set; }
public int start { get; set; }
public int totalPages { get; set; }
}
public class GridRequest
{
public int Page { get; set; }
public int Limit { get; set; }
public string SortIndex { get; set; }
public string SortOrder { get; set; }
}
Create auxiliar extensions
We are going to pass a list of objects to be ordered and paged, but the list could be of any type of object, and so the properties to be displayed. We need to create methods to execute a LINQ query regardless of the List of objects sent. We need other methods to create a json string from any type of object, so we can send it to javascript and rendered to the grid.
public static class JQGridExtensions
{
//Method to do the sorting and the paging of the grid
public static IQueryable<T> SortBy<T>(this IQueryable<T> source, string propertyName)
{
if (source == null)
{
throw new ArgumentNullException("source");
}
// DataSource control passes the sort parameter with a direction
// if the direction is descending
int descIndex = propertyName.IndexOf(" DESC");
int ascIndex = propertyName.IndexOf(" ASC");
if (descIndex >= 0)
{
propertyName = propertyName.Substring(0, descIndex).Trim();
}
if (ascIndex >= 0)
{
propertyName = propertyName.Substring(0, ascIndex).Trim();
}
if (String.IsNullOrEmpty(propertyName))
{
return source;
}
ParameterExpression parameter = Expression.Parameter(source.ElementType, String.Empty);
MemberExpression property = Expression.Property(parameter, propertyName);
LambdaExpression lambda = Expression.Lambda(property, parameter);
string methodName = (descIndex < 0) ? "OrderBy" : "OrderByDescending";
Expression methodCallExpression = Expression.Call(typeof(Queryable), methodName,
new Type[] { source.ElementType, property.Type },
source.Expression, Expression.Quote(lambda));
return source.Provider.CreateQuery<T>(methodCallExpression);
}
//Method to create a list of GridRow according to the selected properties
public static List<GridRow> ToGridRowList<T>(this IQueryable<T> source, string id, string[] properties)
{
List<GridRow> result = new List<GridRow>();
if (source == null)
{
throw new ArgumentNullException("source");
}
if (string.IsNullOrEmpty(id))
{
throw new ArgumentNullException("id");
}
if (properties == null || properties.Length == 0)
{
throw new ArgumentNullException("properties");
}
// string test = "dafdas";
foreach (var item in source)
{
GridRow row = new GridRow();
Type type = item.GetType();
//Use a bit of reflection to get the property names an values
PropertyInfo info = type.GetProperty(id);
row.id = info.GetValue(item, null).ToString();
row.cell = new string[properties.Length];
for (int i = 0; i < properties.Length; i++)
{
info = type.GetProperty(properties[i]);
row.cell[i] = info.GetValue(item, null).ToString();
}
result.Add(row);
}
return result;
}
//Method to Create a Json from any Object
public static string ToJson<T>(this T obj)
{
return ToJson<T>(obj, SerializerType.JavaScriptSerializer);
}
//Method to Create a Json from any Object
public static string ToJson<T>(this T obj, SerializerType type)
{
string retval = "{}";
if (obj != null)
{
switch (type)
{
case SerializerType.JavaScriptSerializer:
{
retval = _JavaScriptSerializer.Serialize(obj);
break;
}
case SerializerType.DataContractSerializer:
{
DataContractJsonSerializer dcs = new DataContractJsonSerializer(typeof(T));
MemoryStream stream = new MemoryStream();
dcs.WriteObject(stream, obj);
retval = Encoding.UTF8.GetString(stream.ToArray());
stream.Close();
stream.Dispose();
break;
}
default:
{
retval = _JavaScriptSerializer.Serialize(obj);
break;
}
}
}
return retval;
}
}
public enum SerializerType
{
JavaScriptSerializer = 1,
DataContractSerializer = 2
}
}
Construct helper objects from Grid Request.
We need a method to pull jqGrid params from the HttpContext.Request object. We also need another helper method to calculate paging arguments. (Include this methods within the previous class)
private static PagingArguments GetPagingVariables(int countItems, int page, int limit)
{
PagingArguments arguments = new PagingArguments();
//Set initial Values
arguments.page = page;
arguments.limit = limit;
//Prepare paging variables
float numberOfPages = (float)countItems / (float)limit;
if (countItems > 0)
{
arguments.totalPages = (int)Math.Ceiling(numberOfPages);
}
//At least one page
if (arguments.totalPages == 0)
{
arguments.totalPages = 1;
}
//Set actual page
if (arguments.page > arguments.totalPages)
{
arguments.page = arguments.totalPages;
}
if (arguments.limit < 0)
{
arguments.limit = 0;
}
//Get paging variables
arguments.start = arguments.limit * arguments.page - arguments.limit;
if (arguments.start <= 0)
{
arguments.start = 0;
}
return arguments;
}
public static GridRequest ToGridRequest(this System.Collections.Specialized.NameValueCollection parameters)
{
GridRequest request = null;
if (parameters["page"] != null && parameters["rows"] != null && parameters["sidx"] != null && parameters["sord"] != null)
{
int page = 0;
int rows = 0;
Int32.TryParse(parameters["page"], out page);
Int32.TryParse(parameters["rows"], out rows);
request = new GridRequest();
request.Page = page;
request.Limit = rows;
request.SortIndex = parameters["sidx"];
request.SortOrder = parameters["sord"];
}
return request;
}
Construct the Final extension method.
With all this classes and methods we are set, so we can create or final extension Method to transform a list of any type to a GridResult object.
public static GridResult ToJQGridResult<T>(this List<T> response, int page, int limit, string sortIndex, string sortOrder, string idColumn, string[] properties)
{
//Set the first column as sort order by Default
PagingArguments arguments = GetPagingVariables(response.Count(), page, limit);
// Type parentType = response.First().GetType();
//Order List
StringBuilder sortName = new StringBuilder();
sortName.Append(sortIndex);
sortName.Append(" ");
sortName.Append(sortOrder.ToUpper());
var sortedResult = response.AsQueryable<T>().SortBy<T>(sortName.ToString())
.Skip(arguments.start)
.Take(arguments.limit);
//Generate Results object
GridResult results = new GridResult();
results.page = arguments.page.ToString();
results.records = response.Count().ToString();
results.total = arguments.totalPages;
results.rows = sortedResult.ToGridRowList(idColumn, properties);
return results;
}
Time to Test It!!
So we are all set!!!. It’s Time to test it. We need to create our JqGrid in a Js file within our project. For this example I’m using an MVC 3 project. So here is the html for the view containing the jqGrid.
<div id="divUserListMain">
<h1>Actual users</h1>
<table id="userGrid"></table>
<div id="userGridPager"></div>
<p>
<input type="button" value="@Resources.CorporateUser_Delete" class="btn-light-blue" id="btn-delete"/>
</p>
<div id="divColumnHeaders" style="display:none">
<span id="colNameFullName">@Resources.UserList_FullName</span>
</div>
</div>
This is the JS code that I use to create the grid. Be sure to add the jqGrid js and css files, so the plugin can be properly used:
var names = getGridHeaders();//Get Localized Headers
$("#userGrid", _userListContainer).jqGrid({
url: $Url.resolve("~/CorporateAccount/UserList"),
datatype: "json",
colNames: names,
colModel:[
{name:'Id',index:'Id', width:55, hidden: true},
{name:'FullName',index:'FullName', width:190}
],
rowNum: 3,
rowList:[3, 5, 20],
pager: '#userGridPager',
sortname: 'Id',
viewrecords: true,
sortorder: "desc",
caption:"",
multiselect: true
});
And then the controller method:
public ActionResult UserList()
{
int accountId = _userRepository.Account != null ? _userRepository.Account.AccountId : 0;
//if the account is not found, send an error message
if (accountId == 0)
{
SuccessResponse response = new SuccessResponse { Success = false, Message = Resources.Ajax_GenericError };
return Content(response.ToJson());
}
else
{
FindCorporateUserResponse response = _accountService.SelectCorporateUsers(accountId); //Get user list from DB using a custom service
if (response.Success && response.UserFound)
{
//Get Values from request to paging and ordering
GridRequest request = HttpContext.Request.Params.ToGridRequest();
if (request != null)
{
string idField = "Id";
string [] properties = {"Id", "FullName"};
GridResult jsonResults = response.Users.ToJQGridResult<CorporateUserView>(request.Page, request.Limit, request.SortIndex, request.SortOrder, idField, properties);
return Content(jsonResults.ToJson());
}
else
{
SuccessResponse responseJson = new SuccessResponse { Success = false, Message = Resources.Ajax_GenericError };
return Content(responseJson.ToJson());
}
}
else
{
return Content(response.ToJson());
}
}
}
And that’s all. Now you have a method that can transform any kind of List to grid results json object.
Enjoy!!