SQL Server数据库 分页

一、分页

方法一：

 /// <summary>
        /// 获得分页后的数据列表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="strWhere">筛选条件</param>
        /// <param name="strOrder">排序条件</param>
        /// <param name="startIndex">开始的索引</param>
        /// <param name="endIndex">结束的索引</param>
        /// <returns></returns>
        public DataSet GetListByPaged(string tableName, string strWhere, string strOrder, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(strOrder.Trim()))
            {
                strSql.Append("order by T." + strOrder);
            }
            strSql.Append(")AS num, T.*  from " + tableName + " T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.num between {0} and {1}", startIndex, endIndex);
            return DbManagerSQL.Query(strSql.ToString());
        }

方法二：

        /// <summary>
        /// 分页方法
        /// Created by roc,2009/07/11
        /// </summary>
        /// <param name="Tables">表名,多表联合查询请使用 tA a inner join tB b On a.AID = b.AID</param>
        /// <param name="PK">主键，可以带表头 a.AID</param>
        /// <param name="Sort">排序字段</param>
        /// <param name="PageNumber">开始页码</param>
        /// <param name="PageSize">页大小</param>
        /// <param name="Fields">读取字段</param>
        /// <param name="Filter">Where条件</param>
        /// <param name="Group">分组</param>
        /// <param name="IsCount">是否获得总记录数,0表示不获得记录总数,1表示获得分页数据的同时获得记录总数,2表示仅获得记录总数</param>
        /// <returns>分页结果集</returns>
        public static DataSet GetRecordByPage_1_0(string Tables, string PK, string Sort, int PageNumber, int PageSize, string Fields, string Filter, string Group, int IsCount)
        {
            //获得命令
            //string sqlCommand = "Pg_Paging";
            //DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

            SqlParameter[] parameters = {
                    new SqlParameter("@Tables", SqlDbType.VarChar, 1000),
                    new SqlParameter("@PK", SqlDbType.VarChar, 100),
                    new SqlParameter("@Sort", SqlDbType.VarChar,100),
                    new SqlParameter("@PageNumber",SqlDbType.Int),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@Fields", SqlDbType.VarChar,1000),
                    new SqlParameter("@Filter", SqlDbType.VarChar,1000),
                    new SqlParameter("@Group", SqlDbType.VarChar,1000),
                    new SqlParameter("@isCount", SqlDbType.Int)
                    };
            parameters[0].Value = Tables;
            parameters[1].Value = PK;
            parameters[2].Value = Sort;
            parameters[3].Value = PageNumber;
            parameters[4].Value = PageSize;
            parameters[5].Value = Fields;
            parameters[6].Value = Filter;
            parameters[7].Value = Group;
            parameters[8].Value = IsCount;

            return RunProcedure("P_GetRecordByPage_1_0", parameters, "ds");
        }


    #region 存储过程操作



        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, "DataTable");
                connection.Close();
                return dataSet;
            }
            finally
            {
                connection.Dispose();
            }
        } 

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
        {
            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 );
                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)
            {
                command.Parameters.Add( parameter );
            }
            return command;            
        }
        /// <summary>
        /// 执行存储过程，返回影响的行数        
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection,storedProcName, parameters );
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                return result;
            }
        }

        /// <summary>
        /// CDW 扩展可以用外面的数据库对串联接
        /// </summary>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="command">SQLCommon对像</param>
        /// <param name="rowsAffected">返回影响数据条数</param>
        /// <returns>返回值</returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters,SqlCommand command, out int rowsAffected)
        {
            int result;
            if (command.Connection != null && command.Connection.State == ConnectionState.Closed)
                command.Connection.Open();
            CommandType oldcommandtype = command.CommandType;
            if (oldcommandtype != CommandType.StoredProcedure)
                command.CommandType = CommandType.StoredProcedure;
            command.CommandText = storedProcName;

            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));

            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            rowsAffected = command.ExecuteNonQuery();
            result = (int)command.Parameters["ReturnValue"].Value;

            if (oldcommandtype != CommandType.StoredProcedure)
                command.CommandType = oldcommandtype;
            return result;

        }


        public static string RunProcedureScalar(string storedProcName, IDataParameter[] parameters, string retName)
        {
            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                command.ExecuteNonQuery();
                return System.Convert.ToString(command.Parameters[retName].Value);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            finally
            {
                connection.Dispose();
            }

        } 

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)    
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection,storedProcName, parameters );
            command.Parameters.Add( new SqlParameter ( "ReturnValue",
                SqlDbType.Int,4,ParameterDirection.ReturnValue,
                false,0,0,string.Empty,DataRowVersion.Default,null ));
            return command;
        }
        #endregion    

存储过程：

create PROCEDURE [dbo].[P_GetRecordByPage_1_0]
    @Tables varchar(3000),        --表名,多表联合查询请使用 tA a inner join tB b On a.AID = b.AID
    @PK varchar(100),            --主键，可以带表头 a.AID
    @Sort varchar(100) = '',    --排序字段
    @PageNumber int = 1,        --开始页码
    @PageSize int = 20,            --页大小
    @Fields varchar(3000) = '*',--读取字段
    @Filter varchar(1000) = NULL,--Where条件
    @Group varchar(1000) = NULL,--分组
    @IsCount int = 0            --是否获得总记录数,0表示不获得记录总数,1表示获得分页数据的同时获得记录总数,2表示仅获得记录总数
AS
declare @strFilter varchar(2000)
declare @sql varchar(8000)
declare @strTotal varchar(6000)
declare @grpCountSql varchar(6000)        /*存在分组数据统计总数时的sql处理*/
set @grpCountSql = ''

IF((@Filter IS NOT NULL) AND (@Filter != ''))
BEGIN
    SET @strFilter = ' WHERE ' + @Filter + ' '
END
ELSE
BEGIN
    SET @strFilter = ''
END

if(@IsCount != 2)
begin
    if @Sort = ''
    begin
        set @Sort = @PK-- + ' DESC '
    end

    IF @PageNumber < 1
    begin
        SET @PageNumber = 1
    end

--    if @PageNumber = 1 --第一页提高性能
--    begin 
-- top 跟 BETWEEN 取出来的数据有可能不一样，导致CWT\行旅 UATP客户设置展示不全
--        set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ '  from ' + @Tables + ' ' + @strFilter + @Group + ' ORDER BY  '+ @Sort
--    --    print @sql
----        print 'assss'
----        return
--    end 
--    else
--    begin
        /**//*Execute dynamic query*/    
        DECLARE @START_ID varchar(50)
        DECLARE @END_ID varchar(50)
        SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
        SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)

        set @sql =  ' SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, '
            +@Fields+ ' FROM '+@Tables+ @strFilter + @Group + ') AS D where rownum BETWEEN '+@START_ID
            +' AND ' +@END_ID
    exec(@sql)
end
/*分组时的记录总数*/
set @grpCountSql = 'select count(*) from (' + 'select '+@Fields+ '  from ' + @Tables + ' ' + @strFilter + @Group + ') grpTb'
if((@IsCount = 1) or (@IsCount = 2)) --是否获得记录条数
begin
    set @strTotal = 'SELECT  Count(*) FROM ' + @Tables + @strFilter
    if(len(@Group) > 0)
    begin
        set @strTotal = @grpCountSql
    end
    exec(@strTotal)
end