I’m back to experimenting with jggrid again, this time trying to get server side multi search and sorting.
This time I am using asp.net and linq and traditional asmx web service. Why not WCF? I’m not sure, I just started down this road this time. WCF is the recommended way I gather, or maybe asp.net MVC.
I also needed a way to have a concise search strategy to allow searching across several column sand with different search operations (equal, not equal, contains)
I found this solution on code project that uses asp.net MVC by Ilya Builuk. I included his helpers in my project (see project explorer below) and got the grid working –yay!!!
So here’s the code – sarting with the html page, then the entities and classes representing the filters, then finally the asmx web service using the linq extension methods I got from Ilya Builuk
1st the html page
<!-- all the header html stuff here -->
<script type="text/javascript">
$(function() {
$.extend($.jgrid.defaults,
{
jsonReader: {
root: "d.rows", page: "d.page", total: "d.total", records: "d.records", repeatitems: false, cell: "", id: "0"
},
datatype: 'json',
ajaxGridOptions: { contentType: "application/json;charset=utf-8" },
mType: 'POST',
serializeGridData: function(postData) { return JSON.stringify(postData); }
});
$('#grid').jqGrid({
url: "/GridService.asmx/SearchGrid",
datatype: 'local',
colNames: ['ProductID', 'Name', 'ProductModel', 'Culture','Description'],
colModel: [
{ name: 'ProductID', width: 100, index: 'ProductId',
searchoptions: { sopt: ['eq', 'ne'] }
},
{ name: 'Name', index: 'Name',
searchoptions: { sopt: ['eq', 'ne', 'cn'] }
},
{ name: 'ProductModel', index: 'ProductModel',
searchoptions: { sopt: ['eq', 'ne', 'cn'] }
},
{ name: 'Culture', index: 'Culture',
searchoptions: { sopt: ['eq', 'ne', 'cn'] }
},
{ name: 'Description', index: 'Description',
searchoptions: { sopt: ['eq', 'ne', 'cn'] }
}
],
sortname: 'Name',
rowNum: 10,
rowList: [10, 20, 50],
sortorder: "asc",
pager: 'pager',
caption: 'Product Descriptions',
viewrecords: true,
mtype: 'POST'
});
$('#grid').jqGrid('navGrid', '#pager', { add: false, edit: false, view: false, del: false,
beforeRefresh:function(){
$('#grid').jqGrid('setGridParam', { datatype: 'local' }).trigger('reloadGrid');
}
// refresh:false
},
{}, {}, {},
{ multipleSearch: true,
closeAfterSearch: true,
closeOnEscape:true,
onSearch: function() {
//console.log('search clicked');
$('#grid').jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid');
},
onReset: function() {
$('#grid').jqGrid('setGridParam', { datatype: 'local' }).trigger('reloadGrid');
}
});
$('#grid').jqGrid('gridResize');
$('#loadGrid').button();
$('#loadGrid').click(function() {
$('#grid').jqGrid('setGridParam', { url: "/GridService.asmx/GetGrid", datatype: 'json' }).trigger('reloadGrid');
});
//$('#grid').jqGrid('setGridParam', {datatype: 'json' }).trigger('reloadGrid');
});
</script>
</head>
<body>
<input id= "loadGrid" type="button" value="Load Grid" />
<table id="grid" />
<div id="pager" />
</body>
</html>
The entities and filter objects
namespace JqGridFilter.Data
{
public class Product
{
public int ProductID { get; set; }
public string Name { get; set; }
public string ProductModel { get; set; }
public string Culture { get; set; }
public string Description { get; set; }
}
public class JgGrid
{
public List<vProductAndDescription> rows;
public int page { get; set; }
public int total { get; set; }
public int records { get; set; }
public JgGrid()
{
}
public JgGrid(int page, IQueryable<vProductAndDescription> products, int rows)
{
this.page = page;
this.records = products.Count();
this.total = (int)Math.Ceiling((double)this.records / rows);
this.rows = products.Skip(page * rows - rows).Take(rows).ToList();
}
}
[Serializable()]
public class rule
{
public string field { get; set; }
public string op { get; set; }
public string data { get; set; }
}
[Serializable()]
public class filters
{
public string groupOp { get; set; }
public List<rule> rules;
}
}
Then here comes the web service
namespace JqGridFilter
{
/// <summary>
/// Summary description for Service1
/// </summary>
[WebService(Namespace = "http://wwww.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class Service1 : System.Web.Services.WebService
{
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public JgGrid SearchGrid(int rows, int page, string sidx, string sord,string filters)
{
AdvWorksDataContext dc = new AdvWorksDataContext();
JavaScriptSerializer serializer = new JavaScriptSerializer();
filters f = serializer.Deserialize<filters>(filters);
var p = dc.vProductAndDescriptions.AsQueryable();
if (f.groupOp == "AND")
foreach (var rule in f.rules)
p = p.Where<vProductAndDescription>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op)
);
else
{
//Or
var temp = (new List<vProductAndDescription>()).AsQueryable();
foreach (var rule in f.rules)
{
var t = p.Where<vProductAndDescription>(
rule.field, rule.data,
(WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op)
);
temp = temp.Concat<vProductAndDescription>(t);
}
p = temp;
}
p = p.OrderBy<vProductAndDescription>(sidx, sord);
return new JgGrid(page, p, rows);
}
}
}
kodesnippetsnstuff 
Trần Trọng Hiếu
June 15, 2012 at 9:16 am
Sory, but I can’t understand the WhereOperation. Thanks for any explain for it
curiouskoder
November 27, 2012 at 2:43 am
I had seen one other dynamic linq approach that did not address data types (like string vs date) but that was more flexible in search parameters than my solution. Yours seems to be the most comprehensive I have seen so far so I will give it a try.. thanks