使用 ODP.NET 与企业库 DAAB 连接 Oracle

Connecting Oracle using ODP.NET with Enterprise Library DAAB

提问by vijay

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

我们的应用程序使用企业库 DAAB 来支持 oracle 和 sql 数据库。

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

存储过程之一是用于将图像上传到表。它是一个 BLOB 字段，参数设置为 DbType.Binary。

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

此功能对 SQL 没有任何问题，但是当涉及到 Oracle 时，我遇到了 32K 参数大小限制问题。

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

正如 SO 中所建议的，我将代码移至 ODP.NET，但我仍然面临同样的问题。

My App.config file setting:

我的 App.config 文件设置：

<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.

现在，当我运行应用程序时，Ent Lib DAAP 应该使用 Oracle.DataAccess.Client，但它仍然通过 System.Data.OracleClient 连接到 oracle db。所以32K的限制仍然存在。

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

为什么它不使用我在 App.config 中明确提到的 Oracle Data Provider？

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.

但是实例化的数据库是 GenericDatabase 而不是 OracleDatabase，这可能就是为什么当文件大小超过 32K 时，即使这项工作仍然会抛出异常。

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

如何将 ODP.NET 与 Enterprise Library 结合使用以解决 32K 大小限制问题？

RESOLVED:

已解决：

I followed hridyawalk 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.

我跟着hridya走过。正如他提到的，有 XML 注释错误，可以关闭（看这里）。还有一些命名空间冲突通过选择 Oracle.DataAccess.Client 解决。在这些之后，它编译成功。

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.)

这是我用来测试更改的示例应用程序中的代码片段。（示例解决方案现在引用新编译的 Data 和 Common dll。）

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.

我检查了命令对象，现在它是 Oracle.DataAccess.Client.OracleCommand 类型，而以前它是 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.

请记住，由于我已经修改了 DAAB 以使用 ODP.NET，因此我不需要使用 providerMappings 标记在配置文件中显式设置提供程序。

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.

但是当文件大小超过32K时，我仍然得到同样的错误，逐行进入代码发现问题出在DbType.Binary上。它没有更改为正确的 OracleDbType。

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

为了使它工作，我在 Enterprise Lib 的数据项目中添加了一个更多的代码修复。

File: \Oracle\OracleDatabase.cs

文件：\Oracle\OracleDatabase.cs

Method: AddParameter

方法：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

为 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.

我不知道这是否是正确的方法或其他一些时尚的解决方法已经可用。但它奏效了。

采纳答案by hridya pv

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

我希望以下步骤会给你正确的结果。

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

用 Oracle.DataAccess.Client 替换 System.Data.OracleClient；和 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

下载并安装最新版本的 Microsoft Enterprise Library ver 3.1 可在此处找到：- 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

从 Oracle 网站下载并安装 Oracle ODP.Net 您的 DLL 文件应该在：- 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

如果没有，请在以下路径 C:\Program Files\Microsoft Enterprise Library 3.1 - May 2007\src 上运行 msi

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

库的代码存储在以下路径 C:\EntLib3Src\App Blocks

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

备份原始 src 文件夹，以防以后需要它们 - C:\EntLib3Src\App Blocks\Src

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

打开解决方案文件EnterpriseLibrary.sln，进入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

将 Oracle.DataAccess.dll 引用添加到数据项目。您的 DLL 文件应该在：- 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]

搜索并替换以下内容 [您可以下载并使用本文附带的更新的 DLL]

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

文件：- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
文件：- C:\EntLib3Src\App Blocks\Src\Data\DatabaseConfigurationView.cs
文件：- C:\EntLib3Src\App Blocks\Src \Data\Oracle\OracleDataReaderWrapper.cs

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

查找：-using System.Data.OracleClient;
替换为：-using Oracle.DataAccess.Client; using Oracle.DataAccess.Types;

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

文件：- C:\EntLib3Src\App Blocks\Src\Data\Configuration\DbProviderMapping.cs 类：- DbProviderMapping

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

查找：-System.Data.OracleClient
替换为：-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

文件：- C:\EntLib3Src\App Blocks\Src\Data\Configuration\Manageability\ ConnectionStringsManageabilityProvider.cs
方法：-AddAdministrativeTemplateDirectives
查找：-System.Data.OracleClient
替换为：-Oracle.DataAccess.Client

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

文件：- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
方法：-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)

找 ：- 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)

用。。。来代替：- 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

原因：- OracleType 替换为 OracleDbType 作为第三个参数作为 odp.net API 中的类型名称

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

文件：- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
删除：- [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

文件：- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
查找：-OracleType.Raw
替换为：-OracleDbType.Raw

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

查找：-param.OracleType
替换为：-param.OracleDbType

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

查找：-OracleType.Cursor
替换为：-OracleDbType.RefCursor

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

查找：-parameter.OracleType
替换为：-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.

立即编译，如果出现错误，请执行以下警告为错误：XML 注释 - 删除突出显示的错误内容/用适当的注释替换它希望它现在应该可以正常编译。

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

现在编译上述项目生成的DLL可以同时用于SqlServer和Oracle [ODP.Net]

回答by Steve Pettifer

Bit late on this, but I can't see a good reason why the responder suggested the use of EL3.1 That is very out of date - 5 is the latest version (and was out for almost 2 years before this post) and the OP was using 4.1 according to the code sample. It is much simpler to use ODP with EL5 (have done so myself, utilising the EntlibContrib project) and although I cannot speak for any potential issues with parameter sizes as we do not use BLOBs, EntlibContrib natively allows the use of the OracleDbType so I think it probably wouldn't be a problem.

有点晚了，但我看不出为什么响应者建议使用 EL3.1 的充分理由。根据代码示例，OP 使用 4.1。将 ODP 与 EL5 一起使用要简单得多（我自己已经这样做了，利用 EntlibContrib 项目），虽然我不能说参数大小的任何潜在问题，因为我们不使用 BLOB，但 EntlibContrib 本身允许使用 OracleDbType，所以我认为应该不会有问题。

Just in case anyone stumbles on this post wondering how to get EL5 and ODP playing nicely with minimum fuss I can supply details - PM me.

以防万一有人偶然发现这篇文章，想知道如何让 EL5 和 ODP 以最小的麻烦很好地发挥作用，我可以提供详细信息 - PM 我。