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); } } }