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\$
  • \$\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\$ – t3chb0t Nov 24 '18 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\$ – Sᴀᴍ Onᴇᴌᴀ Nov 24 '18 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\$ – ADH Nov 24 '18 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\$ – t3chb0t Nov 25 '18 at 7:57
  • 1
    \$\begingroup\$ @t3chb0t Updated post. I did not realize the exception was a bug with Oracle. \$\endgroup\$ – ADH Nov 25 '18 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.