sql2005存储过程分页及ASP.NET分页类的实现
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CerEgip.DAL;
namespace CerEgip.Common
{
/// <summary>
/// 分页类,通过存储过程进行分页
/// </summary>
public class Pager
{
#region 参数
private int pageIndex = 1;
private int recordCount = 0;
private int pageSize = 20;
private int pageCount = 0;
private int rowCount = 0;
private string tableName = "";
private string whereCondition = "1=1";
private string selectStr = "*";
private string order = "";
private string procedure = "pager";
private bool orderType = true;
private string _FirstStr = "";
private string _PrevStr = "";
private string _NextStr = "";
private string _LastStr = "";
private string _TurnUrlStr = "";
private string _Options = "";
private string strCountww = ""; //共N条信息
private string strPageww = ""; //第N页/共N页
private string strTurnww; //跳转控件
private string pageindexName = "page";
/// <summary>
/// 所要操作的存储过程名称,已有默认的分页存储过程
/// </summary>
public string Procedure
{
get
{
return this.procedure;
}
set
{
if (value == null || value.Length <= 0)
{
this.procedure = "pager";
}
else
{
this.procedure = value;
}
}
}
/// <summary>
/// 当前所要显示的页面数
/// </summary>
public int PageIndex
{
get
{
return this.pageIndex;
}
set
{
this.pageIndex = value;
}
}
/// <summary>
/// 总的页面数
/// </summary>
public int PageCount
{
get
{
return this.pageCount;
}
set
{
this.pageCount = value;
}
}
/// <summary>
/// 总行数
/// </summary>
public int RecordCount
{
get
{
return this.recordCount;
}
set
{
this.recordCount = value;
}
}
/// <summary>
/// 每页条数
/// </summary>
public int PageSize
{
get
{
return this.pageSize;
}
set
{
this.pageSize = value;
}
}
/// <summary>
/// 表名称
/// </summary>
public string TableName
{
get
{
return tableName;
}
set
{
this.tableName = value;
}
}
/// <summary>
/// 条件查询
/// </summary>
public string WhereCondition
{
get
{
return whereCondition;
}
set
{
whereCondition = value;
}
}
/// <summary>
/// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号
/// </summary>
public string SelectStr
{
get
{
return selectStr;
}
set
{
selectStr = value;
}
}
/// <summary>
/// 排序表达式
/// </summary>
public string Order
{
get
{
return order;
}
set
{
order = value;
}
}
/// <summary>
/// 排序类型 true:asc false:desc
/// </summary>
public bool OrderType
{
get
{
return orderType;
}
set
{
orderType = value;
}
}
/// <summary>
/// 得到当前返回的数量
/// </summary>
public int RowCount
{
get
{
return this.rowCount;
}
}
/// <summary>
/// 首页 显示样式
/// </summary>
public string FirstStr
{
get { return _FirstStr; }
set { _FirstStr = value; }
}
/// <summary>
/// 上一页 显示样式
/// </summary>
public string PrevStr
{
get { return _PrevStr; }
set { _PrevStr = value; }
}
/// <summary>
/// 下一页 显示样式
/// </summary>
public string NextStr
{
get { return _NextStr; }
set { _NextStr = value; }
}
/// <summary>
/// 尾页 显示样式
/// </summary>
public string LastStr
{
get { return _LastStr; }
set { _LastStr = value; }
}
/// <summary>
/// 跳转 的url链接
/// </summary>
public string TurnUrlStr
{
get { return _TurnUrlStr; }
set { _TurnUrlStr = value; }
}
/// <summary>
/// 跳转的url链接的参数前面不要加问号和与号
/// </summary>
public string Options
{
get { return _Options; }
set { _Options = value; }
}
/// <summary>
/// 分页参数名称
/// </summary>
public string PageIndexName
{
get { return pageindexName; }
set { pageindexName = value; }
}
#endregion 参数
/// <summary>
/// 分页查寻结果
/// </summary>
public DataTable GetDatas(int pageIndex)
{
this.pageIndex = pageIndex;
Pager pager = this;
DataTable returnTb = Pagination(ref pager).Tables[0];
this.rowCount = returnTb.Rows.Count;
return returnTb;
}
/// <summary>
/// 分页操作存储过程函数
/// </summary>
/// <param name="pager">Pager</param>
/// <returns>返回DataSet</returns>
private DataSet Pagination(ref Pager pager)
{
SqlParameter[] par = new SqlParameter[8];
par[0] = new SqlParameter("@TableName",SqlDbType.NVarChar,200);
par[0].Value = pager.TableName;
par[1] = new SqlParameter("@orderBy", SqlDbType.NVarChar, 200);
par[1].Value = pager.Order;
par[2] = new SqlParameter("@fieldlist",SqlDbType.NVarChar,200);
par[2].Value = pager.SelectStr;
par[3] = new SqlParameter("@WhereCondition", SqlDbType.NVarChar, 200);
par[3].Value = pager.WhereCondition;
par[4] = new SqlParameter("@PageIndex",SqlDbType.Int);
par[4].Value = pager.pageIndex;
par[5] = new SqlParameter("@pageSize",SqlDbType.Int);
par[5].Value = pager.PageSize;
par[6] = new SqlParameter("@RecordCount",SqlDbType.Int);
par[6].Direction = ParameterDirection.InputOutput;
par[7] = new SqlParameter("@PageCount",SqlDbType.Int);
par[7].Direction = ParameterDirection.InputOutput;
DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure,pager.Procedure,par);
pager.RecordCount = (int)par[6].Value;
pager.pageCount = (int)par[7].Value;
return ds;
}
#region 返回分页后的页码显示
/// <summary>
/// 返回分页后的页码显示
/// </summary>
/// <param name="bolCount">是否显示 共N条信息</param>
/// <param name="bolPage">是否显示 第N页/共N页</param>
/// <param name="bolFirst">是否显示 首页</param>
/// <param name="bolLast">是否显示 尾页</param>
/// <param name="bolTurn">是否显示 跳转控件</param>
/// <param name="IsChinese">是否 用中文显示</param>
/// <param name="intStyle">样式选择 1:字符 2:符号</param>
/// <param name="intShowNum">每页显示多少个数字</param>
/// <param name="isHtml">是否HTML分页</param>
/// <param name="exName">如果为HTML分页,要输入HTML后缀名</param>
/// <returns>返回分页后的页码显示</returns>
public string GetShowPageStr(bool bolCount, bool bolPage, bool bolFirst, bool bolLast, bool bolTurn, bool IsChinese, int intStyle, int intShowNum,bool isHtml,string exName)
{
string strPageShowww = "";
string _FirstStr2 = "";
string _PrevStr2 = "";
string _NextStr2 = "";
string _LastStr2 = "";
#region 公共处理
//总页数
pageCount = (recordCount + pageSize - 1) / pageSize;
//超出最小页码
if (pageIndex < 1)
{
pageIndex = 1;
}
//超出最大页码
if (pageIndex > pageCount)
{
pageIndex = pageCount;
}
if (IsChinese)//中文分页
{
//跳转
strTurnww = "<input value='" + pageIndex.ToString() + "' id='txtPageGo' name='txtPageGo' type='text' style='width:35px;'><input name='btnGo' type='button' id='btnGo' value='跳转' onclick=\"javascript:window.location.href='" + _TurnUrlStr + "?Page=' + document.getElementById('txtPageGo').value + '" + "&" + Options + "'\">";
//共N条信息
strCountww = "共 " + recordCount.ToString() + " 条信息";
//第N页/共N页
strPageww = "第" + pageIndex.ToString() + "页/共" + pageCount.ToString() + "页";
//处理页码显示样式
if (intStyle == 1)
{
if (_FirstStr == "")
{
_FirstStr = "首页";
}
if (_PrevStr == "")
{
_PrevStr = "上一页";
}
if (_NextStr == "")
{
_NextStr = "下一页";
}
if (_LastStr == "")
{
_LastStr = "尾页";
}
}
else
{
if (_FirstStr == "")
{
_FirstStr = " << ";
}
if (_PrevStr == "")
{
_PrevStr = " < ";
}
if (_NextStr == "")
{
_NextStr = " > ";
}
if (_LastStr == "")
{
_LastStr = " >> ";
}
}
}
else//英文文分页
{
//跳转
strTurnww = "<input value='" + pageIndex.ToString() + "' id='txtPageGo' name='txtPageGo' type='text' style='width:35px;'><input name='btnGo' type='button' id='btnGo' value='Goto' onclick=\"javascript:window.location.href='" + _TurnUrlStr + "?Page=' + document.getElementById('txtPageGo').value + '" + "&" + Options + "'\">";
//共N条信息
strCountww = "Total " + recordCount.ToString() + " Infos";
//第N页/共N页
strPageww = " " + pageIndex.ToString() + "/" + pageCount.ToString() + " ";
//处理页码显示样式
if (intStyle == 1)
{
if (_FirstStr == "")
{
_FirstStr = " First ";
}
if (_PrevStr == "")
{
_PrevStr = " Previous ";
}
if (_NextStr == "")
{
_NextStr = " Next ";
}
if (_LastStr == "")
{
_LastStr = " Last ";
}
}
else
{
if (_FirstStr == "")
{
_FirstStr = " << ";
}
if (_PrevStr == "")
{
_PrevStr = " < ";
}
if (_NextStr == "")
{
_NextStr = " > ";
}
if (_LastStr == "")
{
_LastStr = " >> ";
}
}
}
#endregion
//没有记录
if (recordCount <= 0)
{
strPageShowww = strCountww;
}
//有记录
else
{
//只有一页
if (pageCount <= 1)
{
strPageShowww = strCountww + " " + strPageww;
}
//不止一页
else
{
//页码链接处理
#region 页码链接处理
//第一页
if (pageIndex == 1)
{
_FirstStr2 = _FirstStr;
_PrevStr2 = _PrevStr;
}
else
{
if (isHtml)
{
_FirstStr2 = "<a href=\"" + _TurnUrlStr + "_1." + exName + "\">" + _FirstStr + "</a>";
_PrevStr2 = "<a href=\"" + _TurnUrlStr + "_" + Convert.ToString(pageIndex - 1) + "." + exName + "\">" + _PrevStr + "</a>";
}
else
{
_FirstStr2 = "<a href=\"" + _TurnUrlStr + "?" + pageindexName + "=1" + "&" + _Options + "\">" + _FirstStr + "</a>";
_PrevStr2 = "<a href=\"" + _TurnUrlStr + "?" + pageindexName + "=" + Convert.ToString(pageIndex - 1) + "&" + _Options + "\">" + _PrevStr + "</a>";
}
}
//最后一页
if (pageIndex == pageCount)
{
_NextStr2 = _NextStr;
_LastStr2 = _LastStr;
}
else
{
if (isHtml)
{
_NextStr2 = "<a href=\"" + _TurnUrlStr + "_" + Convert.ToString(pageIndex + 1) + "." + exName+ "\">" + _NextStr + "</a>";
_LastStr2 = "<a href=\"" + _TurnUrlStr + "_" + pageCount + "." + exName + "\">" + _LastStr + "</a>";
}
else
{
_NextStr2 = "<a href=\"" + _TurnUrlStr + "?"+pageindexName+"=" + Convert.ToString(pageIndex + 1) + "&" + _Options + "\">" + _NextStr + "</a>";
_LastStr2 = "<a href=\"" + _TurnUrlStr + "?" + pageindexName + "=" + pageCount + "&" + _Options + "\">" + _LastStr + "</a>";
}
}
//----处理显示页码-----------
if (bolCount == true)//共N条信息
{
strPageShowww = strPageShowww + " " + strCountww;
}
if (bolPage == true)//第N页/共N页
{
strPageShowww = strPageShowww + " " + strPageww;
}
if (bolFirst == true) //首页
{
strPageShowww = strPageShowww + " " + _FirstStr2;
}
strPageShowww = strPageShowww + "{0}";//上一页
strPageShowww = strPageShowww + "{1}{2}";//下一页
if (bolLast == true)//尾页
{
strPageShowww = strPageShowww + " " + _LastStr2;
}
if (bolTurn == true)//跳转控件
{
strPageShowww = strPageShowww + " " + strTurnww;
}
#endregion
#region 样式一: 共X条信息 第N页/共M页 首页 上一页 下一页 尾页 跳转
if (intStyle == 1)
{
strPageShowww = strPageShowww.Replace("{0}", " " + _PrevStr2);//上一页
strPageShowww = strPageShowww.Replace("{1}", " " + _NextStr2);//下一页
strPageShowww = strPageShowww.Replace("{2}", "");//
}
#endregion
#region 样式二: 共X条信息 第N页/共M页 首页 1 2 3 尾页 跳转
if (intStyle == 2)
{
int PageTemp = 0;
string strPageNum = "";
string strTempNow = "";
//当页码超过最后一批该显示
if (pageIndex > pageCount - intShowNum + 1)
{
PageTemp = pageCount < intShowNum ? 0 : pageCount - intShowNum;
for (int i = 1; i <= intShowNum; i++)
{
if (i > pageCount) break;
strTempNow = Convert.ToString(PageTemp + i);
//当前页不显示超链接
if( PageIndex == PageTemp + i)
{
strPageNum = strPageNum + "<b>" + strTempNow + "</b> ";
}
else
{
if (isHtml)
{
strPageNum = strPageNum + "<a href=\"" + _TurnUrlStr + "_" + strTempNow + "." + exName + "\">" + strTempNow + "</a> ";
}
else
{
strPageNum = strPageNum + "<a href=\"" + _TurnUrlStr + "?"+pageindexName+"=" + strTempNow + "&" + _Options + "\">" + strTempNow + "</a> ";
}
}
}
}
else
{
for (int i = 0; i < intShowNum; i++)
{
strTempNow = Convert.ToString(PageIndex + i);
//当前页不显示超链接
if (i == 0)
{
strPageNum = strPageNum + "<b>" + strTempNow + "</b> ";
}
else
{
if (isHtml)
{
strPageNum = strPageNum + "<a href=\"" + _TurnUrlStr + "_" + strTempNow + "." + exName + "\">" + strTempNow + "</a> ";
}
else
{
strPageNum = strPageNum + "<a href=\"" + _TurnUrlStr + "?P"+pageindexName+"=" + strTempNow + "&" + _Options + "\">" + strTempNow + "</a> ";
}
}
}
}
//
strPageShowww = strPageShowww.Replace("{0}", " " + _PrevStr2);//上一页
strPageShowww = strPageShowww.Replace("{1}", " " + strPageNum);//显示数字
strPageShowww = strPageShowww.Replace("{2}", " " + _NextStr2);//下一页
}
#endregion
}
}
return strPageShowww;
}
#endregion
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CerEgip.DAL;
namespace CerEgip.Common
{
/// <summary>
/// 分页类,通过存储过程进行分页
/// </summary>
public class Pager
{
#region 参数
private int pageIndex = 1;
private int recordCount = 0;
private int pageSize = 20;
private int pageCount = 0;
private int rowCount = 0;
private string tableName = "";
private string whereCondition = "1=1";
private string selectStr = "*";
private string order = "";
private string procedure = "pager";
private bool orderType = true;
private string _FirstStr = "";
private string _PrevStr = "";
private string _NextStr = "";
private string _LastStr = "";
private string _TurnUrlStr = "";
private string _Options = "";
private string strCountww = ""; //共N条信息
private string strPageww = ""; //第N页/共N页
private string strTurnww; //跳转控件
private string pageindexName = "page";
/// <summary>
/// 所要操作的存储过程名称,已有默认的分页存储过程
/// </summary>
public string Procedure
{
get
{
return this.procedure;
}
set
{
if (value == null || value.Length <= 0)
{
this.procedure = "pager";
}
else
{
this.procedure = value;
}
}
}
/// <summary>
/// 当前所要显示的页面数
/// </summary>
public int PageIndex
{
get
{
return this.pageIndex;
}
set
{
this.pageIndex = value;
}
}
/// <summary>
/// 总的页面数
/// </summary>
public int PageCount
{
get
{
return this.pageCount;
}
set
{
this.pageCount = value;
}
}
/// <summary>
/// 总行数
/// </summary>
public int RecordCount
{
get
{
return this.recordCount;
}
set
{
this.recordCount = value;
}
}
/// <summary>
/// 每页条数
/// </summary>
public int PageSize
{
get
{
return this.pageSize;
}
set
{
this.pageSize = value;
}
}
/// <summary>
/// 表名称
/// </summary>
public string TableName
{
get
{
return tableName;
}
set
{
this.tableName = value;
}
}
/// <summary>
/// 条件查询
/// </summary>
public string WhereCondition
{
get
{
return whereCondition;
}
set
{
whereCondition = value;
}
}
/// <summary>
/// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号
/// </summary>
public string SelectStr
{
get
{
return selectStr;
}
set
{
selectStr = value;
}
}
/// <summary>
/// 排序表达式
/// </summary>
public string Order
{
get
{
return order;
}
set
{
order = value;
}
}
/// <summary>
/// 排序类型 true:asc false:desc
/// </summary>
public bool OrderType
{
get
{
return orderType;
}
set
{
orderType = value;
}
}
/// <summary>
/// 得到当前返回的数量
/// </summary>
public int RowCount
{
get
{
return this.rowCount;
}
}
/// <summary>
/// 首页 显示样式
/// </summary>
public string FirstStr
{
get { return _FirstStr; }
set { _FirstStr = value; }
}
/// <summary>
/// 上一页 显示样式
/// </summary>
public string PrevStr
{
get { return _PrevStr; }
set { _PrevStr = value; }
}
/// <summary>
/// 下一页 显示样式
/// </summary>
public string NextStr
{
get { return _NextStr; }
set { _NextStr = value; }
}
/// <summary>
/// 尾页 显示样式
/// </summary>
public string LastStr
{
get { return _LastStr; }
set { _LastStr = value; }
}
/// <summary>
/// 跳转 的url链接
/// </summary>
public string TurnUrlStr
{
get { return _TurnUrlStr; }
set { _TurnUrlStr = value; }
}
/// <summary>
/// 跳转的url链接的参数前面不要加问号和与号
/// </summary>
public string Options
{
get { return _Options; }
set { _Options = value; }
}
/// <summary>
/// 分页参数名称
/// </summary>
public string PageIndexName
{
get { return pageindexName; }
set { pageindexName = value; }
}
#endregion 参数
/// <summary>
/// 分页查寻结果
/// </summary>
public DataTable GetDatas(int pageIndex)
{
this.pageIndex = pageIndex;
Pager pager = this;
DataTable returnTb = Pagination(ref pager).Tables[0];
this.rowCount = returnTb.Rows.Count;
return returnTb;
}
/// <summary>
/// 分页操作存储过程函数
/// </summary>
/// <param name="pager">Pager</param>
/// <returns>返回DataSet</returns>
private DataSet Pagination(ref Pager pager)
{
SqlParameter[] par = new SqlParameter[8];
par[0] = new SqlParameter("@TableName",SqlDbType.NVarChar,200);
par[0].Value = pager.TableName;
par[1] = new SqlParameter("@orderBy", SqlDbType.NVarChar, 200);
par[1].Value = pager.Order;
par[2] = new SqlParameter("@fieldlist",SqlDbType.NVarChar,200);
par[2].Value = pager.SelectStr;
par[3] = new SqlParameter("@WhereCondition", SqlDbType.NVarChar, 200);
par[3].Value = pager.WhereCondition;
par[4] = new SqlParameter("@PageIndex",SqlDbType.Int);
par[4].Value = pager.pageIndex;
par[5] = new SqlParameter("@pageSize",SqlDbType.Int);
par[5].Value = pager.PageSize;
par[6] = new SqlParameter("@RecordCount",SqlDbType.Int);
par[6].Direction = ParameterDirection.InputOutput;
par[7] = new SqlParameter("@PageCount",SqlDbType.Int);
par[7].Direction = ParameterDirection.InputOutput;
DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure,pager.Procedure,par);
pager.RecordCount = (int)par[6].Value;
pager.pageCount = (int)par[7].Value;
return ds;
}
#region 返回分页后的页码显示
/// <summary>
/// 返回分页后的页码显示
/// </summary>
/// <param name="bolCount">是否显示 共N条信息</param>
/// <param name="bolPage">是否显示 第N页/共N页</param>
/// <param name="bolFirst">是否显示 首页</param>
/// <param name="bolLast">是否显示 尾页</param>
/// <param name="bolTurn">是否显示 跳转控件</param>
/// <param name="IsChinese">是否 用中文显示</param>
/// <param name="intStyle">样式选择 1:字符 2:符号</param>
/// <param name="intShowNum">每页显示多少个数字</param>
/// <param name="isHtml">是否HTML分页</param>
/// <param name="exName">如果为HTML分页,要输入HTML后缀名</param>
/// <returns>返回分页后的页码显示</returns>
public string GetShowPageStr(bool bolCount, bool bolPage, bool bolFirst, bool bolLast, bool bolTurn, bool IsChinese, int intStyle, int intShowNum,bool isHtml,string exName)
{
string strPageShowww = "";
string _FirstStr2 = "";
string _PrevStr2 = "";
string _NextStr2 = "";
string _LastStr2 = "";
#region 公共处理
//总页数
pageCount = (recordCount + pageSize - 1) / pageSize;
//超出最小页码
if (pageIndex < 1)
{
pageIndex = 1;
}
//超出最大页码
if (pageIndex > pageCount)
{
pageIndex = pageCount;
}
if (IsChinese)//中文分页
{
//跳转
strTurnww = "<input value='" + pageIndex.ToString() + "' id='txtPageGo' name='txtPageGo' type='text' style='width:35px;'><input name='btnGo' type='button' id='btnGo' value='跳转' onclick=\"javascript:window.location.href='" + _TurnUrlStr + "?Page=' + document.getElementById('txtPageGo').value + '" + "&" + Options + "'\">";
//共N条信息
strCountww = "共 " + recordCount.ToString() + " 条信息";
//第N页/共N页
strPageww = "第" + pageIndex.ToString() + "页/共" + pageCount.ToString() + "页";
//处理页码显示样式
if (intStyle == 1)
{
if (_FirstStr == "")
{
_FirstStr = "首页";
}
if (_PrevStr == "")
{
_PrevStr = "上一页";
}
if (_NextStr == "")
{
_NextStr = "下一页";
}
if (_LastStr == "")
{
_LastStr = "尾页";
}
}
else
{
if (_FirstStr == "")
{
_FirstStr = " << ";
}
if (_PrevStr == "")
{
_PrevStr = " < ";
}
if (_NextStr == "")
{
_NextStr = " > ";
}
if (_LastStr == "")
{
_LastStr = " >> ";
}
}
}
else//英文文分页
{
//跳转
strTurnww = "<input value='" + pageIndex.ToString() + "' id='txtPageGo' name='txtPageGo' type='text' style='width:35px;'><input name='btnGo' type='button' id='btnGo' value='Goto' onclick=\"javascript:window.location.href='" + _TurnUrlStr + "?Page=' + document.getElementById('txtPageGo').value + '" + "&" + Options + "'\">";
//共N条信息
strCountww = "Total " + recordCount.ToString() + " Infos";
//第N页/共N页
strPageww = " " + pageIndex.ToString() + "/" + pageCount.ToString() + " ";
//处理页码显示样式
if (intStyle == 1)
{
if (_FirstStr == "")
{
_FirstStr = " First ";
}
if (_PrevStr == "")
{
_PrevStr = " Previous ";
}
if (_NextStr == "")
{
_NextStr = " Next ";
}
if (_LastStr == "")
{
_LastStr = " Last ";
}
}
else
{
if (_FirstStr == "")
{
_FirstStr = " << ";
}
if (_PrevStr == "")
{
_PrevStr = " < ";
}
if (_NextStr == "")
{
_NextStr = " > ";
}
if (_LastStr == "")
{
_LastStr = " >> ";
}
}
}
#endregion
//没有记录
if (recordCount <= 0)
{
strPageShowww = strCountww;
}
//有记录
else
{
//只有一页
if (pageCount <= 1)
{
strPageShowww = strCountww + " " + strPageww;
}
//不止一页
else
{
//页码链接处理
#region 页码链接处理
//第一页
if (pageIndex == 1)
{
_FirstStr2 = _FirstStr;
_PrevStr2 = _PrevStr;
}
else
{
if (isHtml)
{
_FirstStr2 = "<a href=\"" + _TurnUrlStr + "_1." + exName + "\">" + _FirstStr + "</a>";
_PrevStr2 = "<a href=\"" + _TurnUrlStr + "_" + Convert.ToString(pageIndex - 1) + "." + exName + "\">" + _PrevStr + "</a>";
}
else
{
_FirstStr2 = "<a href=\"" + _TurnUrlStr + "?" + pageindexName + "=1" + "&" + _Options + "\">" + _FirstStr + "</a>";
_PrevStr2 = "<a href=\"" + _TurnUrlStr + "?" + pageindexName + "=" + Convert.ToString(pageIndex - 1) + "&" + _Options + "\">" + _PrevStr + "</a>";
}
}
//最后一页
if (pageIndex == pageCount)
{
_NextStr2 = _NextStr;
_LastStr2 = _LastStr;
}
else
{
if (isHtml)
{
_NextStr2 = "<a href=\"" + _TurnUrlStr + "_" + Convert.ToString(pageIndex + 1) + "." + exName+ "\">" + _NextStr + "</a>";
_LastStr2 = "<a href=\"" + _TurnUrlStr + "_" + pageCount + "." + exName + "\">" + _LastStr + "</a>";
}
else
{
_NextStr2 = "<a href=\"" + _TurnUrlStr + "?"+pageindexName+"=" + Convert.ToString(pageIndex + 1) + "&" + _Options + "\">" + _NextStr + "</a>";
_LastStr2 = "<a href=\"" + _TurnUrlStr + "?" + pageindexName + "=" + pageCount + "&" + _Options + "\">" + _LastStr + "</a>";
}
}
//----处理显示页码-----------
if (bolCount == true)//共N条信息
{
strPageShowww = strPageShowww + " " + strCountww;
}
if (bolPage == true)//第N页/共N页
{
strPageShowww = strPageShowww + " " + strPageww;
}
if (bolFirst == true) //首页
{
strPageShowww = strPageShowww + " " + _FirstStr2;
}
strPageShowww = strPageShowww + "{0}";//上一页
strPageShowww = strPageShowww + "{1}{2}";//下一页
if (bolLast == true)//尾页
{
strPageShowww = strPageShowww + " " + _LastStr2;
}
if (bolTurn == true)//跳转控件
{
strPageShowww = strPageShowww + " " + strTurnww;
}
#endregion
#region 样式一: 共X条信息 第N页/共M页 首页 上一页 下一页 尾页 跳转
if (intStyle == 1)
{
strPageShowww = strPageShowww.Replace("{0}", " " + _PrevStr2);//上一页
strPageShowww = strPageShowww.Replace("{1}", " " + _NextStr2);//下一页
strPageShowww = strPageShowww.Replace("{2}", "");//
}
#endregion
#region 样式二: 共X条信息 第N页/共M页 首页 1 2 3 尾页 跳转
if (intStyle == 2)
{
int PageTemp = 0;
string strPageNum = "";
string strTempNow = "";
//当页码超过最后一批该显示
if (pageIndex > pageCount - intShowNum + 1)
{
PageTemp = pageCount < intShowNum ? 0 : pageCount - intShowNum;
for (int i = 1; i <= intShowNum; i++)
{
if (i > pageCount) break;
strTempNow = Convert.ToString(PageTemp + i);
//当前页不显示超链接
if( PageIndex == PageTemp + i)
{
strPageNum = strPageNum + "<b>" + strTempNow + "</b> ";
}
else
{
if (isHtml)
{
strPageNum = strPageNum + "<a href=\"" + _TurnUrlStr + "_" + strTempNow + "." + exName + "\">" + strTempNow + "</a> ";
}
else
{
strPageNum = strPageNum + "<a href=\"" + _TurnUrlStr + "?"+pageindexName+"=" + strTempNow + "&" + _Options + "\">" + strTempNow + "</a> ";
}
}
}
}
else
{
for (int i = 0; i < intShowNum; i++)
{
strTempNow = Convert.ToString(PageIndex + i);
//当前页不显示超链接
if (i == 0)
{
strPageNum = strPageNum + "<b>" + strTempNow + "</b> ";
}
else
{
if (isHtml)
{
strPageNum = strPageNum + "<a href=\"" + _TurnUrlStr + "_" + strTempNow + "." + exName + "\">" + strTempNow + "</a> ";
}
else
{
strPageNum = strPageNum + "<a href=\"" + _TurnUrlStr + "?P"+pageindexName+"=" + strTempNow + "&" + _Options + "\">" + strTempNow + "</a> ";
}
}
}
}
//
strPageShowww = strPageShowww.Replace("{0}", " " + _PrevStr2);//上一页
strPageShowww = strPageShowww.Replace("{1}", " " + strPageNum);//显示数字
strPageShowww = strPageShowww.Replace("{2}", " " + _NextStr2);//下一页
}
#endregion
}
}
return strPageShowww;
}
#endregion
}
}
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Pager]
@TableName nvarchar(200) --查询表名1
,@orderBy nvarchar(200) --排序表达式2
,@fieldlist nvarchar(200) = '*' --查询的列3
,@WhereCondition nvarchar(200) = '' --查询条件4
,@PageIndex int = 1 --索引页5
,@pageSize int = NULL --每页数量6
,@RecordCount int out --总行数7
,@PageCount int out --总页数8
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max) -- SQL to execute
IF LTRIM(RTRIM(@WhereCondition)) = '' SET @WhereCondition = '1 = 1'
IF @pageSize IS NULL BEGIN
SET @STMT = 'SELECT ' + @fieldlist +
'FROM ' + @TableName +
'WHERE ' + @WhereCondition +
'ORDER BY ' + @orderBy
EXEC (@STMT) -- return requested records
END ELSE BEGIN
SET @STMT = 'SELECT @RecordCount = COUNT(*)
FROM ' + @TableName + '
WHERE ' + @WhereCondition
EXEC sp_executeSQL @STMT, @params = N'@RecordCount INT OUTPUT', @RecordCount = @RecordCount OUTPUT
--SELECT @RecordCount AS RecordCount -- return the total # of records
DECLARE
@lbound int,
@ubound int
SET @PageIndex = ABS(@PageIndex)
SET @pageSize = ABS(@pageSize)
IF @PageIndex < 1 SET @PageIndex = 1
IF @pageSize < 1 SET @pageSize = 1
SET @lbound = ((@PageIndex - 1) * @pageSize)
SET @ubound = @lbound + @pageSize + 1
IF @lbound >= @RecordCount BEGIN
SET @ubound = @RecordCount + 1
SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if -- no records would be on the
-- specified page
END
SET @STMT = 'SELECT ' + @fieldlist + '
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
FROM ' + @TableName + '
WHERE ' + @WhereCondition + '
) AS tbl
WHERE
row > ' + CONVERT(varchar(9), @lbound) + ' AND
row < ' + CONVERT(varchar(9), @ubound)
EXEC (@STMT) -- return requested records
END
-- 获取总页数
-- "CEILING"函数:取得不小于某数的最小整数
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Pager]
@TableName nvarchar(200) --查询表名1
,@orderBy nvarchar(200) --排序表达式2
,@fieldlist nvarchar(200) = '*' --查询的列3
,@WhereCondition nvarchar(200) = '' --查询条件4
,@PageIndex int = 1 --索引页5
,@pageSize int = NULL --每页数量6
,@RecordCount int out --总行数7
,@PageCount int out --总页数8
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max) -- SQL to execute
IF LTRIM(RTRIM(@WhereCondition)) = '' SET @WhereCondition = '1 = 1'
IF @pageSize IS NULL BEGIN
SET @STMT = 'SELECT ' + @fieldlist +
'FROM ' + @TableName +
'WHERE ' + @WhereCondition +
'ORDER BY ' + @orderBy
EXEC (@STMT) -- return requested records
END ELSE BEGIN
SET @STMT = 'SELECT @RecordCount = COUNT(*)
FROM ' + @TableName + '
WHERE ' + @WhereCondition
EXEC sp_executeSQL @STMT, @params = N'@RecordCount INT OUTPUT', @RecordCount = @RecordCount OUTPUT
--SELECT @RecordCount AS RecordCount -- return the total # of records
DECLARE
@lbound int,
@ubound int
SET @PageIndex = ABS(@PageIndex)
SET @pageSize = ABS(@pageSize)
IF @PageIndex < 1 SET @PageIndex = 1
IF @pageSize < 1 SET @pageSize = 1
SET @lbound = ((@PageIndex - 1) * @pageSize)
SET @ubound = @lbound + @pageSize + 1
IF @lbound >= @RecordCount BEGIN
SET @ubound = @RecordCount + 1
SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if -- no records would be on the
-- specified page
END
SET @STMT = 'SELECT ' + @fieldlist + '
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
FROM ' + @TableName + '
WHERE ' + @WhereCondition + '
) AS tbl
WHERE
row > ' + CONVERT(varchar(9), @lbound) + ' AND
row < ' + CONVERT(varchar(9), @ubound)
EXEC (@STMT) -- return requested records
END
-- 获取总页数
-- "CEILING"函数:取得不小于某数的最小整数
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
