这次真正的用到数据库中的数据了,其实这也是我们用jqGrid的根本,谁本地数据也用这么庞大的插件。
因为jqGrid数据都是异步获取的,其实我们应该首先明白,数据是如何去获取的,jqGrid实例图本身就向我们展示的很清楚了,
我们要传递给后台的数据就是1:pagesize-页大小(即页面显示多少条记录,通常的分页大小),2:curpage-当前页码(要让后台知道你要取哪一页的数据),3:排序字段,4:排序方式。 当然我们从数据库中查询后回馈给jqGrid的有 recordcount (总记录数)和总的页数。
先看看效果图:当前是第5页,我点下一页后的画面
HTML页面代码:
View Code
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>jqGrid的JSON数据显示</title> <link href="css/redmond/jquery-ui-1.8.14.custom.css" rel="Stylesheet" /> <link href="css/ui.jqgrid.css" rel="Stylesheet" /> <script type="text/javascript" src="js/jquery-1.6.2.min.js"></script> <script type="text/javascript" src="js/jquery-ui-1.8.14.custom.min.js"></script> <script type="text/javascript" src="js/grid.locale-cn.js"></script> <script type="text/javascript" src="js/jquery.jqGrid.min.js"></script> <script type="text/javascript"> $(document).ready(function(){ $("#list1").jqGrid({ url:'getJsonData.aspx', datatype:"json", mtype:'GET', colNames:['编号','地区编号','地区名称','所属城市编号'], colModel:[ {name:'ID',index:'ID',width:55}, {name:'AreaID',index:'AreaID',width:100}, {name:'Area',index:'Area',width:180, sortable:false}, {name:'Father',index:'Father',width:180} ], //autowidth:true, jsonReader:{ page:"page", total:"total", repeatitems:false }, pager:jQuery('#pager1'), rowNum:10, rowList:[10,20,30], sortname:'ID', sortorder:'desc', viewrecords:true, caption:'jqGrid使用JSON数据测试' }).navGrid("#pager1",{edit:false,add:false,del:false,search:false}); }); </script> </head> <body> <table id="list1"></table> <div id="pager1"></div> </body> </html>
用于取得json数据的getJsonData.aspx后台代码:
View Code
//因为是get方式传值,我也把那些传值做成了该页面的属性 protected void Page_Load(object sender, EventArgs e) { /** * jqGrid默认期望返回的json对象格式要求如下: * {"page":"1","total":"2","records":"13", * "rows":[ * {id:"1",cell:["1","jancyxue","男","jancyxue@163.com","410958040","江西余干"]}, * {id:"2",cell:["2","linda","女","linda@163.com","111111111","湖南"]}, * {id:"3",cell:["3","jason","男","jason@sina.com","222222222","湖北"]}, * {id:"4",cell:["4","lucy","女","lucy@sina.com","33333333","北京"]} * ] * } * 当然,在js中,可以通过jqGrid的jsonReader属性来修改默认格式 * 我们可以在jsonReader中定义repeatitems : false。这样,可以使用下面的数据格式(为了防止出错,我们在写colModel的时候如果实在不会就对应着下面的每个字段写,就不容易出错了: * "rows":[ * {id:"1",engName:"jancyxue",gender:" 男",email:"jancyxue@163.com",QQ:"410958040",address:"江西余干"]}, * {id:"2",engName:"linda",gender:" 女",email:"linda@163.com",QQ:"111111111","address:湖南"]}, * {id:"3",engName:"jason",gender:" 男",email:"jason@sina.com",QQ:"222222222",address:"湖北"]}, * {id:"4",engName:"lucy",gender:" 女",email:"lucy@sina.com",QQ:"33333333",address:"北京"]} * ] * 就像这样colModel:[{name:'id',index:'id',width:55},{name:'engName',index:'engName',width:100},{name:'gender',index:'gender',width:180, sortable:false},{name:'email',index:'email',width:180}, {name:'QQ',index:'QQ',width:280},{name:'address',index:'address',width:280}] * */ //数据的获取,传进去的字段看名字应该知道是什么意思了,其实就是根据这些字段从数据库中获取所需的页数据,这样理解,就是我页大小是10条,从数据库中就取10条数据 IList<areaInfo> areas = areaInfo.GetAreaInfoByPageNoCondition(PageIndex, PageSize, OrderField, OrderType); string result = JsonConvert.SerializeObject(areas); //这里用了Newtonsoft作了Json转换,自己手写容易出错,最后再完整的把jqGrid所需的JSON数据格式构建出来 Response.Write("{\"total\": \"" + PageCount.ToString() + "\", \"page\": \"" + PageIndex.ToString() + "\", \"records\": \"" + RecordCount.ToString() + "\", \"rows\" : " + result + "}"); } #region 页索引,第几页 public int PageIndex //页索引,第几页 { get { int pageindex = 1; if (Request["page"] != null) { pageindex = int.Parse(Request["page"].ToString()); } return pageindex; } } #endregion #region 分页大小,每页显示多少条记录 public int PageSize //分页大小,每页显示多少条记录,用于查询的时候每次取多少条数据 { get { int pagesize = 10; if (Request["rows"] != null) { pagesize = int.Parse(Request["rows"].ToString()); } return pagesize; } } #endregion #region 排序字段 public string OrderField { get { try { return Request.QueryString["sidx"].ToString(); //也就是对应前台colModel中的index } catch { return "id"; //这里默认用id,注意跟着自己的数据变动 } } } #endregion #region 排序方式 asc 或者 desc public string OrderType //排序方式 asc 或者 desc { get { try { return Request.QueryString["sord"].ToString(); } catch { return "asc"; } } } #endregion public int RecordCount //查询总记录数 { get { return areaInfo.GetRecordsCountNoCondition(); } } public int PageCount { get { int pagesize = 10; if (Request["rows"] != null) { pagesize = int.Parse(Request["rows"].ToString()); } int totalPage = 0; int recordCount = this.RecordCount; if (recordCount > 0) { if (recordCount % pagesize == 0) totalPage = recordCount / pagesize; else totalPage = recordCount / pagesize + 1; } return totalPage; } } #region 查询条件 主要用于搜索 public string Condition //条件 { get { try { return Request.QueryString["condition"].ToString(); } catch { return "1=1"; } } } #endregion
用到的areaInfo类:
View Code
public class areaInfo { public areaInfo() { // //TODO: 在此处添加构造函数逻辑 // } public int ID { get; set; } //表中的自动编号 public string AreaID { get; set; } //区域ID号 public string Area { get; set; } //地区名称 public string Father { get; set; } //父类编号,也即所属城市编号 /// <summary> /// 取得记录的总数 /// </summary> /// <returns>记录的总数(int)</returns> public static int GetRecordsCountNoCondition() { string sql = "select count(1) from area"; return (int)OleDBHelper.GetSingle(sql); } /// <summary> /// 取地区的分页数据 /// </summary> /// <param name="pageindex">页索引</param> /// <param name="pagesize">页大小</param> /// <param name="orderfield">排序字段</param> /// <param name="ordertype">排序类型</param> /// <returns>地区数据</returns> public static IList<areaInfo> GetAreaInfoByPageNoCondition(int pageindex, int pagesize, string orderfield, string ordertype) { IList<areaInfo> areas = new List<areaInfo>(); string sql = string.Empty; if (pageindex == 1) { sql = "select top " + pagesize + " * from area order by " + orderfield + " " + ordertype; } else { //因为我的数据库是access考虑到效率,用的是如下构建查询的方式(大于最大ID,小于最小ID方式) if (ordertype == "asc") //如果是升序 { sql = "select top " + pagesize + " * from area where " + orderfield + ">(select max(" + orderfield + ") from (select top " + (pageindex - 1) * pagesize + " " + orderfield + " from area where 1=1 order by " + orderfield + " asc)) order by " + orderfield + " asc"; } else//否则降序 { sql = "select top " + pagesize + " * from area where " + orderfield + "<(select min(" + orderfield + ") from (select top " + (pageindex - 1) * pagesize + " " + orderfield + " from area where 1=1 order by " + orderfield + " desc)) order by " + orderfield + " desc"; } } OleDbDataReader reader = OleDBHelper.ExecuteReader(sql); while (reader.Read()) { areaInfo area = new areaInfo(); area.ID = Convert.ToInt32(reader["id"].ToString()); area.AreaID = reader["areaID"].ToString(); area.Area = reader["area"].ToString(); area.Father = reader["father"].ToString(); areas.Add(area); } reader.Close(); return areas; } }