根据自己在项目过程中的总结,在此记录下这个比较高效的分页,是建立在三层架构的基础上的,现在贴出每层所写的代码。以下代码已经通过测试,没有问题。
实现的效果如下:
page.aspx:
Code
table width="100%" border="0" cellspacing="0" cellpadding="5" align="left">
<tr>
<td align="left">
共<span class="t13px_FF0000_B"><%=RowsCount%></span>首歌</td>
<td align="right"><%=PageIndex%> 页 / <%=MaxPage%> 页 | <%=RowsCount%> 条
<a href="javascript:;" onclick="<%=PageIndex<=1?"return false;":""%>goPager(1)" <%=PageIndex<=1?"disabled=disabled":""%>>首页</a> |
<a href="javascript:;" onclick="<%=PageIndex<=1?"return false;":""%>goPager(<%=PageIndex-1%>)" <%=PageIndex<=1?"disabled=disabled":""%>>上页</a> |
<a href="javascript:;" onclick="<%=PageIndex>=MaxPage?"return false;":""%>goPager(<%=PageIndex+1%>)" <%=PageIndex>=MaxPage?"disabled=disabled":""%>>下页</a> |
<a href="javascript:;" onclick="<%=PageIndex>=MaxPage?"return false;":""%>goPager(<%=MaxPage%>)" <%=PageIndex>=MaxPage?"disabled=disabled":""%>>尾页</a>
<select onchange="<%=MaxPage<=1?"return false;":""%>goPager(this.selectedIndex+1)" <%=MaxPage<=1?"disabled=disabled":""%>><%=SelectButton%></select>
<a href="javascript:;" ></a>
</td>
</tr>
</table>
table width="100%" border="0" cellspacing="0" cellpadding="5" align="left">
<tr>
<td align="left">
共<span class="t13px_FF0000_B"><%=RowsCount%></span>首歌</td>
<td align="right"><%=PageIndex%> 页 / <%=MaxPage%> 页 | <%=RowsCount%> 条
<a href="javascript:;" onclick="<%=PageIndex<=1?"return false;":""%>goPager(1)" <%=PageIndex<=1?"disabled=disabled":""%>>首页</a> |
<a href="javascript:;" onclick="<%=PageIndex<=1?"return false;":""%>goPager(<%=PageIndex-1%>)" <%=PageIndex<=1?"disabled=disabled":""%>>上页</a> |
<a href="javascript:;" onclick="<%=PageIndex>=MaxPage?"return false;":""%>goPager(<%=PageIndex+1%>)" <%=PageIndex>=MaxPage?"disabled=disabled":""%>>下页</a> |
<a href="javascript:;" onclick="<%=PageIndex>=MaxPage?"return false;":""%>goPager(<%=MaxPage%>)" <%=PageIndex>=MaxPage?"disabled=disabled":""%>>尾页</a>
<select onchange="<%=MaxPage<=1?"return false;":""%>goPager(this.selectedIndex+1)" <%=MaxPage<=1?"disabled=disabled":""%>><%=SelectButton%></select>
<a href="javascript:;" ></a>
</td>
</tr>
</table>
page.aspx.cs :
Code
private void Bind()
{
SR.Lib.Space.Model.Pager p = new SR.Lib.Space.Model.Pager();
p.PageIndex = PageIndex;//当前页
p.PageSize = 15;//每页中显示的数据数
p.TableName = "Space_Music";//用来分页的表名
p.Order = "AddTime";//排序的字段
p.OrderType = false;//排序规则
p.SelectStr = " [ID],[UserID],Song,Songer,SongType,[Format],SongPath,Click,AddTime
";//所要检索的数据字段
if (UserID != 0)
{
p.WhereCondition = " UserID=" + UserID;//检索条件
}
SR.Lib.Space.BLL.SpaceMusic music = new SR.Lib.Space.BLL.SpaceMusic();
RepSongList.DataSource = music.GetList(ref p);
RepSongList.DataBind();
this.maxPage = p.PageCount;//页总数
this.rowsCount = p.RecordCount;//总数据数
}
private int rowsCount;
protected int RowsCount
{
get { return rowsCount; }
set { rowsCount = value; }
}
private int maxPage;
protected int MaxPage
{
get { return maxPage; }
set { maxPage = value; }
}
protected int PageIndex
{
get { return My.QueryInt("page", 1); }
}
protected string SelectButton
{
get
{
string reStr = string.Empty;
for (int i = 1; i <= this.MaxPage; i++)
{
reStr += string.Format("<option value=\"{0}\" {1}>{0}</option>\n", i, i ==
this.PageIndex ? "Selected" : "");
}
return reStr;
}
}
private void Bind()
{
SR.Lib.Space.Model.Pager p = new SR.Lib.Space.Model.Pager();
p.PageIndex = PageIndex;//当前页
p.PageSize = 15;//每页中显示的数据数
p.TableName = "Space_Music";//用来分页的表名
p.Order = "AddTime";//排序的字段
p.OrderType = false;//排序规则
p.SelectStr = " [ID],[UserID],Song,Songer,SongType,[Format],SongPath,Click,AddTime
";//所要检索的数据字段
if (UserID != 0)
{
p.WhereCondition = " UserID=" + UserID;//检索条件
}
SR.Lib.Space.BLL.SpaceMusic music = new SR.Lib.Space.BLL.SpaceMusic();
RepSongList.DataSource = music.GetList(ref p);
RepSongList.DataBind();
this.maxPage = p.PageCount;//页总数
this.rowsCount = p.RecordCount;//总数据数
}
private int rowsCount;
protected int RowsCount
{
get { return rowsCount; }
set { rowsCount = value; }
}
private int maxPage;
protected int MaxPage
{
get { return maxPage; }
set { maxPage = value; }
}
protected int PageIndex
{
get { return My.QueryInt("page", 1); }
}
protected string SelectButton
{
get
{
string reStr = string.Empty;
for (int i = 1; i <= this.MaxPage; i++)
{
reStr += string.Format("<option value=\"{0}\" {1}>{0}</option>\n", i, i ==
this.PageIndex ? "Selected" : "");
}
return reStr;
}
}
存储过程:
Code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--GetPagingData 'Source','*','DigNum','ID',1,50,'UserName=''akisscn''and Type=0 and ClickNum>=3',0,0,0
ALTER PROCEDURE [dbo].[GetPagingData]
(
@tablename varchar(100),--表名或视图表
@fieldlist varchar(4000)='*',--欲选择字段列表
@orderfield varchar(100),--排序字段
@keyfield varchar(100),--主键
@pageindex int,--页号,从0开始
@pagesize int=20,--页尺寸
@strwhere varchar(4000),--条件
@ordertype bit=1,--排序,1,降序,0,升序
@RecordCount int out,
@PageCount int out
)
AS
/**//**//**//*
名称:GetPagingRecord
作用:按任意字段进行排序分页
作者:菩提树(MARK MA)
时间:2004-12-14
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明
*/
SET NOCOUNT ON
declare @sqlstr varchar(6000)
--处理SQL中危险字符,并且将条件处理成易嵌入的形式
set @sqlstr='declare @Rcount int;'
if @strWhere =''
set @strWhere ='1=1'
set @sqlstr=@sqlstr+'set @rcount=(select count('+@keyfield+') from '+@tablename+' where '+@strWhere+');'
set @strwhere=replace(@strwhere,'''','''''')
set @strwhere=replace(@strwhere,'--','')
set @strwhere=replace(@strwhere,';','')
set @sqlstr=@sqlstr+'declare @Rnum int;'
set @sqlstr=@sqlstr+'set @rnum=@rcount-'+cast(@pagesize as varchar)+'*'+cast(@pageindex as varchar)+';'
set @sqlstr=@sqlstr+'if(@rnum<0)set @rnum =@rcount;declare @sqlstr varchar(6000);'
if @ordertype=1
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100
percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' asc) as b order by '+@orderfield+' desc) as a order by '+@orderfield+' desc '';'
end
else
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100
percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' desc) as b order by '+@orderfield+' asc) as a order by '+@orderfield+' asc '';'
end
set @sqlstr=@sqlstr+'if @Rcount>0 begin execute(@sqlstr) end'
--print @sqlstr
execute(@sqlstr)
declare @strSelCount nvarchar(4000)
IF @strwhere <>'1=1'
Begin
set @strwhere = replace(@strwhere,'''''','''')
SET @strSelCount = 'SELECT -1 FROM ' + @tablename + ' Where '+@strwhere
End
ELSE
Begin
SET @strSelCount = 'SELECT -1 FROM ' + @tablename
End
--print @strSelCount
EXEC SP_EXECUTESQL @strSelCount
SET @RecordCount = @@RowCount
print @RecordCount
-- 获取总页数
-- "CEILING"函数:取得不小于某数的最小整数
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
print @PageCount
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--GetPagingData 'Source','*','DigNum','ID',1,50,'UserName=''akisscn''and Type=0 and ClickNum>=3',0,0,0
ALTER PROCEDURE [dbo].[GetPagingData]
(
@tablename varchar(100),--表名或视图表
@fieldlist varchar(4000)='*',--欲选择字段列表
@orderfield varchar(100),--排序字段
@keyfield varchar(100),--主键
@pageindex int,--页号,从0开始
@pagesize int=20,--页尺寸
@strwhere varchar(4000),--条件
@ordertype bit=1,--排序,1,降序,0,升序
@RecordCount int out,
@PageCount int out
)
AS
/**//**//**//*
名称:GetPagingRecord
作用:按任意字段进行排序分页
作者:菩提树(MARK MA)
时间:2004-12-14
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明
*/
SET NOCOUNT ON
declare @sqlstr varchar(6000)
--处理SQL中危险字符,并且将条件处理成易嵌入的形式
set @sqlstr='declare @Rcount int;'
if @strWhere =''
set @strWhere ='1=1'
set @sqlstr=@sqlstr+'set @rcount=(select count('+@keyfield+') from '+@tablename+' where '+@strWhere+');'
set @strwhere=replace(@strwhere,'''','''''')
set @strwhere=replace(@strwhere,'--','')
set @strwhere=replace(@strwhere,';','')
set @sqlstr=@sqlstr+'declare @Rnum int;'
set @sqlstr=@sqlstr+'set @rnum=@rcount-'+cast(@pagesize as varchar)+'*'+cast(@pageindex as varchar)+';'
set @sqlstr=@sqlstr+'if(@rnum<0)set @rnum =@rcount;declare @sqlstr varchar(6000);'
if @ordertype=1
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100
percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' asc) as b order by '+@orderfield+' desc) as a order by '+@orderfield+' desc '';'
end
else
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100
percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' desc) as b order by '+@orderfield+' asc) as a order by '+@orderfield+' asc '';'
end
set @sqlstr=@sqlstr+'if @Rcount>0 begin execute(@sqlstr) end'
--print @sqlstr
execute(@sqlstr)
declare @strSelCount nvarchar(4000)
IF @strwhere <>'1=1'
Begin
set @strwhere = replace(@strwhere,'''''','''')
SET @strSelCount = 'SELECT -1 FROM ' + @tablename + ' Where '+@strwhere
End
ELSE
Begin
SET @strSelCount = 'SELECT -1 FROM ' + @tablename
End
--print @strSelCount
EXEC SP_EXECUTESQL @strSelCount
SET @RecordCount = @@RowCount
print @RecordCount
-- 获取总页数
-- "CEILING"函数:取得不小于某数的最小整数
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
print @PageCount
数据连接层 DAL:
Code
public static DataTable SelectPage(ref SR.Lib.Space.Model.Pager pager)
{
using (SqlConnection myConnection = new SqlConnection(connectionString))
{
// 调用分页
SqlDataAdapter myCommand = new SqlDataAdapter("GetPagingData", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@tablename", SqlDbType.NVarChar));
myCommand.SelectCommand.Parameters["@tablename"].Value = pager.TableName;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@fieldlist", SqlDbType.NVarChar));
myCommand.SelectCommand.Parameters["@fieldlist"].Value = pager.SelectStr;
SqlParameter parameterOrder = new SqlParameter("@orderfield", SqlDbType.NVarChar);
parameterOrder.Value = pager.Order;
myCommand.SelectCommand.Parameters.Add(parameterOrder);
SqlParameter parameterOrderType = new SqlParameter("@ordertype", SqlDbType.Bit);
parameterOrderType.Value = pager.OrderType == true ? 0 : 1;
myCommand.SelectCommand.Parameters.Add(parameterOrderType);
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@keyfield", SqlDbType.NVarChar));
myCommand.SelectCommand.Parameters["@keyfield"].Value = pager.KeyField;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
myCommand.SelectCommand.Parameters["@pageindex"].Value = pager.PageIndex - 1;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@pagesize", SqlDbType.Int));
myCommand.SelectCommand.Parameters["@pagesize"].Value = pager.PageSize;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@strwhere", SqlDbType.NVarChar));
myCommand.SelectCommand.Parameters["@strwhere"].Value = pager.WhereCondition;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@RecordCount", SqlDbType.Int));
myCommand.SelectCommand.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@PageCount", SqlDbType.Int));
myCommand.SelectCommand.Parameters["@PageCount"].Direction = ParameterDirection.Output;
DataSet ds = new DataSet();
myCommand.Fill(ds, "pager");
pager.RecordCount = (int)myCommand.SelectCommand.Parameters["@RecordCount"].Value;
pager.PageCount = (int)myCommand.SelectCommand.Parameters["@PageCount"].Value;
myConnection.Close();
return ds.Tables["pager"];
}
}
public static DataTable SelectPage(ref SR.Lib.Space.Model.Pager pager)
{
using (SqlConnection myConnection = new SqlConnection(connectionString))
{
// 调用分页
SqlDataAdapter myCommand = new SqlDataAdapter("GetPagingData", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@tablename", SqlDbType.NVarChar));
myCommand.SelectCommand.Parameters["@tablename"].Value = pager.TableName;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@fieldlist", SqlDbType.NVarChar));
myCommand.SelectCommand.Parameters["@fieldlist"].Value = pager.SelectStr;
SqlParameter parameterOrder = new SqlParameter("@orderfield", SqlDbType.NVarChar);
parameterOrder.Value = pager.Order;
myCommand.SelectCommand.Parameters.Add(parameterOrder);
SqlParameter parameterOrderType = new SqlParameter("@ordertype", SqlDbType.Bit);
parameterOrderType.Value = pager.OrderType == true ? 0 : 1;
myCommand.SelectCommand.Parameters.Add(parameterOrderType);
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@keyfield", SqlDbType.NVarChar));
myCommand.SelectCommand.Parameters["@keyfield"].Value = pager.KeyField;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@pageindex", SqlDbType.Int));
myCommand.SelectCommand.Parameters["@pageindex"].Value = pager.PageIndex - 1;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@pagesize", SqlDbType.Int));
myCommand.SelectCommand.Parameters["@pagesize"].Value = pager.PageSize;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@strwhere", SqlDbType.NVarChar));
myCommand.SelectCommand.Parameters["@strwhere"].Value = pager.WhereCondition;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@RecordCount", SqlDbType.Int));
myCommand.SelectCommand.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@PageCount", SqlDbType.Int));
myCommand.SelectCommand.Parameters["@PageCount"].Direction = ParameterDirection.Output;
DataSet ds = new DataSet();
myCommand.Fill(ds, "pager");
pager.RecordCount = (int)myCommand.SelectCommand.Parameters["@RecordCount"].Value;
pager.PageCount = (int)myCommand.SelectCommand.Parameters["@PageCount"].Value;
myConnection.Close();
return ds.Tables["pager"];
}
}
数据持久层model:
Code
using System;
using System.Text;
using System.Data;
namespace SR.Lib.Space.Model
{
public enum PageMode
{
Num =0,
Str =1
}
/**//// <summary>
/// 分页类,能过存储过程进行分页,功能相当强大。
/// </summary>
public class Pager
{
private int pageIndex = 0;
private int recordCount = 0;
private int pageSize = 0;
private int pageCount = 0;
private string tableName = "";
private string whereCondition = "";
private string selectStr = "";
private string order = "";
private bool orderType = true;
private PageMode pageMode = PageMode.Num;
public Pager()
{
}
private string keyfield = "id";
/**//// <summary>
///搜索表或视图中的主键 如:ID
/// </summary>
public string KeyField
{
get { return this.keyfield; }
set { this.keyfield = value; }
}
public int PageIndex
{
get
{
return this.pageIndex;
}
set
{
this.pageIndex = value;
}
}
public PageMode PageMode
{
get
{
return this.pageMode;
}
set
{
this.pageMode = value;
}
}
public int PageCount
{
get
{
return this.pageCount;
}
set
{
this.pageCount = value;
}
}
/**//// <summary>
/// 总行数
/// </summary>
public int RecordCount
{
get
{
return this.recordCount;
}
set
{
this.recordCount = value;
}
}
/**//// <summary>
/// 每页条数
/// </summary>
public int PageSize
{
get
{
return this.pageSize;
}
set
{
this.pageSize = value;
}
}
/**//// <summary>
/// 表名称
/// </summary>
public string TableName
{
get
{
return tableName;
}
set
{
this.tableName = value;
}
}
/**//// <summary>
/// 条件查询
/// </summary>
public string WhereCondition
{
get
{
return whereCondition;
}
set
{
whereCondition = value;
}
}
/**//// <summary>
/// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号
/// </summary>
public string SelectStr
{
get
{
return selectStr;
}
set
{
selectStr = value;
}
}
/**//// <summary>
/// 排序的列
/// </summary>
public string Order
{
get
{
return order;
}
set
{
order = value;
}
}
/**//// <summary>
/// 排序类型 true:asc false:desc
/// </summary>
public bool OrderType
{
get
{
return orderType;
}
set
{
orderType = value;
}
}
// /// <summary>
// /// 得到当前返回的数量
// /// </summary>
// public int RowCount
// {
// get
// {
// return this.rowCount;
// }
// }
private string groupby;
public string Groupby
{
get
{
return this.groupby;
}
set
{
this.groupby = value;
}
}
public string OutPager(string url)
{
return Pager.OutPager(this,url);
}
}
}
using System;
using System.Text;
using System.Data;
namespace SR.Lib.Space.Model
{
public enum PageMode
{
Num =0,
Str =1
}
/**//// <summary>
/// 分页类,能过存储过程进行分页,功能相当强大。
/// </summary>
public class Pager
{
private int pageIndex = 0;
private int recordCount = 0;
private int pageSize = 0;
private int pageCount = 0;
private string tableName = "";
private string whereCondition = "";
private string selectStr = "";
private string order = "";
private bool orderType = true;
private PageMode pageMode = PageMode.Num;
public Pager()
{
}
private string keyfield = "id";
/**//// <summary>
///搜索表或视图中的主键 如:ID
/// </summary>
public string KeyField
{
get { return this.keyfield; }
set { this.keyfield = value; }
}
public int PageIndex
{
get
{
return this.pageIndex;
}
set
{
this.pageIndex = value;
}
}
public PageMode PageMode
{
get
{
return this.pageMode;
}
set
{
this.pageMode = value;
}
}
public int PageCount
{
get
{
return this.pageCount;
}
set
{
this.pageCount = value;
}
}
/**//// <summary>
/// 总行数
/// </summary>
public int RecordCount
{
get
{
return this.recordCount;
}
set
{
this.recordCount = value;
}
}
/**//// <summary>
/// 每页条数
/// </summary>
public int PageSize
{
get
{
return this.pageSize;
}
set
{
this.pageSize = value;
}
}
/**//// <summary>
/// 表名称
/// </summary>
public string TableName
{
get
{
return tableName;
}
set
{
this.tableName = value;
}
}
/**//// <summary>
/// 条件查询
/// </summary>
public string WhereCondition
{
get
{
return whereCondition;
}
set
{
whereCondition = value;
}
}
/**//// <summary>
/// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号
/// </summary>
public string SelectStr
{
get
{
return selectStr;
}
set
{
selectStr = value;
}
}
/**//// <summary>
/// 排序的列
/// </summary>
public string Order
{
get
{
return order;
}
set
{
order = value;
}
}
/**//// <summary>
/// 排序类型 true:asc false:desc
/// </summary>
public bool OrderType
{
get
{
return orderType;
}
set
{
orderType = value;
}
}
// /// <summary>
// /// 得到当前返回的数量
// /// </summary>
// public int RowCount
// {
// get
// {
// return this.rowCount;
// }
// }
private string groupby;
public string Groupby
{
get
{
return this.groupby;
}
set
{
this.groupby = value;
}
}
public string OutPager(string url)
{
return Pager.OutPager(this,url);
}
}
}