I wrote the following code to use Entity Framework 6 and Managed Oracle Providers to call an Oracle stored procedure that returns multiple cursors.
Would like some input about the code I used to open and close the database connection. Please note that the open and close code is a workaround for a possible bug in the Oracle library that occurs every time using is used. Doing that instead of the workaround would throw the following exception:
System.ObjectDisposedException: 'Cannot access a disposed object.Object name: 'OracleConnection'.'
Would also like some input on the way I am getting the multiple cursors and whether or not there might be a better way.
Working Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.Data.Entity.Infrastructure;
namespace MyCompany
{
public class MyClass
{
private MyDbContext dbContext = new MyDbContext();
public MyItems GetMyItems(string id)
{
var sqlQuery = "";
var oracleParameters = new List<OracleParameter>();
var oneEntityList = new List<OneEntity>();
var twoEntityList = new List<TwoEntity>();
var threeEntityList = new List<ThreeEntity>();
sqlQuery = @"
BEGIN
MY_PACKAGE.GetMyItems(:id, :p_cursor1, :p_cursor2, :p_cursor3);
END;
";
oracleParameters = new List<OracleParameter>
{
new OracleParameter("p_id", id),
new OracleParameter("p_cursor1", OracleDbType.RefCursor, ParameterDirection.Output),
new OracleParameter("p_cursor2", OracleDbType.RefCursor, ParameterDirection.Output),
new OracleParameter("p_cursor3", OracleDbType.RefCursor, ParameterDirection.Output)
};
var connection = dbContext.Database.Connection;
connection.Open();
var command = connection.CreateCommand();
command.CommandText = sqlQuery;
command.Parameters.AddRange(oracleParameters.ToArray());
using (var reader = command.ExecuteReader())
{
oneEntityList = ((IObjectContextAdapter)dbContext).ObjectContext
.Translate<OneEntity>(reader)
.ToList();
reader.NextResult();
twoEntityList = ((IObjectContextAdapter)dbContext).ObjectContext
.Translate<TwoEntity>(reader)
.ToList();
reader.NextResult();
threeEntityList = ((IObjectContextAdapter)dbContext).ObjectContext
.Translate<ThreeEntity>(reader)
.ToList();
}
connection.Close();
return new MyItems { OneEntity = oneEntityList, TwoEntity = twoEntityList, ThreeEntity = threeEntityList };
}
}
}
System.ObjectDisposedException: 'Cannot access a disposed object.Object name: 'OracleConnection'.'” from the description, yet didn’t change the code. Does the error still occur? \$\endgroup\$usingstatement to which you will replay that it doesn't work because an exception is thrown... this is a vicious circle. You've removed a very important fact from your question. Please clarify that this is a workaround for the apparent bug in the Oracle library that occurs everytimeusingis used and it throws the exception you've named in the first version. \$\endgroup\$