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.
<script type="text/javascript">
  $(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>
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:
/// <summary>
///
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);
}
SelectPartial.ascx has very simple content:
<select>
  <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>
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:
$('#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>
///
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;
}
Let's take a look at our work result (source code is available here).