2
\$\begingroup\$

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 };

        }
    }
}
\$\endgroup\$
6
  • \$\begingroup\$ In my experience EF and Oracle don't like each other. EF is great only for the sql server. With oracle it's easier to use Dapper and raw queries. \$\endgroup\$ Commented Nov 24, 2018 at 15:07
  • 1
    \$\begingroup\$ It appears that you removed the errorSystem.ObjectDisposedException: 'Cannot access a disposed object.Object name: 'OracleConnection'.'” from the description, yet didn’t change the code. Does the error still occur? \$\endgroup\$ Commented Nov 24, 2018 at 19:12
  • 1
    \$\begingroup\$ No, the error does not occur. There never was an error on the code I posted. I was mentioning that if I used a using statement an error occurred. This code review post is legit and should not be on hold. \$\endgroup\$ Commented Nov 24, 2018 at 20:30
  • \$\begingroup\$ This code review post is legit - I think it could be if you reframed it because 1000% the first suggestion will be to use the using statement 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 everytime using is used and it throws the exception you've named in the first version. \$\endgroup\$ Commented Nov 25, 2018 at 7:57
  • 1
    \$\begingroup\$ @t3chb0t Updated post. I did not realize the exception was a bug with Oracle. \$\endgroup\$ Commented Nov 25, 2018 at 13:38

0

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.