打造通用的分页排序查询表格

一，ligerGrid是如何工作的。

在没有开始做之前，需要先了解一下ligerGrid是如何工作的。简单来说，liger grid 就是提交准备好的数据到指定的目标请求数据，拿到数据以后，显示出来。

所以问题在于发送什么数据，拿到什么数据。先来看看ligerGrid的参数：

url: null,

pageSize: 10,

sortName : null,

sortOrder:null,

root :'Rows', //数据源字段名

record:'Total', //数据源记录数字段名

pageParmName :'page', //页索引参数名，(提交给服务器)

pagesizeParmName:'pagesize', //页记录数参数名，(提交给服务器)

sortnameParmName:'sortname', //页排序列名(提交给服务器)

sortorderParmName:'sortorder', //页排序方向(提交给服务器)

发送什么数据

page、pagesize、sortname、sortorder 这几个是默认发送到服务器的参数，可用于正确返回分页、排序后的数据。如果需要查询，可能需要配置一下parms，格式如

parms:[{name:'wherestr',value:'status="active"'}] ，这样后台就可以收到这个参数了

比如我在页面上这样配置：

pageSize: 30,url: "service/BillListData.ashx?gridviewname=authors", sortName: 'au_id'

后台收到的数据，将会是：

拿到什么数据

Rows、Total这两个是返回数据的默认字段名，Rows是数据的json格式，Total是记录的总数，需要的数据类似这样的：

效果如下：

二，存储过程

CREATE PROCEDURE[dbo].[P_GridViewPager] (

@recordTotal INT OUTPUT, --输出记录总数

@viewName VARCHAR(800), --表名

@fieldName VARCHAR(800) ='*', --查询字段

@keyName VARCHAR(200) ='Id', --索引字段

@pageSize INT=20, --每页记录数

@pageNo INT=1, --当前页

@orderString VARCHAR(200), --排序条件

@whereString VARCHAR(800) ='1=1'--WHERE条件)

AS

BEGIN

DECLARE@beginRow INT

DECLARE@endRow INT

DECLARE@tempLimit VARCHAR(200)

DECLARE@tempCount NVARCHAR(1000)

DECLARE@tempMain VARCHAR(1000)

SET@beginRow= (@pageNo-1) *@pageSize+1

SET@endRow=@pageNo*@pageSize

SET@tempLimit='rows BETWEEN '+CAST(@beginRow AS VARCHAR) +' AND '+CAST(@endRow AS VARCHAR)

--输出参数为总记录数

SET@tempCount='SELECT @recordTotal = COUNT(*) FROM (SELECT '+@keyName+' FROM '+@viewName+' WHERE '+@whereString+') AS my_temp' EXECUTE sp_executesql @tempCount,N'@recordTotal INT

OUTPUT',@recordTotal OUTPUT

--主查询返回结果集

SET@tempMain='SELECT * FROM (SELECT ROW_NUMBER() OVER ('+@orderString+') AS rows ,'+@fieldName+' FROM '+@viewName+' WHERE

'+@whereString+') AS main_temp WHERE '+@tempLimit

--PRINT @tempMain

EXECUTE (@tempMain)

END

GO

三，准备一个通用的ashx页面

<%@ WebHandler Language="C#" Class="BillListData" %>

using System;

using System.Web;

using https://www.360docs.net/doc/7617937618.html,mon;

using ligerUI.Utility.LigerGrid;

public class BillListData : IHttpHandler {

public void ProcessRequest (HttpContext context) {

context.Response.ContentType = "text/plain";

try

{

TryGetGridViewData();

}

catch (Exception err)

{

context.Response.Write("null");

}

context.Response.End();

}

public void TryGetGridViewData()

{

System.Web.HttpContext context = System.Web.HttpContext.Current;

GridViewPager pager = new GridViewPager();

string datajson = pager.GetDataJSON();

context.Response.Write(datajson);

}

public bool IsReusable {

get {

return false;

}

}

}

四，实现处理方法（接收分页排序信息，并返回正确的数据）

public DataTable GetGridView(string gridViewName, string fieldName, string gridKeyName, int pageNo, i nt pageSize, string orderStr, string whereStr, ref int recordTotal)

{

string sqlCommad = "P_GridViewPager";

SqlParameter[] parms = new SqlParameter[]{

new SqlParameter("viewName",SqlDbType.VarChar,50),

new SqlParameter("fieldName",SqlDbType.VarChar,50),

new SqlParameter("keyName",SqlDbType.VarChar,50),

new SqlParameter("pageNo",SqlDbType.Int),

new SqlParameter("pageSize",SqlDbType.Int),

new SqlParameter("orderString",SqlDbType.VarChar,50),

new SqlParameter("whereString",SqlDbType.VarChar,50),

new SqlParameter("recordTotal",SqlDbType.VarChar,50)

};

parms[0].Value = gridViewName;

parms[1].Value = fieldName;

parms[2].Value = gridKeyName;

parms[3].Value = pageNo;

parms[4].Value = pageSize;

parms[5].Value = orderStr;

parms[6].Value = whereStr;

parms[7].Direction = ParameterDirection.Output;

DataTable dt = SqlHelper.ExecuteDataset(DataBaseHelper.connectionstring, CommandType.Stored Procedure, sqlCommad, parms).Tables[0];

recordTotal = CommonHelper.ObjToInt(parms[7].Value);

return dt;

}

public string GetDataJSON()

{

System.Web.HttpContext ctx = System.Web.HttpContext.Current;

int pageno = CommonHelper.ObjToInt(ctx.Request.Params["page"]);

int pagesize = CommonHelper.ObjToInt(ctx.Request.Params["pagesize"]);

string sortname = CommonHelper.ObjToStr(ctx.Request.Params["sortname"]);

string sortorder = CommonHelper.ObjToStr(ctx.Request.Params["sortorder"]);

string gridviewname = CommonHelper.ObjToStr(ctx.Request.Params["gridviewname"]);

string gridsearch = CommonHelper.ObjToStr(ctx.Request.Params["gridsearch"]);

if (string.IsNullOrEmpty(gridviewname))

throw new ArgumentNullException("Grid视图名[gridsearch]不能为空");

if (pageno == 0 || pagesize == 0)

{

if (string.IsNullOrEmpty(sortorder) && string.IsNullOrEmpty(gridsearch))

{

return GetDataJSON(gridviewname);

}

if(string.IsNullOrEmpty(sortorder))

return GetDataJSON(gridviewname, gridsearch);

if (string.IsNullOrEmpty(gridsearch))

return GetDataJSONUseSQL(string.Format("select * from {0} order by {1} {2}", gridviewnam e, sortname, sortorder.ToLower() == "asc" ? "asc" : "desc"));

return GetDataJSON(gridviewname, gridsearch, string.Format("order by {0} {1}", sortname, sorto rder));

}

if (string.IsNullOrEmpty(gridsearch) && string.IsNullOrEmpty(sortname))

{

return GetDataJSON(gridviewname, pageno, pagesize);

}

if (string.IsNullOrEmpty(gridsearch) && !string.IsNullOrEmpty(sortname))

{

return GetDataJSON(gridviewname, pageno, pagesize, sortname, sortorder);

}

if (!string.IsNullOrEmpty(gridsearch) && string.IsNullOrEmpty(sortname))

{

return GetDataJSON(gridviewname, pageno, pagesize, gridsearch);

}

if (!string.IsNullOrEmpty(gridsearch) && !string.IsNullOrEmpty(sortname))

{

return GetDataJSON(gridviewname, pageno, pagesize, sortname, sortorder, gridsearch); }

return@"{""Rows"":[],""Total"":""0""}";

}