在SQL Server中编写通用数据访问方法

网络整理 - 07-27

引言

在负责咨询工作的过去 6 年中，我曾多次听说关于数据访问和操作方面的问题，它时刻困扰着用户：“如何编写应用程序，以便只需对其进行很少的改动或不进行改动即可使用数据库服务器 x、y 和 z？”由于知道数据访问层仍然是现代应用程序的最关键部分，并且通常是经验不足的开发人员的头号敌人，因此我的第一反应始终是：根本办不到！

面对着人们惶惶不安的面孔以及“使用 Microsoft 在 ADO 中提供的通用数据访问方法如何？”这样的问题，我决定针对此问题提供更详细的说明以及建议的解决方案。

问题在于，如果应用程序是较小的原型，或者如果并发用户较少并且数据访问逻辑比较简单，那么即使您选择下面这些最简单的方法，也不会遇到任何问题：使用 RAD 工具（如 Data Environment in Microsoft® Visual Basic® 6.0），或某些“一揽子”解决方案（如 ActiveX® Data Control 和其他第三方组件），这些解决方案通常会隐藏应用程序与特定数据源之间进行的复杂交互。然而，当用户数量增加使得必须解决并发操作问题时，由于频繁使用动态记录集、服务器端光标以及不必要的锁定策略，导致出现许多性能问题。为达到用户目标而必须对系统所做的设计和代码更改将花费您大量的时间，因为您从开始时就没有考虑过这一问题。

使用通用数据访问方法

在将 ADO 可靠地并入 MDAC（Microsoft Data Access Components 2.1 版）后，Microsoft 掀起了通用数据访问的使用高潮。其主导思想是向开发人员展示，通过使用简单的对象模型（“连接”、“命令”和“记录集”），可以编写出能够与各种不同的数据源（无论是关系数据源还是非关系数据源）连接的应用程序。文档（以及当时的大多数文章和示例）中通常未曾提及的是，即使使用相同的数据访问技术，各种数据源的可编程性和特征也千差万别。

其结果是，在需要从多个数据源获取数据的应用程序中，最简单的方法是使用所有数据源所提供的功能的“共同点”，但因此会失去使用数据源特定选项的好处，即为访问和操作各种 RDBMS 中的信息提供最佳方法。

我对该方法始终存在的怀疑是，经过与我的客户进行更详细的分析后，我们通常一致认为与应用程序中处理显示和业务逻辑的其他部分相比，与数据源进行交互的只是应用程序很小的一部分。通过进行精心的模块化设计，可以将 RDBMS 特定代码隔离在一些容易互换的模块中，从而避免对数据访问使用“通用”方法。然而，我们可以使用非常特定的数据访问代码（根据数据源的不同，使用存储过程、命令批处理和其他特性），而不触及其他大多数应用程序代码。这总是提醒大家：正确的设计是编写可移植的有效代码的关键。

ADO.NET 将一些重要的变化引入到数据访问编码领域，如专用 .NET 数据提供程序这样的概念。使用特定的提供程序，可以绕过为数众多但有时没必要的一系列软件接口和服务（它们是 OLE DB 和 ODBC 层在数据访问代码与数据库服务器之间插入的内容），从而以最佳方式连接到数据源。但每个数据源仍然存在不同的特征和特性（具有不同的 SQL Dialect），且编写高效的应用程序仍然必须使用这些特定特征而不是“共同点”。从可移植性观点看来，托管和非托管的数据访问技术仍然非常类似。

除“利用数据源的唯一特征”外，编写良好数据访问层所必需的其他规则对每个数据源通常都是相同的：

• 在可能的情况下使用连接池机制。

• 节约使用数据库服务器的有限资源。

• 注意网络的往返。

• 在适当的情况下，增强执行计划的重复使用率并避免重复编译。

• 使用适当的锁定模型管理并发性。

从我使用模块化设计方法的个人经验来看，整个应用程序中专用于处理特定数据源的代码量不会超过总量的 10%。显而易见，这比仅仅更改配置文件中的连接字符串更复杂，但我认为，这样做会获得性能收益，因此这是一个可接受的折衷办法。

使用基本接口

此处的目标是使用抽象，并将特定于特殊数据源的代码封装在类层中，从而使应用程序的其他部分独立于后端数据库服务器或免受其影响。

.NET Framework 的面向对象这一特性将在该过程中为我们提供帮助，使我们能够选择要使用的抽象级别。选项之一是使用每个 .NET 数据提供程序都必须实现的基本接口（IDbConnection、IDbCommand、IDataReader 等）。另一个选项是创建一组类（数据访问层），用于管理应用程序的所有数据访问逻辑（例如，使用 CRUD 范例）。为检查这两种可能性，我们首先从基于 Northwind 数据库的订单输入应用程序示例入手，然后插入和检索不同数据源中的信息。

数据提供程序基本接口标识应用程序与数据源进行交互通常所需的典型行为：

• 定义连接字符串。

• 打开和关闭与数据源的物理连接。

• 定义命令和相关参数。

• 执行可以创建的不同种类的命令。

• 返回一组数据。

• 返回标量值。

• 对数据执行操作但不返回任何内容。

• 对返回的数据集提供只向前型访问和只读型访问。

• 定义使数据集与数据源（数据适配器）的内容保持同步所需的一组操作。

但事实上，如果将检索、插入、更新和删除不同数据源（使用不同的数据提供程序）中的信息所需的各种操作封装在数据访问层中，并且只公开基本接口的成员，则可以实现第一级抽象-至少从数据提供程序的角度来看是这样。让我们看一看以下演示该设计思想的代码：

using System;using System.Data;using System.Data.Common;using System.Data.SqlClient;using System.Data.OleDb;using System.Data.OracleClient;namespace DAL{ public enum DatabaseType { Access, SQLServer, Oracle // 任何其他数据源类型 } public enum ParameterType { Integer, Char, VarChar // 定义公用参数类型集 } public class DataFactory { private DataFactory(){} public static IDbConnection CreateConnection (string ConnectionString, DatabaseType dbtype) { IDbConnection cnn; switch(dbtype) { case DatabaseType.Access: cnn = new OleDbConnection (ConnectionString); break; case DatabaseType.SQLServer: cnn = new SqlConnection (ConnectionString); break; case DatabaseType.Oracle: cnn = new OracleConnection (ConnectionString); break; default: cnn = new SqlConnection (ConnectionString); break; } return cnn; } public static IDbCommand CreateCommand (string CommandText, DatabaseType dbtype, IDbConnection cnn) { IDbCommand cmd; switch(dbtype) { case DatabaseType.Access: cmd = new OleDbCommand (CommandText, (OleDbConnection)cnn); break; case DatabaseType.SQLServer: cmd = new SqlCommand (CommandText, (SqlConnection)cnn); break; case DatabaseType.Oracle: cmd = new OracleCommand (CommandText, (OracleConnection)cnn); break; default: cmd = new SqlCommand (CommandText, (SqlConnection)cnn); break; } return cmd; } public static DbDataAdapter CreateAdapter (IDbCommand cmd, DatabaseType dbtype) { DbDataAdapter da; switch(dbtype) { case DatabaseType.Access: da = new OleDbDataAdapter ((OleDbCommand)cmd); break; case DatabaseType.SQLServer: da = new SqlDataAdapter ((SqlCommand)cmd); break; case DatabaseType.Oracle: da = new OracleDataAdapter ((OracleCommand)cmd); break; default: da = new SqlDataAdapter ((SqlCommand)cmd); break; } return da; } }}

该类的作用是向应用程序的较高级别隐藏与创建特定类型（来自特定的数据提供程序）的实例有关的细节，应用程序现在可以使用通过基本接口公开的一般行为与数据源进行交互。

让我们了解一下如何从应用程序的其他部分使用该类：

using System;using System.Data;using System.Data.Common;using System.Configuration; namespace DAL{ public class CustomersData { public DataTable GetCustomers() { string ConnectionString = ConfigurationSettings.AppSettings ["ConnectionString"]; DatabaseType dbtype = (DatabaseType)Enum.Parse (typeof(DatabaseType), ConfigurationSettings.AppSettings ["DatabaseType"]); IDbConnection cnn = DataFactory.CreateConnection (ConnectionString,dbtype); string cmdString = "SELECT CustomerID" + ",CompanyName,ContactName FROM Customers"; IDbCommand cmd = DataFactory.CreateCommand( cmdString, dbtype,cnn); DbDataAdapter da = DataFactory.CreateAdapter(cmd,dbtype); DataTable dt = new DataTable("Customers"); da.Fill(dt); return dt; } public CustomersDS GetCustomerOrders(string CustomerID) { // 待定 return null; } public CustomersList GetCustomersByCountry (string CountryCode) { // 待定 return null; } public bool InsertCustomer() { // 待定 return false; } }}

在 CustomerData 类的 GetCustomers() 方法中，我们可以看到通过读取配置文件中的信息。可以使用 DataFactory 类通过特定连接字符串创建 XxxConnection 实例，并编写与基本数据源没有特定依赖性的其余代码部分。

与数据层交互的一个业务层类示例看起来可能类似下面这样：

using System;using System.Data; using DAL;namespace BLL{ public class Customers { public DataTable GetAllCustomers() { CustomersData cd = new CustomersData(); DataTable dt = cd.GetCustomers(); return dt; } public DataSet GetCustomerOrders() { // 待定 return null; } }}

这样看来，此方法出现什么问题了？此处的问题是，只有一个重要细节将代码绑定到特定数据源：命令字符串的 SQL 语法！实际上，如果以这种方式编写应用程序，则使其具有可移植性的唯一办法是采用可以由任何数据源解释的基本 SQL 语法，但这样可能会失去从特定数据源的特定功能获得好处的机会。如果应用程序只对数据进行很简单和很标准的操作，并且如果您不希望使用特定数据源中的高级功能（如 XML 支持），这可能是个小问题。但通常此方法将导致性能降低，因为您无法使用每个数据源的最佳特性。

编写专门的数据访问层

因此，只使用基本接口不足以通过不同数据源提供可接受级别的抽象。这种情况下，一个好的解决方案是提高此抽象的级别，即创建一组类（如 Customer、Order 等）来封装特定数据提供程序的使用，并通过与特定数据源、类型化的“数据集”、对象集合等无关的数据结构与应用程序的其他级别交换信息。

可以在特定程序集内部创建此层的专用类（为每个受支持的数据源分别创建一个专用类），并可以在需要的情况下按照配置文件中的说明从应用程序加载它们。这样，如果您希望向应用程序中添加全新的数据源，唯一要做的事情是针对一组通用接口组中定义的“合同”实现一组新类。

让我们看一个实际例子：如果希望将 Microsoft® SQL Server™ 和 Microsoft® Access 作为数据源为其提供支持，则应该在 Microsoft® Visual Studio® .NET 中创建两个不同项目，每个数据源分别创建一个。

为 SQL Server 创建的项目将类似于如下所示：

using System;using System.Data;using System.Data.Common;using System.Data.SqlClient; using System.Configuration; using Common;namespace DAL{ public class CustomersData : IDbCustomers { public DataTable GetCustomers() { string ConnectionString = ConfigurationSettings.AppSettings ["ConnectionString"]; using (SqlConnection cnn = new SqlConnection (ConnectionString)) { string cmdString = "SELECT CustomerID," + "CompanyName,ContactName " + "FROM Customers"; SqlCommand cmd = new SqlCommand (cmdString, cnn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable("Customers"); da.Fill(dt); return dt; } } public DataTable GetCustomerOrders(string CustomerID) { // 待定 return null; } public DataTable GetCustomersByCountry (string CountryCode) { // 待定 return null; } public bool InsertCustomer() { // 待定 return false; } }}

从 Microsoft® Access 进行数据检索的代码类似于如下所示：

using System;using System.Data;using System.Data.Common;using System.Data.OleDb; using System.Configuration; using Common;namespace DAL{ public class CustomersData : IDbCustomers { public DataTable GetCustomers() { string ConnectionString = ConfigurationSettings.AppSettings ["ConnectionString"]; using (OleDbConnection cnn = new OleDbConnection (ConnectionString)) { string cmdString = "SELECT CustomerID," + "CompanyName,ContactName " + "FROM Customers"; OleDbCommand cmd = new OleDbCommand (cmdString, cnn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable dt = new DataTable("Customers"); da.Fill(dt); return dt; } } public DataTable GetCustomerOrders(string CustomerID) { // 待定 return null; } public DataTable GetCustomersByCountry (string CountryCode) { // 待定 return null; } public bool InsertCustomer() { // 待定 return false; } }}

CustomersData 类实现 IdbCustomers 接口。需要支持新数据源时，只能创建一个实现该接口的新类。

此类型的接口可以类似于如下所示：

using System;using System.Data; namespace Common{ public interface IDbCustomers { DataTable GetCustomers(); DataTable GetCustomerOrders(string CustomerID); DataTable GetCustomersByCountry(string CountryCode); bool InsertCustomer(); }}

我们可以创建专用程序集或共享程序集来封装这些数据访问类，在第一种情况下，程序集加载程序将搜索我们在 AppBase 文件夹的配置文件内指定的程序集，或者使用典型探测规则在子目录内进行搜索。如果我们必须与其他应用程序共享这些类，则可以将这些程序集置于全局程序集缓存中。

从其他层使用数据访问类

这两个几乎相同的 CustomersData 类包含在应用程序其余部分将使用的两个不同程序集内。通过下面的配置文件，我们现在可以指定要加载的程序集以及面向的数据源。

可能的配置文件示例将类似于如下所示：

<?xml version="1.0" encoding="utf-8" ?> <configuration> <appSettings> <add key="ConnectionString" value="Server=(local);Database=Northwind; User ID=UserDemo;Pwd=UserDemo" /> <add key="DALAssembly" value="DALAccess, version=1.0.0.0, PublicKeyToken=F5CD5666253D6082" /> </appSettings></configuration>

我们必须在此文件内指定两条信息。第一条信息是规范的连接字符串（用于为更改提供机会），如服务器名称或其他一些用于连接的参数。第二条信息是程序集的完全限定名，应用程序的上一层将动态加载此程序集以查找与特定数据源一起使用的类：

让我们再来看一下这部分代码：

using System;using System.Data; using System.Configuration;using System.Reflection;using Common;namespace BLL{ public class Customers { public DataTable GetAllCustomers() { string AssemblyName = ConfigurationSettings.AppSettings ["DALAssembly"]; string TypeName = "DAL.CustomersData"; IDbCustomers cd = // (IDbCustomers)= Assembly.Load(AssemblyName). CreateInstance(mytype); DataTable dt = cd.GetCustomers(); return dt; } public DataSet GetCustomerOrders() { // 待定 return null; } }}

您可以看到，程序集使用从配置文件中读取的名称进行加载，并创建和使用 CustomersData 类的实例。

一些可能的改进

要了解我所建议的方法的示例，请查看 NET Pet Shop v3.0 示例应用程序。建议您下载此示例并深入了解它，不仅是为了解决可移植性问题，同时也是为了解决其他相关问题（如缓存和性能优化）。

在为可移植应用程序设计数据访问层的过程中，一个需要注意的重要问题是如何与其他层进行信息通信。在本文的示例中，我只使用了一个普通的 DataTable 实例；在生产环境中，您可能希望根据必须表示的数据类型（您必须处理分层结构等）考虑不同的解决方案。在这里，我不希望从头开始，建议您查阅 Designing Data Tier Components and Passing Data Through Tiers 指南，它详细描述了不同情况以及所建议的解决方案的优点。

如我简介中所述，在设计阶段，应该考虑您的目标数据源所公开的特定特性以及总体数据访问。这应该涵盖存储过程、XML 序列化等事项。关于 Microsoft® SQL Server™ 2000，您可以在下面的网址中找到有关如何优化使用这些特性的介绍：.NET Data Access Architecture Guide。强烈建议您阅读一下该指南。

我总是收到许多关于 Data Access Application Block 以及它如何与参数关联（如本文所述）的请求。这些 .NET 类充当 SQL Server .NET 数据提供程序之上的抽象层，并使您能够编写更多优秀代码与数据库服务器进行交互。下面是一段演示可行操作的代码：

DataSet ds = SqlHelper.ExecuteDataset( connectionString, CommandType.StoredProcedure, "getProductsByCategory", new SqlParameter("@CategoryID", categoryID));

此方法还有一个外延，您可以在 GotDotNet 上的开放源代码 Data Access Block 3.0 (Abstract Factory Implementation) 示例中找到。此版本实现相同的抽象工厂模式，并使您能够根据可用的 .NET 数据提供程序使用不同数据源。

结论

您现在应能够根据选择的特定数据源构建不需要修改的业务逻辑类，并可以利用给定数据源的唯一特性获得更好的效果。这是有代价的：我们必须实现多组类，以便封装特定数据源的低级别操作，以及可以为每个特定数据源（存储过程、函数等）构建的所有可编程对象。如果希望获得高性能和高可移植性，就必须付出这样的代价。根据我的实际经验，这是完全值得的！