C # paging query of Oracle stored procedures

Simple post main partCode.

. SQL

-- Define a package
Create or replace package pkg_g_selectsp as type p_g_cursor is ref cursor;
Procedure p_g_getpagingdata (
Q_ SQL varchar2,
Rowcountpage number,
Indexnowpage number,
Totalrows out number,
Totalpages out number,
P_corsor out pkg_g_selectsp.p_g_cursor
);
End pkg_g_selectsp;

-- Query data stored procedures by PAGE
Create or replace procedure p_g_getpagingdata (
Q_ SQL varchar2,
Rowcountpage number, -- number of records per page
Indexnowpage number, -- current page number
Totalrows out number, -- total number of records
Totalpages out number, -- total number of pages
P_corsor out pkg_g_selectsp.p_g_cursor -- cursor, used to return the result set
)
-- Start record number of the page
Startrownum number: = (indexNowPage-1) * rowcountpage + 1;
-- Number of the end records by PAGE
Endrownum number: = indexnowpage * rowcountpage;
-- Query data SQL string
S_ SQL varchar2 (5000 );
SC _ SQL varchar2 (5000 );

Begin
S_ SQL: = 'select * from (select T1. *, rownum rn from ('| q_ SQL |') T1 where rownum <= '| endrownum | ') where rn> = '| startrownum;
-- Open the cursor and associate the SQL statement
Open p_corsor for s_ SQL;

-- Query the total number of records
SC _ SQL: = 'select count (*) from ('| q_ SQL | ')';
Execute immediate SC _ SQL into totalrows;
-- Calculate the total number of pages
If Mod (totalrows, rowcountpage) = 0 then
Totalpages: = totalrows/rowcountpage;
Else
Totalpages: = totalrows/rowcountpage + 1;
End if;
End;

. Aspx

Public oracleconnection oconnection;
Public oraclecommand ocommand;

Public oracledatareader odatareader;

Public int totalrows = 0;

Public int totalpages = 0;

// The part connecting to the database is omitted ....

// Define the method for calling the Stored Procedure

//

// call the stored procedure to query the data of the number of records on the specified page number
///
// / Basic Data Query
// Number of records displayed on each page
// page number
// total number of records queried
// total number of pages
Public void procedureselectdata (string q_ SQL, int rowscountpage, int indexnowpage, ref int totalrows, ref int totalpages) {
try {
This. ocommand = new oraclecommand ();
This. ocommand. connection = This. oconnection;
ocommand. commandtext = "p_g_getpagingdata";
ocommand. commandtype = commandtype. storedprocedure;

oracleparameter op0 = new oracleparameter ("q_ SQL", oracledbtype. varchar2, 2000);
oracleparameter OP1 = new oracleparameter ("rowcountpage", oracledbtype. int32, 10);
oracleparameter OP2 = new oracleparameter ("indexnowpage", oracledbtype. int32, 10);
oracleparameter OP3 = new oracleparameter ("totalrows", oracledbtype. int32, 10);
oracleparameter op4 = new oracleparameter ("totalpages", oracledbtype. int32, 10);
oracleparameter OP5 = new oracleparameter ("p_corsor", oracledbtype. refcursor);

Op0.direction = parameterdirection. input;
Op1.direction = parameterdirection. input;
Op2.direction = parameterdirection. input;
Op3.direction = parameterdirection. output;
Op4.direction = parameterdirection. output;
Op5.direction = parameterdirection. output;

Op0.value = q_ SQL;
Op1.value = rowscountpage;
Op2.value = indexnowpage;

Ocommand. Parameters. Add (op0 );
Ocommand. Parameters. Add (OP1 );
Ocommand. Parameters. Add (OP2 );
Ocommand. Parameters. Add (OP3 );
Ocommand. Parameters. Add (op4 );
Ocommand. Parameters. Add (OP5 );

This. odatareader = ocommand. executereader ();
Totalrows = int32.parse (op3.value. tostring ());
Totalpages = int. parse (op4.value. tostring ());
} Catch (exception ){
}
}

// Call Method

Procedureselectdata (sqlstr, 200, 1, ref this. totalrows, ref this. totalpages );

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/lifeng_beijing/archive/2009/12/22/5054261.aspx