jqGrid and ASP.NET MVC - Searching
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.
Now we should prepare controller actions which returns markup for our 'select' fields:
There are four searching approaches in jqGrid. We will go through all of them, but first let's prepare some common configuration.
<script type="text/javascript">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.
$(document).ready(function() {
$('#jqgProducts').jqGrid({
//url from wich data should be requested
url: '/Home/ProductsGridData/',
//type of data
datatype: 'json',
//url access method type
mtype: 'POST',
//columns names
colNames: ['ProductID', 'ProductName', 'Supplier', 'Category', 'QuantityPerUnit', 'UnitPrice', 'UnitsInStock'],
//columns model
colModel: [
{ name: 'ProductID', index: 'ProductID', align: 'left', search: true, stype: 'text', searchoptions: { sopt: ['eq', 'ne'] } },
{ name: 'ProductName', index: 'ProductName', align: 'left', search: true, stype: 'text', searchoptions: { sopt: ['eq', 'ne'] } },
{ name: 'Supplier', index: 'SupplierID', align: 'left', formatter: supplierFormatter, search: true, stype: 'select', edittype: 'select', surl: '/Home/SuppliersSelect', searchoptions: { sopt: ['eq', 'ne']} },
{ name: 'Category', index: 'CategoryID', align: 'left', search: true, stype: 'select', edittype: 'select', surl: '/Home/CategoriesSelect', searchoptions: { sopt: ['eq', 'ne']} },
{ name: 'QuantityPerUnit', index: 'QuantityPerUnit', align: 'left', search: false },
{ name: 'UnitPrice', index: 'UnitPrice', align: 'left', formatter: 'currency', formatoptions: { decimalSeparator: '.', thousandsSeparator: ',', decimalPlaces: 2, prefix: '$'}, search: false },
{ name: 'UnitsInStock', index: 'UnitsInStock', align: 'left', formatter: unitsInStockFormatter, search: false }
],
//pager for grid
pager: $('#jqgpProducts'),
//number of rows per page
rowNum: 10,
//initial sorting column
sortname: 'ProductID',
//initial sorting direction
sortorder: 'asc',
//we want to display total records count
viewrecords: true
});
});
</script>
Now we should prepare controller actions which returns markup for our 'select' fields:
/// <summary>SelectPartial.ascx has very simple content:
/// Provides select markup for suppliers filter
/// </summary>
/// <returns>select markup</returns>
public ActionResult SuppliersSelect()
{
Dictionary<int, string> suppliers = new Dictionary<int, string>();
foreach (Supplier supplier in _repository.GetSuppliers())
suppliers.Add(supplier.SupplierID, supplier.CompanyName);
return PartialView("SelectPartial", suppliers);
}
/// <summary>
/// Provides select markup for categories filter
/// </summary>
/// <returns>select markup</returns>
public ActionResult CategoriesSelect()
{
Dictionary<int, string> categories = new Dictionary<int, string>();
foreach (Category category in _repository.GetCategories())
categories.Add(category.CategoryID, category.CategoryName);
return PartialView("SelectPartial", categories);
}
<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:
<option value=''></option>
<% foreach (KeyValuePair<int,string> value in (Dictionary<int, string>)Model) { %>
<option value='<%= Html.Encode(value.Key.ToString())%>'>
<%= Html.Encode(value.Value)%>
</option>
<% } %>
</select>
$('#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:
$('#jqgsProducts').filterGrid('#jqgProducts', { gridModel: true, gridNames: true, formtype: 'vertical', autosearch: false, enableSearch: true, enableClear: true });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.
$('#jqgProducts').searchGrid({ multipleSearch: true });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>Let's take a look at our work result (source code is available here).
/// 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)]
public ActionResult 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);
}
private string GetFilter(string searchField, string searchOper, string searchValue)
{
if ((searchField == "ProductID") || (searchField == "SupplierID") || (searchField == "CategoryID"))
return searchField + " " + searchOper + " " + searchValue;
if ((searchField == "ProductName"))
return searchField + " " + searchOper + " \"" + searchValue + "\"";
return String.Empty;
}