ASP.net翻页功能实现需要两部分处理:
1. Oracle DB package 处理每次得到一定行数的sql;
2. UserControl.ascx 处理换页各种事件;
下面一一解释其中的做法:
Package name is GRIDVIEWPAGES, and Spec is
CREATE OR REPLACE package BLUESKY.GridViewPages
is
type ResultData is ref cursor;
procedure sp_Page(p_PageSize number, --pagesize
p_PageNo number, --page index start from 1
p_SqlSelect varchar2, --select statement and contain order by
p_SqlCount varchar2, --get total num of recorders
p_OutRecordCount out number,--return recorders num
p_OutCursor out ResultData);
end GridViewPages;
/
body is:
CREATE OR REPLACE package body BLUESKY.GridViewPages
is
procedure sp_Page(p_PageSize number, -- pagesize
p_PageNo number, --page index start from 1
p_SqlSelect varchar2, --select statement and contain order by
p_SqlCount varchar2, --get total num of recorders
p_OutRecordCount out number,--return recorders num
p_OutCursor out ResultData)
as
v_sql varchar2(8000);
v_count number;
v_heiRownum number;
v_lowRownum number;
begin
----get recorder num
execute immediate p_SqlCount into v_count;
p_OutRecordCount := v_count;
v_heiRownum := p_PageNo * p_PageSize;
v_lowRownum := v_heiRownum - p_PageSize +1;
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| p_SqlSelect ||') A
WHERE rownum <= '|| to_char(v_heiRownum) || '
) B
WHERE rn >= ' || to_char(v_lowRownum) ;
--note: alias of rownum be used first is rownum name,the second is rn
OPEN p_OutCursor FOR v_sql;
end sp_Page;
end GridViewPages;
/
PageControl.ascx :