Asp. NET calls Oracle stored procedures for quick paging

asp.net|oracle| Stored Procedures | paging

Package Definition:

Create or Replace package materialmanage is
TYPE T_cursor is REF CURSOR;
Procedure Per_quickpage
(
Tbname in Varchar2,--table name
Fieldstr in Varchar2,--field set
RowFilter in VARCHAR2--filtration conditions
Sortstr in Varchar2,--Sort set
Rownumfieldstr in Varchar2,--Paging condition
TotalCount out number,--Total records
Cur_returncur out T_cursor--the cursor returned
);
End Materialmanage;

Package Body:

Create or Replace package body Materialmanage is
Procedure Per_quickpage
(
Tbname in Varchar2,--table name
Fieldstr in Varchar2,--field set
RowFilter in VARCHAR2--filtration conditions
Sortstr in Varchar2,--Sort set
Minrownum in number--paging small value
Maxrownum in number--Paging large value
TotalCount out number,--Total records
Cur_returncur out T_cursor
)
Is
V_SOURCETB1 VARCHAR2 (3000); --Dynamic Table name 1
V_SOURCETB2 VARCHAR2 (3000); --Dynamic Table Name 2
V_SOURCETB3 VARCHAR2 (3000); --Dynamic Table name 3
V_SOURCETB4 VARCHAR2 (3000); --Dynamic Table name 4
V_totalcount VARCHAR2 (50); --Total number of records
V_sql VARCHAR2 (3000); --Dynamic SQL
Begin
V_SOURCETB1: = ' (SELECT ' | | Fieldstr | | ' From ' | | Tbname | | ') SourceTb1 ';
V_SOURCETB2: = ' (select * from ' | | v_sourcetb1 | | ' WHERE ' | | RowFilter | | ' '|| Sortstr | | ') SourceTb2 ';
V_SOURCETB3: = ' (select RowNum as rowindex,sourcetb2.* from ' | | v_sourcetb2 | | ' where rownum<= ' | | Maxrownum | | ') SourceTb3 ';
V_SOURCETB4: = ' (select * from ' | | v_sourcetb1 | | ' WHERE ' | | RowFilter | | ') SourceTb4 ';
V_sql: = ' SELECT count (*) as TotalCount from ' | | V_SOURCETB4;
Execute immediate v_sql into V_totalcount;
TotalCount: = V_totalcount;
V_sql: = ' select * from ' | | v_sourcetb3 | | ' where RowIndex >= ' | | Minrownum;
Open cur_returncur for V_sql;
End Per_quickpage;
End Materialmanage;

Because Oracle has a rownum feature, paging is implemented using RowNum. If you have any better way to remember to let me know, thank you, because I tested the above paging method efficiency is not very high.

The stored procedure returned two parameters: TotalCount: The total number of records under current conditions cur_returncur: cursor type, which is the collection of records to be read

The following is the code called in asp.net:

<summary>
Calling a stored procedure to implement quick paging
</summary>
<param name= "tbname" > table name </param>
<param name= "fieldstr" > Field name </param>
<param name= "RowFilter" > Filtration conditions </param>
<param name= "Sortstr" > Sort fields </param>
<param name= "Minpagenum" > Paging small value </param>
<param name= "Maxpagenum" > Paging big Value </param>
<param name= "TotalCount" > Total records (need to return) </param>
<returns>DataTable</returns>
Public DataTable quickpage (string tbname,string fieldstr,string rowfilter,string sortstr,int minrownum,int MaxRowNum, ref int RecordCount)
{
OracleConnection conn = new OracleConnection (configurationsettings.appsettings["Oracleconnstr"). ToString ());
OracleCommand cmd = new OracleCommand ();
Cmd. Connection = conn;
Cmd.commandtext = "Materialmanage.per_quickpage";
Cmd.commandtype = CommandType.StoredProcedure;

Cmd.    Parameters.Add ("Tbname", oracletype.varchar,50); Table name
Cmd. parameters["Tbname"]. Direction = ParameterDirection.Input;
Cmd. parameters["Tbname"]. Value = Tbname;

Cmd.   Parameters.Add ("Fieldstr", oracletype.varchar,3000); field set
Cmd. parameters["Fieldstr"]. Direction = ParameterDirection.Input;
Cmd. parameters["Fieldstr"]. Value = Fieldstr;

Cmd.  Parameters.Add ("RowFilter", oracletype.varchar,3000); Filter conditions
Cmd. parameters["RowFilter"]. Direction = ParameterDirection.Input;
Cmd. parameters["RowFilter"]. Value = RowFilter;

Cmd.   Parameters.Add ("Sortstr", oracletype.varchar,3000); Sort fields
Cmd. parameters["Sortstr"]. Direction = ParameterDirection.Input;
Cmd. parameters["Sortstr"]. Value = Sortstr;

Cmd.    Parameters.Add ("Minrownum", Oracletype.number); Paging Small value
Cmd. parameters["Minrownum"]. Direction = ParameterDirection.Input;
Cmd. parameters["Minrownum"]. Value = Minrownum;

Cmd.    Parameters.Add ("Maxrownum", Oracletype.number); Paging Large value
Cmd. parameters["Maxrownum"]. Direction = ParameterDirection.Input;
Cmd. parameters["Maxrownum"]. Value = Maxrownum;

Cmd.    Parameters.Add ("TotalCount", Oracletype.number); Total number of page records
Cmd. parameters["TotalCount"]. Direction = ParameterDirection.Output;
Cmd. parameters["TotalCount"]. Value = 0;

Cmd.   Parameters.Add ("Cur_returncur", oracletype.cursor); The cursor returned
Cmd. parameters["Cur_returncur"]. Direction = ParameterDirection.Output;

DataSet Ds = new DataSet ();
OracleDataAdapter adapter= new OracleDataAdapter (CMD);
Adapter. Fill (Ds);
Conn. Close ();

Total number of records
RecordCount = Int. Parse (cmd. parameters["TotalCount"]. Value.tostring ());
return ds.tables[0];
}
Well, the code is listed above, as for the use, we should know it