C # + Paging for Oracle stored procedures

1. Run the PL/SQL block creation stored procedure in Oracle sqlplus or other tools.

------------------------------------------------------------
-- Paging Stored Procedure
------------------------------------------------------------
-- Create a package
Create or replace package testpackage
Type test_cursor is ref cursor;
End testpackage;
/

-- Creation process
Create or replace procedure fenye
(
Tablename in varchar2, -- table name
Fields in varchar2, -- query the result display field
Wherecase in varchar2, -- Query Condition
Pagesize in number, -- number of records displayed on one page
Pagenow in number, -- current page
Orderfield varchar2, -- sorting field. If it is null, it indicates not sorting.
Orderflag number, -- Sort ID 0: Forward 1: reverse
Myrows out number, -- total number of records
Mypagecount out number, -- total page
P_cursor out testpackage. test_cursor -- returned record set
) Is
-- Definition
-- Define the SQL statement string
V_ SQL varchar2 (1000 );
-- Define two integers
V_begin number: = (pageNow-1) * pagesize + 1; -- start record
V_end number: = pagenow * pagesize; -- end record
-- Sort SQL
V_ordersql varchar2 (100): = '';
V_wherecase varchar2 (100): = '';
Begin
-- Execution part
-- If orderfield is not empty, sort it. If orderflag = 0 is ascending, 1 is descending.
If orderfield is not null then
If orderflag = 0 then
V_ordersql: = 'ORDER BY' | orderfield;
Elsif orderflag = 1 then
V_ordersql: = 'ORDER BY' | orderfield | 'desc ';
Else
NULL;
End if;
End if;
-- Condition judgment statement
If wherecase is not null then
V_wherecase: = 'where' | wherecase;
End if;
V_ SQL: = 'select * from
(Select T1. *, rownum rn from (select '| fields | 'from' | tablename | v_wherecase | ''| v_ordersql | ') t1 where rownum <= '| v_end | ')
Where rn> = '| v_begin;
-- Associate a cursor with an SQL statement
Open p_cursor for v_ SQL;
-- Calculate myrows and mypagecount
-- Organize an SQL statement
V_ SQL: = 'select count (*) from' | tablename | v_wherecase | ''| v_ordersql;
-- Execute the SQL statement and assign the returned value to myrows;
Execute immediate v_ SQL into myrows;
-- Calculate mypagecount
If Mod (myrows, pagesize) = 0 then
Mypagecount: = myrows/pagesize;
Else
Mypagecount: = myrows/pagesize + 1;
End if;
-- Close the cursor
-- Close p_cursor;
End;
/

2. Oracle Process calling class in. net

/// <Summary>
/// C # Call the Oracle process for paging
/// Author: sirc_ljp
/// Date: 2011.11.16
/// </Summary>
Public class Paging
{
Private string _ connectionstring;
Private string _ tablename;
Private string _ fields = "*";
Private string _ whercase = "";
Private int _ pagesize = 10;
Private int _ pagenow = 1;
Private string _ orderfield = "";
Private int _ orderflag = 0;
Private int _ myrows;
Private int _ mypagecount;
Private able _ result;

/// <Summary>
/// Database connection string
/// </Summary>
Public String connectionstring
{
Get {return _ connectionstring ;}
Set {_ connectionstring = value ;}
}

/// <Summary>
/// Table name
/// </Summary>
Public String tablename
{
Get {return _ tablename ;}
Set {_ tablename = value ;}
}
/// <Summary>
/// Query result display field
/// </Summary>
Public String Fields
{
Get {return _ fields ;}
Set {_ fields = value ;}
}
/// <Summary>
/// Query Conditions
/// </Summary>
Public String whercase
{
Get {return _ whercase ;}
Set {_ whercase = value ;}
}
/// <Summary>
/// Number of records displayed on the page
/// </Summary>
Public int pagesize
{
Get {return _ pagesize ;}
Set {_ pagesize = value ;}
}
/// <Summary>
/// Current page
/// </Summary>
Public int pagenow
{
Get {return _ pagenow ;}
Set {_ pagenow = value ;}
}
/// <Summary>
/// Sorting field. If it is null, "" indicates not sorting.
/// </Summary>
Public String orderfield
{
Get {return _ orderfield ;}
Set {_ orderfield = value ;}
}
/// <Summary>
/// Sorting ID 0: Forward 1: reverse
/// </Summary>
Public int orderflag
{
Get {return _ orderflag ;}
Set {_ orderflag = value ;}
}
/// <Summary>
/// Total number of records
/// </Summary>
Public int myrows
{
Get {return _ myrows ;}
}
/// <Summary>
/// Total page
/// </Summary>
Public int mypagecount
{
Get {return _ mypagecount ;}
}
/// <Summary>
/// Returned record set
/// </Summary>
Public datatable result
{
Get {return _ result ;}
}

Public Paging ()
{
Fenye ();
}

Public Paging (string connectionstring, string tablename, string fields, string wherecase, int pagesize, int pagenow, string orderfield, int orderflag, out int myrows, out int mypagecount, out datatable result)
{
_ Connectionstring = connectionstring;
_ Tablename = tablename;
_ Fields = fields;
_ Whercase = wherecase;
_ Pagesize = pagesize;
_ Pagenow = pagenow;
_ Orderfield = orderfield;
_ Orderflag = orderflag;

Fenye ();
Myrows = _ myrows;
Mypagecount = _ mypagecount;
Result = _ result;

}

Private void fenye ()
{
Oracleconnection conn = new oracleconnection (_ connectionstring );
Conn. open ();

Try
{
Oraclecommand cmd = new oraclecommand ();
Cmd. Connection = conn;
Cmd. commandtype = commandtype. storedprocedure;

// Call the stored procedure to query data
Cmd. commandtext = "fenye ";
Oracleparameter [] parameters = new oracleparameter [10];
// Note that the parameter name and type number here are the same as those in the Stored Procedure
Parameters [0] = new oracleparameter ("tablename", oracletype. varchar );
Parameters [1] = new oracleparameter ("fields", oracletype. varchar );
Parameters [2] = new oracleparameter ("wherecase", oracletype. varchar );
Parameters [3] = new oracleparameter ("pagesize", oracletype. int32 );
Parameters [4] = new oracleparameter ("pagenow", oracletype. int32 );
Parameters [5] = new oracleparameter ("orderfield", oracletype. varchar );
Parameters [6] = new oracleparameter ("orderflag", oracletype. int32 );
Parameters [7] = new oracleparameter ("myrows", oracletype. int32 );
Parameters [8] = new oracleparameter ("mypagecount", oracletype. int32 );
Parameters [9] = new oracleparameter ("p_cursor", oracletype. cursor );

Parameters [0]. value = _ tablename;
Parameters [1]. value = _ fields;
Parameters [2]. value = _ whercase;
Parameters [3]. value = _ pagesize;
Parameters [4]. value = _ pagenow;
Parameters [5]. value = _ orderfield;
Parameters [6]. value = _ orderflag;
Parameters [7]. Direction = parameterdirection. output;
Parameters [8]. Direction = parameterdirection. output;
Parameters [9]. Direction = parameterdirection. output;

Foreach (oracleparameter parameter in parameters)
{
Cmd. Parameters. Add (parameter );
}
// Execute the command
Oracledataadapter ODA = new oracledataadapter (CMD );
Dataset DS = new dataset ();
ODA. Fill (DS );
// Obtain the query result table
_ Result = Ds. Tables [0];

// Retrieve the total number of rows
_ Myrows = convert. toint32 (parameters [7]. value );
// Retrieve the total number of pages
_ Mypagecount = convert. toint32 (parameters [8]. value. tostring ());

}
Catch (exception ex)
{

Throw;
}
Finally
{
Conn. Close ();
}

}

}