后台绑定数据源及初始化控件
编写一个读取分页数据函数,例如(里面读取和分页过程略):
public DataTable GetDataList(int pageSize, int pageIndex, out int records)
{
DataTable dt; //返回的数据集
//.... //(从数据库读取数据省略)
records = ...; //赋值给总记录数(省略)
return dt;
}
至于如何读取分页数据,建议用存储过程进行分页,给出一个支持多表查询(Left join等)的存储过程分页:
分页存储过程SQL
-- =============================================
-- Foolin收集(来自网上,佚名)
-- Create date: 2010-09-22
-- Description: 存储过程分页,支持分页查询
-- 调用:(排序ASC不可少)
-- exec Wb_Page 'select * from [YouTable] ', 20, 0, 'CreateTime ASC, Money DESC'
-- =============================================
CREATE PROCEDURE [dbo].[SP_Page]
(
@Sql nvarchar(1024), --查询语句
@Sort nvarchar(100) = '', --排序字段
@PageSize int = 20, --分页大小
@PageIndex int = 1, --分页索引
@TotalCount int = 0 output --总数
)
AS
-- 值默认值
if (IsNUll(@PageSize,0)=0)
Set @PageSize=20
if (IsNull(@PageIndex,0)=0)
Set @PageIndex=1
set nocount on
/*声明查询字符串*/
declare @strSQL nvarchar(4000)
set @strSQL = ' select @TotalCount=count(*) from ('+@Sql+') as t '
/*取得查询结果总数*/
exec sp_executesql
@strSQL,
N'@TotalCount int=0 OUTPUT',
@TotalCount=@TotalCount OUTPUT
declare @ItemCount int
declare @_PageIndex int
set @_PageIndex = @PageIndex; --索引从1开始
--set @_PageIndex = @PageIndex + 1; --索引从0开始
/*确定搜索边界*/
set @ItemCount = @TotalCount - @PageSize * @_PageIndex
if(@ItemCount < 0)
set @ItemCount = @ItemCount + @PageSize
else
set @ItemCount = @PageSize
if(@ItemCount < 0) return 1
if(@Sort != '')
begin
/*声明排序变量*/
declare @IndexSort1 nvarchar(50), @IndexSort2 nvarchar(50), @Sort1 nvarchar(50), @Sort2 nvarchar(50)
SET @Sort1 = @Sort
SET @Sort2 = Replace(Replace(Replace(@Sort, 'DESC', '@SORT'), 'ASC', 'DESC'), '@SORT', 'ASC')
set @strSQL = 'SELECT * FROM
(SELECT TOP ' + STR(@ItemCount) + ' * FROM
(SELECT TOP ' + STR(@PageSize * @_PageIndex) + ' * FROM
('+@Sql+') AS t0
ORDER BY '+@Sort1 +') AS t1
ORDER BY '+@Sort2 +') AS t2
ORDER BY ' +@Sort
end
else
begin
set @strSQL = 'SELECT * FROM
(SELECT TOP ' + STR(@ItemCount) + ' * FROM
(SELECT TOP ' + STR(@PageSize * @_PageIndex) + ' * FROM
('+@Sql+') As t0)
aS t1)
AS t2'
end
exec sp_executesql
@strSQL
后台aspx.cs页面绑定Repeater数据源、初始化控件:
1 int records = 0; //定义输出全部记录数变量
2 repList.DataSource = GetDataList(Pager1.PageSize, Pager1.PageIndex, out records); //获取数据源
3 repList.DataBind(); //绑定数据
4 Pager1.SetPage(totalCount); //初始化分页,总记录数作为参数传入。