SQL通用分页查询存储过程

存储过程

    CREATE PROCEDURE [dbo].[QueryByPage]
    @TableName NVARCHAR(50),--表名
    @PageSize INT =10,--每次多少条
    @PageIndex INT =1,--第几页
    @Filter NVARCHAR(MAX) =NULL,--筛选条件
    @SortColumn NVARCHAR(MAX) =NULL,--按哪个字段排序，这个必须赋值
    @IsAsc BIT=1,--按ASC还是DESC排序,
    @ShowColumn NVARCHAR(MAX) ='*'--显示的列名字

    AS
    --用到的变量
    DECLARE @SQLStr NVARCHAR(MAX)   --最终执行的sql语句
    DECLARE @CountSqlStr NVARCHAR(MAX)  --获取总共条数的sql语句
    DECLARE @AscStr  NVARCHAR(100) --排序
    DECLARE @FilterStr NVARCHAR(MAX) --筛选
    DECLARE @Count INT --总共的条数

    BEGIN

    ---必传参数判断
    IF @TableName IS NULL OR  @TableName='' OR @SortColumn IS NULL OR @SortColumn=''
    BEGIN
        RETURN
    END

    --如果没有填写显示的列，默认显示全部
    IF @ShowColumn IS NULL OR @ShowColumn=''
    BEGIN 
    SET @ShowColumn='*'
    END 
    --1:ASC;0:DESC
    IF @IsAsc=1
    BEGIN
    SET @AscStr =@SortColumn+' ASC ';
    END
    ELSE 
    BEGIN
    SET @AscStr =@SortColumn+' DESC ';
    END

    --组合筛选的SQL
    IF @Filter IS  NULL OR @Filter =''
    BEGIN
    SET @FilterStr='';
    END
    ELSE
    BEGIN
    SET @FilterStr=' WHERE '+@Filter;
    END

    ---拼接分页查询的SQL
    SET  @SQLStr='SELECT '+@ShowColumn +' FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY  '
    +@AscStr+' ) AS NUM,'+@ShowColumn + ' FROM '+@TableName +@FilterStr +' )  AS T WHERE T.NUM BETWEEN ('
    +CONVERT(NVARCHAR,((@PageIndex-1)*@PageSize+1))+') AND ' +CONVERT(NVARCHAR,(@PageSize*@PageIndex));

    ---拼接查询总条数的SQL
    SET @CountSqlStr= ' SELECT @TEST=COUNT(*) FROM '+@TableName +@FilterStr  
    EXEC   sp_executesql   @CountSqlStr ,N'@TEST INT OUTPUT',@Count  OUTPUT  
    SELECT @Count AS Total;

    ---执行
    EXEC(@SQLStr)
    END

    GO

C#调用

        var sqlParas = new SqlParameter[7];
        sqlParas[0] = new SqlParameter("@ShowColumn", DbType.String)
        {
            Value = "Column1,Column2,Column3,Column4",
            Direction = System.Data.ParameterDirection.Input
        };
        sqlParas[1] = new SqlParameter("@TableName", DbType.String)
        {
            Value = "TableName",
            Direction = System.Data.ParameterDirection.Input
        };
        sqlParas[2] = new SqlParameter("@PageSize", DbType.Int32)
        {
            Value = pageSize,
            Direction = System.Data.ParameterDirection.Input
        };
        sqlParas[3] = new SqlParameter("@PageIndex", DbType.Int32)
        {
            Value = pageIndex,
            Direction = System.Data.ParameterDirection.Input
        };
        sqlParas[4] = new SqlParameter("@Filter", DbType.String)
        {
            //类似 :" USERID >= '100' AND TIME >= GETDATE()"
            Value = filterStr,
            Direction = System.Data.ParameterDirection.Input
        };
        sqlParas[5] = new SqlParameter("@SortColumn", DbType.String)
        {
            //按哪个字段排序，这个必须赋值
            Value = "SortColumn",
            Direction = System.Data.ParameterDirection.Input
        };
        sqlParas[6] = new SqlParameter("@IsAsc", DbType.Binary)
        {
            Value = 0,
            Direction = System.Data.ParameterDirection.Input
        };
        #endregion
        DBHelper dbhelper = new DBHelper();
        var ds = dbhelper.ExcuteDataByProcedure("QueryByPage", sqlParas);
        //总条数
        total = int.Parse(ds.Tables[0].Rows[0][0].ToString());
        //pageSize 和pageIndex 查出的值
        var dt = ds.Tables[1];