jQuery liger ui ligerGrid 打造通用的分页排序查询表格(提供下载)
来源:互联网 发布:五子棋java程序代码 编辑:程序博客网 时间:2024/05/17 00:53
一,ligerGrid是如何工作的。
在没有开始做之前,需要先了解一下ligerGrid是如何工作的。简单来说,liger grid 就是提交准备好的数据到指定的目标请求数据,拿到数据以后,显示出来。
所以问题在于发送什么数据,拿到什么数据。先来看看ligerGrid的参数:
pageSize: 10,
sortName : null,
sortOrder:null,
root :'Rows', //数据源字段名
record:'Total', //数据源记录数字段名
pageParmName :'page', //页索引参数名,(提交给服务器)
pagesizeParmName:'pagesize', //页记录数参数名,(提交给服务器)
sortnameParmName:'sortname', //页排序列名(提交给服务器)
sortorderParmName:'sortorder', //页排序方向(提交给服务器)
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条件)ASBEGIN 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)ENDGO
三,准备一个通用的ashx页面
<%@ WebHandler Language="C#" Class="BillListData" %>
using System;
using System.Web;
using ligerUI.Utility.Common;
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;
}
}
}
using System;
using System.Web;
using ligerUI.Utility.Common;
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, int 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.StoredProcedure, sqlCommad, parms).Tables[0];
recordTotal = CommonHelper.ObjToInt(parms[7].Value);
return dt;
}
{
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.StoredProcedure, 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}", gridviewname, sortname, sortorder.ToLower() == "asc" ? "asc" : "desc"));
return GetDataJSON(gridviewname, gridsearch, string.Format("order by {0} {1}", sortname, sortorder));
}
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""}";
}
{
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}", gridviewname, sortname, sortorder.ToLower() == "asc" ? "asc" : "desc"));
return GetDataJSON(gridviewname, gridsearch, string.Format("order by {0} {1}", sortname, sortorder));
}
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""}";
}
五,提供下载
基于存储过程的通用分页排序查询表格 下载
分类: 02.LigerUI
- jQuery liger ui ligerGrid 打造通用的分页排序查询表格(提供下载)
- jQuery liger ui ligerGrid 打造通用的分页排序查询表格(提供下载)
- jQuery liger ui ligerGrid 打造通用的分页排序查询表格(提供下载)
- jQuery liger ui ligerGrid 打造通用的分页排序查询表格
- jQuery ligerGrid 打造通用的分页排序查询表格(提供下载)
- jQuery的UI框架 Liger UI
- jQuery的UI框架 Liger UI
- liger ui 表格
- mvc ligerGrid 分页查询方法
- Liger UI JQuery 简洁 功能丰富
- ligerGrid Ajax分页获取排序 详细介绍
- 表格拖动排序jquery-ui
- liger ui组件的抽取与封装
- 扩展jquery实现客户端表格的分页、排序
- Winform 通用分页控件实战篇(提供源码下载)
- hibernate 通用分页,查询分页的泛型类
- Liger UI框架
- Liger ui grid 参数
- C#高效编程话题集(三)
- 灵飞经4·西城八部 第十七章 八部之主 6
- Centos操作系统下 VPN服务器(PPTP)server的安装部署
- TFIDF
- 美国编程师程序员面试宝典,年薪十万美金不是梦
- jQuery liger ui ligerGrid 打造通用的分页排序查询表格(提供下载)
- WYSIWIS WYSIWYG ...
- CentOS 6.3 VNC安装
- 使用servlet处理HTTP响应
- 兼容所有浏览器,获取元素文本的方法
- maven的基本原理和maven2的新特性
- [win32] Winsock 中应使用 DisconnectEx 关闭连接
- SAP HANA列存储懒加载(SAP HANA Lazy Loading for column store)
- 线性方程组算法(C语言)