数据库分页查询(Sql封装及存储过程)

一、存储过程实现

--分页存储过程-实现
CREATE PROCEDURE [dbo].[Pro_QueryPageTable]
(
@Select nvarchar(255), --查询关键字
@TableName nvarchar(30),--表名称
@PageIndex int, --当前页码
@PageSize int, --页容量
@Where nvarchar(255), --条件
@OrderBy nvarchar(255), --排序


@RowCount int OUTPUT,--输出总行数
@PageCount int OUTPUT--输出总页数
)
AS


DECLARE @strSql nvarchar(255), --最终查询语句
@strSqlCount nvarchar(255), --记录数查询语句
@StartRow int, --记录起始行
@EndRow int --记录结束行


IF(@PageIndex < 1)
SET @PageIndex=1
IF(@PageSize < 1)
SET @PageSize=7
--查询记录数量
SET @strSqlCount='select @RowCount=count(*),@PageCount=CEILING((COUNT(*)+0.0)/'+CAST(@PageSize AS varchar)+') from '+@TableName+' where '+@Where
EXEC sys.sp_executesql @strSqlCount,N'@RowCount INT OUTPUT,@PageCount INT OUTPUT',@RowCount OUTPUT,@PageCount OUTPUT
--查询记录
SET @StartRow=(@PageIndex - 1) * @PageSize + 1
SET @EndRow=@PageIndex * @PageSize
SET @strSql = 'select * from (select '+@Select+',row_number() over (order by '+@OrderBy+') as RowNum from '+@TableName+' as C) as s where s.RowNum between '+CAST(@StartRow AS varchar)+'and '+CAST(@EndRow AS varchar)
--执行查询
EXEC(@strSql)

--存储过程使用方法1
--DECLARE @RowCount int,@PageCount int,@return_value int
--EXEC Pro_QueryPageTable
--@Select='*', @TableName='T_Category', @PageIndex=1, 
--@PageSize=5, @Where='1=1', @OrderBy='id desc', 
--@RowCount = @RowCount OUTPUT,@PageCount = @PageCount OUTPUT
--SELECT @RowCount as N'@RowCount',@PageCount as N'@PageCount'


--存储过程使用方法2
--DECLARE @RowCount int,@PageCount int,@return_value int


--EXEC Pro_QueryPageTable
--'*', 'T_Category',1, 5, '1=1','id desc', @RowCount OUTPUT,@PageCount OUTPUT


--SELECT @RowCount as N'@RowCount',@PageCount as N'@PageCount'

二、Sql封装实现

public static DataTable QueryPageTable(string Select, string Table, string Query, string Order,ref PageInfo pageInfo, params SqlParameter[] Paras)
        {
            DataTable dt = new DataTable();
            //记录总条数
            string strCount = string.Format("select count(1) from {0} where {1}", Table, Query);
            int nRecordCount = Convert.ToInt32(SqlHelper.QuerySingle(strCount, Paras));


            pageInfo.recordCount = nRecordCount;
            pageInfo.pageNum = (nRecordCount-1) / pageInfo.pageSize + 1;
            //返回当前页数据
            if (pageInfo.curPageNum > 0)
            {
                SqlParameter[] arry = new SqlParameter[2 + Paras.Length];
                Paras.CopyTo(arry, Paras.Length);
                arry[Paras.Length] = new SqlParameter("StartIndex", (pageInfo.curPageNum - 1) * pageInfo.pageSize + 1);
                arry[Paras.Length + 1] = new SqlParameter("EndIndex", pageInfo.curPageNum * pageInfo.pageSize);


                string strSql = string.Format("select * from (select {0},row_number() over (order by {1}) as RowNum from {2} as C) as s where s.RowNum between @StartIndex and @EndIndex", Select, Order, Table);
                dt = SqlHelper.QueryTable(strSql, arry);
            }
            return dt;
        }

  注：PageInfo 是个分页信息类

public class PageInfo
    {
        /// <summary>
        /// 记录数
        /// </summary>
        public int recordCount { get; set; }

        /// <summary>
        /// 记录总页数
        /// </summary>
        public int pageNum { get; set; }

        /// <summary>
        /// 页容量
        /// </summary>
        public int pageSize { get; set; }

        /// <summary>
        /// 当前页
        /// </summary>
        public int curPageNum { get; set; }
    }

 注：SqlHelper.QueryTable是个封装好的查询表的函数。传入Sql语句、及参数列表进行查询