oraclehelper filldataset or another way to get SYS_REFCURSOR values at ASP.NET

To whom it may respond to,

We are developing our project using .net framework 4.0,Oracle 11gR2. The problem is that , we have to use Oraclehelper class, no other options, and we can't get SYS_REFCURSOR values . 开发者_JAVA技巧When googled , we have catched some pages writing about filldataset method of oraclehelper class, but this class doesn't exist in our Oraclehelper class.

Any workarounds, templates, examples etc. to get SYS_REFCURSOR values via Oraclehelper class?

Thank you for your concern,

Best Regards,

Kayhan YÜKSEL

assuming you are using the sourceforge.net/projects/oraclehelpernet "oraclehelper" it is build ontop of ODP (ie Oracle.DataAccess.Client) all you would need to do is: (this is from http://download.oracle.com/docs/cd/B28359_01/win.111/b28375/featRefCursor.htm)

String cmdTxt1 = "begin open :1 for select col1 from test; end;";
OracleCommand cmd = new OracleCommand(cmdTxt1, conn);
OracleParameter outRefPrm = cmd.Parameters.Add("outRefPrm",
      OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
    cmd.ExecuteNonQuery(); // Execute the anonymous PL/SQL block

You can also look in %oracle_home%\client_1\odp.net\samples\4\RefCursor for 7 examples (this is when ODP is installed that is)

Since the OracleHelper just creates a wrapper around ODP, all you need to do is create the parameter as OracleDbType.RefCursor and pass it into the call (be it an execute non-query or datareader or whatnot)

now to do this via a procedure:

PROCEDURE Get1CurOut(p_cursor1 out refCursor) is 
      BEGIN 
      OPEN p_cursor1 for select * from emp; 
      END Get1CurOut; 

and to the C#

OracleCommand cmd = new OracleCommand("Get1CurOut", con);
      cmd.CommandType = CommandType.StoredProcedure;

      // Bind 
OracleParameter oparam = cmd.Parameters.Add("refcursor", OracleDbType.RefCursor);
  oparam.Direction = ParameterDirection.Output;

      try 
      {
        // Execute command; Have the parameters populated
        cmd.ExecuteNonQuery();

        // Create the OracleDataAdapter
        OracleDataAdapter da = new OracleDataAdapter(cmd);

        // Populate a DataSet with refcursor1.
        DataSet ds = new DataSet();
        da.Fill(ds, "refcursor1", (OracleRefCursor)(cmd.Parameters["refcursor1"].Value));

        // Print out the field count the REF Cursor
        Console.WriteLine("Field count: " + ds.Tables["refcursor1"].Columns.Count);
      }

this is lifted (with slight modification) from %oracle_home%\client_1\odp.net\samples\4\RefCursor\sample1.cs

here is an (untested) OracleHelper example:

string connectionString = "User Id=scott;Password=tiger;Data Source=oracle";
CommandType commandType  = CommandType.StoredProcedure;
string commandText = "Get1CurOut";

OracleParameter oparam = cmd.Parameters.Add("refcursor", OracleDbType.RefCursor);
      oparam.Direction = ParameterDirection.Output;



 OracleDataReader reader;

        reader = OracleHelper.ExecuteReader(connectionString, commandType, commandText, oparam) ;

        // show the first row
        reader.Read();

        // Print out SCOTT.EMP EMPNO column
        Console.WriteLine("EMPNO: {0}", reader.GetDecimal(0));

        // Print out SCOTT.EMP ENAME column
        Console.WriteLine("ENAME: {0}", reader.GetString(1));