Read Oracle SYS_REFCURSOR in C# Entity Framework?

Question

I have a simple C# console application and its code is like this:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            SHRSContext shrsContext = new SHRSContext();

            DbCommand cmd = shrsContext.Database.Connection.CreateCommand();

            cmd.CommandText = "PKG_SHRS.GETLOGINATTEMPT";
            cmd.CommandType = CommandType.StoredProcedure;

            var pinUsername = new OracleParameter("pinUsername", OracleDbType.Varchar2, ParameterDirection.Input);
            pinUsername.Value = "admin";

            var poutLoginAttemptCount = new OracleParameter("poutLoginAttemptCount", OracleDbType.Int16, ParameterDirection.Output);

            cmd.Parameters.Add(pinUsername);
            cmd.Parameters.Add(poutLoginAttemptCount);

            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
            cmd.Connection.Close();

            Console.WriteLine(poutLoginAttemptCount.Value.ToString());
            Console.ReadLine();
        }
    }
}

It uses entity framework and Oracle 11g as back-end. It calls a Oracle Procedure in a package PKG_SHRS.GETLOGINATTEMPT and it works perfectly.

The above code just provide a single output parameter as a numeric data type. If I need to get a table SYS_REFCURSOR as output parameter, what do I need to change in the given code?

Answer 1

I assume VALIDATELOGIN procedure's output parameter is poutUserCursor and its type SYS_REFCURSOR. Try this code.

SHRSContext shrsContext = new SHRSContext();

DbCommand cmd = shrsContext.Database.Connection.CreateCommand();

cmd.CommandText = "PKG_SHRS.GETLOGINATTEMPT";
cmd.CommandType = CommandType.StoredProcedure;

var pinUsername = new OracleParameter("pinUsername", OracleDbType.Varchar2, ParameterDirection.Input);
pinUsername.Value = "admin";

// Assuming output parameter in the procedure is poutUserCursor
var poutUserCursor = new OracleParameter("poutUserCursor", OracleDbType.RefCursor, ParameterDirection.Output);

cmd.Parameters.Add(pinUsername);
cmd.Parameters.Add(poutUserCursor);

cmd.Connection.Open();

DbDataReader dr = cmd.ExecuteReader();

string column1 = string.Empty;
string column2 = string.Empty;

// Assume this will return one row. If multiple rows return, use while loop
if (dr.Read())
{
    // GetString will return string type. You can change as you need
    column1 = dr.GetString(0);
    column2 = dr.GetString(1);
}

cmd.Connection.Close();