[How To] Integrate jQGrid in asp.net MVC c# | Full Tutorial

jQGrid Integration In MVC asp.net

In this tutorial we are going to see, how jQGrid is integrated with asp.net MVC website project developed in c# language. This tutorial deals with following

1. Dynamic binding of data with paging

Dynamic jQGrid in asp.net

In the above image a sample jQGrid created is displayed. In the View Information section, you can see that there is only 10 items (data) is displayed out of 257 data. Which means only 10 data is fetching at a time from database, this will leads the grid loading is faster. Also we could define Number of Items visible per page, by doing that the grid is get refreshed and data will be displayed according to our specification. Paging controls and counts provide you information about number of pages, current page and control the paging (use arrow keys).

Just checkout how to implement new jQGrid bootstrap reponsive design, will be helpful with this tutorial

2. Inline grid searching

jQGrid Inline Searching

Inline searching means, search in the grid data by using different fields and different operations. User could add Multiple inline search boxes by clicking on the plus symbol and also define which field to be searched and operations to be performed. Default operations list in jQGrid are

1. equal
2. not equal
3. begins with
4. does not begin with
5. ends with
6. does not end with
7. contains
8. does not contain
9. is null
10. is not null
11. is in
12. is not in

Which operation need to be performed globally choose from the top combo box show in the pic as "all". all means AND operation and any means OR operation.

There are two parts we need to focus on while configuring the jQGrid

jQGrid | JavaScript | Client Side Configurations

First of all we need to include following JavaScript libraries in your project

• jQuery latest version
• jQuery UI latest version
• jQGrid latest version
• For the bootstrap design concept visit here. (some additional styles needed for this design).

Now we need to add a table element and pager(div) element for the jQGrid configuration in body section of the HTML file

    <div id="dvContainer">
        <table id="tblDemo"></table>
        <div id="dvPager"></div>
    </div>

Container dvContainer is added for full removal of jQGrid and attached DOM elements while re-creating it. In the JavaScript section/file put the below code

var lastPage=0;
var totalRecords=0;
var isPaging=false;
$(document).ready(function(){
 loadJQGrid();
});

function loadJQGrid(){
 lastPage=0;
 isPaging=false;
 if ($("#tblDemo").length > 0) { //Checking whether the jqGrid is already loaded. If loaded it will destroyed for re-creating.
        $("#tblDemo").jqGrid('GridDestroy');
        $("#tblDemo").remove();
        $("#dvPager").remove();
        $("#dvContainer").append('<table id="tblDemo"></table> <div id="dvPager"></div>');
    }
    else {
        $("#dvContainer").append('<table id="tblDemo"></table> <div id="dvPager"></div>');
    }
 
 $("#tblDemo").jqGrid({
        url: 'Server side url for fetching data', //example /Home/GetData
        datatype: "json",
        mtype: 'POST',
        prmNames: { _search: "_search", nd: "nd", rows: "rows", page: "page", sidx: "sidx", sord: "sord", searchField: "searchField", searchString: "searchString", searchOper: "searchOper", filters: "filters", total: "total" },
        postData: { extraParam1: "", extraParam2: 3, totalPageCount: lastPage, isPaging: isPaging, totalRecords: totalRecords },
        colNames: ['ID', 'First Name', 'Date', 'Place', 'Amount'],
        colModel: [
            { name: 'ID', index: 'ID', width: 55 }, //name and index must be the model property that you are passed as json result in ajax call in all the col model.
            { name: 'FirstName', index: 'FirstName', width: 55 },
            { name: 'Date', index: 'Date', width: 55, formatter: 'date', formatoptions: { newformat: 'd/m/Y' }, searchoptions: { dataInit: function (element) { $(element).datepicker({ id: 'orderDate_datePicker', format: ' dd/mm/yyyy' }) }, attr: { title: 'Select Date' } } },
            { name: 'Place', index: 'Place', width: 55, hidden: true },
            { name: 'Amount', index: 'Amount', width: 55, hidden: true }

        ],
        rowNum: 10,
        rowList: [10, 20, 30],
        pager: '#dvPager',
        sortname: 'ID', //field that need to be sorted
        viewrecords: true,
        sortorder: "desc", //defining sort order
        caption: gridcaption,
        ajaxRowOptions: {
            success: function () {
                $("#tblDemo").trigger("reloadGrid");
            }
        },
        onPaging: function (data) {
            if (data == "records") { //finding that if that's a paging navigation or not.
                isPaging = false;
            }
            else {
                isPaging = true;
            }
            totalRecords = $("#tblDemo").getGridParam('records');
            if (lastPage <= 0) {
                lastPage = $("#tblDemo").getGridParam('lastpage');
                $('#tblDemo').jqGrid('setGridParam', { postData: { extraParam1: "", extraParam2: 3, totalPageCount: lastPage, isPaging: isPaging, totalRecords: totalRecords } }).trigger('reloadGrid');
            }
            $('.ui-paging-info', "#dvPager").attr('title', $('.ui-paging-info', "#dvPager").text());
        },
        loadComplete: function (data) {
            lastPageComposed = 0;
            totalRecordsComposed = $("#tblDemo").getGridParam('records');
            if (lastPageComposed <= 0) {
                lastPageComposed = $("#tblDemo").getGridParam('lastpage');
                $('#tblDemo').jqGrid('setGridParam', {
                    postData: { extraParam1: "", extraParam2: 3, totalPageCount: lastPage, isPaging: isPaging, totalRecords: totalRecords }
                }).trigger('reloadGrid');
            }
            $('.ui-paging-info', "#dvPager").attr('title', $('.ui-paging-info', "#dvPager").text()); // to display records count details in mouse over at grid bottom right corner.
        },
        gridComplete: function () {
            //codes that need to be executed after grid complete event.
        }

    });
}

//For styling the grid to know more visit http://www.techdoubts.net/2015/11/working-jqgrid-responsive-css-boostrap.html
function ChangejQGridDesign(table, pager) {
 jQuery(table).jqGrid('navGrid', pager, {
  edit: false, add: false, del: false,
  search: true,
  searchicon: 'ace-icon fa fa-search orange',
  refresh: true,
  refreshicon: 'ace-icon fa fa-refresh green',
  view: false,
  viewicon: 'ace-icon fa fa-search-plus grey'
 },
 {},
 {},
 {},
  {
   //search form
   recreateForm: true,
   afterShowSearch: function (e) {
    var form = $(e[0]);
    form.closest('.ui-jqdialog').find('.ui-jqdialog-title').wrap('<div class="widget-header" />')
    style_search_form(form);
   },
   afterRedraw: function () {
    style_search_filters($(this));
   }
      ,
   multipleSearch: true,
   closeAfterSearch: true,
   onSearch: function () {
    isPaging = false;
    $('#tblDemo').jqGrid('setGridParam', {
     postData: { extraParam1: "", extraParam2: 3, totalPageCount: lastPage, isPaging: isPaging, totalRecords: totalRecords }

    });
    return true; // return true to close the search grid
   }
   /**
   multipleGroup:true,
   showQuery: true
   */
  }
 );
 //navButtons

 jQuery(table).jqGrid('inlineNav', pager,
  {  //navbar options
   edit: false,
   editicon: 'ace-icon fa fa-pencil blue',
   add: false,
   addicon: 'ace-icon fa fa-plus-circle purple',
   del: false,
   delicon: 'ace-icon fa fa-trash-o red',
   save: false,
   cancel: false
  });
 var replacement =
 {
  'ui-icon-seek-first': 'ace-icon fa fa-angle-double-left bigger-140',
  'ui-icon-seek-prev': 'ace-icon fa fa-angle-left bigger-140',
  'ui-icon-seek-next': 'ace-icon fa fa-angle-right bigger-140',
  'ui-icon-seek-end': 'ace-icon fa fa-angle-double-right bigger-140'
 };
 $('.ui-pg-table:not(.navtable) > tbody > tr > .ui-pg-button > .ui-icon').each(function () {
  var icon = $(this);
  var $class = $.trim(icon.attr('class').replace('ui-icon', ''));

  if ($class in replacement) icon.attr('class', 'ui-icon ' + replacement[$class]);
 });

 // enableTooltips
 $('.navtable .ui-pg-button').tooltip({ container: 'body' });
 $(table).find('.ui-pg-div').tooltip({ container: 'body' });

 var $grid = $(table),
 newWidth = $grid.closest(".ui-jqgrid").parent().width();
 $grid.jqGrid("setGridWidth", newWidth, true);

 $(window).on("resize", function () {
  var $grid = $(table),
   newWidth = $grid.closest(".ui-jqgrid").parent().width();
  $grid.jqGrid("setGridWidth", newWidth, true);
 });
}

Some global variables lastPage,totalRecords and isPaging is used for data fetching manipulations at the back end. Now we need to define the server side data fetching codes, some additional helpers need to use for extracting operations and searching details from jQGrid parameters

public class JQGridHelper
    {
        /// <summary>
        /// method to get all operations in jqGrid multi search as an object of class Operation
        /// </summary>
        /// <param name="stringList">multi search filter string </param>
        /// <returns>filter string coverted to object of class operation</returns>
        public static Operation GetAllOperations( string stringList)
        {
            try
            {
                return Newtonsoft.Json.JsonConvert.DeserializeObject<Operation>(stringList);
            }
            catch (Exception ex)
            {
                return null;
            }
        }
    }
     public class Operation
    {
        public string groupOp { get; set; }
        public List<Rules> rules { get; set; }
    }

    public class Rules
    {
        public string field { get; set; }
        public string op { get; set; }
        public string data { get; set; }
    }

Above helper will help you to convert in-line search operations into Operation class, so it will be more easier to manage.

public static string GetMySqlSearch(string op, string field, string value)
{
 string oper = op;
 switch (op)
 {
  case "eq": oper = field + "='" + value + "'"; break;
  case "ne": oper = field + "<>'" + value + "'"; break;
  case "bw": oper = field + " LIKE '" + value + "%'"; break;
  case "bn": oper = field + "NOT LIKE '" + value + "%'"; break;
  case "ew": oper = field + " LIKE '%" + value + "'"; break;
  case "en": oper = field + " NOT LIKE '%" + value + "'"; break;
  case "cn": oper = field + " LIKE '%" + value + "%'"; break;
  case "nc": oper = field + " NOT LIKE '%" + value + "%'"; break;
  case "nu": oper = field + " IS NULL "; break;
  case "nn": oper = field + " IS NOT NULL "; break;
  case "in": oper = field + " IS IN ('" + value + "') "; break;
  case "ni": oper = field + " IS NOT IN ('" + value + "') "; break;
 }
 return oper;
}

Above method will help you to convert jQGrid operations to SQL query manner. So you just need to pass the field and value to in it and you will get the appropriate search query, just need to join with the main query only.

Another case we need to focus on our database fields. We are giving some other heading or property values to jQGrid to display data on grid. But in the back end (in database) field names are different. So there is a database field finding helper is needed to find the corresponding fields returned by the jQGrid. Below method is for that purpose

private string GetDBFieldByJQGridField(string field)
{
 try
 {
  string dbField = field;
  switch (field)
  {
   case "ID": dbField = "DB_Field1"; break;
   case "FirstName": dbField = "DB_Field2"; break;
   case "Date": dbField = "DB_Field3"; break;
   case "Place": dbField = "DB_Field4"; break;
   case "Amount": dbField = "DB_Field5"; break;
  }
  return dbField;
 }
 catch (Exception ex)
 {
  return "";
 }
}

In the jQGrid defined above I have specified our database names to ID, FirstName, Date, Place, Amount etc. By using the above helper method we are converting it into the corresponding database fields.

I think you are in a confusion now, confused about when to use above helper. Don't worry below code will eliminate all your doubts.

public IEnumerable<dynamic> GetData(int page, int rows, string sidx, string sord, bool isSearch, string searchField, string searchString, string searchOper, string filter, int totalNumPages, bool isPaging, out int totalPages, out int totalRecords)
{
 try
 {
  string mainSqlQuery="SELECT DB_Field1 AS ID,DB_Field2 AS FirstName,DB_Field3 AS Date,DB_Field4 AS Place,DB_Field5 AS Amount FROM demo_table WHERE DB_Field1>0 "; //WHERE checking is just done to append other jQGrid searches exists
  string countSqlQuery="SELECT COUNT(DB_Field1) Count FROM demo_table WHERE DB_Field1>0 "; //this is to get the total count of records in the table according to search.
  if (isSearch) //if it is a search or not
  {
   Operation operations = JQGridHelper.GetAllOperations(filter); //Get all in-line search operations performed in jQGrid as Operation class.
   for (int j = 0; j < operations.rules.Count(); j++)
   {
    string searchStringAppend = GetMySqlSearch(operations.rules[j].op, GetDBFieldByJQGridField(operations.rules[j].field), operations.rules[j].data); //Get search string by required parameters
    if (j == 0 || operations.groupOp == "AND")
    {
     mainSqlQuery += " AND " + searchStringAppend + "";
     countSqlQuery += " AND " + searchStringAppend + "";
    }
    else
    {
     mainSqlQuery += " OR " + searchStringAppend + "";
     countSqlQuery += " OR " + searchStringAppend + "";
    }
   }
  }
  using(connection) //open your database connection here
  {
   int totalNumberOfPages = totalNumPages;
   int totalNumberOfRecords = 0;
   if (totalNumberOfPages <= 0 || !isPaging || isPaging)
   {
    int count = connection.GetCountByUsing(countSqlQuery);
    totalNumberOfRecords = count;
    if (count >= 0)
     totalNumberOfPages = Convert.ToInt32(Math.Ceiling(count / Convert.ToDecimal(rows))); //finding total number of pages according to the count and number of results displayed per page.
   }
   if (page > totalNumberOfPages)
    page = totalNumberOfPages;
   int start = rows * page - rows; //finding starting of data display (if there is any paging is happened value will be greater than zero)
   if (start < 0)
   {
    start = 0;
   }
   mainSqlQuery += " ORDER BY"+GetDBFieldByJQGridField(sidx)+" "+sord;
   mainSqlQuery += "  LIMIT " + start + "," + rows;
   totalPages = totalNumberOfPages;
   totalRecords = totalNumberOfRecords;
   return connection.Query<dynamic>(mainSqlQuery);
  }
 }
 catch(Exception ex)
 {
  totalPages = 0;
  totalRecords = 0;
 }
}

I think now you got an idea of how to use the helpers I had provided. Above one is the core modal class to fetch data from database table according to the jQGrid specifications. Finally we need to define the ajax method, that need to be invoked when calling from client side by jQGrid plugin.

public class HomeController : Controller
{
 [HttpPost]
 public JsonResult GetData(bool _search, string nd, int rows, int page, string sidx, string sord, string searchField, string searchString, string searchOper, string filters,string extraParam1,int extraParam2, int totalPageCount, bool isPaging)
 {
  int totalPages = 0;
  int totalRecords = 0;
  IEnumerable<dynamic> data=GetData(page, rows, sidx, sord, _search, searchField, searchString, searchOper, filters, userID, totalPageCount, isPaging, out totalPages, out totalRecords)
  return Json(new { page = page, total = totalPages, records = totalRecords, rows =data });
 }
}

This tutorial is based on asp.net MVC project. But any type of users can avail the benefit of this by just re-factoring it. Just try, jQGrid is better option than the old asp.net GridView. If you have any doubts comment below, I will try my level best to answer it.