ExecuteSprocAccessor with Refcursor in EnterpriseLibrary 6.0 DAAB

Topics: Data Access Application Block
Jun 25, 2013 at 3:44 PM
I am using VS2012, EnterpriseLibrary 6.0 [DAAB]. My connectivity to the Database [Oracle] is fine.

I have a Oracle procedure, which does not have any input parameter only the output parameter is there, which is of type RefCursor.

I am able to get the output by calling the procedure using Execute Reader and ExecuteDataset.

Now I am trying to use DatabaseExtension.ExecuteSprocAccessor. I am getting the error, which says invalid type of argument.

Can any one provide me a good example for using the ExecuteSprocAccessor.

Thanks
Editor
Jun 27, 2013 at 3:53 AM
I'm assuming you are using OracleDatabase and System.Data.OracleClient.

When you invoke ExecuteDataset, ExecuteReader, and LoadDataSet the OracleDatabase adds the cur_OUT refcursor parameter. It looks like when using accessors that this never gets called.

The implementation for adding the refcursor in OracleDatabase is private so it looks like it's a bit tricky to get it working. This thread, http://entlib.codeplex.com/discussions/239555, seems to use a similar approach to what I thought would work -- a custom parameter mapper to add the cur_OUT refcursor. Unfortunately, it seems like a resolution was not found. It might be worth trying something similar.

Other approaches that might work would be a custom SprocAccessor or a custom OracleDatabase implementation that adds the refcursor OracleParameter.

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Jun 27, 2013 at 5:57 PM
Hi..

I am using OracleDatabase, but NOT System.Data.OralceClient. We are using ODP.net [Oracle.DataAccess.Client].

In fact, I had tired the method given above, but still the issue is appearing. As mentioned before, the command obj is missing.

Thanks
Editor
Jun 28, 2013 at 3:25 PM
I'm curious what data provider you are using for ODP.NET? Is it from EntlibContrib?

To give you an idea this is basically what the source code is doing:
AddParameter(command as OracleCommand, "CUR_OUT", OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);

What if you try this IParameterMapper?
    public class OracleParameterMapper : IParameterMapper
    {
        readonly OracleDatabase database;
        public OracleParameterMapper(Database database)
        {
            this.database = database as OracleDatabase;
        }

        public void AssignParameters(DbCommand command, object[] parameterValues)
        {
            if (parameterValues.Length > 0)
            {
                GuardParameterDiscoverySupported();
                database.AssignParameters(command, parameterValues);
            }

            if (CommandType.StoredProcedure == command.CommandType)
            {
                bool doesQueryProcedureNeedCursorParameter = true;

                foreach (OracleParameter parameter in command.Parameters)
                {
                    if (parameter.OracleType == OracleType.Cursor)
                    {
                        doesQueryProcedureNeedCursorParameter = false;
                    }
                }

                if (doesQueryProcedureNeedCursorParameter)
                {
                    database.AddParameter(command as OracleCommand, "CUR_OUT", OracleType.Cursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
                }
            }
        }

        private void GuardParameterDiscoverySupported()
        {
            if (!database.SupportsParemeterDiscovery)
            {
                throw new InvalidOperationException(
                    "Parameter Discovery Not Supported");
            }
        }
    }

If that doesn't work then I think you will need to try a different approach: either modify the source code to add the ref cursor or alternately create a custom Accessor or custom OracleDatabase to add the ref cursor. Try to recreate the ExecuteReader/DataSet (since they work) logic for how the parameters (as well as the ref cursor parameter) are added for an accessor.

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Jul 2, 2013 at 3:23 PM
Hi Randy Levy..

Thanks for your reply.

To give you more info, I give below the environement details:

ODP.net: 4.112.3.0 [ Downloaded from Oracle site]
EnterpriseLibrary: 6.0.0.0

I had created a parameter mapper and IRowmapper, still the same issue is happening.

Can anyone, explain me, what could be the issue. We are planning to convert the whole project from old Library to New Library.

Thanks in Advance.
Jul 2, 2013 at 4:08 PM
Hi Randy Levy..

//GREAT NEWS //

I found the ROOT CAUSE of the issue and it is in the Enterprise Library 6.0, DataBlock source code in the class CommandAccessor.cs at Line no. 66.
Because inside the command object, the parameter collections are not there. To resolve this, we need to do the following:-

In the below method, the command object was inside the using statement. Due to that, the command looses it is parameter collection values when it is outside the scope of the using clause. So, I have removed the using clause and it is working fine.

/// <summary>
    /// Executes the stored procedure and returns an enumerable of <typeparamref name="TResult"/>.
    /// The enumerable returned by this method uses deferred loading to return the results.
    /// </summary>
    /// <param name="parameterValues">Values that will be interpret by an <see cref="IParameterMapper"/> and function as parameters to the stored procedure.</param>
    /// <returns>An enumerable of <typeparamref name="TResult"/>.</returns>
    public override IEnumerable<TResult> Execute(params object[] parameterValues)
    {
        /*
        using (DbCommand command = Database.GetStoredProcCommand(procedureName))
        {
            parameterMapper.AssignParameters(command, parameterValues);
            return base.Execute(command);
        }
        */
        DbCommand command = Database.GetStoredProcCommand(procedureName);
        parameterMapper.AssignParameters(command, parameterValues);
        return base.Execute(command);
    }
I am now very happy, to find the root cause. It is now working fine. Output is coming like a bullet.

Thanks
Jul 2, 2013 at 4:17 PM
Sorry for not mentioning the class, which needs the correction:

The class is SprocAccessor.cs, [The method is in Line no.97]

Thanks
Sep 4, 2013 at 3:48 PM
Any full source code sample us9ing ODP.net: 4.112.3.0 - EnterpriseLibrary: 6.0.0.0 ? EntLib Contrib ?

ODP.NET has new version, fully managed .NET.

http://stackoverflow.com/a/18193544/206730