jquery easy ui datagrid 纯后台分页实现

最新推荐文章于 2024-09-21 19:42:30 发布

天一生水water

最新推荐文章于 2024-09-21 19:42:30 发布

阅读量1.6w

点赞数 3

本文链接：https://blog.csdn.net/Metal1/article/details/48995591

版权

软件开发专栏收录该内容

130 篇文章 2 订阅

订阅专栏

jquery easyui的datagrid可以容易实现分页，但大多数是实现前台javascirpt分页,如果数据库数据特别大，一次查询的数据交给前台分页，浏览器多半死机。所以需要在后台查询指定的页的数据，送到前台datagrid中，性能才好。

查询指定页的SQL语句并不难，关键问题是：

（1）在SQL中查询了一页的数据，比如10条，在datagrid中只会显示总记录数10条，共1页

（2）在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

(4)在UI层编写js代码

初始化datagrid pagenation代码

   //根据查询得到井眼信息
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;
}
}