博客开张,先发以前的几个老物件儿,以前写下来的,现在发上来权当记录和分享。这个类是用来生成分页SQL的,记得那时思考写一个只传一条查询语句就能生成分页SQL的方法,然后发现了双TOP分页法,不过双TOP法在后半页就很慢,后来一个同学发过来的一篇文章:2分法-通用存储过程分页(top max模式)版本(性能相对之前的not in版本极大提高)通过它,发现了还二分法这东东,甚感神奇,不过它是用MAX的方式,逐改为双TOP法。
代码如下:
- view sourceprint?using System;
- using System.Collections.Generic;
- using System.Text;
- /// <summary>
- /// 构造分页后的SQL语句
- /// </summary>
- public static class PagingHelper
- {
- /// <summary>
- /// 获取分页SQL语句,排序字段需要构成唯一记录
- /// </summary>
- /// <param name="_recordCount">记录总数</param>
- /// <param name="_pageSize">每页记录数</param>
- /// <param name="_pageIndex">当前页数</param>
- /// <param name="_safeSql">SQL查询语句</param>
- /// <param name="_orderField">排序字段,多个则用“,”隔开</param>
- /// <returns>分页SQL语句</returns>
- public static string CreatePagingSql(int _recordCount, int _pageSize, int _pageIndex, string _safeSql, string _orderField)
- {
- //重新组合排序字段,防止有错误
- string[] arrStrOrders = _orderField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
- StringBuilder sbOriginalOrder = new StringBuilder(); //原排序字段
- StringBuilder sbReverseOrder = new StringBuilder(); //与原排序字段相反,用于分页
- for (int i = 0; i < arrStrOrders.Length; i++)
- {
- arrStrOrders[i] = arrStrOrders[i].Trim(); //去除前后空格
- if (i != 0)
- {
- sbOriginalOrder.Append(", ");
- sbReverseOrder.Append(", ");
- }
- sbOriginalOrder.Append(arrStrOrders[i]);
- int index = arrStrOrders[i].IndexOf(" "); //判断是否有升降标识
- if (index > 0)
- {
- //替换升降标识,分页所需
- bool flag = arrStrOrders[i].IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) != -1;
- sbReverseOrder.AppendFormat("{0} {1}", arrStrOrders[i].Remove(index), flag ? "ASC" : "DESC");
- }
- else
- {
- sbReverseOrder.AppendFormat("{0} DESC", arrStrOrders[i]);
- }
- }
- //计算总页数
- _pageSize = _pageSize == 0 ? _recordCount : _pageSize;
- int pageCount = (_recordCount + _pageSize - 1) / _pageSize;
- //检查当前页数
- if (_pageIndex < 1)
- {
- _pageIndex = 1;
- }
- else 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(" 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 BY {0} ", sbReverseOrder.ToString());
- sbSql.Append(" ) AS T ");
- sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
- }
- //前半页数时的分页
- else if (_pageIndex < (pageCount / 2 + pageCount % 2))
- {
- sbSql.Append(" SELECT * FROM ");
- sbSql.Append(" ( ");
- sbSql.AppendFormat(" SELECT TOP {0} * FROM ", _pageSize);
- sbSql.Append(" ( ");
- sbSql.AppendFormat(" SELECT TOP {0} * ", _pageSize * _pageIndex);
- sbSql.AppendFormat(" FROM ({0}) AS T ", _safeSql);
- sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
- sbSql.Append(" ) AS T ");
- sbSql.AppendFormat(" ORDER BY {0} ", sbReverseOrder.ToString());
- sbSql.Append(" ) AS T ");
- sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
- }
- //后半页数时的分页
- else
- {
- sbSql.AppendFormat(" SELECT TOP {0} * FROM ", _pageSize);
- sbSql.Append(" ( ");
- sbSql.AppendFormat(" SELECT TOP {0} * ", ((_recordCount % _pageSize) + _pageSize * (pageCount - _pageIndex)));
- sbSql.AppendFormat(" FROM ({0}) AS T ", _safeSql);
- sbSql.AppendFormat(" ORDER BY {0} ", sbReverseOrder.ToString());
- sbSql.Append(" ) AS T ");
- sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
- }
- return sbSql.ToString();
- }
- /// <summary>
- /// 获取记录总数SQL语句
- /// </summary>
- /// <param name="_n">限定记录数</param>
- /// <param name="_safeSql">SQL查询语句</param>
- /// <returns>记录总数SQL语句</returns>
- public static string CreateTopnSql(int _n, string _safeSql)
- {
- return string.Format(" SELECT TOP {0} * FROM ({1}) AS T ", _n, _safeSql);
- }
- /// <summary>
- /// 获取记录总数SQL语句
- /// </summary>
- /// <param name="_safeSql">SQL查询语句</param>
- /// <returns>记录总数SQL语句</returns>
- public static string CreateCountingSql(string _safeSql)
- {
- return string.Format(" SELECT COUNT(1) AS RecordCount FROM ({0}) AS T ", _safeSql);
- }
- }
双TOP法相比于NOT IN和MAX法,就是可以传入一条SQL语句来生成分页SQL语句,也可多字段排序;但是有利也有弊,它要求排序字段必须构成唯一记录,且SELECT后的字段列表中,不允许出现与排序字段同名的字段。虽然SQL2K5及以上版本已经提供了ROWNUM()来进行分页处理,但是使用SQL2K进行开发的还是较多的。
原文链接:http://www.cnblogs.com/jitian/archive/2011/03/22/1991961.html
【编辑推荐】