设计思路
- 存储过程
存储过程取得数据查询结果及记录总数
- 数据读取
将返回的记录填充至 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
}