《数据分页_双top二分法》由会员分享,可在线阅读,更多相关《数据分页_双top二分法(4页珍藏版)》请在金锄头文库上搜索。
1、using System; using System.Collections.Generic; using System.Text;/ / 构造分页后的 SQL 语句 / public static class PagingHelper / / 获取分页 SQL 语句,排序字段需要构成唯一记录/ / 记录总数/ 每页记录数/ 当前页数/ SQL 查询语句/ 排序字段,多个则用“,”隔开/ 分页 SQL 语句public static string CreatePagingSql(int _recordCount, int _pageSize, int _pageIndex, string _s
2、afeSql, string _orderField)/重新组合排序字段,防止有错误string arrStrOrders = _orderField.Split(new char , , StringSplitOptions.RemoveEmptyEntries);StringBuilder sbOriginalOrder = new StringBuilder(); /原排序 字段StringBuilder sbReverseOrder = new StringBuilder(); /与原排 序字段相反,用于分页for (int i = 0; i 0)/替换升降标识,分页所需bool fl
3、ag = arrStrOrdersi.IndexOf(“ DESC“, StringComparison.OrdinalIgnoreCase) != -1;sbReverseOrder.AppendFormat(“0 1“, arrStrOrdersi.Remove(index), flag ? “ASC“ : “DESC“);elsesbReverseOrder.AppendFormat(“0 DESC“, arrStrOrdersi);/计算总页数_pageSize = _pageSize = 0 ? _recordCount : _pageSize;int pageCount = (_r
4、ecordCount + _pageSize - 1) / _pageSize;/检查当前页数if (_pageIndex pageCount)_pageIndex = pageCount;StringBuilder sbSql = new StringBuilder();/第一页时,直接使用 TOP n,而不进行分页查询if (_pageIndex = 1)sbSql.AppendFormat(“ SELECT TOP 0 * “, _pageSize);sbSql.AppendFormat(“ FROM (0) AS T “, _safeSql);sbSql.AppendFormat(“
5、ORDER BY 0 “, sbOriginalOrder.ToString();/最后一页时,减少一个 TOP n else if (_pageIndex = pageCount)sbSql.Append(“ SELECT * FROM “);sbSql.Append(“ ( “);sbSql.AppendFormat(“ SELECT TOP 0 * “, _recordCount - _pageSize * (_pageIndex - 1);sbSql.AppendFormat(“ FROM (0) AS T “, _safeSql);sbSql.AppendFormat(“ ORDER
6、 BY 0 “, sbReverseOrder.ToString();sbSql.Append(“ ) AS T “);sbSql.AppendFormat(“ ORDER BY 0 “, sbOriginalOrder.ToString();/前半页数时的分页else if (_pageIndex / 获取记录总数 SQL 语句/ / 限定记录数/ SQL 查询语句/ 记录总数 SQL 语句public static string CreateTopnSql(int _n, string _safeSql)return string.Format(“ SELECT TOP 0 * FROM (1) AS T “, _n, _safeSql);/ / 获取记录总数 SQL 语句/ / SQL 查询语句/ 记录总数 SQL 语句public static string CreateCountingSql(string _safeSql)return string.Format(“ SELECT COUNT(1) AS RecordCount FROM (0) AS T “, _safeSql);