Repeater分页(Oracle通用分页存储过程和AspNetPage结合)(原创)
分页是开发中常用功能之一,尤其是Repeater分页,收集了一些资料.发现Repeater的分页通常分为两种.
1.假分页(PagedDataSource实现) 2.真分页.本文关注的是后者.虽本人用的是.net和oracle结合作为例
子.实际上其它平台实现方法类似.
1.存储过程的编写
View Code
--定义一个包 用于返回分页总数和当前页数据记录 为分页存储过程作准备
create or replace package pck_page is
type T_Page is ref cursor; --定义游标变量用于返回记录集
procedure getpagerecord(
pindex in number, --分页索引
psql in varchar2, --产生dataset的sql语句
psize in number, --页面大小
pcount out number, --返回分页总数
v_cur out T_Page --返回当前页数据记录
);
end pck_page
--定义包体
create or replace package body pck_page is
procedure getpagerecord(
pindex in number,
psql in varchar2,
psize in number,
pcount out number,
v_cur out T_Page
)
as
v_sql varchar2(1000);
v_count number;
v_plow number;
v_phei number;
begin
v_sql := 'select count(*) from (' || psql || ')';
execute immediate v_sql into v_count;
pcount := ceil(v_count/psize);
v_phei := pindex * psize + psize;
v_plow := v_phei - psize + 1;
--psql := select rownum rn,t.* from test t ; --要求必须包含rownum字段
v_sql := 'select * from (' || psql || ') where rn between ' || v_plow || ' and ' || v_phei ;
open v_cur for v_sql;
end getpagerecord;
end pck_page;
--定义一个存储过程 用来获取总记录条数 为分页存储过程做准备
create or replace procedure p_app_GetCount(
p_sql in varchar2,
p_count out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || p_sql || ')';
execute immediate v_sql into v_prcount;
p_count := v_prcount; --返回记录总数
end p_app_GetCount;
--定义分页存储过程
create or replace procedure p_app_page(
p_pagesql in varchar2,--产生分页的sql语句 sql要求必须包含rownum字段且其别名为rn,如例子:select rownum rn,t.* from test t
p_pagesize in number, --每页显示的条数
p_pageindex in number,--显示页的索引 从0开始
p_totalcount out number,--总条数
p_pagecount out number,--总页数
p_currentpagedata out pck_page.T_Page --返回当前页的数据
)
as
begin
pck_page.getpagerecord(p_pageindex,p_pagesql,p_pagesize,p_pagecount,p_currentpagedata);
p_app_getcount(p_pagesql,p_totalcount);
end;
2.AspNetPage和Repeater
View Code
AspNetPage页面注册：
<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
AspNetPage页面放置:
<webdiyer:AspNetPager ID="AspNetPager1" runat="server"AlwaysShow="True" CustomInfoHTML="第%CurrentPageIndex%页 共%RecordCount%条记录"
FirstPageText="首页" LastPageText="末页" NextPageText=
"下一页" PrevPageText="上一页" ShowCustomInfoSection="Right"
ShowPageIndexBox="Never" SubmitButtonText="Go" TextAfterPageIndexBox="页"OnPageChanged=
"AspNetPager1_PagChanged"CurrentPageButtonPosition="Center" CustomInfoSectionWidth="45%" CustomInfoTextAlign="Center"
PageSize="10" ShowPageIndex="False" Width="18%" PagingButtonLayoutType="Span" PagingButtonSpacing="1px">
</webdiyer:AspNetPager>
Repeater:
<asp:Repeater ID="rptInStorageHistory" runat="server" >
<HeaderTemplate>
<table border="0" style="width: 100%;"
class="TableStyle">
<tr id="TableTitle" align="center" valign="middle">
<td width="2%" style="border-left: 0px solid
#000;">
</td>
<td width="11%">
交易号
</td>
<td width="11%">
名称
</td>
<td width="12%">
规格型号
</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr align="center" valign="middle" class="TableDetail2">
<td width="2%" style="border-left: 0px solid #000;">
</td>
<td width="12%">
<%# Eval("id") %>
</td>
<td width="12%">
<%# Eval("name") %>
</td>
<td width="12%">
<%# Eval("name") %>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
3.调用测试
View Code
--调用分页存储过程 得到 总条数 总页数 当前页数据
public DataTable GetPageRecord(MPage page, out ArrayList result)
{
OracleParameter[] oracleparameter = new OracleParameter[6];
oracleparameter[0] = new OracleParameter("p_pagesql", OracleDbType.Varchar2, ParameterDirection.Input);
oracleparameter[0].Value = page.pagesql;
oracleparameter[1] = new OracleParameter("p_pagesize", OracleDbType.Decimal, ParameterDirection.Input);
oracleparameter[1].Value = page.pagesize;
oracleparameter[2] = new OracleParameter("p_pageindex", OracleDbType.Decimal, ParameterDirection.Input);
oracleparameter[2].Value = page.pageindex;
oracleparameter[3] = new OracleParameter("p_totalcount", OracleDbType.Decimal, ParameterDirection.Output);
oracleparameter[4] = new OracleParameter("p_pagecount", OracleDbType.Decimal, ParameterDirection.Output);
oracleparameter[5] = new OracleParameter("p_currentpagedata", OracleDbType.RefCursor, ParameterDirection.Output);
return dataaccess.ExecuteProcedureWithTable(ref oracleparameter, "p_app_page", out result);
}
--绑定Repeater
///<summary>
/// 网上下载的分页控件
///</summary>
///<param name="aspnetpager">分页控件AspNetPager</param>
///<param name="pagesql">sql文加rownum列,并起别名为rn.如形式：select t.*,rownum rn from asset_fa_stock t</param>
public void BindingRepeaterWithAspnetpager(AspNetPager aspnetpager, Repeater bindingtarget, String pagesql)
{
DCommonBase dbase = null;
int recordcount = 0;
MPage page = null;
ArrayList result=null;
page = new MPage();
dbase = new DCommonBase();
page.pagesize = aspnetpager.PageSize;
page.pageindex = aspnetpager.StartRecordIndex / aspnetpager.PageSize;
page.pagesql = pagesql;
bindingtarget.DataSource=dbase.GetPageRecord(page, out result);
bindingtarget.DataBind();
if (result != null && result.Count > 0)
{
if (int.TryParse(result[0].ToString(), out recordcount))
{
aspnetpager.RecordCount = recordcount;
}
else
{
aspnetpager.RecordCount = 0;
}
}
}
--页面上调用
void bindData()
{
g_assetinstoragemanager.BindingRepeaterWithAspnetpager(AspNetPager1, rptInStorageHistory, "select t.*,rownum rn from stock t");
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
bindData();
}
protected void btnPage_Click(object sender, EventArgs e)
{
bindData();
}
备注:
1.关于AspNetPage的用法，请自行去参考相关资料
2.关于分页思路请参考本人另一篇:SqlServer与Oracle的分页(收集整理)
