使用ODP.NET将Oracle与企业库DAAB连接起来 [英] Connecting Oracle using ODP.NET with Enterprise Library DAAB

查看：103 发布时间：2020/5/19 20:53:01 c# oracle enterprise-library odp.net

本文介绍了使用ODP.NET将Oracle与企业库DAAB连接起来的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我们的应用程序正在使用企业库DAAB支持oracle和sql数据库.

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

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

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

此功能对于SQL来说没有任何问题，但是对于Oracle而言我遇到了32K参数大小限制问题.

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

按照SO中的建议，我将代码移到了ODP.NET，但是我仍然面临着同样的问题.

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

我的App.config文件设置:

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>

在我的应用程序代码中，我正在使用企业库来访问数据库

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

我在项目中引用了以下程序集:

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

为什么不使用我在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);

这似乎很有效.

但是实例化的数据库是GenericDatabase而不是OracleDatabase，这也许就是为什么即使文件大小超过32K时，这种解决方法仍会引发异常的原因.

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.

如何将ODP.NET与Enterprise Library一起使用以解决32K大小限制问题?

已解决:

我关注了 hridya . 正如他提到的那样，存在XML注释错误，可以将其关闭(请参阅此处) . 另外，通过选择Oracle.DataAccess.Client也解决了几个命名空间冲突.之后，编译成功.

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.

这是我用来测试更改的示例应用程序中的代码片段. (示例解决方案现在引用了新的编译数据和Common dll.)

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

我检查了命令对象，现在它是Oracle.DataAccess.Client.OracleCommand类型，而以前是System.Data.OracleClient.OracleCommand.

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

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

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.

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

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.

为使其正常工作，我在Enterprise Lib的Data Project中又添加了一个代码修复程序.

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

文件: \ Oracle \ OracleDatabase.cs

方法: AddParameter

原始代码:

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

为DbType.Binary添加了条件

Added condition for DbType.Binary

修改后的代码:

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.

使用ODP.NET将Oracle与企业库DAAB连接起来 [英] Connecting Oracle using ODP.NET with Enterprise Library DAAB

问题描述

推荐答案

相关文章

C#/.NET最新文章

热门教程

热门工具

登录关闭

使用ODP.NET将Oracle与企业库DAAB连接起来 [英] Connecting Oracle using ODP.NET with Enterprise Library DAAB

问题描述

推荐答案

相关文章

C#/.NET最新文章

热门教程

热门工具

登录 关闭

登录关闭