using System; namespace CountryPark.DAL{ /**/ /// <summary> /// PageList 的摘要说明。 /// </summary> public sealed class PageList { static PageList() { } /**/ /// <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 = "" , SqlSelect = "" , SqlCounts = "" ; if (_Dist == 0 ) { SqlSelect = " SELECT " ; SqlCounts = " COUNT(*) " ; } else { SqlSelect = " SELECT DISTINCT " ; SqlCounts = " COUNT(DISTINCT " + _ID + " ) " ; } if (_strCondition == string .Empty) { strTmp = SqlSelect + " @Counts= " + SqlCounts + " FROM " + _tbName; } else { strTmp = SqlSelect + " @Counts= " + SqlCounts + " FROM " + " WHERE (1=1) " + _strCondition; } return strTmp; } /**/ /// <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"> ----查询条件,不需where </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, out int _PageCount, int _Counts, string _fldSort, int _Sort, string _strCondition, string _ID, int _Dist) { string strTmp = "" ; // ---strTmp用于返回的SQL语句 string SqlSelect = "" , strSortType = "" , strFSortType = "" ; 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; } // --取得分页总数 _PageCount = (tmpCounts + _PageSize - 1 ) / _PageSize; // /** // **当前页大于总页数 取最后一页**/ if (_Page > _PageCount) { _Page = _PageCount; } if (_Page <= 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 )) // --前半部分数据处理 { if (_Page == 1 ) { strTmp = SqlSelect + " TOP " + _PageSize + " " + _fldName + " FROM " + _tbName + " ORDER BY " + _fldSort + " " + strFSortType; } else { strTmp = SqlSelect + " TOP " + _PageSize + " " + _fldName + " FROM " + _tbName + " WHERE " + _ID + " <(SELECT MIN( " + _ID + " ) FROM ( " + SqlSelect + " TOP " + _PageSize * (_Page - 1 ) + " " + _ID + " FROM " + _tbName + " ORDER BY " + _fldSort + " " + strFSortType + " ) AS TBMinID) ORDER BY " + _fldSort + " " + strFSortType; } } 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 " + _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 + " ) AS TBMaxID) ORDER BY " + _fldSort + " " + strSortType + " ) AS TempTB ORDER BY " + _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 " + _fldSort + " " + strFSortType; } else { strTmp = 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 + " ) AS TBMaxID) " + _strCondition + " ORDER BY " + _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 " + _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 + " ) AS TBMaxID) " + _strCondition + " ORDER BY " + _fldSort + " " + strSortType + " ) AS TempTB ORDER BY " + _fldSort + " " + strFSortType; } } } return strTmp; } }} //-- 以上代码是针对之前写的TOP MAX模式的分页存储过程修改 //-- 以上分页算法对SQL SERVER 和 ACCESS同样有效
