SQL分页存储过程的实现

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

SQL代码:

USE [test_db]GO/****** Object:  StoredProcedure [dbo].[SP_PageList]    Script Date: 05/19/2017 14:58:27 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER 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,到这里就结束了