Oracle and C # Stored Procedure Paging

-- Paging
Select * from (select t1.ename, rownum rn from (select * from emp) t1 where rownum <= 10) where rn> = 6;

 

-- Develop a package
Create or replace package tespackage
Type test_cursor is ref cursor;
End tespackage;

-- Paging Stored Procedure
Create or replace procedure sp_fengye
(TableName in varchar2, -- parameter 1 Table Name
Pagesize in number,
PageNow in number,
Myrows out number, -- total number of records
MyPageCount out number, -- total number of pages
P_cursor out tespackage. test_cursor -- return record set
) Is
-- Definition
-- Define the SQL statement string
V_ SQL varchar2 (1000 );
-- Define two integers
V_begin number: = (pageNow-1) * pagesize + 1; -- where to get
V_end number: = pageNow * pagesize;
Begin
-- Execution part
V_ SQL: = 'select * from (select t1.ename, rownum rn from
(Select * from '| tablename | 'order by SAL) T1 where rownum <=' | v_end | ') Where rn> =' | v_begin;
-- Associate a cursor with an SQL statement
Open p_cursor for v_ SQL;
-- Calculate the values of myrows and mypagecount
-- Organize an SQL statement
V_ SQL: = 'select count (*) from' | tablename;
-- Execute the SQL statement and assign the returned value to myrows;
Execute immediate v_ SQL into myrows;
-- Calculate mypagwecount
If Mod (myrows, pagesize) = 0 then
Mypagecount: = myrows/pagesize;
Else
Mypagecount: = myrows/pagesize + 1;
End if;
-- Close the cursor
Close p_cursor;
End;

 

Directly call the stored procedure in Oracle to complete the paging of the table. The code is highly closed and efficient. The following code is the C # paging code, including the PLSQL code of the stored procedure on the Oracle database.

 

# Endregion

# Endregion
Using System;
Using System. Collections. Generic;
Using System. Web;
Using System. Data. OracleClient;
Using System. Data;

/// <Summary>
/// Call the Oracle stored procedure to paging the table
/// </Summary>
Public class Pagination
{

String m_procedureName; // name of the stored procedure to be called
OracleConnection m_oracleConnection; // Oracle connection object

/// <Summary>
/// Constructor, passing in the stored procedure name and connection object
/// </Summary>
/// <Param name = "procedureName"> stored procedure name </param>
/// <Param name = "orclConnection"> Oracle connection object connected to the string after initialization </param>
Public Pagination (string procedureName, OracleConnection orclConnection)
{
M_procedureName = procedureName;
M_oracleConnection = orclConnection;
}

/// <Summary>
/// Execution page
/// </Summary>
/// <Param name = "tableName"> name of the paging table </param>
/// <Param name = "paeSize"> Number of records per page </param>
/// <Param name = "indexNowPage"> current page number </param>
/// <Param name = "totalRows"> reference parameter, total number of records </param>
/// <Param name = "totalPages"> reference parameter, total page number </param>
/// <Returns> paging result set </returns>
Public DataTable Paging (string tableName, int paeSize, int indexNowPage, ref int totalRows, ref int totalPages)
{
Try
{
// Open the connection
OpenOracleConnection ();

// Define the OracleCommand object and set the command type to Stored Procedure
OracleCommand pOracleCMD = new OracleCommand (m_procedureName, m_oracleConnection );
POracleCMD. CommandType = CommandType. StoredProcedure;

// Generate a parameter object based on the number and type of stored procedure parameters
OracleParameter p1 = new OracleParameter ("rowCountPerPage", OracleType. Number, 10 );
OracleParameter p2 = new OracleParameter ("indexNowPage", OracleType. Number, 10 );
OracleParameter p3 = new OracleParameter ("tabName", OracleType. VarChar, 50 );
OracleParameter p4 = new OracleParameter ("totalRows", OracleType. Number, 10 );
OracleParameter p5 = new OracleParameter ("totalPages", OracleType. Int16, 10 );
OracleParameter p6 = new OracleParameter ("p_cursor", OracleType. Cursor );

// Set the input and output types of parameters. The default value is input.
P1.Direction = ParameterDirection. Input;
P2.Direction = ParameterDirection. Input;
P3.Direction = ParameterDirection. Input;
P4.Direction = ParameterDirection. Output;
P5.Direction = ParameterDirection. Output;
P6.Direction = ParameterDirection. Output;

// Define the initial value of the input parameter. The output parameter does not need to be assigned a value.
P1.value = paesize;
P2.value = indexnowpage;
P3.value = tablename;

// Add parameters in sequence to the oraclecommand object parameter set
Poraclecmd. Parameters. Add (P1 );
Poraclecmd. Parameters. Add (P2 );
Poraclecmd. Parameters. Add (P3 );
Poraclecmd. Parameters. Add (P4 );
Poraclecmd. Parameters. Add (P5 );
Poraclecmd. Parameters. Add (P6 );

// Execute and enter the paging result set in the datatable
Oracledataadapter poracledataadapter = new oracledataadapter (poraclecmd );
Datatable = new datatable ();
Poracledataadapter. Fill (datatable );

// After execution, obtain the corresponding values from the Stored Procedure output parameters and place them in the reference parameters for the program to call.
Totalrows = int. parse (p4.value. tostring ());
TotalPages = int. Parse (p5.Value. ToString ());

// Close the connection
CloseOracleConnection ();

Return datatable;
}
Catch (Exception ex)
{
Return null;
}

}

/// <Summary>
/// Close the connection
/// </Summary>
Private void closeoracleconnection ()
{
If (m_oracleconnection.state = connectionstate. open)
{
M_oracleconnection.close ();
}
}

/// <Summary>
/// Open the connection
/// </Summary>
Private void openoracleconnection ()
{
If (m_oracleconnection.state = connectionstate. Closed)
{
M_oracleconnection.open ();
}
}

}