保持应用程序数据库不可知（ADO.NET与封装数据库逻辑） [英] Keeping an application database agnostic (ADO.NET vs encapsulating DB logic)

问题描述

我们正在做一个相当严重的应用程序，需要对客户端想要使用的DB保持不可知。最初我们计划支持MySQL，Oracle& SQL Server。表格&视图很简单，查询（没有真正的花哨SQL），因此问题：

1. 使用本机数据库驱动程序（MySQLDbConnection等）封装执行查询和处理结果的逻辑。


2. 使用通用OleDbConnection

2不涉及开销，但我假定性能不如原生访问那么大？

解决方案

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

让我们假设你有一个连接字符串定义在 app.config ：

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

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

（请注意，非标准供应商， NET Framework默认情况下，首先需要注册，在 app.config 或客户端计算机的 machine.config 。）

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

 使用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）; 
 // ^^^^^^^^^^^^^^ 
 
使用（IDbConnection connection = factory.CreateConnection（））
 {
连接.ConnectionString = cs.ConnectionString; 
 // ^^^^^^^^^^^^^^^^^^ 
 connection.Open（）; 
 try 
 {
 using（IDbCommand command = connection.CreateCommand（））
 {
 ... //使用数据库执行某些操作
} 
} 
 finally 
 {
 connection.Close（）; 
} 
} 
  

注意这个代码只适用于接口类型。指定特定DB提供程序的唯一位置是通过 app.config 文件中的 providerName 属性值。 （我已标记所有使用 ^^^ 的设置从 app.config

---

进一步阅读：

• 通用编码的技术文章使用ADO.NET 2.0基本类和工厂：
  
  类似于我的答案，但更详细。



• p> ADO.NET托管供应商和DataSet开发人员中心：
  
  包括可用的ADO.NET数据库提供者的索引。

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:

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

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

解决方案

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.

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

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

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

(Note that non-standard providers, i.e. those that are not in the .NET Framework by default, need to be registered first, either in app.config or in the client machine's 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 providerName attribute value in the app.config file. (I've marked all the places where a setting from app.config is taken with ^^^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.

这篇关于保持应用程序数据库不可知（ADO.NET与封装数据库逻辑）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！