c# 手写分页功能模块
重点是sql语句的组合运用
大致的部分为翻页sql语句（再多层中包含参数的定义mode，数据的访问dal）、构造翻页控件语句
先介绍翻页sql语句
1.mode层中的参数设置
private string _tableName;//表名
private string _fldName;//返回的列(*)
private int _PageSize;//每页显示多少条
private int _PageIndex;//要显示哪一页的记录
private int _pageCount;//查询结果分页后的总页数
private int _Counts;//查询到的记录数
private string _fldSort;//排序字段列表或条件
private string _addSort;//附加到最后的排序字段
private int _Sort;//排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
private string _strCondition;//查询条件,不需where(注意: 不要加where[格式：AND 列名='值'])
private string _ID;//主表的主键
private int _Dist;//是否添加查询字段的 DISTINCT 默认0不添加/1添加
2.bll中的翻页sql的合成
1）查找总页数的sql
/// <summary>
/// 分页查询数据记录总数获取
/// </summary>
/// <param name="_tbName">----要显示的表或多个表的连接</param>
/// <param name="_ID">----主表的主键</param>
/// <param name="_strCondition">----查询条件,不需where</param>
/// <param name="_Dist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>
/// <returns></returns>
public static string getPageListCounts(string _tbName, string _ID, string _strCondition, int _Dist)
{//---存放取得查询结果总数的查询语句 //---对含有DISTINCT的查询进行SQL构造
//---对含有DISTINCT的总数查询进行SQL构造
string strTmp = string.Empty//所返回的sql语句, SqlSelect = string.Empty//sql语句查找头select, SqlCounts = string.Empty;//查找的条数
if (_Dist == 0)
{//判断是否去掉重复的数据
SqlSelect = "SELECT ";
SqlCounts = "COUNT(*)";
}
else
{
SqlSelect = "SELECT DISTINCT ";
SqlCounts = "COUNT(DISTINCT " + _ID + ")";
}
if (_strCondition == string.Empty)
{//判断查找的条件是否为空
strTmp = SqlSelect + SqlCounts + " FROM " + _tbName;
}
else
{
strTmp = SqlSelect + SqlCounts + " FROM " + _tbName + " WHERE (1=1) " + _strCondition;
}
return strTmp;
}
语句原型是select count(*) from tableName where (1=1) 考虑改进写法讲count(*)更改为count(1)
下面一段代码是获取页数的方法
/// <summary>
/// 获取列表中的数据
/// </summary>
/// <param name="key"></param>
/// <param name="curPage">当前页数</param>
/// <param name="pageCount">条数</param>
/// <param name="pageSize">每页显示几条</param>
/// <param name="Counts"></param>
/// <returns></returns>
public DataView getParkDataList(Model.NewPage model)
{//返回分页控件绑定数据string key, int curPage, out int pageCount, int pageSize, int Counts
string SQL = getPageListSql(model.tableName, model.fldName, model.PageSize, model.PageIndex, model.pageCount, model.Counts, model.fldSort, model.Sort, model.strCondition, model.ID, model.Dist, model.addSort);
DataView dv = dal.getParkDataList(SQL);
return dv;
}
2)核心代码翻页sql数据查找
/**/
/// <summary>
/// 获取分页数据查询SQL
/// </summary>
/// <param name="_tbName">----要显示的表或多个表的连接</param>
/// <param name="_fldName">----要显示的字段列表</param>
/// <param name="_PageSize">----每页显示的记录个数</param>
/// <param name="_Page">----要显示那一页的记录</param>
/// <param name="_PageCount">----查询结果分页后的总页数</param>
/// <param name="_Counts">----查询到的记录数</param>
/// <param name="_fldSort">----排序字段列表或条件(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')</param>
/// <param name="_Sort">----排序方法，0为升序，1为降序</param>
/// <param name="_strCondition">----查询条件,不需whereout</param>
/// <param name="_ID">----主表的主键</param>
/// <param name="_Dist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>
/// <returns></returns>
public static string getPageListSql(string _tbName, string _fldName, int _PageSize, int _Page, int _PageCount, int _Counts, string _fldSort, int _Sort, string _strCondition, string _ID, int _Dist, string _AddSort)
{//组合成sql语句
string strTmp = string.Empty; //---strTmp用于返回的SQL语句
string SqlSelect = string.Empty//数据查找头, strSortType = string.Empty//查找顺序的类型, strFSortType = string.Empty//查找后半页专用;
if (_Dist == 0)
{//判断是否去掉查找重复内容的数据
SqlSelect = "SELECT ";
}
else
{
SqlSelect = "SELECT DISTINCT ";
}
if (_Sort == 0)
{//判断排列的顺序这个蛮重要他是整个语句的关键后面会讲到
strFSortType = " ASC";
strSortType = " DESC";
}
else
{
strFSortType = " DESC";
strSortType = " ASC";
}
//----取得查询结果总数量-----
int tmpCounts = 1;
if (_Counts != 0)
{
tmpCounts = _Counts;//将查询结果的总数赋给tmpCounts
}
_PageCount = (tmpCounts + _PageSize - 1)//这个处理很完美将得到的数据总数加上每页显示的条数在处于每页显示的条数不怕除不尽 / _PageSize; //--取得分页总数
//**//**当前页大于总页数 取最后一页**/
if (_Page > _PageCount)
{//如果当前的页数大于我们得到的页数将得到的页数赋予它
_Page = _PageCount;
}
if (_Page <= 0)
{//当前页小于0则取第一页
_Page = 1;
}
//--/*-----数据分页2分处理-------*/
int pageIndex = tmpCounts / _PageSize;//总页数处于每页显示的条数 页码数
int lastCount = tmpCounts % _PageSize;//如果不能被整除的情况下再加一页 判断得到最后一页
if (lastCount > 0)
{//没有被整除页码数加上一
pageIndex = pageIndex + 1;
}
else
{//如果能被整除的话将其值赋予它
lastCount = _PageSize;
}
if (_strCondition == string.Empty)
{// --没有设置显示条件时
if (pageIndex < 2 || _Page <= (pageIndex / 2 + pageIndex % 2))
{ //--前半部分数据处理 pageIndex < 2在应对小数据量时 (pageIndex / 2 + pageIndex % 2)解释当前为100条数据时此时的页数为_Page 40 则小于50 若为51则这个条件不成立
if (_Page == 1)
{//此时为第一页时
strTmp = SqlSelect + " TOP " + _PageSize + " " + _fldName + " FROM " + _tbName + " ORDER BY " + _AddSort//自己附加的条件以应对多条件查询的情况 + "," + _fldSort + " " + strFSortType;
//原型select top count from tabName order by
}
else
{//不为第一页时
strTmp = SqlSelect + " * FROM (" + SqlSelect + " TOP " + _PageSize + " " + _fldName + " FROM " + _tbName + " WHERE " + _ID + " <(SELECT MIN(" + _ID + ") FROM (" + SqlSelect + " TOP " + _PageSize * (_Page - 1) + " " + _ID + " FROM " + _tbName + " ORDER BY " + _fldSort + " " + strFSortType + "," + _AddSort + ") AS TBMinID) ORDER BY " + _fldSort + " " + strFSortType + ") AS TempTB ORDER BY " + _AddSort + "," + _fldSort + " " + strFSortType;
//语句解剖先从内核接起(SELECT MIN(" + _ID + ") FROM (" + SqlSelect + " TOP " + _PageSize * (_Page - 1) + " " + _ID + " FROM " + _tbName + " ORDER BY " + _fldSort + " " + strFSortType + "," + _AddSort + ") AS TBMinID)得到最大id值 这个在整个语句中最为重要数据的查找全依赖与他 这个也是这个查询语句的高妙之处他将一些语句
}
}
else
{
_Page = pageIndex - _Page + 1; //后半部分数据处理
if (_Page <= 1) //--最后一页数据显示
{
strTmp = SqlSelect + " * FROM (" + SqlSelect + " TOP " + lastCount + " " + _fldName + " FROM " + _tbName + " ORDER BY " + _fldSort + " " + strSortType + ") AS TempTB" + " ORDER BY " + _AddSort + "," + _fldSort + " " + strFSortType;
}
else
{
strTmp = SqlSelect + " * FROM (" + SqlSelect + " TOP " + _PageSize + " " + _fldName + " FROM " + _tbName +
" WHERE " + _ID + " >(SELECT MAX(" + _ID + ") FROM(" + SqlSelect + " TOP " + (_PageSize * (_Page - 2) + lastCount) + " " + _ID + " FROM " + _tbName +
" ORDER BY " + _fldSort + " " + strSortType + ", " + _AddSort + ") AS TBMaxID) ORDER BY " + _fldSort + " " + strSortType + ") AS TempTB ORDER BY " + _AddSort + "," + _fldSort + " " + strFSortType;
}
}
}
else // --有查询条件
{
if (pageIndex < 2 || _Page <= (pageIndex / 2 + pageIndex % 2))//--前半部分数据处理
{
if (_Page == 1)
{
strTmp = SqlSelect + " TOP " + _PageSize + " " + _fldName + " FROM " + _tbName + " WHERE 1=1 " + _strCondition + " ORDER BY " + _AddSort + "," + _fldSort + " " + strFSortType;
}
else
{
strTmp = SqlSelect + " * FROM (" + SqlSelect + " TOP " + _PageSize + " " + _fldName + " FROM " + _tbName +
" WHERE " + _ID + " <(SELECT MIN(" + _ID + ") FROM (" + SqlSelect + " TOP " + (_PageSize * (_Page - 1)) + " " + _ID + " FROM " + _tbName +
" WHERE 1=1 " + _strCondition + " ORDER BY " + _fldSort + " " + strFSortType + ", " + _AddSort + ") AS TBMaxID) " + _strCondition + " ORDER BY " + _fldSort + " " + strFSortType +
") AS TempTB ORDER BY " + _AddSort + "," + _fldSort + " " + strFSortType;
}
}
else //--后半部分数据处理
{
_Page = pageIndex - _Page + 1;
if (_Page <= 1) //--最后一页数据显示
{
strTmp = SqlSelect + " * FROM (" + SqlSelect + " TOP " + lastCount + " " + _fldName + " FROM " + _tbName +
" WHERE 1=1 " + _strCondition + " ORDER BY " + _fldSort + " " + strSortType + ") AS TempTB ORDER BY " + _AddSort + "," + _fldSort + " " + strFSortType;
}
else
{
strTmp = SqlSelect + " * FROM (" + SqlSelect + " TOP " + _PageSize + " " + _fldName + " FROM " + _tbName +
" WHERE " + _ID + " >(SELECT MAX(" + _ID + ") FROM(" + SqlSelect + " TOP " + (_PageSize * (_Page - 2) + lastCount) + " " + _ID + " FROM " + _tbName +
" WHERE 1=1 " + _strCondition + " ORDER BY " + _fldSort + " " + strSortType + ", " + _AddSort + ") AS TBMaxID) " + _strCondition +
" ORDER BY " + _fldSort + " " + strSortType + ") AS TempTB ORDER BY " + _AddSort + " ," + _fldSort + " " + strFSortType;
}
}
}
return strTmp;
}
2.画出分页控件
//参数解释 Url此处是定义Url路径 Class所要传的参数 PageCount所有数据的总页数 CurrentPage当前的页数 PageSize每页显示的条数 counts一共有多少条数据 Isshow是否显示
public static string GetNumericPages(string Url, string Class, int PageCount, int CurrentPage,int PageSize,int counts, bool Isshow)
{//画出分页控件
int next = 0;//下一页数int pre = 0//上一页数;int startcount = 0;//开始页数int endcount = 0;//结束页数string pagestr = ";//显示分页控件
if (Isshow)
{
if (PageCount <= 0)
{
pagestr = "没有任何记录!";
//<div style="height:25px;"><%=GetNumericPages == "没有任何记录!" ? Common.Alert.None_Tips(7, 0) : GetNumericPages%> </div> //建议换成这种形式 在前台引用这种形式
}
else
{
if (CurrentPage < 0)
{//第一次加载时
CurrentPage = 1;
}
next = CurrentPage + 1;//下一页 pre = CurrentPage - 1;//上一页
startcount = (CurrentPage + 5) > PageCount ? PageCount - 9 : CurrentPage - 4;//开始数 就是没次显示的开头的数据（显示专用）
endcount = CurrentPage < 5 ? 10 : CurrentPage + 5;//结束数
if (startcount < 1)
{
startcount = 1;
}
if (PageCount < endcount) { endcount = PageCount; }
pagestr = " 共检索到<b>" + counts + "</b>条记录";
pagestr += " 页次：<b>" + CurrentPage + "</b>/<b>" + PageCount + "</b> ";
pagestr += "每页<b>" + PageSize + "</b>条 ";
pagestr += CurrentPage > 1 ? "<a href=\"" + Url + "?" + Class + "\">首页</a> <a href=\"" + Url + "?" + Class + "&Page=" + pre + "\">上一页</a>" : "首页 上一页";
for (int i = startcount; i <= endcount; i++)
{
pagestr += CurrentPage == i ? " <font color=\"#ff0000\">" + i + "</font>" : " <a href=\"" + Url + "?" + Class + "&Page=" + i + "\">" + i + "</a>";
}
pagestr += CurrentPage != PageCount ? " <a href=\"" + Url + "?" + Class + "&Page=" + next + "\">下一页</a> <a href=\"" + Url + "?" + Class + "&Page=" + PageCount + "\">末页</a>" : " 下一页 末页";
}
}
return pagestr;
