C# 当 Oracle.DataAccess 起作用时，为什么 Oracle.ManagedDataAccess 不起作用？

Question

提问by Jeff

I'm developing a very simple application which I intend to use to troubleshoot an issue I am having on a few machines but before I even got that far I ran into a few issues, including cpu architecture differences and Oracle database libraries.

我正在开发一个非常简单的应用程序，我打算用它来解决我在几台机器上遇到的问题，但在我还没到那一步之前，我遇到了一些问题，包括 CPU 架构差异和 Oracle 数据库库。

I have a database server listed in tnsnames.ora, sitting in my C:\oracle\11g\network\admindirectory. If I tnsping this server I get the desired response. If I code my C# program to connect to this server with the following code using Oracle.DataAccess.Client, it works.

我在中列出了一个数据库服务器tnsnames.ora，位于我的C:\oracle\11g\network\admin目录中。如果我 tnsping 这个服务器，我会得到想要的响应。如果我使用 Oracle.DataAccess.Client 编写我的 C# 程序以使用以下代码连接到此服务器，则它可以工作。

string connectionString = "Data Source=DSDSDS;User Id=UNUNUN;Password=PWPWPW;";
DataTable dataTable = new DataTable();

using (var connection = new OracleConnection(connectionString)) {
    connection.Open();
    using (var command = new OracleCommand()) {
        command.Connection = connection;
        command.CommandText = sql;
        command.CommandType = CommandType.Text;
        using (var oda = new OracleDataAdapter(command)) {
            oda.Fill(dataTable);
        }
    }
}

However Oracle.DataAccess is dependent on the architecture of the system it runs on. I saw that there is another library Oracle.ManagedDataAccess which is architecture independent. When I use this library it no longer is able to connect to the server. An ORA-12545: Network Transport: Unable to resolve connect hostnameis thrown.

然而，Oracle.DataAccess 依赖于它运行的系统架构。我看到还有另一个与架构无关的库 Oracle.ManagedDataAccess。当我使用这个库时，它不再能够连接到服务器。一个ORA-12545: Network Transport: Unable to resolve connect hostname被抛出。

Why is this the case? What is different between these two libraries because based upon what I've read thus far this shouldn't be an issue.

为什么会这样？这两个库之间有什么不同，因为根据我迄今为止所阅读的内容，这应该不是问题。

Extra information:

额外的信息：

%ORACLE_HOME% and %TNS_ADMIN% are NOT defined (remember that tnsping and Oracle.DataAccess work)
PATH has C:\oracle\11g\BINdefined.
My machine only has one tnsnames.orafile

%ORACLE_HOME% 和 %TNS_ADMIN% 没有定义（记住 tnsping 和 Oracle.DataAccess 工作）
PATH 已C:\oracle\11g\BIN定义。
我的机器只有一个tnsnames.ora文件

If I move tnsnames.ora to the same location as my .exe file, it works. Why can Oracle.DataAccess find tnsnames.ora in the C:\oracle\11g\network\admindirectory but Oracle.ManagedAccess cannot?

如果我将 tnsnames.ora 移动到与我的 .exe 文件相同的位置，它就可以工作。为什么Oracle.DataAccess 可以在C:\oracle\11g\network\admin目录中找到tnsnames.ora而Oracle.ManagedAccess 不能？

Answer 1

采纳答案by metalheart

The order of precedence for resolving TNS names in ODP.NET, Managed Driver is this (see here):

在 ODP.NET、托管驱动程序中解析 TNS 名称的优先顺序是这样的（参见此处）：

data source alias in the 'dataSources' section under section in the .NET config file.
data source alias in the tnsnames.ora file at the location specified by 'TNS_ADMIN' in the .NET config file.
data source alias in the tnsnames.ora file present in the same directory as the .exe.
data source alias in the tnsnames.ora file present at %TNS_ADMIN% (where %TNS_ADMIN% is an environment variable setting).
data source alias in the tnsnames.ora file present at %ORACLE_HOME%\network\admin (where %ORACLE_HOME% is an environment variable setting).

.NET 配置文件部分下的“dataSources”部分中的数据源别名。
.NET 配置文件中“TNS_ADMIN”指定位置的 tnsnames.ora 文件中的数据源别名。
tnsnames.ora 文件中的数据源别名与 .exe 位于同一目录中。
tnsnames.ora 文件中的数据源别名位于 %TNS_ADMIN%（其中 %TNS_ADMIN% 是环境变量设置）。
tnsnames.ora 文件中的数据源别名位于 %ORACLE_HOME%\network\admin（其中 %ORACLE_HOME% 是环境变量设置）。

I believe the reason your sample works with Oracle.DataAccess but not with Oracle.ManagedDataAccess is that Windows registry based configuration is not supported for the latter (see documentation) - the ODP.NET installation sets an ORACLE_HOME registry key (HLKM\SOFTWARE\Oracle\Key_NAME\ORACLE_HOME) which is recognized only by the unmanaged part.

我相信您的示例适用于 Oracle.DataAccess 但不适用于 Oracle.ManagedDataAccess 的原因是后者不支持基于 Windows 注册表的配置（请参阅文档）- ODP.NET 安装设置了 ORACLE_HOME 注册表项 (HLKM\SOFTWARE\Oracle \Key_NAME\ORACLE_HOME），它只能被非托管部分识别。

Answer 2

回答by kolbasov

Try to add the path to tnsnames.ora to the config file:

尝试将 tnsnames.ora 的路径添加到配置文件中：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <oracle.manageddataaccess.client>
    <version number="4.112.3.60">
      <settings>
        <setting name="TNS_ADMIN" value="C:\oracle\product.2.0\client_1\NETWORK\ADMIN\" />
      </settings>
    </version>
  </oracle.manageddataaccess.client>
</configuration>

Answer 3

回答by Siddiqui

I had the similar issue......to solve this what I did was to uninstall the ODP. Net and re-install in the same directory as oracle server......with server option you will notice that most of the products are already installed (while 12c database installation) so just select the other features and finally finish the installation....

我有类似的问题......为了解决这个问题，我所做的是卸载 ODP。net并重新安装在oracle服务器同一目录下......使用服务器选项您会注意到大部分产品已经安装（而12c数据库安装）所以只需选择其他功能并最终完成安装。 ...

Please note that this workaround works only if you have installed 12c on the same machine i.e. on your laptop............

请注意，此解决方法仅在您在同一台机器上（即在您的笔记本电脑上）安装了 12c 时才有效......

If your database is located on the server machine other than your laptop then please select client option and not the server and then include TNS_ADMIN in your app.config and do not forget to specify the version...

如果您的数据库位于笔记本电脑以外的服务器计算机上，那么请选择客户端选项而不是服务器，然后在您的 app.config 中包含 TNS_ADMIN 并且不要忘记指定版本...

since my installation is on my laptop so my App.config is as below:

因为我的安装是在我的笔记本电脑上，所以我的 App.config 如下：

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
    </startup>
</configuration>


 /////////the below code is a sample from oracle company////////////////


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;

///copy these lines in a button click event 
    string constr = "User Id=system; Password=manager; Data Source=orcl;";
// Click here and then press F9 to insert a breakpoint
        DbProviderFactory factory =
    DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");
            using (DbConnection conn = factory.CreateConnection())
            {
                conn.ConnectionString = constr;
                try
                {
                    conn.Open();
                    OracleCommand cmd = (OracleCommand)factory.CreateCommand();
                    cmd.Connection = (OracleConnection)conn;

//to gain access to ROWIDs of the table
//cmd.AddRowid = true;
                    cmd.CommandText = "select * from all_users";

                    OracleDataReader reader = cmd.ExecuteReader();

                    int visFC = reader.VisibleFieldCount; //Results in 2
                    int hidFC = reader.HiddenFieldCount;  // Results in 1

                    MessageBox.Show(" Visible field count: " + visFC);

                    MessageBox.Show(" Hidden field count: " + hidFC);


                    reader.Dispose();
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                      MessageBox.Show(ex.StackTrace);
                }
            }

Answer 4

回答by Coleman

I received the same error message. To resolve this I just replaced the Oracle.ManagedDataAccessassembly with the older Oracle.DataAccessassembly. This solution may not work if you require new features found in the new assembly. In my case I have many more higher priority issues then trying to configure the new Oracleassembly.

我收到了同样的错误信息。为了解决这个问题，我只是用Oracle.ManagedDataAccess旧的Oracle.DataAccess程序集替换了程序集。如果您需要在新装配中找到新特征，则此解决方案可能不起作用。在我的情况下，我有更多更高优先级的问题，然后尝试配置新Oracle程序集。

Answer 5

回答by Underground

Once I found what format it was looking for in the connection string, it worked just fine like this with Oracle.ManagedDataAccess. Without having to mess around with anything separately.

一旦我在连接字符串中找到它正在寻找的格式，它就可以像这样使用 Oracle.ManagedDataAccess。无需单独处理任何事情。

DATA SOURCE=DSDSDS:1521/ORCL;

Answer 6

回答by user3902302

To avoid all the Oracle mess of not knowing where it is looking for the TNSNAMES.ORA (I have the added confusion of multiple Oracle versions and 32/64 bit), you can copy the setting from your existing TNSNAMES.ORA to your own config file and use that for your connection.
Say you're happy with the 'DSDSDS' reference in TNSNAMES.ORA which maps to something like:

为了避免不知道在哪里寻找 TNSNAMES.ORA 的所有 Oracle 混乱（我有多个 Oracle 版本和 32/64 位的额外混淆），您可以将设置从现有的 TNSNAMES.ORA 复制到您自己的配置文件并将其用于您的连接。
假设您对 TNSNAMES.ORA 中的“DSDSDS”引用感到满意，它映射到以下内容：

DSDSDS=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=DSDSDSHost)(Port=4521)))(CONNECT_DATA=(SERVICE_NAME=DSDSDSService)))

您可以在第一个“=”之后获取文本并在使用“DSSDSD”的任何地方使用该文本，并且无需查找 TNSNAMES.ORA 即可知道如何连接。

现在您的连接字符串将如下所示：

string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=DSDSDSHost)(Port=4521)))(CONNECT_DATA=(SERVICE_NAME=DSDSDSService)));User Id=UNUNUN;Password=PWPWPW;";

string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=DSDSDSHost)(Port=4521)))(CONNECT_DATA=(SERVICE_NAME=DSDSDSService)));User Id=UNUNUN;Password=PWPWPW;";

Answer 7

回答by ppenchev

In my case everything said above was OK, but I still have been receiving ORA-12545: Network Transport: Unable to resolve connect hostname

就我而言，上面说的一切都很好，但我仍然收到 ORA-12545: Network Transport: Unable to resolve connect hostname

I tried to ping the Oracle machine and found out I cannot see it and added it to the hosts file. Then I received another error message ORA-12541: TNS:no listener. After investigation I realized that pinging the same hostname from different machines getting different IP addresses(I don't know why) and I changed the IP address in my host file, which resolved the problem on 100%.

我尝试 ping Oracle 机器，发现我看不到它并将其添加到主机文件中。然后我收到了另一条错误信息ORA-12541: TNS:no listener。经过调查，我意识到从不同的机器 ping 相同的主机名会得到不同的 IP 地址（我不知道为什么），我更改了主机文件中的 IP 地址，这 100% 解决了问题。

I'm bothering to write my experience as it seems obvious, but although I was sure the problem is in the above settings I totally forgot to check if I really can see the remote DB machine out there. Keep it in mind when you are out of ideas what is going on.....

我很费心去写我的经验，因为它看起来很明显，但是尽管我确定问题出在上述设置中，但我完全忘记检查我是否真的可以看到那里的远程数据库机器。当你对正在发生的事情一无所知时，请记住它......

These links helped me a lot:

这些链接对我帮助很大：

http://www.moreajays.com/2013/03/ora-12545-connect-failed-because-target.html http://www.orafaq.com/wiki/ORA-12541

Answer 8

回答by T.S.

A "little" late to the party but the real answer to this - if you use Oracle.ManagedDataAccessODP.NET provider, you should forget about things like network\admin, Oracle client, Oracle_Home, etc.

一个“小”迟到了，但真正的答案，这一点-如果你使用Oracle.ManagedDataAccessODP.NET提供者，你应该忘记这样的事情network\admin，Oracle client，Oracle_Home，等。

Here is what you need

这是你需要的

Download and install Oracle Developer Tools for VSor ODAC. Note - Dev tools will install ODAC for you. This will create relatively small installation under C:\Program Files (x86). With full dev tools, under 60Mb
In your project you will install Nuget package with corresponding version of ODP.net (Oracle.ManagedDataAccess.dll)which you will reference
At this point you have 2 options to connect.
- a) In the connection string set datasourcein the following format
  DataSource=ServerName:Port/SID . . .or DataSource=IP:Port/SID . . .
- b) Create tnsnames.orafile (only it is going to be different from previous experiences). Have entry in it:
  AAA = (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ServerNameOrIP)(PORT = 1521))
  (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SIDNAME)))
  And place this file into your bin folder, where your application is running from. Now you can connect using your connection name - DataSource=AAA . . .So, even though you have tnsnames.ora, with ODP.net managedit works a bit different - you create local TNS file. And now, it is easy to manage it.

下载并安装适用于 VS或ODAC 的Oracle Developer Tools。注 - 开发工具将为您安装 ODAC。这将在C:\Program Files (x86). 使用完整的开发工具，低于 60Mb
在您的项目中，您将安装带有相应版本的 ODP.net (Oracle.ManagedDataAccess.dll) 的Nuget 包，您将引用它
此时，您有 2 个连接选项。
- a) 在连接字符串中设置datasource如下格式
  DataSource=ServerName:Port/SID . . .或者 DataSource=IP:Port/SID . . .
- b) 创建tnsnames.ora文件（只是它会与以前的体验不同）。在其中输入：
  AAA = (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ServerNameOrIP)(PORT = 1521))
  (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SIDNAME)))
  并将此文件放入运行应用程序的 bin 文件夹中。现在您可以使用您的连接名称进行连接 -DataSource=AAA . . .因此，即使您有tnsnames.ora，使用 ODP.net管理它的工作方式也有所不同 - 您创建本地 TNS 文件。现在，管理它很容易。

To summarize - with managed, no need for heavy Oracle Client, Oracle_homeor knowing depths of oracle installation folders. Everything can be done within your .net application structures.

总结一下 - 使用managed，不需要繁重的 Oracle 客户端，Oracle_home或者知道 oracle 安装文件夹的深度。一切都可以在您的 .net 应用程序结构中完成。

C# 当 Oracle.DataAccess 起作用时，为什么 Oracle.ManagedDataAccess 不起作用？

提问by Jeff

采纳答案by metalheart

回答by kolbasov

回答by Siddiqui

回答by Coleman

回答by Underground

回答by user3902302

回答by ppenchev

回答by T.S.

相关推荐

最近更新

标签

C# 当 Oracle.DataAccess 起作用时，为什么 Oracle.ManagedDataAccess 不起作用？

提问by Jeff

采纳答案by metalheart

回答by kolbasov

回答by Siddiqui

回答by Coleman

回答by Underground

回答by user3902302

回答by ppenchev

回答by T.S.

相关推荐

C# 在新页面中创建 ASP.NET 内容，（弹出窗口）

C# 执行永无止境的任务的正确方法。（计时器与任务）

在 C# 中使用计时器实现循环

C# - ThreadPool QueueUserWorkItem 使用？

相关推荐

最近更新

标签