ofcourse it wont . in grid.base.js check out addJSONData and see how result should be sent from server
To give you some idea modified your code . go through this
public struct s_GridResult
{
public int page; // Current page of data that is being viewed/requested
public int total; // Total number of pages avaialble to view in the entire list
public int records; // Number of records available in the rows[] array
public s_RowData[] rows; // Rows of data
}
public struct s_RowData
{
public int id; // ItemID value for the row
public string[] cell; // Array of strings that hold the field values for the given row
}
///
public s_GridResult GetDataTable(string sidx, string sord, int page, int pageSize)
{
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 sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=library;Integrated Security=True"].ConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlcon);
var rows = adapter.Fill(dt);
s_GridResult result = new s_GridResult();
List<s_RowData> rowsadded = new List<s_RowData>();
//
// here for each row in dt create new row object of s_RowData and add the row in a list like
// s_RowData newrow = new s_RowData();
// newrow.id = item.ID;
// newrow.cell = new string[fields.Length]; //total number of columns
// newrow.cell[0] = "Content for first column"; ..etc
// rowsadded.Add(newrow);
//
result.page = page;
result.total = dt.rows.count ...
result.record = rowsadded.count
return result;
}
Did u try and modified what i wrote , because that does cover what you asking for .. what you can do is , show me the updated code and i can try help to complete it .
public struct s_GridResult
{
public int page;
public int total;
public int record;
public s_RowData[] rows;
}
public struct s_RowData
{
public int id;
public string[] cell;
}
public s_GridResult GetDataTable(string sidx, string sord, int page, int pagesize)
{
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>();
//I dont know what should I write here
result.page = page;
result.total = dt.Rows.Count;
result.record = rowsadded.Count;
return result;
}
bahare
Member
31 Points
208 Posts
connect jqgrid to database
Nov 18, 2010 06:38 AM|LINK
hi
I have a jqgrid and I want to connect to database in c# (Webforms) and I write below code,but it doesn t show any data in my jqgrid
would you please check my code and tell me what should I do?
<!------Gridpage.aspx-----------!>
<head runat="server"> <title></title> <link rel="Stylesheet" type="text/css" media="screen" href="Themes/jquery-ui-1.8.2.custom.css" /> <link rel="Stylesheet" type="text/css" media="screen" href="Themes/ui.jqgrid.css" /> <script type="text/javascript" src="js/jquery-1.4.2.min.js"></script> <script type="text/javascript" src="js/grid.locale-en.js"></script> <script type="text/javascript" src="js/jquery.jqGrid.min.js"></script> <script type="text/javascript" > jQuery(document).ready(function () { jQuery("#Grid1").jqGrid({ url: 'data.aspx', datatype: 'json', mtype: 'GET', colNames: ['BookID', 'BookName'], colModel: [{ name: 'BookID', index: 'BookID', width: 200 }, { name: 'BookName', index: 'BookName', width: 300}], pager: jQuery('#pager'), sortname: 'id', viewrecoreds: true, imgpath: 'Themes/images' }).navGrid(pager,{edit:true,add:true,del:true,refresh:true,search:true}); }); </script> </head> <body> <form id="form1" runat="server"> <table id="Grid1" class="scroll" align="center" width="100%"></table> <div id="pager" class="scroll" style="text-align:center;"> </div> </form> </body>public DataTable GetDataTable(string sidx, string sord, int page, int pageSize) { 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 sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=library;Integrated Security=True"].ConnectionString); SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlcon); var rows = adapter.Fill(dt); Console.Write("rows"); return dt; }Anirudh.Gupt...
Member
633 Points
171 Posts
Re: connect jqgrid to database
Nov 18, 2010 07:08 AM|LINK
ofcourse it wont . in grid.base.js check out addJSONData and see how result should be sent from server
To give you some idea modified your code . go through this
public struct s_GridResult { public int page; // Current page of data that is being viewed/requested public int total; // Total number of pages avaialble to view in the entire list public int records; // Number of records available in the rows[] array public s_RowData[] rows; // Rows of data } public struct s_RowData { public int id; // ItemID value for the row public string[] cell; // Array of strings that hold the field values for the given row } /// public s_GridResult GetDataTable(string sidx, string sord, int page, int pageSize) { 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 sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=library;Integrated Security=True"].ConnectionString); SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlcon); var rows = adapter.Fill(dt); s_GridResult result = new s_GridResult(); List<s_RowData> rowsadded = new List<s_RowData>(); // // here for each row in dt create new row object of s_RowData and add the row in a list like // s_RowData newrow = new s_RowData(); // newrow.id = item.ID; // newrow.cell = new string[fields.Length]; //total number of columns // newrow.cell[0] = "Content for first column"; ..etc // rowsadded.Add(newrow); // result.page = page; result.total = dt.rows.count ... result.record = rowsadded.count return result; }bahare
Member
31 Points
208 Posts
Re: connect jqgrid to database
Nov 20, 2010 07:09 AM|LINK
thanks for your consideration
but it still doesnt work.I want to create jqgrid but my problem is that I dont know how to connect my jqgrid to my database.
in my database I have a table(Books) which has 9 fields (BookId,BookName,MenuID,RegDate,Author,Publisher,Price,Description,Status)
I want to show all this fields in jqgrid.what should I do?
in all codes that I found the page that connect to database is written in php or mvc but I want to write in c# (webform).
would you please help me more
Anirudh.Gupt...
Member
633 Points
171 Posts
Re: connect jqgrid to database
Nov 20, 2010 07:20 AM|LINK
Did u try and modified what i wrote , because that does cover what you asking for .. what you can do is , show me the updated code and i can try help to complete it .
Anirudh.Gupt...
Member
633 Points
171 Posts
Re: connect jqgrid to database
Nov 20, 2010 07:25 AM|LINK
also i noticed your jquery code is wrong a bit , pager should be id and not jquery element also the url . try this
jQuery("#Grid1").jqGrid({ url: 'data.aspx/GetDataTable', datatype: 'json', mtype: 'GET', 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' }).navGrid(pager,{edit:true,add:true,del:true,refresh:true,search:true});bahare
Member
31 Points
208 Posts
Re: connect jqgrid to database
Nov 20, 2010 07:37 AM|LINK
public struct s_GridResult { public int page; public int total; public int record; public s_RowData[] rows; } public struct s_RowData { public int id; public string[] cell; } public s_GridResult GetDataTable(string sidx, string sord, int page, int pagesize) { 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>(); //I dont know what should I write here result.page = page; result.total = dt.Rows.Count; result.record = rowsadded.Count; return result; }Anirudh.Gupt...
Member
633 Points
171 Posts
Re: connect jqgrid to database
Nov 20, 2010 08:34 AM|LINK
update getdatatable to this . This doesnt take care of paging right now , but we can go to that on next level.
[WebMethod] [ScriptMethod(ResponseFormat = ResponseFormat.Json)] public s_GridResult GetDataTable(string sidx, string sord, int page, int pagesize) { 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; }bahare
Member
31 Points
208 Posts
Re: connect jqgrid to database
Nov 20, 2010 12:03 PM|LINK
I write the exact code you told me but it still doesnt work. grid is created but my data does'nt load in it and it`s empty.
would you please tell me what to do?
Anirudh.Gupt...
Member
633 Points
171 Posts
Re: connect jqgrid to database
Nov 20, 2010 12:29 PM|LINK
can u open in firefox and see what is the response result ?
use https://addons.mozilla.org/en-US/firefox/addon/1843/ for checking out what gets posted and what is the response
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