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 