Connecting Oracle using ODP.NET with Enterprise Library DAAB

Question

Our application is using Enterprise Library DAAB to support both oracle and sql databases.

One of the Stored Procedure is for uploading Image to Table. It's a BLOB field and the parameter is set to DbType.Binary.

This functionality works without any problem for SQL, But when comes to Oracle I hit the 32K parameter size limit issue.

As suggested in SO, I moved the code to ODP.NET, but I am still facing the same problem.

My App.config file setting:

<configuration>
<configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
</configSections>
<dataConfiguration defaultDatabase="Oracle">
    <providerMappings>
        <add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
            name="Oracle.DataAccess.Client" />
    </providerMappings>
</dataConfiguration>
<connectionStrings>
    <add name="Oracle" connectionString="Data Source=MYORACSER;USER ID=UNAME;PASSWORD=MYPWD;"
        providerName="Oracle.DataAccess.Client" />
</connectionStrings>

In my application code I am using enterprise library to access the DB

 Database db = DatabaseFactory.CreateDatabase(); 
 DbCommand cmd = db.GetStoredProcCommand(spName);
 cmd.CommandType = CommandType.StoredProcedure;
 db.AddInParameter(cmd, "DOCIMAGE", DbType.Binary, GetByteArrayFromFile(filePath));
 db.AddOutParameter(cmd, "return_value", DbType.Int32, 128);
 int row = db.ExecuteNonQuery(cmd);

I have the following assemblies referenced in my project:

Now when I run the application, Ent Lib DAAP is supposed to be using Oracle.DataAccess.Client, but It is still wired to oracle db through System.Data.OracleClient. So 32K limit is still there.

Why it is not using Oracle Data Provider as I have clearly mentioned in App.config?

In one post, It is mentioned to use the following snippet as a workaround,

DbProviderFactory providerFactory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
Database db = GenericDatabase(connectionString, providerFactory); 

This one seems working.

But the instantiated Database is of GenericDatabase instead of OracleDatabase, may be that's why even this work around still throwing exception when file size is over 32K.

How do I use ODP.NET with Enterprise Library for 32K size limit issue?

RESOLVED:

I followed hridya walk through. As he mentioned there were XML comment errors, which can be turned off (Look here). Also there were couple of namespace conflicts which were resolved by choosing Oracle.DataAccess.Client. After these, It compiled successfully.

Here is my code snippet from the sample application I made it to test the changes. (The sample solution now references new compiled Data and Common dlls.)

Database db = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand(sqlCode);
            cmd.CommandType = CommandType.StoredProcedure;
            db.AddInParameter(cmd, "DOCIMAGE", DbType.Binary, GetByteArrayFromFile(filePath));
            db.AddOutParameter(cmd, "return_value", DbType.Int32, 128);
            int rowID = db.ExecuteNonQuery(cmd);

I checked command object, now it is of type Oracle.DataAccess.Client.OracleCommand whereas previously it was System.Data.OracleClient.OracleCommand.

Remember since I have already modified the DAAB to use ODP.NET, I don't need to set the provider explicitly in the config file using providerMappings tag.

But I still get the same error when the file size exceeds 32K, Stepping into the code line by line revealed that the problem is with the DbType.Binary. It didn't get changed to proper OracleDbType.

To make it work I have added one more code fix in Enterprise Lib's Data Project.

File: \Oracle\OracleDatabase.cs

Method: AddParameter

Original code:

public override void AddParameter(DbCommand command, string name, DbType dbType, int size,
        ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn,
        DataRowVersion sourceVersion, object value)
    {
        if (DbType.Guid.Equals(dbType))
        {
            object convertedValue = ConvertGuidToByteArray(value);

            AddParameter((OracleCommand)command, name, OracleDbType.Raw, 16, direction, nullable, precision,
                scale, sourceColumn, sourceVersion, convertedValue);

            RegisterParameterType(command, name, dbType);
        }
        else
        {
            base.AddParameter(command, name, dbType, size, direction, nullable, precision, scale,
                sourceColumn, sourceVersion, value);
        }
    }

Added condition for DbType.Binary

Modified Code:

public override void AddParameter(DbCommand command, string name, DbType dbType, int size,
        ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn,
        DataRowVersion sourceVersion, object value)
    {
        if (DbType.Guid.Equals(dbType))
        {
            object convertedValue = ConvertGuidToByteArray(value);

            AddParameter((OracleCommand)command, name, OracleDbType.Raw, 16, direction, nullable, precision,
                scale, sourceColumn, sourceVersion, convertedValue);

            RegisterParameterType(command, name, dbType);
        }
        else if(DbType.Binary.Equals(dbType))
        {
            AddParameter((OracleCommand)command, name, OracleDbType.Blob, size, direction, nullable, precision,
                scale, sourceColumn, sourceVersion, value);

        }
        else
        {
            base.AddParameter(command, name, dbType, size, direction, nullable, precision, scale,
                sourceColumn, sourceVersion, value);
        }
    }

I don't know If this the right way to do it or some other sleek workaround is already available. But it worked.

Answer 1

I hope the following steps will give you the correct result.

To replace System.Data.OracleClient with Oracle.DataAccess.Client; and Oracle.DataAccess.Types

Download and install the latest version of Microsoft Enterprise Library ver 3.1 Can be found here:- http://msdn2.microsoft.com/en-us/library/aa480453.aspx

Download and install the Oracle ODP.Net from Oracle website Your DLL file should be in :- C:\oracle\product\11.1.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll

When it prompts you to install the source do so by using the checkbox.

If you didn't then run the msi on the following path C:\Program Files\Microsoft Enterprise Library 3.1 - May 2007\src

The code for the library gets stored on the following path C:\EntLib3Src\App Blocks

Take a backup of the original src folder in case you need them later - C:\EntLib3Src\App Blocks\Src

Open the solution file EnterpriseLibrary.sln And get to the data project under Data Access Application Block

Add Oracle.DataAccess.dll Reference to the Data Project. Your DLL file should be in :- C:\oracle\product\11.1.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll

Search and replace the following [ Instead you could download and use the updated DLL thats attached to this article]

File :- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
File :- C:\EntLib3Src\App Blocks\Src\Data\DatabaseConfigurationView.cs
File :- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDataReaderWrapper.cs

Find :- using System.Data.OracleClient;
Replace with:- using Oracle.DataAccess.Client; using Oracle.DataAccess.Types;

File :- C:\EntLib3Src\App Blocks\Src\Data\Configuration\DbProviderMapping.cs Class:- DbProviderMapping

Find :- System.Data.OracleClient
Replace with:- Oracle.DataAccess.Client

File :- C:\EntLib3Src\App Blocks\Src\Data\Configuration\Manageability\ ConnectionStringsManageabilityProvider.cs
Method:- AddAdministrativeTemplateDirectives
Find :- System.Data.OracleClient
Replace with:- Oracle.DataAccess.Client

File :- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
Method:- AddParameter

Find :- public void AddParameter(OracleCommand command, string name, OracleType oracleType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)

Replace with:- public void AddParameter(OracleCommand command, string name, OracleDbType oracleType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)

Reason:- OracleType replaced with OracleDbType as the third parameter as that the type name in the odp.net API

File:- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
Remove:- [OraclePermission(SecurityAction.Demand)] -

Haven't got a clue what that does if someone does please brief on feedback session

File:- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
Find:- OracleType.Raw
Replace with:- OracleDbType.Raw

Find:- param.OracleType
Replace with:- param.OracleDbType

Find:- OracleType.Cursor
Replace with:- OracleDbType.RefCursor

Find:- parameter.OracleType
Replace with:- parameter.OracleDbType

Compile now and if you get an error do the following Warning as Error : XML comment on - Remove the highlighted error content / replace it with approp comment Hopefully it should compile fine now.

Now the DLL that was generated by compiling the above project can be used against both SqlServer and Oracle [ODP.Net]