Sign In| Join
Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Member
633 Points
171 Posts
Nov 20, 2010 01:50 PM|LINK
Ok i understand your problem now :) .... loads of stuff to do make jqgrid to work with webmethod
firstly download JSON plugin from this link
http://code.google.com/p/jquery-json/downloads/detail?name=jquery.json-2.2.min.js
then your jquery code should look like this
// change these references as per your own solution <script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script> <script src="Scripts/jquery.json-2.2.min.js" type="text/javascript"></script> <script src="Scripts/jquery-ui-1.8.5.custom.min.js" type="text/javascript"></script> <script src="src/i18n/grid.locale-en.js" type="text/javascript"></script> <script src="src/jquery.jqGrid.min.js" type="text/javascript"></script> <script> $(document.ready(){ // Update default settings //required to make it work for JSON data handling // this just overrides ajax settings and datatype $.extend($.jgrid.defaults, {datatype: 'json'}, {ajaxGridOptions: { contentType: "application/json" , success: function(data, textStatus) { if (textStatus == "success") { var thegrid = $("#Grid1")[0]; thegrid.addJSONData(data.d); thegrid.grid.hDiv.loading = false; switch (thegrid.p.loadui) { case "disable": break; case "enable": $("#load_" + thegrid.p.id).hide(); break; case "block": $("#lui_" + thegrid.p.id).hide(); $("#load_" + thegrid.p.id).hide(); break; } } } } }); jQuery("#Grid1").jqGrid({ url: 'WebForm3.aspx/GetDataTable', dataType: "json", mtype: 'POST', colNames: ['BookID', 'BookName'], colModel: [{ name: 'BookID', index: 'BookID', width: 200 }, { name: 'BookName', index: 'BookName', width: 300}], pager: '#pager', sortname: 'id', viewrecoreds: true, imgpath: 'Themes/images', serializeGridData: function(data) { return $.toJSON(data); /// done to override default serialization } }).navGrid("#pager", { edit: true, add: true, del: true, refresh: true, search: true }); }); </script>
Now update the webmethod definition to this
[WebMethod] public static s_GridResult GetDataTable(string _search ,string nd ,int rows ,int page ,string sidx , string sord ) { int startindex = (page - 1) * pagesize; int endindex = page * pagesize; string sql = @" WITH PAGED_CUSTOMERS AS { SELECT BookID,BookName ROW_NUMBER() OVER(ORDER BY " + sidx + @" " + sord + @") AS ROWNUMBER FROM BOOKS}"; DataTable dt = new DataTable(); SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=library;Integrated Security=True"].ConnectionString); SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); var rows = adapter.Fill(dt); s_GridResult result = new s_GridResult(); List<s_RowData> rowsadded = new List<s_RowData>(); int idx = 1; foreach (DataRow row in dt.Rows) { s_RowData newrow = new s_RowData(); newrow.id = idx++; newrow.cell = new string[2]; //total number of columns newrow.cell[0] = row[0].ToString(); newrow.cell[1] = row[1].ToString(); rowsadded.Add(newrow); } result.rows = rowsadded.ToArray(); result.page = page; result.total = dt.Rows.Count; result.record = rowsadded.Count; return result; }
Hope this works for u now :) let me know if you get stuck up on something
Anirudh.Gupt...
Member
633 Points
171 Posts
Re: connect jqgrid to database
Nov 20, 2010 01:50 PM|LINK
Ok i understand your problem now :) .... loads of stuff to do make jqgrid to work with webmethod
firstly download JSON plugin from this link
http://code.google.com/p/jquery-json/downloads/detail?name=jquery.json-2.2.min.js
then your jquery code should look like this
// change these references as per your own solution <script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script> <script src="Scripts/jquery.json-2.2.min.js" type="text/javascript"></script> <script src="Scripts/jquery-ui-1.8.5.custom.min.js" type="text/javascript"></script> <script src="src/i18n/grid.locale-en.js" type="text/javascript"></script> <script src="src/jquery.jqGrid.min.js" type="text/javascript"></script> <script> $(document.ready(){ // Update default settings //required to make it work for JSON data handling // this just overrides ajax settings and datatype $.extend($.jgrid.defaults, {datatype: 'json'}, {ajaxGridOptions: { contentType: "application/json" , success: function(data, textStatus) { if (textStatus == "success") { var thegrid = $("#Grid1")[0]; thegrid.addJSONData(data.d); thegrid.grid.hDiv.loading = false; switch (thegrid.p.loadui) { case "disable": break; case "enable": $("#load_" + thegrid.p.id).hide(); break; case "block": $("#lui_" + thegrid.p.id).hide(); $("#load_" + thegrid.p.id).hide(); break; } } } } }); jQuery("#Grid1").jqGrid({ url: 'WebForm3.aspx/GetDataTable', dataType: "json", mtype: 'POST', colNames: ['BookID', 'BookName'], colModel: [{ name: 'BookID', index: 'BookID', width: 200 }, { name: 'BookName', index: 'BookName', width: 300}], pager: '#pager', sortname: 'id', viewrecoreds: true, imgpath: 'Themes/images', serializeGridData: function(data) { return $.toJSON(data); /// done to override default serialization } }).navGrid("#pager", { edit: true, add: true, del: true, refresh: true, search: true }); }); </script>Now update the webmethod definition to this
[WebMethod] public static s_GridResult GetDataTable(string _search ,string nd ,int rows ,int page ,string sidx , string sord ) { int startindex = (page - 1) * pagesize; int endindex = page * pagesize; string sql = @" WITH PAGED_CUSTOMERS AS { SELECT BookID,BookName ROW_NUMBER() OVER(ORDER BY " + sidx + @" " + sord + @") AS ROWNUMBER FROM BOOKS}"; DataTable dt = new DataTable(); SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=library;Integrated Security=True"].ConnectionString); SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); var rows = adapter.Fill(dt); s_GridResult result = new s_GridResult(); List<s_RowData> rowsadded = new List<s_RowData>(); int idx = 1; foreach (DataRow row in dt.Rows) { s_RowData newrow = new s_RowData(); newrow.id = idx++; newrow.cell = new string[2]; //total number of columns newrow.cell[0] = row[0].ToString(); newrow.cell[1] = row[1].ToString(); rowsadded.Add(newrow); } result.rows = rowsadded.ToArray(); result.page = page; result.total = dt.Rows.Count; result.record = rowsadded.Count; return result; }Hope this works for u now :) let me know if you get stuck up on something