SQL分页存储过程的实现

目前,网上的好多sql分页都是转载的,然而在实际使用过程中竟然有错误,转载也请负责点嘛,好多都说自己是百万级的分页实现,然而实际使用时,可能第二页数据显示就会有问题,我来一篇真正验证过的分页储存过程,需要的可以直接使用.

SQL代码:

USE [test_db]
GO
/****** Object:  StoredProcedure [dbo].[SP_PageList]    Script Date: 05/19/2017 14:58:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_PageList]
(
 @tableName varchar(50),         --表名
 @fieldName varchar(200) = '*',  --字段名(全部字段为*)
 @sqlOrder varchar(200),         --排序字段(必须!支持多字段不用加order by)
 @sqlWhere varchar(500) =N'',    --条件语句(不用加where)
 @pageSize int,                  --每页多少条记录
 @pageIndex int = 1 ,            --指定当前为第几页
 @totalCount int output          --返回总记录数
)
AS
  
BEGIN    
 
    --处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(2000);    
    set @StartRecord = (@pageIndex-1)*@pageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1 
    SET @TotalCountSql= N'select @totalCount = count(*) from ' + @tableName;--总记录数语句
    SET @SqlString = N'(select row_number() over (order by '+ @sqlOrder +') as rowId,'+@fieldName+' from '+ @tableName;--查询语句
    --
    IF (@sqlWhere! = '' or @sqlWhere!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + '  where '+ @sqlWhere;
            SET @SqlString =@SqlString+ '  where '+ @sqlWhere;            
        END
    --第一次执行得到
    --IF(@totalCount is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N'@totalCount int out',@totalCount output;--返回总记录数
    --  END
    ----执行主语句
    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    Exec(@SqlString)    
END

C#后台调用:

/// <summary>
        /// 获得分页列表
        /// </summary>
        /// <param name="sqlWhere">查询参数</param>
        /// <param name="sqlOrder">排序字段DESC,ASC</param>
        /// <param name="totalCount">总条数</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="pageIndex">当前页码</param>
        /// <returns></returns>
        public DataSet GetPageList(string sqlWhere, string sqlOrder, out int totalCount, int pageSize = 14, int pageIndex = 1)
        {
            totalCount = 0;
            SqlParameter[] parameters = {
                new SqlParameter("@tableName", SqlDbType.NVarChar,4000),
                new SqlParameter("@fieldName",SqlDbType.NVarChar,50),
                new SqlParameter("@pageIndex",SqlDbType.Int),
                new SqlParameter("@pageSize",SqlDbType.Int),
                new SqlParameter("@sqlWhere",SqlDbType.NVarChar,8000),
                new SqlParameter("@sqlOrder",SqlDbType.NVarChar,4000),
                new SqlParameter("@totalCount", SqlDbType.Int)
                                        };
            parameters[0].Value = "view_toolAgeManage";
            parameters[1].Value = "*";
            parameters[2].Value = pageIndex;
            parameters[3].Value = pageSize;
            parameters[4].Value = sqlWhere;
            parameters[5].Value = sqlOrder;
            parameters[6].Direction = ParameterDirection.Output;

            return SQLDataBase.RunProcedure("SP_PageList", parameters, "tb", out totalCount);
        }


/// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="totalCount">总条数</totalCount>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, out int totalCount)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                totalCount = int.Parse(sqlDA.SelectCommand.Parameters["@totalCount"].Value.ToString());
                connection.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

OK,到这里就结束了