jqgrid with asp.net webmethod and json working with sorting, paging, searching and LINQ

Posted by aimlessWonderer on Stack Overflow See other posts from Stack Overflow or by aimlessWonderer
Published on 2010-03-09T22:21:43Z Indexed on 2010/03/11 18:54 UTC
Read the original article Hit count: 16791

Filed under:
|
|
|
|

THIS WORKS!

Most topics covering jqgrid and asp.net seem to relate to just receiving JSON, or working in the MVC framework, or utilizing other handlers or web services... but not many dealt with actually passing parameters back to an actual webmethod in the codebehind. Furthermore, scarce are the examples that contain successful implementation the AJAX paging, sorting, or searching along with LINQ to SQL for asp.net jqGrid.

Below is a working example that may help others who need help to pass parameters to jqGrid in order to have correct paging, sorting, filtering.. it uses pieces from here and there...

==================================================

First, THE JAVASCRIPT

<script type="text/javascript">
$(document).ready(function() {

    var grid = $("#list");

    $("#list").jqGrid({
       // setup custom parameter names to pass to server
       prmNames: { 
            search: "isSearch", 
            nd: null, 
            rows: "numRows", 
            page: "page", 
            sort: "sortField", 
            order: "sortOrder"
       },
      // add by default to avoid webmethod parameter conflicts
      postData: { searchString: '', searchField: '', searchOper: '' },
      // setup ajax call to webmethod
      datatype: function(postdata) {    
      mtype: "GET",
      $.ajax({
       url: 'PageName.aspx/getGridData',  
       type: "POST",  
       contentType: "application/json; charset=utf-8",  
       data: JSON.stringify(postdata),
       dataType: "json",
       success: function(data, st) {
        if (st == "success") {
         var grid = jQuery("#list")[0];
         grid.addJSONData(JSON.parse(data.d));
        }
       },
       error: function() {
        alert("Error with AJAX callback");
       }
      }); 
     },
     // this is what jqGrid is looking for in json callback
     jsonReader: {  
      root: "rows",
      page: "page",
      total: "totalpages",
      records: "totalrecords",
      cell: "cell",
      id: "id", //index of the column with the PK in it 
      userdata: "userdata",
      repeatitems: true
     },
     colNames: ['Id', 'First Name', 'Last Name'],   
     colModel: [
      { name: 'id', index: 'id', width: 55, search: false },
      { name: 'fname', index: 'fname', width: 200, searchoptions: { sopt: ['eq', 'ne', 'cn']} },
      { name: 'lname', index: 'lname', width: 200, searchoptions: { sopt: ['eq', 'ne', 'cn']} }
     ],  
     rowNum: 10,  
     rowList: [10, 20, 30],
     pager: jQuery("#pager"),
     sortname: "fname",   
     sortorder: "asc",
     viewrecords: true,
     caption: "Grid Title Here"
    }).jqGrid('navGrid', '#pager', { edit: false, add: false, del: false },
    {}, // default settings for edit
    {}, // add
    {}, // delete
    { closeOnEscape: true, closeAfterSearch: true}, //search
    {}
)
});
</script>

==================================================

Second, THE C# WEBMETHOD

[WebMethod]
public static string getGridData(int? numRows, int? page, string sortField, string sortOrder, bool isSearch, string searchField, string searchString, string searchOper) {
 string result = null;

 MyDataContext db = null;
 try {
  //--- retrieve the data
  db = new MyDataContext("my connection string path");  
  var query = from u in db.TBL_USERs select u;

  //--- determine if this is a search filter
  if (isSearch) {
      searchOper = getOperator(searchOper); // need to associate correct operator to value sent from jqGrid
  string whereClause = String.Format("{0} {1} {2}", searchField, searchOper, "@" + searchField);

      //--- associate value to field parameter
      Dictionary<string, object> param = new Dictionary<string, object>();
      param.Add("@" + searchField, searchString);

      query = query.Where(whereClause, new object[1] { param });
  }

  //--- setup calculations
  int pageIndex = page ?? 1; //--- current page
  int pageSize = numRows ?? 10; //--- number of rows to show per page
  int totalRecords = query.Count(); //--- number of total items from query
  int totalPages = (int)Math.Ceiling((decimal)totalRecords / (decimal)pageSize); //--- number of pages

  //--- filter dataset for paging and sorting
  IQueryable<TBL_USER> orderedRecords = query.OrderBy(sortfield);
  IEnumerable<TBL_USER> sortedRecords = orderedRecords.ToList();
  if (sortorder == "desc") sortedRecords= sortedRecords.Reverse();
  sortedRecords= sortedRecords
     .Skip((pageIndex - 1) * pageSize) //--- page the data
     .Take(pageSize);

  //--- format json
  var jsonData = new {
   totalpages = totalPages, //--- number of pages
   page = pageIndex, //--- current page
   totalrecords = totalRecords, //--- total items
   rows = (
    from row in sortedRecords
    select new {
     i = row.USER_ID,
     cell = new string[] {
      row.USER_ID.ToString(), row.FNAME.ToString(), row.LNAME 
     }
    }
   ).ToArray()
  };

  result = Newtonsoft.Json.JsonConvert.SerializeObject(jsonData);

 } catch (Exception ex) {
  Debug.WriteLine(ex);
 } finally {
  if (db != null) db.Dispose();
 }

 return result;
}

==================================================

Third, NECESSITIES

  1. In order to have dynamic OrderBy clauses in the LINQ, I had to pull in a class to my AppCode folder called 'Dynamic.cs'. You can retrieve the file from downloading here. You will find the file in the "DynamicQuery" folder. That file will give you the ability to utilized dynamic ORDERBY clause since we don't know what column we're filtering by except on the initial load.

  2. To serialize the JSON back from the C-sharp to the JS, I incorporated the James Newton-King JSON.net DLL found here : http://json.codeplex.com/releases/view/37810. After downloading, there is a "Newtonsoft.Json.Compact.dll" which you can add in your Bin folder as a reference

  3. Here's my USING's block using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Web.UI.WebControls; using System.Web.Services; using System.Linq.Dynamic;

  4. For the Javascript references, I'm using the following scripts in respective order in case that helps some folks: 1) jquery-1.3.2.min.js ... 2) jquery-ui-1.7.2.custom.min.js ... 3) json.min.js ... 4) i18n/grid.locale-en.js ... 5) jquery.jqGrid.min.js

  5. For the CSS, I'm using jqGrid's necessities as well as the jQuery UI Theme: 1) jquery_theme/jquery-ui-1.7.2.custom.css ... 2) ui.jqgrid.css

The key to getting the parameters from the JS to the WebMethod without having to parse an unserialized string on the backend or having to setup some JS logic to switch methods for different numbers of parameters was this block

postData: { searchString: '', searchField: '', searchOper: '' },

Those parameters will still be set correctly when you actually do a search and then reset to empty when you "reset" or want the grid to not do any filtering

Hope this helps some others!!!! Please reply if you find major issues or ways of refactoring or doing better that I haven't considered.

© Stack Overflow or respective owner

Related posts about jqgrid

Related posts about ASP.NET