An easy subject this time. To configure subgrid we need to add few parameters to grid initialization code:
<scripttype="text/javascript">
$(document).ready(function() {
$('#jqgOrders').jqGrid({ //url from wich data should be requested
url: '/Home/OrdersGridData/', //type of data
datatype: 'json', //url access method type
mtype: 'GET', //columns names
colNames: ['OrderID', 'Customer', 'Employee', 'OrderDate', 'RequiredDate', 'ShippedDate', 'ShipCountry'], //columns model
colModel: [
{ name: 'OrderID', index: 'OrderID', align: 'left' },
{ name: 'Customer', index: 'CustomerID', align: 'left' },
{ name: 'Employee', index: 'EmployeeID', align: 'left' },
{ name: 'OrderDate', index: 'OrderDate', align: 'left' },
{ name: 'RequiredDate', index: 'RequiredDate', align: 'left' },
{ name: 'ShippedDate', index: 'ShippedDate', align: 'left' },
{ name: 'ShipCountry', index: 'ShipCountry', align: 'left' }
], //pager for grid
pager: $('#jqgpOrders'), //number of rows per page
rowNum: 10, //initial sorting column
sortname: 'OrderID', //initial sorting direction
sortorder: 'asc', //we want to display total records count
viewrecords: true, //grid width
width: 'auto', //grid height
height: 'auto', //enable subgrid
subGrid: true, //subrid model
subGridModel: [{ //subgrid columns names
name: ['ProductName', 'UnitPrice', 'Quantity', 'Discount'], //subgrid columns widths
width: [200, 100, 100, 100], //subrig columns aligns
align: ['left', 'left', 'left', 'left']
}], //url from which subgrid data should be requested
subGridUrl: '/Home/OrdersDetailsSubgridData/'
});
}); </script>
Controller action for main grid is pretty standard (you can take a look at corresponding sample in jqGrid basics post). The one for subgrid is slightly different. That's because of the different data format - subgrid takes only rows collection with cell table inside.
/// <summary>
///Provides json data for jqGrid subgrid /// </summary>
/// <param name="id">parent row id</param> /// <returns>json data</returns> publicActionResult OrdersDetailsSubgridData(string id)
{ //Preparing anonymous variable with json data var ordersDetails = new
{
rows = (from orderDetails in _repository.GetOrderDetails(Convert.ToInt32(id)) select new
{ //table of cells values
cell = new string[] {
orderDetails.Product.ProductName,
orderDetails.UnitPrice.ToString(),
orderDetails.Quantity.ToString(),
orderDetails.Discount.ToString()
}
}
).ToArray()
};
//Returning json data return Json(ordersDetails);
}
Subgrid is very limited (no support for paging, sorting, searching and editing). There is a solution which uses some of the subgrid functions of the main grid to create not a subgrid, but another fully functional grid. This solution is well described here and I'm not going to dig into it (it's just a little bit tricky initialization javascript). Let's take a look at standard subgrid we have created:
Of course you can download sample application here.
As promised, it's time to go ahead with a CRUD scenario for jqGrid. Of course, we need to do some configuration first. We must define which columns are editable. For ones we will set type of edit field (through edittype parameter). We can also set some options for the edit field (editoptions) like maxlength for 'text' or dataUrl for 'select' (we will use the same actions for providing selects markup as in searching sample). Grid provides as also some validation support, which can be configured through editrules option. Be aware, that options we use in our example are only a small subset of what jqGrid allow us to configure, so I strongly advice reading the documentation. Ok, let's initialize our grid:
We are going to use a navigator as placeholder for add, edit and delete buttons, but it's not necessary. The buttons can be put anywhere on page, they just need to make proper API calls, which are well described in form editing documentation section. For complete CRUD scenario, we need to prepare three operations. We will start with easiest one, which is deleting. All we have to do is enable delete button and set url for operation (in fact the url isn't necessary, all operations can use the editurl from initial configuration, but having seperate actions for each operation is a lot cleaner).
Delete request contains one parameter, which is row id value. In response we can send anything we want (or just nothing). If we sent something, we will receive it in afterSubmit event. Knowing that, we can prepare delete controller action:
//Adding product to repository bool success = _repository.AddProduct(product);
//Returning data - we can hadle this data in form afterSubmit event return Json(success);
}
Last operation to implement is editing. There are two edit approaches to consider (actually there are three, but I'm going to skip cell editing): inline editing and form editing. In inline editing we are adding onSelectRow event handler in which we call editRow (for displaying edit controls) or saveRow (for posting new values to server) method:
$('#jqgProducts').setGridParam({
onSelectRow: function(id){ if(id && id != lastSel){ //save changes in row
$('#jqgProducts').saveRow(lastSel, false);
lastSel=id;
} //trigger inline edit for row
$('#jqgProducts').editRow(id, true);
}
});
Form editing works the same as adding. We just enable the button and then grid constructs the form based on colModel:
No matter which approach we choose, request looks the same. We are getting row id and values for all the editable fields. What we need to do is get object from repository and set new values:
//Returning data - we can hadle this data in form afterSubmit event return Json(success);
}
That makes our CRUD scenario complete. Source code can be downloaded from usual location. To make deleting easy, I have changed FK_Order_Details_Products foreing key in Northwind database.
Today we will look at searching in jqGrid. We will focus on native API mechanisms. There are four searching approaches in jqGrid. We will go through all of them, but first let's prepare some common configuration.
As you can see, we have extended our colModel definition. We have specified if we can search by column (search parameter) and what is the type of searching field (stype parameter). For 'select' search fields we have provided url from where jqGrid can get actual select markup via ajax request (surl parameter). For simplicity we have also limited possible value operators to 'equal' and 'not equal' (sopt array in searchoptions parameter). You should also notice that we have changed method type from GET to POST. We did it, because the list of parameters which will be sent back to server will vary depending on searching approach and set filters. Now we should prepare controller actions which returns markup for our 'select' fields:
<select> <optionvalue=''></option> <%foreach (KeyValuePair<int,string> value in (Dictionary<int, string>)Model) { %> <optionvalue='<%= Html.Encode(value.Key.ToString())%>'> <%= Html.Encode(value.Value)%> </option> <% } %> </select>
When all of the common stuff is done, we can start going through search approaches. Let's start with toolbar searching. When we use this approach, all the inputs elements are created below the header elements. Filter values are passed to application separately for each filter with value definied by user. We turn on this approach like this:
$('#jqgProducts').filterToolbar();
Custom searching creates a search form for the grid (it requires a div placeholder on page). This form can be based on colModel definitions (like in our example), or you can provide filterModel for it. Filter values are passed to application in exactly the same way as for the toolbar searching. Initialization code looks like this:
Single field searching displays popup where you can choose field to filter, operator which will be applied and filter value. Application always receives three parameters: searchField (name of chosen field), searchOper (operator) and searchString (value). It is displayed with following call:
$('#jqgProducts').searchGrid();
Advanced searching works almost the same as single field searching. The difference is that you can set multiply conditions. Application receives only one parameter (filters) which contains JSON string that needs to be parsed. Display call is also pretty much the same, we only need to add multipleSearch parameter.
Now we need to modify controller action to support postData and filtering. There is one very important note to make. Following code is only for example purposes and should never be used in real application (it has no proper validation, types interpretation etc.).
/// <summary>
///Provides json data for jqGrid ///[WARNING: This code is for presentation purposes only, and should never be use in real application, it lacks validation, types interpretation etc.] /// </summary>
/// <param name="postData">POST parameters collection</param>
/// <returns>json data</returns>
[AcceptVerbs(HttpVerbs.Post)] publicActionResult ProductsGridData(FormCollection postData)
{ //Extracting parameters from collection string sortExpression = postData["sidx"]; string sortDirection = postData["sord"]; int pageIndex = Convert.ToInt32(postData["page"]); int pageSize = Convert.ToInt32(postData["rows"]);
string filterExpression = String.Empty; if (Convert.ToBoolean(postData["_search"]))
{ //Preparing filter expression for single field approach if (postData.AllKeys.Contains("searchField")
&& postData.AllKeys.Contains("searchString")
&& postData.AllKeys.Contains("searchOper"))
filterExpression = GetFilter(postData["searchField"],
postData["searchOper"].Equals("eq") ? "=" : "!=",
postData["searchString"]); //Preparing filter expression for advanced approach
//[WARNING: Quite nasty code ahead, don't do it like this ;)] else if (postData.AllKeys.Contains("filters"))
{ string groupOperator = " And "; if (postData["filters"].Contains("\"groupOp\":\"OR\""))
groupOperator = " Or "; string filtersRules = postData["filters"].Substring(postData["filters"].IndexOf("[") + 2);
filtersRules = filtersRules.Remove(filtersRules.Length - 3).Replace("},{", "|").Replace("\"", String.Empty); foreach (string filterRule in filtersRules.Split('|'))
{ Match filterValues = Regex.Match(filterRule, "^field:(.*),op:(.*),data:(.*)$");
filterExpression = filterExpression + GetFilter(filterValues.Groups[1].Value,
filterValues.Groups[2].Value.Equals("eq") ? "=" : "!=",
filterValues.Groups[3].Value) + groupOperator;
}
filterExpression = filterExpression.Remove(filterExpression.Length - groupOperator.Length);
} //Preparing filter expression for toolbar and custom approach
else
{ foreach (string postDataKey in postData.AllKeys)
{ if (!postDataKey.Equals("nd") && !postDataKey.Equals("sidx")
&& !postDataKey.Equals("sord") && !postDataKey.Equals("page")
&& !postDataKey.Equals("rows") && !postDataKey.Equals("_search"))
filterExpression = filterExpression + GetFilter(postDataKey, "=", postData[postDataKey]) + " And ";
}
filterExpression = filterExpression.Remove(filterExpression.Length - 5);
}
}
//Getting total records count from repository int totalRecords = _repository.GetProductsCount();
//Preparing anonymous variable with json data var productsData = new
{ //total pages count
total = (int)Math.Ceiling((float)totalRecords / (float)pageSize), //page number
page = pageIndex, //total records count
records = totalRecords, //table with rows data
rows = (from product in _repository.GetProducts(filterExpression, sortExpression, sortDirection, pageIndex - 1, pageSize) select new
{ //row id
id = product.ProductID, //table of cells values
cell = new string[] {
product.ProductID.ToString(),
product.ProductName, "[" + product.Supplier.SupplierID.ToString()
+ "] " + product.Supplier.CompanyName,
product.Category.CategoryName,
product.QuantityPerUnit,
product.UnitPrice.ToString(),
product.UnitsInStock.ToString()
}
}
).ToArray()
};
//Returning json data return Json(productsData);
}
Last time we have set up our first jqGrid. In this post I'm going to take a look at columns formatting. First let's use predefined formatter. Setting any of predefined formats for column is as simple as adding formatting type and options to colModel definition. In our example we will format UnitPrice as USD. To achieve this, we will set formatting type to currency with following options: decimalSeparator: '.', thousandsSeparator: ',', decimalPlaces: 2 and prefix: '$'.
Now let's take a look at custom formatter. We will prepare formatting javascript function for UnitsInStock column (instead of displaying numbers we will be displaying flags which color will depend on amount of units). Function receives three parameters: cellvalue - original value for cell, options - array of options passed in colModel, rowObject - object with row data. Return value of our function should be new value for a cell. We also have to set formatting in colModel.
Let's take it a little bit further. We will format Supplier column so that it will include a link that displays a popup with supplier details. To achieve this we will use jQuery UI Dialog. To do that, we must reference jQuery UI script file.
function showSupplierDialog(linkElement, supplierId) { //request json data
$.getJSON('/Home/SupplierData/', { supplierId: supplierId }, function(data) { //set values in dialog
$('#sCompanyName').text(data.companyName);
$('#sAddress').text(data.address);
$('#sPostalCode').text(data.postalCode);
$('#sCity').text(data.city);
$('#sCountry').text(data.country);
$('#sPhone').text(data.phone);
$('#sHomePage').text(data.homePage); //get link position var linkPosition = $(linkElement).offset(); //set dialog position
$('#jqdlgSupplier').dialog('option', 'position', [linkPosition.left, linkPosition.top]); //open dialog
$('#jqdlgSupplier').dialog('open');
});
}; </script>
And voila. We can seat and admire our grid.
There is one more thing which needs to be mentioned. If you want custom formatted column to be editable (I'm going to write about CRUD scenario soon), you need to provide unformatter function which will extract original value from cell. As usual, example source code can be found here.
