C # Displays the cursor data set returned by the stored procedure

1 、--Oracle Create Package declaration custom cursor Type

Create or Replace package Pk_var is

Type CSR_TP is REF CURSOR;

End

2 、--Oracle Create stored procedure return data collection

CREATE OR REPLACE PROCEDURE pr_getdata (
V_empno VARCHAR2, RC1 out PK_VAR.CSR_TP)
As
sSQL VARCHAR2 (2000);
Begin
sSQL: = ' select * from emp where empno=v_empno ';

Open RC1 for sSQL;
Return
End Pr_getdata;

3. C # code is as follows:

Create Oracle Parameters

oracleparameter[] Sqlparme = new Oracleparameter[2]

Parameter 1
Sqlparme[0] = new OracleParameter ("RC1", oracletype.cursor);
Sqlparme[0]. Direction = ParameterDirection.Output;
SQLPARME[1] = new OracleParameter ("Tkala", Oracletype.varchar);
SQLPARME[1]. Direction = ParameterDirection.Input;
SQLPARME[1]. Value = Aholeoldnum;

Get a DataTable data source from a stored procedure

Private DataTable Dbexecstoredprocedure (String storeurename, oracleparameter[] sqlparme)
{
Try
{
Using Microsoft's Oracle Access interface
if (DBHelper.G_pDBConn.State = = connectionstate.closed)//Get Data connection
DBHelper.G_pDBConn.Open ();
OracleCommand oracmd = new OracleCommand (Storeurename, dbhelper.g_pdbconn);
Oracmd.commandtype = CommandType.StoredProcedure;
OraCmd.Parameters.Clear ();//Clear First
foreach (OracleParameter Parme in Sqlparme)
{
ORACMD.PARAMETERS.ADD (Parme);
}
DataTable table = new DataTable ();

DateTime begtime = System.DateTime.Now;
OracleDataAdapter da1 = new OracleDataAdapter (oracmd);//Extract data
da1. Fill (table);
return table;
}
catch (Exception ex)
{
MessageBox.Show ("Execution stored procedure failed:" +ex. Message, "Debug");
return null;
}
finally {
DBHelper.G_pDBConn.Close ();
}
}

This article is from the "11046654" blog, please be sure to keep this source http://11056654.blog.51cto.com/11046654/1746175

C # Displays the cursor data set returned by the stored procedure