. Net calls the Oracle stored procedure to return result sets and functions

Oracle segment:
First, create a package and package body in Oracle, and define the function and the returned result set of the stored procedure.
1: Create a package:
Create or replace package Scott. pk_wt
Is
Type mytype is ref cursor;
Procedure p_wt (MYCS out mytype );
Function f_get (STR in varchar2)
Return varchar2;
End;
/
Note: package is just a declaration. Here we define a stored procedure to return a collection and a function to return a string.

2: Create a package body:
Create or replace package body Scott. pk_wt
Is
Procedure p_wt (MYCS out mytype)
Is
Begin
Open MYCS for select * from test;
End p_wt;

Function f_get (STR varchar2)
Return varchar2
Is
Str_temp varchar2 (100): = 'Good luck! ';
Begin
Str_temp: = str_temp | STR;
Return str_temp;
End f_get;

End pk_wt;
/
Note: here, the creation of the package body is a specific description and use, and how will it be implemented ..

C # section:
In C # Code It is divided into two parts: the use of functions and the use of result sets.
Define a connection and get it from webconfig:
Private oracleconnection orcn = new oracleconnection (system. configuration. configurationsettings. deleettings ["Scott"]);
C # Call Oracle functions:
Oraclecommand cmd = new oraclecommand ("pk_wt.f_get", orcn );
Cmd. commandtype = commandtype. storedprocedure;
Oracleparameter p1 = new oracleparameter ("str", oracletype. varchar, 10 );
P1.direction = system. Data. parameterdirection. input;
P1.value = This. textbox1.text;
Oracleparameter P2 = new oracleparameter ("result", oracletype. varchar, 100 );
P2.direction = system. Data. parameterdirection. returnvalue;
Cmd. Parameters. Add (P1 );
Cmd. Parameters. Add (P2 );
Orcn. open ();
Cmd. executenonquery ();
Orcn. Close ();
This. button_function.text = p2.value. tostring ();
The result is the return variable of the system-defined function. Note that the return type of the function parameter must be specified, and the return type of the command type must also be specified, in addition, there is no difference with General stored procedures.

C # Call Oracle return result set:
oraclecommand cmd = new oraclecommand ("pk_wt.p_wt", orcn);
cmd. commandtype = commandtype. storedprocedure;
oracleparameter p1 = new oracleparameter ("MYCS", oracletype. cursor);
p1.direction = system. data. parameterdirection. output;
cmd. parameters. add (P1);
oracledataadapter da = new oracledataadapter (CMD);
dataset DS = new dataset ();
da. fill (DS, "test");
This. datagrid1.datasource = Ds;
This. datagrid1.databind ();