First steps in EntityLite to support ORACLE ref cursors

Executing the procedure

using (var ds = new NorthwindDataService())
{
    object cSubTree;
    ds.EmployeeRepository.EmployeeSubtree(2, out cSubTree);
    IDataReader reader = ((dynamic)cSubTree).GetDataReader();
                
    foreach(var employee in reader.ToList<Employee>())
    {
        Console.WriteLine("{0}, {1}", employee.FirstName, employee.LastName);
    }
}

modified create procedure command method

public static DbCommand CreateEmployeeSubtreeProcedure(DbConnection connection, string parameterPrefix)
{
    var cmd = connection.CreateCommand();
    cmd.CommandText = "employee_subtree";
    cmd.CommandType = CommandType.StoredProcedure;
    IDbDataParameter p = null;

    p = cmd.CreateParameter();
    p.ParameterName = parameterPrefix + "P_EMPLOYEE_ID";
    p.DbType = DbType.Decimal;
    p.Direction = ParameterDirection.Input;
    p.SourceColumn = "P_EMPLOYEE_ID";
    cmd.Parameters.Add(p);

    p = cmd.CreateParameter();
    p.ParameterName = parameterPrefix + "C_SUBTREE";
    Type paramType = p.GetType();
    var pi = paramType.GetProperty("OracleDbType");
    var refCursorValue = Enum.Parse(pi.PropertyType, "RefCursor");
    pi.SetValue(p, refCursorValue, null);


    p.Direction = ParameterDirection.Output;
    p.SourceColumn = "C_SUBTREE";
    cmd.Parameters.Add(p);

    return cmd;
}

original generated create procedure command method

public static DbCommand CreateEmployeeSubtreeProcedure(DbConnection connection, string parameterPrefix)
{
	var cmd = connection.CreateCommand();
	cmd.CommandText = "employee_subtree";
	cmd.CommandType = CommandType.StoredProcedure;
	IDbDataParameter p = null;

	p = cmd.CreateParameter();
	p.ParameterName = parameterPrefix + "P_EMPLOYEE_ID";
	p.DbType = DbType.Decimal;
	p.Direction = ParameterDirection.Input;
	p.SourceColumn = "P_EMPLOYEE_ID";
	cmd.Parameters.Add(p);

	p = cmd.CreateParameter();
	p.ParameterName = parameterPrefix + "C_SUBTREE";
	p.DbType = DbType.Object;
	p.Direction = ParameterDirection.Output;
	p.SourceColumn = "C_SUBTREE";
	cmd.Parameters.Add(p);

	return cmd;
}

procedure

CREATE OR REPLACE PROCEDURE employee_subtree(
  p_employee_id IN BINARY_INTEGER,
  c_subtree OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN c_subtree FOR
  SELECT 
    employee_id, reports_to, first_name, last_name 
  FROM 
    employees
  CONNECT BY PRIOR employee_id = reports_to
  START WITH employee_id = p_employee_id;
END;