[原创] 分页
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Utility
{
public class PagerHelper
{
/// <summary>
/// 总页数
/// </summary>
public static int PCount = 0;
/// <summary>
/// 总行数
/// </summary>
public static int RCount = 0;
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="Table">表</param>
/// <param name="PrimaryKey">主键</param>
/// <param name="Fields">字段</param>
/// <param name="Where">Where条件</param>
/// <param name="OrderField">排序字段</param>
/// <param name="SortOrder">排序方式,1:正序,0:倒序</param>
/// <param name="PageIndex">当前页码</param>
/// <param name="PageSize">页大小</param>
/// <returns></returns>
public static DataTable PagerData(string Table, string PrimaryKey, string Fields, string Where, string OrderField, int SortOrder, int PageIndex, int PageSize)
{
DataTable dt = new DataTable();
if (string.IsNullOrEmpty(Fields))
{
Fields = "*";
}
if (string.IsNullOrEmpty(Where))
{
Where = "1=1";
}
else
{
Where = "1=1" + Where;
}
if (Fields.Split(',').Length > 0 || Fields == "*")
{
List<SqlParameter> plist = new List<SqlParameter>();
plist.Add(DBHelper.CreateSqlParameter("PCount", PCount));
plist.Add(DBHelper.CreateSqlParameter("RCount", RCount));
plist[0].Direction = ParameterDirection.Output;
plist[1].Direction = ParameterDirection.Output;
plist.Add(DBHelper.CreateSqlParameter("sys_Table", Table));
plist.Add(DBHelper.CreateSqlParameter("sys_Key", PrimaryKey));
plist.Add(DBHelper.CreateSqlParameter("sys_Fields", Fields));
plist.Add(DBHelper.CreateSqlParameter("sys_Where", Where));
plist.Add(DBHelper.CreateSqlParameter("sys_Order", SortOrder == 1 ? OrderField + " asc" : OrderField + " desc"));
plist.Add(DBHelper.CreateSqlParameter("sys_Begin", 1));
plist.Add(DBHelper.CreateSqlParameter("sys_PageIndex", PageIndex));
plist.Add(DBHelper.CreateSqlParameter("sys_PageSize", PageSize));
dt = DBHelper.GetDataTableByRunProcedure("sys_Page_v2", plist);
PCount = Convert.ToInt32(plist[0].Value);
RCount = Convert.ToInt32(plist[1].Value);
}
return dt;
}
/// <summary>
/// 获取分页数据EasyUIJson
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="pn">当前页码</param>
/// <returns></returns>
public static string PagerDataToEasyUIJson(DataTable dt, int? pn)
{
string strjosn = "";
if (pn == null)
{
pn = 1;
}
string pager = @"" + JsPagerFooter((int)pn, 10, "LoadDataGrid").Replace("\"", "'");
strjosn += "{\"total\":\"" + RCount + "\",\"page\":\"" + pn + "\",\"pager\":\"" + pager + "\",\"rows\":[";
foreach (DataRow dr in dt.Rows)
{
strjosn += "{";
foreach (DataColumn dc in dr.Table.Columns)
{
strjosn += "\"" + dc.ColumnName + "\":\"" + dr[dc].ToString().Replace("\"", "\\\"") + "\",";
}
strjosn = strjosn.Remove(strjosn.Length - 1, 1);
strjosn += "},";
}
if (strjosn.Substring(strjosn.Length - 1) == ",")
{
strjosn = strjosn.Remove(strjosn.Length - 1, 1);
}
strjosn += "]}";
return strjosn;
}
/// <summary>
/// 获取数据Json
/// </summary>
/// <param name="dt">数据行</param>
/// <returns></returns>
public static string PagerDataToEasyUIJson(DataRow dr)
{
string strjosn = "";
strjosn += "{\"total\":\"" + 1 + "\",\"page\":\"" + 1 + "\",\"rows\":[";
strjosn += "{";
foreach (DataColumn dc in dr.Table.Columns)
{
strjosn += "\"" + dc.ColumnName + "\":\"" + dr[dc].ToString().Replace("\"", "\\\"") + "\",";
}
strjosn = strjosn.Remove(strjosn.Length - 1, 1);
strjosn += "},";
if (strjosn.Substring(strjosn.Length - 1) == ",")
{
strjosn = strjosn.Remove(strjosn.Length - 1, 1);
}
strjosn += "]}";
return strjosn;
}
/// <summary>
/// 页脚分页代码
/// </summary>
/// <param name="pn">当前页码</param>
/// <param name="size">显示页码个数</param>
/// <param name="thisPageUrl">当前页</param>
/// <returns></returns>
public static string PagerFooter(int pn, int size, string thisPageUrl)
{
int pagetotal = PCount;
//分页页脚部分 初始化
string pagerlinks = "";
//判断最大页码
if (pagetotal > 1)
{
//上一页
int prev = pn - 1;
if (prev < 1)
{
prev = 1;
}
//下一页
int next = pn + 1;
if (next > pagetotal)
{
next = pagetotal;
}
if (pn == 1)
{
pagerlinks += "<span class=\"disabled\"><<</span><span class=\"disabled\"><</span>";
}
else if (pn < size)
{
pagerlinks += "<span class=\"disabled\"><<</span><a href=\"{0}pn={1}\" title=\"上一页\"><</a>";
}
else
{
pagerlinks += "<a href=\"{0}pn=1\" title=\"首页\"><<</a><a href=\"{0}pn={1}\" title=\"上一页\"><</a>";
}
int initial = pn - pn % size + 1;
if (initial > pn)
{
initial = initial - size;
}
int n = initial + size;
if (size > pagetotal)
{
initial = 1;
n = pagetotal + 1;
}
if (n > pagetotal)
{
n = pagetotal + 1;
}
for (int j = initial; j < n; j++)
{
if (pn == j)
{
pagerlinks += "<span class=\"current\">" + j + "</span>";
}
else
{
pagerlinks += "<a href=\"{0}pn=" + j + "\">" + j + "</a>";
}
}
if (pn == pagetotal)
{
pagerlinks += "<span class=\"disabled\">></span><span class=\"disabled\">>><span/>";
}
else if (pn > pagetotal - size && pn < pagetotal)
{
pagerlinks += "<a href=\"{0}pn={2}\" title=\"下一页\">></a><span class=\"disabled\">>><span/>";
}
else
{
pagerlinks += "<a href=\"{0}pn={2}\" title=\"下一页\">></a><a href=\"{0}pn={3}\" title=\"尾页\">>></a>";
}
if (thisPageUrl == null)
{
thisPageUrl = "";
}
pagerlinks = string.Format(pagerlinks, thisPageUrl.IndexOf('?') < 0 ? thisPageUrl + "?" : thisPageUrl + "&", prev, next, pagetotal);
}
return pagerlinks;
}
/// <summary>
/// 页脚分页代码
/// </summary>
/// <param name="pn">当前页码</param>
/// <param name="size">显示页码个数</param>
/// <param name="JsFuncName">js方法名 例子：LoadDataGrid，此方法第一个参数必须为页码pn</param>
/// <returns></returns>
public static string JsPagerFooter(int pn, int size, string JsFuncName)
{
JsFuncName = "javascript:" + JsFuncName;
int pagetotal = PCount;
//分页页脚部分 初始化
string pagerlinks = "";
//判断最大页码
if (pagetotal > 1)
{
//上一页
int prev = pn - 1;
if (prev < 1)
{
prev = 1;
}
//下一页
int next = pn + 1;
if (next > pagetotal)
{
next = pagetotal;
}
if (pn == 1)
{
pagerlinks += "<span class=\"disabled\"><<</span><span class=\"disabled\"><</span>";
}
else if (pn < size)
{
pagerlinks += "<span class=\"disabled\"><<</span><a href=\"{0}({1});\" title=\"上一页\"><</a>";
}
else
{
pagerlinks += "<a href=\"{0}(1);\" title=\"首页\"><<</a><a href=\"{0}({1});\" title=\"上一页\"><</a>";
}
int initial = pn - pn % size + 1;
if (initial > pn)
{
initial = initial - size;
}
int n = initial + size;
if (size > pagetotal)
{
initial = 1;
n = pagetotal + 1;
}
if (n > pagetotal)
{
n = pagetotal + 1;
}
for (int j = initial; j < n; j++)
{
if (pn == j)
{
pagerlinks += "<span class=\"current\">" + j + "</span>";
}
else
{
pagerlinks += "<a href=\"{0}(" + j + ");\">" + j + "</a>";
}
}
if (pn == pagetotal)
{
pagerlinks += "<span class=\"disabled\">></span><span class=\"disabled\">>><span/>";
}
else if (pn > pagetotal - size && pn < pagetotal)
{
pagerlinks += "<a href=\"{0}({2});\" title=\"下一页\">></a><span class=\"disabled\">>><span/>";
}
else
{
pagerlinks += "<a href=\"{0}({2});\" title=\"下一页\">></a><a href=\"{0}({3});\" title=\"尾页\">>></a>";
}
pagerlinks = string.Format(pagerlinks, JsFuncName, prev, next, pagetotal);
}
return pagerlinks;
}
}
}
#region 分页存储过程
/******************************************以下为分页存储过程******************************************/
/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_Page_v2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sys_Page_v2]
GO
CREATE PROCEDURE [dbo].[sys_Page_v2]
@PCount int output, --总页数输出
@RCount int output, --总记录数输出
@sys_Table nvarchar(100), --查询表名
@sys_Key varchar(50), --主键
@sys_Fields nvarchar(500), --查询字段
@sys_Where nvarchar(3000), --查询条件
@sys_Order nvarchar(100), --排序字段
@sys_Begin int, --开始位置
@sys_PageIndex int, --当前页数
@sys_PageSize int --页大小
AS
SET NOCOUNT ON
SET ANSI_WARNINGS ON
IF @sys_PageSize < 0 OR @sys_PageIndex < 0
BEGIN
RETURN
END
DECLARE @new_where1 NVARCHAR(3000)
DECLARE @new_order1 NVARCHAR(100)
DECLARE @new_order2 NVARCHAR(100)
DECLARE @Sql NVARCHAR(4000)
DECLARE @SqlCount NVARCHAR(4000)
DECLARE @Top int
if(@sys_Begin <=0)
set @sys_Begin=0
else
set @sys_Begin=@sys_Begin-1
IF ISNULL(@sys_Where,'') = ''
SET @new_where1 = ' '
ELSE
SET @new_where1 = ' WHERE ' + @sys_Where
IF ISNULL(@sys_Order,'') <> ''
BEGIN
SET @new_order1 = ' ORDER BY ' + Replace(@sys_Order,'desc','')
SET @new_order1 = Replace(@new_order1,'asc','desc')
SET @new_order2 = ' ORDER BY ' + @sys_Order
END
ELSE
BEGIN
SET @new_order1 = ' ORDER BY ID DESC'
SET @new_order2 = ' ORDER BY ID ASC'
END
SET @SqlCount = 'SELECT @RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/'
+ CAST(@sys_PageSize AS NVARCHAR)+') FROM ' + @sys_Table + @new_where1
EXEC SP_EXECUTESQL @SqlCount,N'@RCount INT OUTPUT,@PCount INT OUTPUT',
@RCount OUTPUT,@PCount OUTPUT
IF @sys_PageIndex > CEILING((@RCount+0.0)/@sys_PageSize) --如果输入的当前页数大于实际总页数,则把实际总页数赋值给当前页数
BEGIN
SET @sys_PageIndex = CEILING((@RCount+0.0)/@sys_PageSize)
END
set @sql = 'select '+ @sys_fields +' from ' + @sys_Table + ' w1 '
+ ' where '+ @sys_Key +' in ('
+'select top '+ ltrim(str(@sys_PageSize)) +' ' + @sys_Key + ' from '
+'('
+'select top ' + ltrim(STR(@sys_PageSize * @sys_PageIndex + @sys_Begin)) + ' '+@sys_Fields+' FROM '
+ @sys_Table + @new_where1 + @new_order2
+') w ' + @new_order1
+') ' + @new_order2
--print(@sql)
Exec(@sql)
GO
*/
#endregion
使用期间如有报错，欢迎大家指正或改良。
浙公网安备 33010602011771号