如何编写适用于 SqlServer 和 Oracle 的 .Net 应用程序（现在 System.Data.OracleClient 已弃用）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1193066/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write a .Net application that works with both SqlServer and Oracle (now that System.Data.OracleClient is deprecated)
提问by Ian Ringrose
see also System.Data.OracleClient namespace discontinued?
另请参阅System.Data.OracleClient 命名空间已停止使用？
(Firstly don't panic yet, System.Data.OracleClient is not being removed by Microsoft yet, however it is not in the client version of .net 4)
（首先不要惊慌，Microsoft 尚未删除 System.Data.OracleClient，但它不在 .net 4 的客户端版本中）
This use to be possible by using System.Data.OracleClient, however Microsoft has decided to deprecate OracleClient. (OracleClient is the ADO.NET provider for Oracle developed by Microsoft and ships as a part of the .NET Framework.)
通过使用 System.Data.OracleClient 可以实现这种用途，但是 Microsoft 已决定弃用 OracleClient。（OracleClient 是由 Microsoft 开发的 Oracle 的 ADO.NET 提供程序，并作为 .NET Framework 的一部分提供。）
You could use ODP.Net, however do you wish your Sql Server customers to have to install ODP.Net? (Do you wish any of your customers to have to install Oracle software?)
您可以使用 ODP.Net，但是您是否希望您的 Sql Server 客户必须安装 ODP.Net？（您希望您的任何客户必须安装 Oracle 软件吗？）
DataDirectis not an option as it costs an arm and a leg; it may just about be affordable if you need to connect a single server to a mainframe. However it is not an option to expect all customers to buy it.
DataDirect不是一种选择，因为它需要一条胳膊和一条腿；如果您需要将单个服务器连接到大型机，它可能只是负担得起的。但是，不能期望所有客户都购买它。
Moving form C# to Java is not a good option, as I am a C# programmer and wish to be able to earn a living!
从 C# 迁移到 Java 不是一个好的选择，因为我是一个 C# 程序员，希望能够谋生！
Like most ISVs that have to support multiple RDBMS we only need a solution that allows us to use the small subset of Oracle that is the same as SqlServer. Therefore System.Data.OracleClient was powerful enough for us.
像大多数必须支持多个 RDBMS 的 ISV 一样，我们只需要一个解决方案，它允许我们使用与 SqlServer 相同的 Oracle 小子集。因此 System.Data.OracleClient 对我们来说已经足够强大了。
(Maybe we should just start storing all data in flat files, so that customer's DBA stop trying to tell us how to write software. Oracle DBAs are the worse!)
（也许我们应该开始将所有数据存储在平面文件中，这样客户的 DBA 就不再试图告诉我们如何编写软件。Oracle DBA 更糟！）
My real question is how can I write .NET software that talks to Oracle that is us pain free to installed as .NET software that talks to SqlServer. Having to use ODP.NET just makes the oracle client install yet more painful, with yet more to go wrong.
我真正的问题是如何编写与 Oracle 对话的 .NET 软件，我们可以轻松地将其安装为与 SqlServer 对话的 .NET 软件。不得不使用 ODP.NET 只会让 oracle 客户端的安装变得更加痛苦，而且还会出现更多问题。
If I was using JAVA I could just use JDBC type 4 drivers. Microsoft provides one for SqlServer and Oracle provides one for Oracle. However it seems that Oracle wishes to make .Net usage as hard as possible and Microsoft wishes to make Oracle usage as hard as possible.
如果我使用 JAVA，我可以只使用 JDBC 类型 4 驱动程序。Microsoft 为 SqlServer 提供了一个，Oracle 为 Oracle 提供了一个。然而，甲骨文似乎希望使 .Net 的使用尽可能困难，而微软则希望使 Oracle 的使用尽可能困难。
So far the best optionlooks like devArt's dotConnect.
到目前为止，最好的选择看起来像devArt 的 dotConnect。
However I am starting to question weather .NET is a good development system for ISV, as sooner or later you always get a customer that demands oracle support. In the Java world they seem to have this problem solved.
然而，我开始质疑天气 .NET 是一个很好的 ISV 开发系统，因为迟早你总会得到一个需要 oracle 支持的客户。在 Java 世界中，他们似乎解决了这个问题。
It looks like Oracle may be about to bring out a senible Fully Managed ADO.NET provider themselfs it may even be easy to install! see herethat claims Beta – 2011, Production – End of 2011.
看起来 Oracle 可能即将推出一个明智的完全托管的 ADO.NET 提供程序本身，它甚至可能易于安装！看到这里的索赔β - 2011年，生产- 2011年底的。
采纳答案by Christian Shay
EDIT: The fully managed ODP.NET is now available in production. It is very small (less than 10MB) and is not dependent on other files. You can obtain it here:
编辑：完全托管的 ODP.NET 现在可用于生产。它非常小（小于 10MB）并且不依赖于其他文件。你可以在这里获得它：
http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html
http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html
Original answer:
原答案：
One way to easilyensure that the required Oracle client side software (including ODP.NET) is always available on the deployment machine is to embed it with your application. ODP.NET has gotten a loteasier to embed now that XCOPY ODP.NET is available. You can download it from this link:
以一种方式容易确保所需的Oracle客户端软件（包括ODP.NET）总是可用的部署机器上是与您的应用程序中嵌入它。ODP.NET已经得到了很多容易，现在XCOPY ODP.NET可嵌入。你可以从这个链接下载它：
http://www.oracle.com/technology/software/tech/windows/odpnet/index.html
http://www.oracle.com/technology/software/tech/windows/odpnet/index.html
With XCOPY ODP.NET, all you need to do when you deploy your application is the following:
使用 XCOPY ODP.NET，您在部署应用程序时所需要做的就是：
1) Copy your application to the target machine
1) 将您的应用程序复制到目标机器
2) Run "install.bat" which copies a couple of Oracle DLL's to the target machine (including ODP.NET and the Oracle client side (OCI) software)
2) 运行“install.bat”，它将几个 Oracle DLL 复制到目标机器（包括 ODP.NET 和 Oracle 客户端 (OCI) 软件）
3) Run "configure.bat", which does a "gacutil" and updates the registry of the target machine
3) 运行“configure.bat”，执行“gacutil”并更新目标机器的注册表
4) Provide your application with connect string information. You can use the EZCONNECT connect string ("hostname@servicename") or you (or your customer) can share preexisting sqlnet configurations by setting the TNS_ADMIN registry entry or environment variable to point to another Oracle home that has sqlnet connect aliases already configured.
4) 为您的应用程序提供连接字符串信息。您可以使用 EZCONNECT 连接字符串（“hostname@servicename”），或者您（或您的客户）可以通过将 TNS_ADMIN 注册表项或环境变量设置为指向另一个已配置 sqlnet 连接别名的 Oracle 主目录来共享预先存在的 sqlnet 配置。
That's it! It is really that simple.
就是这样！真的就是这么简单。
I hope you will take a good look at ODP.NET XCOPY in the link above to see for yourself how easy it is these days to embed ODP.NET with your app.
我希望您能仔细查看上面链接中的 ODP.NET XCOPY，亲眼看看如今将 ODP.NET 嵌入您的应用程序是多么容易。
Additional notes:
补充说明：
If you choose not to embed ODP.NET with your application, in both the case of Microsoft OracleClient and in the case of ODP.NET, there needs to be additional Oracle client side (OCI) software installed on any deployment machine. The only difference between the two cases is that when you are using ODP.NET, it also needs to exist on the deployment machine. The good news is that a typical Oracle install on your customer machine will include ODP.NET already.
如果您选择不将 ODP.NET 嵌入您的应用程序，无论是 Microsoft OracleClient 还是 ODP.NET，都需要在任何部署机器上安装额外的 Oracle 客户端 (OCI) 软件。两种情况的唯一区别是，当您使用 ODP.NET 时，它也需要存在于部署机器上。好消息是，在您的客户机器上典型的 Oracle 安装将包含 ODP.NET。
Now, if your target machine already has ODP.NET installed you don't need to do anything else. You just need to distribute your application. If you do need to install ODP.NET using the standard installer, you can also download it from the link provided above. The standard ODP.NET install only takes a few minutes and configures everything for you.
现在，如果你的目标机器已经安装了 ODP.NET，你不需要做任何其他事情。您只需要分发您的应用程序。如果您确实需要使用标准安装程序安装 ODP.NET，您也可以从上面提供的链接下载它。标准的 ODP.NET 安装只需几分钟即可为您配置好一切。
And again, you can use EZConnect connect strings to make networking configuration a piece of cake, or use the TNS_ADMIN registry entry or environment variable to take advantage of pre-existing connect aliases that your customer is already used to using.
同样，您可以使用 EZConnect 连接字符串使网络配置变得轻而易举，或者使用 TNS_ADMIN 注册表项或环境变量来利用您的客户已经习惯使用的预先存在的连接别名。
Hope this helps,
希望这可以帮助，
Christian Shay
克里斯蒂安·谢伊
Oracle
甲骨文
Feel free to provide your feedback on this and other future features at our feature request website: http://apex.oracle.com/pls/apex/f?p=18357:46
请随时在我们的功能请求网站上提供有关此功能和其他未来功能的反馈：http: //apex.oracle.com/pls/apex/f?p=18357: 46
回答by Thomas Beck
Have you looked at alternate Oracle providers like devArt's dotConnect? This driver, as an example, is 100% managed code, supports many advanced Oracle features, provides Entity Framework support (that will come some day in ODP.NET, I'm sure).
您是否看过像devArt 的 dotConnect这样的替代 Oracle 提供商？例如，该驱动程序是 100% 托管代码，支持许多高级 Oracle 特性，提供实体框架支持（我相信这会在 ODP.NET 中实现）。
There are a bunch of other features (like ASP.NET provider model support) that we didn't get until the ODP.NET 11g release. Pretty liberal licensing and reasonably priced. Also comes with it's own profiler, if I remember correctly.
在 ODP.NET 11g 发布之前，我们还没有获得许多其他功能（例如 ASP.NET 提供程序模型支持）。相当自由的许可和合理的价格。如果我没记错的话，它还带有自己的分析器。
In their tests, performs favorably compared to ODP.NET. I'd love to point you to something open source but, in my experience, once you've bought into Oracle as a database, you've already accepted the fact that you'll be laying down some cash for your database-related tooling.
在他们的测试中，表现优于 ODP.NET。我很想向您指出一些开源的东西，但是，根据我的经验，一旦您将 Oracle 作为数据库购买，您就已经接受了这样一个事实，即您将为与数据库相关的工具投入一些现金.
回答by davewasthere
There's a big difference between being deprecated and being removed.
被弃用和被删除之间有很大的区别。
If you're just using the subset of functionality that is common to both SQL Server and Oracle, then you should be fine moving through to ASP.Net 4.0 and continuing to program how you do currently.
如果您只是使用 SQL Server 和 Oracle 通用的功能子集，那么您应该可以过渡到 ASP.Net 4.0 并继续编写您当前的工作方式。
Using something like EntitySpaces, would let you be relatively provider-agnostic. (it uses OracleClient in it's DAL)
使用诸如EntitySpaces 之类的东西，可以让您相对提供者不可知。（它在它的 DAL 中使用 OracleClient）
Completely agree with Philippe about Oracle's client software being a pain. I've lost track of the number of times I've installed it and hated the experience. Give me MySQL, SQLServer or even Access in preference.
完全同意 Philippe 的观点，即 Oracle 的客户端软件是一种痛苦。我已经忘记了我安装它的次数并且讨厌这种体验。优先给我 MySQL、SQLServer 甚至 Access。
(Actually, I take back the Access comment) :-)
（实际上，我收回了 Access 评论）:-)
回答by Bob The Janitor
Use the "The Provider Factory Pattern" which is basically using a factory to give a data provider that is completely abstracted away from the database request methods that uses it here is a blogpostwith some sample code that shows how to do this also Jean-Paul Boodhoo on Demystifying Design PatternsPart 1 on dnrtv.com shows how to as well.
使用“提供者工厂模式”，它基本上是使用工厂来提供完全从使用它的数据库请求方法中抽象出来的数据提供者，这是一篇博客文章，其中包含一些示例代码，展示了如何做到这一点，还有Jean-Paul dnrtv.com 上的 Boodhoo 关于 Demystifying Design PatternsPart 1 也展示了如何去做。
This is some very cool stuff basically you have a factory that provides a methods for getting a connection
这是一些非常酷的东西，基本上你有一个工厂，提供获取连接的方法
public IDbConnection GetConnection()
{
IDbConnection connection = _frameworkDBProviderFactory.CreateConnection();
connection.ConnectionString = _authenticationSettings.ConnectionString;
return connection;
}
though an interface so you can call any type of Database who's connection object implements the IDbConnection interface (SQLServer, MySQL, Oracle, etc.) and it just works.
虽然是一个接口，因此您可以调用任何类型的数据库，它的连接对象实现了 IDbConnection 接口（SQLServer、MySQL、Oracle 等）并且它可以正常工作。
By abstracting away what DB your using you can even swap them out at run time and your application will never know, it doesn't need to so to connection to an Orical DB, download the ODP.NET, same thing with mysql connectorboth implement IDbConnection, and write your code against the abstracted connection.
通过抽象出你使用的数据库，你甚至可以在运行时将它们交换出来，你的应用程序永远不会知道，它不需要连接到 Orical DB，下载ODP.NET，同样的事情与mysql 连接器都实现IDbConnection，并针对抽象连接编写代码。
回答by Christian13467
Being database independent is a very hard job because there are a lot of specific things (bind variable naming, object quotation, ...). Use a library like NHibernatewhich puts a layer between your application and the real database.
独立于数据库是一项非常艰巨的工作，因为有很多特定的事情（绑定变量命名、对象引用等）。使用像NHibernate这样的库，它在您的应用程序和真实数据库之间放置了一个层。
If you have to connect to both databases, than it's a question of deployment. If you connect to oracle (using System.Data.OracleClient or Oracle.DataAccess.Client) you need oracle client software installed on your machine. The database provider deployed with framework is not enough to connect to an oracle database.
如果您必须连接到两个数据库，那么这是一个部署问题。如果您连接到 oracle（使用 System.Data.OracleClient 或 Oracle.DataAccess.Client），您需要在您的机器上安装 oracle 客户端软件。使用框架部署的数据库提供程序不足以连接到 oracle 数据库。
If you connect to oracle, you have to install software from oracle. If you fear the deprecation, than install and use ODP.Net. There are some differencesbetween the 2 oracle database provider.
如果连接到oracle，则必须从oracle 安装软件。如果您害怕弃用，请安装和使用 ODP.Net。2 oracle 数据库提供程序之间存在一些差异。
For better deployment oracle introduced the concept of instant client. This client can deployed using xcopy deployment. Sine oracle 11 the instant client can be bundled with ODP.Net.
为了更好的部署，oracle 引入了即时客户端的概念。此客户端可以使用 xcopy 部署进行部署。正弦 oracle 11 即时客户端可以与 ODP.Net 捆绑在一起。
The installation of ODP.Net is described in the post of Christian Shay.
在 Christian Shay 的帖子中描述了 ODP.Net 的安装。
The Oracle.DataAccess assembly must not be in die global assembly cache. Put it in your bin directory. The instant client used by Oracle.DataAccess must not be in a different directory on your client machine. Put it in your bin directory. The documentationdescribes how to configure your application to find a instant client.
Oracle.DataAccess 程序集不得位于全局程序集缓存中。把它放在你的 bin 目录中。Oracle.DataAccess 使用的即时客户端不得位于客户端计算机上的不同目录中。把它放在你的 bin 目录中。该文档描述了如何配置您的应用程序以查找即时客户端。
回答by RichardOD
In addition to what others have suggested you could consider using the Provider pattern and Oracle's ODP.NET.
除了其他人建议您可以考虑使用提供者模式和 Oracle 的 ODP.NET。
回答by devio
As long as you do not invoke an ODP.Net object, you don't need to install ODP if you only access SQL Server.
只要不调用ODP.Net对象，如果只访问SQL Server就不需要安装ODP。
回答by Dan Diplo
回答by Amos Robinson
Would using ODBC for both be feasible?
两者都使用 ODBC 是否可行？
回答by RBarryYoung
I believe that ODBC can still use both SQL Server and Oracle with some transparency, so I would take a look at using ADO.Net with the ODBC provider. It won't give you all of the performance or features that SqlClient or OracleClient would, but it should be pretty close to the same code for Oracle or SQL Server.
我相信 ODBC 仍然可以在一定程度上透明地同时使用 SQL Server 和 Oracle，所以我会看看将 ADO.Net 与 ODBC 提供程序一起使用。它不会为您提供 SqlClient 或 OracleClient 的所有性能或特性，但它应该非常接近于 Oracle 或 SQL Server 的相同代码。
