1. Create an Oracle stored procedure with an output dataset
Create or Replace procedure Pro_test (in_top in Number,cur_out out Sys_refcursor) is
--Query the data for the specified number of records and return a total number of records, returning multiple datasets
Begin
Open Cur_out for
SELECT * from Dept_dict where rownum < in_top;
End Pro_test;
2. C # Call
Pu_sys.getconnobject con = new Pu_sys.getconnobject ();
OracleConnection conn = new OracleConnection (Con. Get_connstr ());
OracleCommand Dcomm = new OracleCommand ("Pro_test", conn);
Dcomm.commandtype = CommandType.StoredProcedure;
OracleParameter Dppass = new OracleParameter ();
Dppass.value = 5;
Dppass.direction = System.Data.ParameterDirection.Input;
Dppass.parametername = "In_top";
DCOMM.PARAMETERS.ADD (Dppass);
OracleParameter dpout = new OracleParameter ("Cur_out", oracletype.cursor);
Dpout.direction = System.Data.ParameterDirection.Output;
DCOMM.PARAMETERS.ADD (dpout);
OracleDataAdapter Da = new OracleDataAdapter (DCOMM);
DataSet Ds = new DataSet ();
Try
{
Da.fill (Ds);
}
catch (Exception)
{
}
Conn. Close ();
Conn. Dispose ();
C # calls Oracle's stored procedures with output datasets