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).

24 comments:

Shalom Keynan said...

Thanks, this is a great article and I found it very helpful.
I am trying to apply a dynamic search to the grid, can you please share the code and approach for the repository? I am using VB.Net and couldn’t find good examples to apply dynamic search.

_repository.GetProducts(filterExpression, sortExpression, sortDirection, pageIndex - 1, pageSize)

S.K

Tomasz Pęczek said...

You can download full application (with repository classes) from here: http://tpeczek.codeplex.com/

Ryan said...

Tomasz,

Excellent job. Thank you for the work you've done here.

For some reason I am having issues getting the search to work properly. If I search by ID, everything is fine, but if I search by another field an exception is thrown in the LinqExtensions class. The exception message says: "No property or field 'Chai' exists in type 'Product'". Any Idea what the problem might be?

Thanks in Advance,
Ryan

Tomasz Pęczek said...

Hi

There is a bug in ProductsGridData action, I have commit fix into repository, you can download it here: https://tpeczek.svn.codeplex.com/svn/trunk/jqGridExample/jqGridExample/Controllers/

Tomasz Pęczek said...

I have updated the post as well.

Anonymous said...

hi..

I am really new for jqgrid...and i was trying to do search on 'cn' rather than 'eq'search option . If u can help me in this case.
Thanx in advance.

Tomasz Pęczek said...

Hi

In my opinion, the best way to implement 'cn' (contains) operator is to translate it to 'like %value%'. The actual realization depends striongly on your database access layer.

Usha said...

Hi again.

Thanx for your suggestion on cn option. It really helped me...& thats why I am asking u my next problem..
Now I am doing editing in edit form..but its getting difficult to fill dropdown options based on value of some other dropdown on the form
Any suggestion may help me.
Waiting for your reply....Thanx

Tomasz Pęczek said...

I'll try to make some sample for you, but problem is I'm away at the moment so it might take me a while.

Usha said...

Hii..
Thanx for your rapid reply...
I'll wait for sample since I really need it..

Tomasz Pęczek said...

I'm sorry but few things came out and I'm pretty busy... Thats why I will give you only some diretions at the moment. Take a look here:
http://www.trirand.com/jqgridwiki/doku.php?id=wiki:form_editing. What you should use is onInitializeForm event. It will give you the id of edit form. Using that id and id of your inputs (the link I gave describes how input identifiers and names are constructed) you can attach javascript to your input onChange event, which will populate the second input from remote action. If you need a deatailed sample, I will provide one as soon as my duties allow me to.

Usha said...

Thanx alot for your directions. I'll surely try them and let you know about the output.
Have a Nice Day.......

Usha said...

I tried ..but not able to populate my dropdown..pls provide me sample
Thanx

Tomasz Pęczek said...

Ok, please provide me some deatils about your scenario (fileds list, dependecies, few sample positions for dropdowns etc.) and I will create as exact sample as possible.

Usha said...

Hii...This is my scenario.
I am having 1 dropdown containing ranks like developer, promoter etc. On the basis of rank selection I need to fill my second dropdown containing broker name, id pair belonging to that particular rank.

$('#jqgProducts').jqGrid({
colNames: ['Rank', 'Introducer'],
colModel: [
{ name: 'rankName', width: 100, index: 'rankId', align: 'left', search: true, searchoptions: { sopt: ['eq', 'ne'], dataUrl: '/broker/RankSelect' }, stype: 'select', editable: true, edittype: 'select', editoptions: { dataUrl: '/broker/RankSelect'
dataEvents: [{ type: 'change', fn: function(e) {
var sel = $('input#brokerName');
$(sel).empty(); $(sel).parent().load('/broker/getBrokers?rnkid=' + e.target.value);
} }] }, editrules: { required: true }, formoptions: { rowpos: 9, colpos: 3 }},
{ name: 'brokerName', width: 140, index: 'brokerId',align: 'left', editable: true, edittype: 'text', search:false , editrules: { required: true }, formoptions: { rowpos: 10, colpos: 1} },
2} },
],
pager: jQuery('#jqgpProducts'),
sortname: 'brokerId',
rowNum: 10,
rowList: [10, 20, 50],
sortorder: "asc",
// postData: { bid: brokerId },
/* width: 600,
height: 250,*/
datatype: 'json',
caption: 'Broker Details',
viewrecords: true,
mtype: 'POST',
jsonReader: {
root: "rows",
page: "page",
total: "total",
records: "records",
repeatitems: false,
userdata: "userdata"
},
onSelectRow: function(id) {
var row = $('#jqgProducts').getRowData(id);
},
url: gridDataUrl + '?brid=' + brid
}).navGrid('#jqgpProducts', { add: false, del: false, edit: true, search: true },
//edit option
{width: 800, url: '/broker/Edit/', closeOnEscape: true, closeAfterEdit: true, top: 10, left: 200, editCaption: 'Edit Broker Records', bSubmit: 'Update', recreateForm: true },
//add options
{width: 'auto', url: '/broker/Create/', closeOnEscape: true, closeAfterAdd: true },
//delete options
{url: '/Home/DeleteProduct/' },

{ closeOnEscape: true, multipleSearch: true, closeAfterSearch: true }, // search options
{} /* view parameters*/
);

and I need this dropdown cascading on edit form.
In this I have used dataevent which fill the broker dropdown but only once.

Tomasz Pęczek said...

Ok, I have made a shirt sample here: https://tpeczek.svn.codeplex.com/svn/trunk/BrokerExample. It should give you waht you want, but it needs some tweeking. Hope that helps.

Usha said...

Hii
Thanx a lot for all your support and help..Being so busy, even then u helped me....really nice
Have a nice day.

Anonymous said...

I would appreciate more visual materials, to make your blog more attractive, but your writing style really compensates it. But there is always place for improvement

Anonymous said...

You have to express more your opinion to attract more readers, because just a video or plain text without any personal approach is not that valuable. But it is just form my point of view

Anonymous said...

Thanks for the great post!!! You have a lot a patience for some that want you do their work. Thanks for the very helpful direction. PS Great name for your blog.

the hope said...

great post, thanks a lot Tomasz Pęczek

Anonymous said...

I can look for the reference to a site on which there is a lot of information on this question.

Amrendra Singh said...

Nice example..by seeing this I am also doing the filtertoolbar in my demo but I got stuck at a point that how to perform filtertoolbar on clientside with loadonce:true

Tomasz Pęczek said...

I would have to look at that source code. You can contact me via email if you want.