sql 数据的分页 自称完美的实现

数据的分页 留下该函数，朋友们看懂应该没什么问题，如果有疑问留言，会在第一时间内帮你解决。注释的部分在速度和版本上都会有问题，没有注释的sql 是通用的，最起码在sql server  系列上是通用的。请大家拍砖我的 数据的分页 

        /// <summary>        /// 数据的分页  分页WithCommand        /// </summary>        /// <param name="cmd">执行命令 该命令必须指定了SQL语句</param>        /// <param name="PageIndex">页数</param>        /// <param name="PageSize">页大小</param>        /// <param name="OrderBy">排序字段 比如 ： ID DESC </param>         /// <param name="TotalCount">返回总记录数</param>        /// <returns>datatable</returns>        public static DataTable PagerWithCommand(SqlCommand cmd, int PageIndex, int PageSize, string OrderBy, ref int TotalCount)        {            //变量定义            StringBuilder sbSql = new StringBuilder();            //第一页时            if (PageIndex == 1)            {                sbSql.Append("SELECT TOP " + PageSize + " *  FROM (" + cmd.CommandText + ") as tempTable Order by " + OrderBy + ";");            }            else            {                // sql 2005  数据的分页 版本以上才可以用                //  sbSql.Append("SELECT TOP " + PageSize * PageIndex + " *  FROM (" + cmd.CommandText + ") AS tempTable except select top " + PageSize * (PageIndex - 1) + " * from (" + cmd.CommandText + ") AS  tempTable Order by " + OrderBy + ";");                // sql 2005 数据的分页  以上才能使用，使用row_number() 速度比较慢                 //sbSql.AppendLine(" select * from                                             ");                 //sbSql.AppendLine("  (select row_number() over (order by  "+ OrderBy +" ) as RowID, *");                 //sbSql.AppendLine("   from  (" + cmd.CommandText + ") as bb ) as aa ");                 //sbSql.AppendLine("  where  aa.RowID between " + (PageIndex - 1) * PageSize + "  and " + PageIndex *PageSize + ";");                // sql 2000 数据的分页  版本以上均可  适合数据量比较小                //sbSql.AppendLine(@" select   identity(int,   1,   1)   as   id_PageRowID,   *   into   #tmpPageTable   from   ( "                //                   +   cmd.CommandText   +  ")   table_TempPage   ;");                //sbSql.AppendLine(@"select   top   " +   PageSize   +   "   *   from   #tmpPageTable   where   id_PageRowID   between    "                //                   + (PageIndex - 1) * PageSize  +     "+ 1  and    " + PageIndex * (PageSize ) + "  ;");                //sbSql.AppendLine(@" drop   table   #tmpPageTable;");                // sql 2000 版本以上  使用排序                sbSql.AppendLine(" select * from                                             ");                sbSql.AppendLine(" (select top " + PageSize + " *  from                      ");                sbSql.AppendLine(" (select top " + PageIndex * (PageSize) + "  *  ");                sbSql.AppendLine(" from (  " + cmd.CommandText + "  ) a  order by " + OrderBy + ")b  order by " + GetReverseOrder(OrderBy) + ") c ");                sbSql.AppendLine(" order by " + OrderBy + " ");            }            sbSql.Append(" SELECT @TotalRecord = count(*) FROM (" + cmd.CommandText + ") tempTable");            //重新设置命令SQL语句            cmd.CommandText = sbSql.ToString();            //总记录数            SqlParameter param  = new SqlParameter("@TotalRecord", SqlDbType.Int);            param.Direction = ParameterDirection.Output;            cmd.Parameters.Add(param);            DataTable dtTemp = ExecuteSearch(cmd);            TotalCount = (int)cmd.Parameters["@TotalRecord"].Value;            return dtTemp;        }        /// <summary>        /// 获得反序 .        /// </summary>        /// <param name="orderBy">排序字段.</param>        /// <returns>获得反序的排序字段</returns>        static string GetReverseOrder(string orderBy)        {            orderBy = orderBy.ToLower();            if (orderBy.Contains(" asc"))            {                orderBy = orderBy.Replace(" asc", " desc");            }            else if (orderBy.Contains(" desc"))            {                orderBy = orderBy.Replace(" desc", " asc");            }            else            {                orderBy = orderBy + " desc";            }            return orderBy;        }

上述分页存在一些问题，当查询到最后一页时，不管最后一页数据够不够PageSize条记录，那么查询出的数据总是PageSize条记录，不合理是吧。那么修订的方案如下：

 /// <summary>        /// 分页WithCommand        /// </summary>        /// <param name="cmd">执行命令 该命令必须指定了SQL语句</param>        /// <param name="PageIndex">页数</param>        /// <param name="PageSize">页大小</param>        /// <param name="OrderBy">排序字段 比如 ： ID DESC </param>         /// <param name="TotalCount">返回总记录数</param>        /// <returns>datatable</returns>        public static DataTable PagerWithCommand(SqlCommand cmd, int PageIndex, int PageSize, string OrderBy, ref int TotalCount)        {            //变量定义            StringBuilder sbSql = new StringBuilder();            // 先计算总记录数          sbSql.AppendLine(" SELECT @TotalRecord = count(*) FROM (" + cmd.CommandText + ") tempTable ;");            //第一页时            if (PageIndex == 1)            {                sbSql.Append(" SELECT TOP (@PageSize) *  FROM (" + cmd.CommandText + ") as tempTable Order by " + OrderBy + ";");            }            else            {                // sql 2005 版本以上才可以用                //  sbSql.Append("SELECT TOP " + PageSize * PageIndex + " *  FROM (" + cmd.CommandText + ") AS tempTable except select top " + PageSize * (PageIndex - 1) + " * from (" + cmd.CommandText + ") AS  tempTable Order by " + OrderBy + ";");                // sql 2000 版本以上均可  适合数据量比较小                //sbSql.AppendLine(@" select   identity(int,   1,   1)   as   id_PageRowID,   *   into   #tmpPageTable   from   ( "                //                   +   cmd.CommandText   +  ")   table_TempPage   ;");                //sbSql.AppendLine(@"select   top   " +   PageSize   +   "   *   from   #tmpPageTable   where   id_PageRowID   between    "                //                   + (PageIndex - 1) * PageSize  +     "+ 1  and    " + PageIndex * (PageSize ) + "  ;");                //sbSql.AppendLine(@" drop   table   #tmpPageTable;");                // sql 2000 版本以上  使用排序                sbSql.AppendLine(" select * from                                             ");                // 如果是最后一页数据条数 小于pagesize，那么只选择 实际数据的记录数                sbSql.AppendLine(" (select top (case when @TotalRecord < @PageIndex*@PageSize then @TotalRecord - (@PageIndex-1)*@PageSize  else @PageSize end) *  from          ");                sbSql.AppendLine(" (select top (@PageIndex*@PageSize)  *  ");                sbSql.AppendLine(" from (  " + cmd.CommandText + "  ) a  order by " + OrderBy + ")b  order by " + GetReverseOrder(OrderBy) + ") c ");                sbSql.AppendLine(" order by " + OrderBy + " ");            }            //重新设置命令SQL语句            cmd.CommandText = sbSql.ToString();            /* 将分页参数追加至SqlParameter */            //当前页            SqlParameter param = new SqlParameter("@PageIndex", SqlDbType.Int);            param.Value = PageIndex;            cmd.Parameters.Add(param);            //每页显示数            param = new SqlParameter("@PageSize", SqlDbType.Int);            param.Value = PageSize;            cmd.Parameters.Add(param);            //总记录数            param = new SqlParameter("@TotalRecord", SqlDbType.Int);            param.Direction = ParameterDirection.Output;            cmd.Parameters.Add(param);            DataTable dtTemp = ExecuteSearch(cmd);            TotalCount = (int)cmd.Parameters["@TotalRecord"].Value;            return dtTemp;        }

哈哈……