C# and Oracle 10g database to call stored procedure

ado.netcdatasetoracle10g

I am a C# + SQL server database developer.

Now I am using Oracle as a database. I have one Oracle stored procedure retuning ref cursor.

CREATE OR REPLACE PROCEDURE Get_Trans_Data ( p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
  OPEN p_return_cur FOR
  select * from test_tbl1 ;
END Get_Trans_Data;

Using Oledb only how can I access this stored proc and save result in dataset?

I am not get the option for ref cursor datatype in Oledb.

Best Answer

You can try something like this. No need to add parameter for OUT's. Read more here 

static void Read()
{
    using (var currentConnection = new OleDbConnection(
        "provider=MSDAORA;Data Source=orcl;User ID=db_test;Password=db_test;"))
    {

        currentConnection.Open();
        using (var myCommand = new OleDbCommand())
        {
            myCommand.Connection = currentConnection;
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = "Get_Data";

            myCommand.Parameters.Add(
                new OleDbParameter(
                    "firstParam",
                    OleDbType.Integer, 0, ParameterDirection.Input,
                    true, 0, 0, "", DataRowVersion.Default, Convert.DBNull));

            myCommand.Parameters[0].Value = 42;

            var myDataReader = myCommand.ExecuteReader();
            while (myDataReader.Read())
                for (int i = 0; i < myDataReader.FieldCount; i++)
                    Console.WriteLine(myDataReader.GetName(i));
        }
    }
}

CREATE OR REPLACE PROCEDURE Get_Data (
   p_return_cur OUT SYS_REFCURSOR,
   firstParam INTEGER) 
IS 
BEGIN 
  OPEN p_return_cur FOR select * from test; 
END Get_Data;
Related Topic