oracle 保持应用程序数据库不可知（ADO.NET 与封装 DB 逻辑）

Keeping an application database agnostic (ADO.NET vs encapsulating DB logic)

提问by Andrew White

We are making a fairly serious application that needs to remain agnostic to the DB a client wants to use. Initially we plan on supporting MySQL, Oracle & SQL Server. The tables & views are simple as are the queries (no real fancy SQL), therefore the question:

我们正在制作一个相当严肃的应用程序，它需要对客户想要使用的数据库保持不可知。最初我们计划支持 MySQL、Oracle 和 SQL Server。表和视图和查询一样简单（没有真正花哨的 SQL），因此问题是：

1. Use native DB drivers (MySQLDbConnection etc.) and encapsulate the logic of executing queries and processing results or
2. Use a generic OleDbConnection

1. 使用原生DB驱动（MySQLDbConnection等），封装执行查询和处理结果的逻辑或
2. 使用通用的 OleDbConnection

Obviously option 2 involves no overhead, but I presuming the performance is not as great as with native access?

显然选项 2 不涉及开销，但我认为性能不如本地访问？

回答by stakx - no longer contributing

Note:This answer is relevant if you decide to use basic ADO.NET 2 functionality instead of an ORM (such as Entity Framework or NHibernate) or LINQ to SQL.

注意：如果您决定使用基本的 ADO.NET 2 功能而不是 ORM（例如实体框架或 NHibernate）或 LINQ to SQL，则此答案是相关的。

Let's assume you've got a connection string defined in your app.config:

假设您已经在您的 中定义了一个连接字符串app.config：

<connectionStrings>
    <add name="SomeConnection"
         providerName="System.Data.SqlClient"
         connectionString="..." />
</connectionStrings>

Notice the presence of the providerNameattribute and its value. You could also put in a value for another DB provider, e.g. System.Data.SQLite.

注意providerName属性及其值的存在。您还可以为另一个数据库提供程序输入一个值，例如System.Data.SQLite.

(Note that non-standard providers, i.e. those that are not in the .NET Framework by default, need to be registeredfirst, either in app.configor in the client machine's machine.config.)

（请注意，非标准提供程序，即默认情况下不在 .NET Framework 中的提供程序，需要先app.config在客户端计算机的machine.config.

Now, you can work with the specified database in a completely provider-agnostic fashion as follows:

现在，您可以以完全与提供者无关的方式使用指定的数据库，如下所示：

using System.Configuration;  // for ConfigurationManager
using System.Data;           // for all interface types
using System.Data.Common;    // for DbProviderFactories

var cs = ConfigurationManager.ConnectionStrings["SomeConnection"];
//                                              ^^^^^^^^^^^^^^^^

var factory = DbProviderFactories.GetFactory(cs.ProviderName);
//                                           ^^^^^^^^^^^^^^^

using (IDbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = cs.ConnectionString;
    //                            ^^^^^^^^^^^^^^^^^^^
    connection.Open();
    try
    {
        using (IDbCommand command = connection.CreateCommand())
        {
            ...  // do something with the database
        }
    }
    finally
    {
        connection.Close();
    }
}

Note how this code only works with interface types. The only place where you indicate a particular DB provider is through the providerNameattribute value in the app.configfile. (I've marked all the places where a setting from app.configis taken with ^^^s.)

请注意此代码如何仅适用于接口类型。指示特定数据库提供程序的唯一地方是通过文件中的providerName属性值app.config。（我已经app.config用^^^s标记了所有设置来自的地方。）

---

---

Further reading:

进一步阅读：

• Generic Coding with the ADO.NET 2.0 Base Classes and Factories:
  similar to my answer, but goes into more detail.

• ADO.NET Managed Providers and DataSet Developer Center:
  includes, among other things, an index of available ADO.NET database providers.

• Generic Coding with the ADO.NET 2.0 Base Classes and Factories：
  类似于我的回答，但更详细。

• ADO.NET 托管提供程序和数据集开发人员中心：
  包括可用 ADO.NET 数据库提供程序的索引等。

回答by Darin Dimitrov

IMHO using an ORMis a good design decision in order to have a database agnostic application. Switching database might be as easy as changing a config setting and connection string.

恕我直言，使用ORM是一个很好的设计决策，以便拥有与数据库无关的应用程序。切换数据库可能就像更改配置设置和连接字符串一样简单。

回答by Stephen Cleary

You don't need OleDbConnectionto access nonspecific ADO.NET providers. Just use DbConnectionet. al. See DbProviderFactorieson MSDNfor more info.

您不需要OleDbConnection访问非特定的 ADO.NET 提供程序。只需使用DbConnectionet。阿尔。有关更多信息，请参阅DbProviderFactoriesMSDN。

回答by Cylon Cat

By including Oracle in that list, you've guaranteed that nothing will be simple.

通过在该列表中包含 Oracle，您可以保证一切都不会简单。

• Oracle uses a different prefix character (colon) for parameters, as compared to SQL Server that uses an "at" symbol.
• Oracle uses a single data type (number) for long, int, short, boolean, float, and decimal; your code will have to be sure that you map these properly.
• You must parameterize Oracle date and time values; if you try to use strings for dates in your SQL statements, you will go insane because of Oracle's date format. (Oracle uses a three-character month abbreviation; the format is 01-JAN-2010.)
• Basic SQL functions for handling nulls can be different, particularly for null coalescing. ("NVL" versus "COALESCE") Oracle is much pickier about reserved words.
• Oracle does not have native identity column support. Workarounds involve sequences, triggers, and requiring transactions just to retrieve an identity value from a new row.

• 与使用“at”符号的 SQL Server 相比，Oracle 使用不同的前缀字符（冒号）作为参数。
• Oracle 对 long、int、short、boolean、float 和 decimal 使用单一数据类型（数字）；您的代码必须确保正确映射这些。
• 您必须参数化 Oracle 日期和时间值；如果您尝试在 SQL 语句中使用字符串作为日期，您会因为 Oracle 的日期格式而发疯。（Oracle 使用三个字符的月份缩写；格式为 01-JAN-2010。）
• 处理空值的基本 SQL 函数可能不同，尤其是空值合并。（“NVL”与“COALESCE”）Oracle 对保留字更加挑剔。
• Oracle 没有本机标识列支持。变通方法涉及序列、触发器和要求事务只是为了从新行检索标识值。

In other words, your app can't be DB-agnostic. If you don't use an ORM, you will definitely want to build a data access layer that hides all these things from the rest of the application.

换句话说，您的应用程序不能与数据库无关。如果您不使用 ORM，您肯定会希望构建一个数据访问层，以对应用程序的其余部分隐藏所有这些内容。

Voice of experience here. Just sayin'. For a common schema across SQL Server and Oracle, we've had to build most of the infrastructure of an ORM, while avoiding the aspects that can degrade performance. Interesting, but non-trivial, definitely!

经验之声在这里。只是在说'。对于跨 SQL Server 和 Oracle 的通用架构，我们必须构建 ORM 的大部分基础架构，同时避免可能降低性能的方面。有趣，但非平凡，绝对！

回答by OMG Ponies

LINQ is a highly regarded .NET ORM, partly because you can use it and stored procedures. Problem is, it's SQL Server only but people are working to provide similar functionality for Oracle& MySQL.

LINQ 是一种备受推崇的 .NET ORM，部分原因是您可以使用它和存储过程。问题是，它只是 SQL Server，但人们正在努力为Oracle和MySQL提供类似的功能。

For database & query optimizations, I cringe at the idea of using an ORM. Data types, functions & overall syntax are not very portable in SQL. The most performant means of interacting with each database will be to tailor the model & queries to each one, but it means expertise, time and money. If need be, focus on one database vendor with the code setup to support vendor swap out & add support for other databases as necessary.

对于数据库和查询优化，我对使用 ORM 的想法感到畏缩。数据类型、函数和整体语法在 SQL 中不是很可移植。与每个数据库交互的最高效方式是为每个数据库定制模型和查询，但这意味着专业知识、时间和金钱。如果需要，请关注具有代码设置的一个数据库供应商，以支持供应商交换并根据需要添加对其他数据库的支持。

回答by Laramie

Why not use the Microsoft Patterns & PracticesEnterprise LibraryData Access Application Block. There's minimal overhead and switching providers is a snap.

为什么不使用 Microsoft模式与实践企业库数据访问应用程序块。开销最小，切换供应商很容易。

Quote:

引用：

The Data Access Application Block takes advantage of these classes and provides a model that further supports encapsulation of database type—specific features, such as parameter discovery and type conversions. Because of this, applications can be ported from one database type to another without modifying the client code.

数据访问应用程序块利用这些类并提供一个模型，该模型进一步支持数据库类型的封装——特定功能，例如参数发现和类型转换。因此，应用程序可以从一种数据库类型移植到另一种数据库类型，而无需修改客户端代码。

回答by Adam Houldsworth

You can always make part of the application database agnostic by having the bulk of the application use the DAL as a bunch of interfaces. The DAL itself would then provide a concrete implementation for the target database.

通过让大部分应用程序将 DAL 用作一堆接口，您始终可以使应用程序数据库的一部分变得不可知。然后，DAL 本身将为目标数据库提供具体的实现。

This way, you get decoupling in the use of the DAL, but the benefit of performance improvements or vendor specific constructs within the DAL.

通过这种方式，您可以在 DAL 的使用中解耦，但可以从 DAL 中的性能改进或特定于供应商的构造中获益。

回答by dkretz

There's no good reason to avoid the most generic interfaces with the broadest support - OleDb and even ODBC if you're comfortable with them. Anything beyond that reduces the pool of products/languages/platforms/tools/developers you can work with. Being closest to the SQL metal, the vendor isn't going to introduce much inefficiency - certainly less than the more esoteric options. They've been around a long, long time to wring out any problems.

如果您对它们感到满意，没有充分理由避免使用具有最广泛支持的最通用接口 - OleDb 甚至 ODBC。除此之外的任何事情都会减少您可以使用的产品/语言/平台/工具/开发人员的数量。作为最接近 SQL 金属的供应商，它不会带来太多的低效率——当然比更深奥的选项要低。他们已经存在了很长很长时间来解决任何问题。

If you're going to add an abstraction layer (your own or someone else's), then that should be decided based on the merits of the abstractions introduced in your particular context, not just to have an abstraction layer (which is just more support unless there's an intentional benefit.)

如果您要添加一个抽象层（您自己的或其他人的），那么应该根据在您的特定上下文中引入的抽象的优点来决定，而不仅仅是有一个抽象层（这只是更多的支持，除非有一个故意的好处。）

As you can see, everyone's mileage varies. :) But in general, I think simpler is better.

如您所见，每个人的里程数都不同。:) 但总的来说，我认为越简单越好。