C # Oracle Paging

//////////////////////////////////////// // Some Oracle stored procedures

Create or replace package pack_page is

 

Type type_cur is ref cursor; -- defines the cursor variable used to return the record set
Procedure proc_page
(
Pindex in number, -- paging Index
Psql in varchar2, -- SQL statement that generates Dataset
Psize in number, -- page size
Pcount out number, -- total number of returned pages
V_cur out type_cur -- returns the current page data record
);
Procedure proc_pagecount
(
Psqlcount in varchar2, -- generate the dataset SQL statement
Prcount out number -- total number of returned records
);

End pack_page;

//////////////////////////////////////// ///////////////

Create or replace package body pack_page is

 

Procedure proc_page
(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
V_cur out type_cur
)
As
V_ SQL varchar2 (5000 );
V_tempsql varchar2 (5000 );
V_count number;
V_plow number;
V_phei number;
Begin
------------------------------------------------------------ Retrieve the total number of pages
V_ SQL: = 'select count (*) from ('| Psql | ')';
Execute immediate v_ SQL into v_count;
Pcount: = Ceil (v_count/psize );
-------------------------------------------------------------- Display any page content
V_phei: = (Pindex-1) * psize + psize;
V_plow: = v_phei-psize + 1;
V_tempsql: = 'select rownum RN, T. * from ('| Psql |') t where rownum <= '| v_phei; -- The rownum field must be included.
V_ SQL: = 'select * from ('| v_tempsql |') Where rn between' | v_plow | 'and' | v_phei;
Open v_cur for v_ SQL;
End proc_page;
--*************************************** **************************************** *******
Procedure proc_pagecount
(
Psqlcount in varchar2,
Prcount out number
)
As
V_ SQL varchar2 (5000 );
V_prcount number;
Begin
V_ SQL: = 'select count (*) from ('| psqlcount | ')';
Execute immediate v_ SQL into v_prcount;
Prcount: = v_prcount; -- total number of returned records
End proc_pagecount;
--*************************************** **************************************** *******
End pack_page;

 

/// // C # Call

/// <Summary>
/// Query by PAGE Conditions
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Param name = "pageindex"> current page </param>
/// <Param name = "pagesize"> Number of records on each page </param>
/// <Param name = "tabname"> name of the table in the dataset </param>
/// <Returns> </returns>
Public dataset querybypage (string SQL, int pageindex, int pagesize, string tabname)
{
Using (oraclehelper orahp = new oraclehelper ())
{
Dataset DS = new dataset ();
DS. Tables. Add (New datatable (tabname ));

 

Orahp. open ();
Orahp. executesp_page (Ds. Tables [tabname], "pack_page.proc_page", SQL, pageindex, pagesize, true );
Orahp. Close ();

Return Ds;
}
}

 

 

Public int executesp_page (datatable outdatatable, string procname, string SQL, int pageindex, int pagesize, bool setcase)
{
If (null = connection)
{
Throw new objectdisposedexception (GetType (). fullname );
}

 

Int nreturn = 0;

using (oraclecommand command = new oraclecommand (procname, connection)
{< br> command. commandtype = commandtype. storedprocedure;
command. commandtimeout = This. _ timeout;

command. parameters. add ("Psql", oracletype. varchar ). value = SQL;
command. parameters. add ("pindex", oracletype. number ). value = pageindex;
command. parameters. add ("psize", oracletype. number ). value = pagesize;
command. parameters. add ("pcount", oracletype. number ). direction = parameterdirection. output;
command. parameters. add ("v_cur", oracletype. cursor ). direction = parameterdirection. output;

If (null! = Transaction)
{< br> command. Transaction = Transaction;
}

Using (oracledataadapter connector adapter = new oracledataadapter (command ))
{
If (setcase) outdatatable. casesensitive = true;
Nreturn = Response Adapter. Fill (outdatatable );
}

Command. Parameters. Clear ();

Return nreturn;
}
}