利用Package实现ASP.net翻页功能

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 :