I wrote the following code to use Entity Framework 6 and Oracle managed providers to call an Oracle stored procedure that returns multiple cursors.
I'm not sure of the code I used to open and close the database connection. When I tried a usage statement for the connection, I received the following error:
System.ObjectDisposedException: Can not access a deleted object. Name of the object: & # 39; OracleConnection & # 39;. & # 39;.
I would also like some comment on the way I am getting multiple cursors and whether or not there is a better way.
Work code:
using the system;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.Data.Entity.Infrastructure;
MyCompany namespace
{
public class MyClass
{
private MyDbContext dbContext = new MyDbContext ();
My public items GetMyItems (string ID)
{
var sqlQuery = "";
var oracleParameters = new list();
var oneEntityList = new list();
var twoEntityList = new list();
var threeEntityList = new list();
sqlQuery = @ "
START
MY_PACKAGE.GetMyItems (: id ,: p_cursor1 ,: p_cursor2 ,: p_cursor3);
FINISH;
";
oracleParameters = new list
{
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(reader)
.To list();
reader.NextResult ();
twoEntityList = ((IObjectContextAdapter) dbContext) .ObjectContext
.Translate(reader)
.To list();
reader.NextResult ();
threeEntityList = ((IObjectContextAdapter) dbContext) .ObjectContext
.Translate(reader)
.To list();
}
connection.close ();
return new MyItems {OneEntity = oneEntityList, TwoEntity = twoEntityList, ThreeEntity = threeEntityList};
}
}
}