jQuery表格插件jqGrid之JSON数据
这次真正的用到数据库中的数据了，其实这也是我们用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;
}
}
自己以前做的示例代码 下载
