通用分页存储过程(sqlServer)

参照网上的一些别人写的存储过程，并修改了一下以方便使用。

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pagination]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@RecordCount int out, -- 返回记录总数
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @tmpSQLCount nvarchar(2000)

if @strWhere !=''
    set @tmpSQLCount = 'select @spIntRecordCount=count(*) from [' + @tblName + '] where '+@strWhere
else
    set @tmpSQLCount = 'select @spIntRecordCount=count(*) from [' + @tblName + ']'
EXECUTE SP_EXECUTESQL
            @tmpSQLCount,
            N'@spIntRecordCount int OUTPUT',
            @spIntRecordCount = @RecordCount OUTPUT　

if @OrderType != 0
    begin
    set @strTmp = '<(select min'
    set @strOrder = ' order by [' + @fldName +'] desc'
    --如果@OrderType不是0，就执行降序，这句很重要！
    end
else
    begin
    set @strTmp = '>(select max'
    set @strOrder = ' order by [' + @fldName +'] asc'
    end

if @PageIndex = 1
    begin
    if @strWhere != ''
    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
    else
    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
    --如果是第一页就执行以上代码，这样会加快执行速度
    end
else
    begin
    --以下代码赋予了@strSQL以真正执行的SQL代码
    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder
    if @strWhere != ''
        set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
        + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
        + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
    end
    

exec (@strSQL)

C#代码：

      public PageDataTable GetList(string fields, string fldName, int pageSize, int pageIndex, bool orderType, string where)
        {
            PageDataTable pageTable = new PageDataTable();
            string procedurName = "pagination";
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@tblName",SqlDbType.VarChar,255);
            param[1] = new SqlParameter("@strGetFields",SqlDbType.VarChar,1000);
            param[2] = new SqlParameter("@fldName",SqlDbType.VarChar,255);
            param[3] = new SqlParameter("@PageSize",SqlDbType.Int);
            param[4] = new SqlParameter("@PageIndex",SqlDbType.Int);
            param[5] = new SqlParameter("@RecordCount", SqlDbType.Int);
            param[6] = new SqlParameter("@OrderType",SqlDbType.Bit);
            param[7] = new SqlParameter("@strWhere",SqlDbType.VarChar,1500);
            param[0].Value = "product";
            param[1].Value = fields;
            param[2].Value = fldName;
            param[3].Value = pageSize;
            param[4].Value = pageIndex;
            param[5].Direction = ParameterDirection.Output;
            param[6].Value = orderType;
            param[7].Value = where;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procedurName;
                foreach (SqlParameter p in param)
                    cmd.Parameters.Add(p);
                
                SqlDataReader reader = cmd.ExecuteReader();
                DataTable table = new DataTable();
                table.Load(reader);

                int recordCount = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
                pageTable.dt = table;
                pageTable.recordCount = recordCount;

            }
            return pageTable;
        }

PageDataTable类

      public class PageDataTable
    {
        public int recordCount { get; set; }
        public DataTable dt { get; set; }
    }

页面使用：

            string strGetFields = "productId,productName,productName_en";
            string fldName = "productId";
            bool orderType = true;
            string strWhere = "productName like '%凱沙%'";
            PageDataTable table = new ProductDAO().GetList(strGetFields, fldName, AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, orderType, strWhere);
            rProductList.DataSource = table.dt;
            rProductList.DataBind();
            AspNetPager1.RecordCount = table.recordCount;

基本AspNetPager1为AspNetPager控件