系统列表分页的实现

设计思路

• 存储过程

          存储过程取得数据查询结果及记录总数

• 数据读取

          将返回的记录填充至 SDataSet 仅填充指定长度的数据记录

• 显示分页

          SDataSet 中提供方法可以根据自己的记录数量进行分页  

 

代码举例

• 用户查询存储过程

PROCEDURE [dbo].[ET_USER_LIST]
    @Key varchar(255) = null,
    @GradeId int = 0,
    @LevelId int = 0
AS
    --    声明变量
    Declare @SqlWhere varchar(255)
    Declare @ReturnSql nvarchar(255)
    Declare @ReturnValue int
    --    初始化变量
    Set @SqlWhere = '';
    Set @ReturnSql = '';
    --    关键字查询
    If @Key<>'' Begin
        Set @SqlWhere = ' Where (Username=''' + @Key + ''' Or Email=''' + @Key + ''' ) ';
    End
    Else
        Set @SqlWhere = ' Where UserId>0 ';
    --    是否检索分组
    If @GradeId>0
        Set @SqlWhere = @SqlWhere + ' And GradeId=' + lTrim(@GradeId) + ' ';
    --    是否检索分级
    If @LevelId>0
        Set @SqlWhere = @SqlWhere + ' And LevelId=' + lTrim(@LevelId) + ' ';
    --    统计记录总数
    Set @ReturnSql = 'Set @Return = (Select Count(*) From DB_USER ' + @SqlWhere + ')'
    exec ('Select * From DB_USER ' + @SqlWhere);
    exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
    Return @ReturnValue;

• 数据获取

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="Procedure">string 存储过程的名称</param>
        /// <param name="PageId">int 页码</param>
        /// <param name="PageSize">int 页尺寸</param>
        /// <param name="Params">SqlParameter[] 存储过程所需参数</param>
        /// <param name="Data">SDataSet 返回数据</param>
        /// <returns>int 返回值</returns>
        public int RunProc(string Procedure, int PageId, int PageSize, SqlParameter[] Params, ref SDataSet Data)
        {
            int _RecordStart = ((PageId - 1) * PageSize);
            SqlCommand _Comd = CreateCommand(Procedure, Params, CommandType.StoredProcedure);
            SqlDataAdapter _Adap = new SqlDataAdapter(_Comd);
            _Adap.Fill(Data, _RecordStart, PageSize, "Table");

            this.Close();
            return (int)_Comd.Parameters["ReturnValue"].Value;
        }

• 分页代码       

    public class SDataSet : DataSet
    {
        /// <summary>
        /// 获取分页代码
        /// </summary>
        /// <param name="PageId">int 当前页码</param>
        /// <param name="PageSize">int 页码尺寸</param>
        public int[] GetPages(int PageId, int PageSize)
        {
            ArrayList _Result = new ArrayList();
            int _PageId = PageId > 0 ? PageId : 1;
            int _PageSize = PageSize > 0 ? PageSize : 12;
            int _PageCount = (int)Math.Ceiling((double)_RecordCount / (double)_PageSize);
            int _PagePrevious = ((_PageId - 1 < 1) ? -1 : (_PageId - 1 > _PageCount ? _PageCount : _PageId - 1));
            int _PageNext = (_PageId + 1 > _PageCount ? -1 : (_PageId + 1 < 1 ? 1 : _PageId + 1));
            int _PageLeft = _PageId - 4;
            int _PageRight = _PageId + 4;
            if (_PageCount - _PageId < 7)
            {
                _PageLeft = _PageId - (10 - (_PageCount - _PageId));
                _PageRight = _PageCount;
            }
            if (_PageId < 7)
            {
                _PageLeft = 1;
                _PageRight = _PageId + (10 - (_PageId - 1));
            }
            _Result.Add(_PagePrevious);
            if (_PageLeft > 1)
            {
                _Result.Add(1);
                _Result.Add(-2);
            }
            for (int i = _PageLeft; i <= _PageRight; i++)
            {
                if (i <= _PageCount && i > 0)
                {
                    _Result.Add(i);
                }
            }
            if (_PageRight < _PageCount)
            {
                _Result.Add(-2);
                _Result.Add(_PageCount);
            }
            _Result.Add(_PageNext);
            return (int[])_Result.ToArray(typeof(int));
        }
        #endregion
    }