jquery easyui的datagrid可以容易实现分页，但大多数是实现前台javascirpt分页,如果数据库数据特别大，一次查询的数据交给前台分页，浏览器多半死机。所以需要在后台查询指定的页的数据，送到前台datagrid中，性能才好。
查询指定页的SQL语句并不难，关键问题是：
（1）在SQL中查询了一页的数据，比如10条，在datagrid中只会显示总记录数10条，共1页
pagination中点击上一页，下一页，可以调用后台重新查询新的页面数据
解决问题的方法：
(1)如何让datagrid收到一页的记录，但会显示查询的总记录数，总页数
方法:观察datagrid中的json数据格式
{"total":10,"rows":[
{"productid":"FI-SW-01","productname":"Koi","unitcost":10.00,"status":"P","listprice":36.50,"attr1":"Large","itemid":"EST-1"},
{"productid":"K9-DL-01","productname":"Dalmation","unitcost":12.00,"status":"P","listprice":18.50,"attr1":"Spotted Adult Female","itemid":"EST-10"},
{"productid":"RP-SN-01","productname":"Rattlesnake","unitcost":12.00,"status":"P","listprice":38.50,"attr1":"Venomless","itemid":"EST-11"},
{"productid":"RP-SN-01","productname":"Rattlesnake","unitcost":12.00,"status":"P","listprice":26.50,"attr1":"Rattleless","itemid":"EST-12"},
{"productid":"RP-LI-02","productname":"Iguana","unitcost":12.00,"status":"P","listprice":35.50,"attr1":"Green Adult","itemid":"EST-13"},
{"productid":"FL-DSH-01","productname":"Manx","unitcost":12.00,"status":"P","listprice":158.50,"attr1":"Tailless","itemid":"EST-14"},
{"productid":"FL-DSH-01","productname":"Manx","unitcost":12.00,"status":"P","listprice":83.50,"attr1":"With tail","itemid":"EST-15"},
{"productid":"FL-DLH-02","productname":"Persian","unitcost":12.00,"status":"P","listprice":23.50,"attr1":"Adult Female","itemid":"EST-16"},
{"productid":"FL-DLH-02","productname":"Persian","unitcost":12.00,"status":"P","listprice":89.50,"attr1":"Adult Male","itemid":"EST-17"},
{"productid":"AV-CB-01","productname":"Amazon Parrot","unitcost":92.00,"status":"P","listprice":63.50,"attr1":"Adult Male","itemid":"EST-18"}
]}
会发现其中有个total属性，正常情况下total显示的数据库查询那页的数据量，比如total:10,我们只要修改total为查询数据库的记录数，前台datagrid就会显示总共多少页，多少记录.比如total:1200,前台pagenation就会显示120页，1200条记录，欺骗了datagrid.所以在json返回给datagrid之前，修改total就ok
(2)在pagination中点击上一页，下一页，可以调用后台重新查询新的页面数据
解决方法，可以设置pagination的事件 onSelectPage,在点击的时候重新查询数据库.
具体实现
（1）分页查询sql语句实现，以下是针对sql2005,其他类似
#region 将原始SQL语句改造成支持分页的SQL语句
/// <summary>
/// 将原始SQL语句改造成支持分页的SQL语句ion
/// </summary>
/// <param name="sql">原始sql语句</param>
/// <param name="page">第几页</param>
/// <param name="pageSize">每页多少记录</param>
/// <param name="primaryField">原始sql语句主键字段</param>
/// <returns></returns>
public static string GetPageSql(string sql, int page, int pageSize, string primaryField)
{
StringBuilder pageSQL = new StringBuilder();
pageSQL.Append(string.Format("SELECT TOP {0} * From ", pageSize));
pageSQL.Append(" ( ");
pageSQL.Append(string.Format(" SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber,* FROM ", primaryField));
pageSQL.Append(string.Format("({0}) tempTable1", sql));
pageSQL.Append(" ) tempTable2 ");
pageSQL.Append(string.Format(" WHERE RowNumber > {0}*({1}-1) ", pageSize, page));
return pageSQL.ToString();
}
#endregion(2)在业务层实现两个方法分别是分页查询，和求查询记录总数
求查询记录总数
public int GetRecordCount(string strWhere)
{
return dal.GetRecordCount(strWhere);
} //分页查询
public DataSet GetList(string strWhere, int page, int pagesize)
{
return dal.GetList(strWhere, page, pagesize);
}(3)在UI层的设置datagrid
<div id="divSearch" style="height: 40px;">
<table>
<tr>
<td>
井号
</td>
<td>
<input type="text" id="inputWellNo" data-options="required:true" ></input>
</td>
<td>
<a href="#" class="easyui-linkbutton" data-options="plain:true,iconCls:'icon-search'"
οnclick="Query()">查询</a>
</td>
</tr>
</table>
</div>
<table id="tableWell" title="井眼信息列表" class="easyui-datagrid" style="width: auto;
height: 400px;" toolbar="#gridWellToolbar" pagination="true" rownumbers="true"
singleselect="true">
<thead>
<tr>
<th field="WellID" width="20" hidden="true">
井眼ID
</th>
<th field="WellNo" width="100">
井号
</th>
<th field="Area" width="200">
构造名称
</th>
<th field="WellType" width="50">
井型
</th>
<th field="WellCategory" width="50">
井别
</th>
</tr>
</thead>
</table>
<div id="gridWellToolbar">
<a href="#" class="easyui-linkbutton" iconcls="icon-add" plain="true" οnclick="AddWell()">
添加</a>
<a href="#" class="easyui-linkbutton" iconcls="icon-edit" plain="true" οnclick="EditWell()"> 编辑 </a>
<a href="#" class="easyui-linkbutton" iconcls="icon-remove" plain="true" οnclick="DeleteWell()"> 删除 </a>
</div>
(4)在UI层编写js代码
初始化datagrid pagenation代码
<script type="text/javascript">
var url;
//初始化查询条件
$(function () {
InitPagenation();
});
///初始化分页
function InitPagenation() {
//设置分页控件
var p = $('#tableWell').datagrid('getPager');
$(p).pagination({
pageSize: 10, //每页显示的记录条数，默认为10
pageList: [10, 20, 30], //可以设置每页记录条数的列表
onSelectPage: function (pageNumber, pageSize) {
Query();//分页查询
}
});
} //根据查询得到井眼信息
function Query() {
var wellNo = $('#inputWellNo').val(); //得到查询关键字--井号
var opts = $('#tableWell').datagrid('options');
var page = opts.pageNumber;//获取页码
var pageSize = opts.pageSize;//获取每页多少记录
var handler = "Ajax/GetWellListHandler.ashx?wellNo=" + escape(wellNo) + "&page=" + escape(page)+ "&pageSize=" + escape(pageSize);
$('#tableWell').datagrid('options').url = handler; //设置表格数据的来源URL
$('#tableWell').datagrid('reload'); //重新加载表格
}(5)在后台Ajax/GetWellListHandler.ashx中调用业务逻辑，使用分页查询
using System;
using System.Web;
public class GetWellListHandler : IHttpHandler {
public void ProcessRequest (HttpContext context) {
string wellNo = context.Request["wellNo"];//得到传参值井号
int pageSize=10, page=1;
if (null != context.Request.QueryString["pageSize"])
{//获取前台传过来的每页显示数据的条数
pageSize = int.Parse(context.Request.QueryString["pageSize"].ToString().Trim());
}
if (null != context.Request.QueryString["page"])
{
//获取当前的页码
page = int.Parse(context.Request.QueryString["page"].ToString().Trim());
}
string where = "";
if (wellNo != "") where = string.Format(" WellNo like '{0}%' ", wellNo);
//从业务工厂得到处理对象
IBLL.IBLL_Well iBLL_Well = BLLFactory.Factory.Get_BLL_Well();
int totalRows = iBLL_Well.GetRecordCount(where);//获取记录总数
System.Data.DataSet ds = iBLL_Well.GetList(where, page, pageSize);//获取指定页数的记录
string json = JsonType.DataTableToJsonPage(ds.Tables[0],totalRows );//将datatable数据转换为json,其中total设置为数据库查询的记录数
if (json == null) json = "[]";
context.Response.Write(json);
}
public bool IsReusable {
get {
return false;
}
}
}附录: JsonType.DataTableToJsonPage函数
/// <summary>
/// 分页查询用
/// </summary>
/// <param name="dt"></param>
/// <param name="total">总记录数</param>
/// <returns></returns>
public static string DataTableToJsonPage(DataTable dt,int total)
{
System.Text.StringBuilder JsonString = new StringBuilder();
//Exception Handling
if (dt != null && dt.Rows.Count > 0)
{
JsonString.Append("{ ");
JsonString.Append(string.Format("\"total\":{0},\"rows\":[ ", total));
for (int i = 0; i < dt.Rows.Count; i++)
{
JsonString.Append("{ ");
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j < dt.Columns.Count - 1)
{
JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\":" + "\"" + ReplaceJsonInvalideCode(dt.Rows[i][j].ToString().Trim()) + "\",");
}
else if (j == dt.Columns.Count - 1)
{
JsonString.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\":" + "\"" + ReplaceJsonInvalideCode(dt.Rows[i][j].ToString().Trim()) + "\"");
}
}
/**/
/**/
/**/
/*end Of String*/
if (i == dt.Rows.Count - 1)
{
JsonString.Append("} ");
}
else
{
JsonString.Append("}, ");
}
}
JsonString.Append("]}");
string s = JsonString.ToString();
return s;
}
else
{
return null;
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
