C # calling the Oracle stored procedure page

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

PLSQL code of the stored procedure.

 

// Program call example:

// Int totalrows = 0; // total number of records
// Int totalpages = 0; // total number of pages

// String tablename = "Temp"; // Oracle table name

// Int pagesize = 5; // number of records per page

// Int indexnowpage = 2; // current page number

// String strconnection = "datasource = temp; user id = system; Password = admin"; // connection string

// Oracleconnection porclconnection = new oracleconnection (strconnection );
// Pagination ppagintion = new pagination ("sp_cur2", porclconnection); // sp_cur2 indicates the name of the stored procedure.
// Datatable = ppagintion. Paging (tablename, pagesize, indexnowpage, ref totalrows, ref

Totalpages );

# Region ----------------------------------------------------------- Oracle-side paging storage process --------------------

--------------------------------

# Region package p_pak1
// Create or replace package p_pak1 as type p_cur1 is ref cursor;
// End p_pak1;
# Endregion

# Region storage process sp_pro2
// Create or replace procedure sp_cur2
// (Rowcountperpage number, -- number of records per page
// Indexnowpage number, -- current page number
// Tabname varchar2, -- Name of the paging table
// Totalrows out number, -- total number of records
// Totalpages out number, -- total number of pages
// P_cursor out p_pak1.p_cur1 -- cursor, used to return the result set
//) Is

// -- Start record number of the page
// V_startrownum number: = (indexNowPage-1) * rowcountperpage + 1;
// -- Number of the End record of the page
// V_endrownum number: = indexnowpage * rowcountperpage;

// V_ SQL varchar2 (1000 );

// Begin
// -- Query core SQL statements by PAGE
// V_ SQL: = 'select * from (select T1. *, rownum rn from
// (Select * from '| tabname |') T1 where rownum <= '| v_endrownum |') Where

Rn> = '| v_startrownum;

// -- Open the cursor and associate the SQL statement
// Open p_cursor for v_ SQL;

// -- Query the total number of records
// V_ SQL: = 'select count (*) from' | tabname;
// Execute immediate v_ SQL into totalrows;

// -- Calculate the total number of pages
// If Mod (totalrows, rowcountperpage) = 0
// Then
// Totalpages: = totalrows/rowcountperpage;
// Else
// Totalpages: = totalrows/rowcountperpage + 1;
// End if;

// -- Close the cursor and an overflow error is reported during execution.
// -- Close p_cursor;

// End;

# 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 ();
}
}

}