ASP. NET Oracle Stored Procedures

Oracle Code

CREATE OR REPLACE PROCEDUREGd_cursor (MYCS1 out sys_refcursor,mycs2 off Sys_refcursor,a outvarchar) asBEGINA:='Test'; OPENMYCS1 for  SELECT 1  fromdual; OPENMYCS2 for  SELECT 2  fromdual;END;

C # code

  /// <summary>        ///executing an Oracle stored procedure returns multiple result sets/// </summary>        /// <param name= "strProcName" >Stored Procedure name</param>        /// <param name= "Resultcount" >number of returns</param>        /// <param Name= "Paras" >Parameters</param>        /// <returns>an array of arbitrary objects</returns>         Public  Object[] Excuteproc_n_result (stringstrProcName,intResultcount,paramsOracleparameter[] Paras) {            using(OracleConnection conn =NewOracleConnection ("User id= username; password= password; data source= database;") ) {OracleCommand cmd=NewOracleCommand (STRPROCNAME, conn); if(Paras! =NULL&& paras. Length >0)                {                     for(intj =0; J < paras. Length; J + +)                    {                        if(Paras[j]. Value = =NULL) {Paras[j]. Value=DBNull.Value; }}} cmd.                Parameters.addrange (paras); Cmd.commandtype=CommandType.StoredProcedure; Conn.                Open (); Cmd.                ExecuteNonQuery (); inti =0; //int noutputparameterscount = 0;                Object[] Objresult =New Object[Resultcount]; foreach(OracleParameter Pinchcmd. Parameters) {if(p.direction = = ParameterDirection.Output | | p.direction = =parameterdirection.inputoutput) {if(P.value isOracleDataReader) {OracleDataReader Reader= P.value asOracleDataReader; Objresult[i++] =convertdatareadertodatatable (reader); }                        Else{objresult[i++] =P.value; }                    }                }                returnObjresult; }        }        /// <summary>         ///convert DataReader to DataTable/// </summary>         /// <param name= "DataReader" >OleDbDataReader</param>         protectedDataTable convertdatareadertodatatable (OracleDataReader Reader) {DataTable objdatatable=NewDataTable ("tmpdatatable"); Try            {                intIntfieldcount = reader. FieldCount;//gets the number of columns in the current row;                 for(intIntcounter =0; Intcounter <= Intfieldcount-1; intcounter++{objDataTable.Columns.Add (reader). GetName (intcounter), reader.                GetFieldType (intcounter)); }                //Populate DataTableObjdatatable.beginloaddata (); //object[] objvalues = new Object[intfieldcount-1];                 Object[] Objvalues =New Object[Intfieldcount];  while(reader. Read ()) {reader.                    GetValues (objvalues); Objdatatable.loaddatarow (Objvalues,true); } reader.                Close ();                Objdatatable.endloaddata (); returnobjdatatable; }            Catch(Exception ex) {Throw NewException ("Error in conversion error!", ex); }        }

Calling methods

oracleparameter[] OracleParameter = new oracleparameter[]{
New OracleParameter ("MYCS1", OracleType.Cursor),
New OracleParameter ("MYCS2", OracleType.Cursor),
New OracleParameter ("a", oracletype.varchar,200),
};

Oracleparameter[0]. Direction = ParameterDirection.Output;
ORACLEPARAMETER[1]. Direction = ParameterDirection.Output;
ORACLEPARAMETER[2]. Direction = ParameterDirection.Output;

object[] xxx = Excuteproc_n_result ("Gd_cursor", 3, OracleParameter);

ASP. NET Oracle Stored Procedures