jqgrid with asp.net webmethod and json working with sorting, paging, searching and LINQ
- by aimlessWonderer
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
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.
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
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;
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
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.
