如何为 ADO.NET DataAdapter 避免编写 SQL

• 下载源代码文件 - 25.1 Kb
• 下载演示项目 - 19.3 Kb

目录

• 引言
• ADO.NET 已包含的内容
• 关于强类型数据集的一两句话
• CustomCommandBuilder
• 性能考虑因素
• 结论

引言

从 C#（或其他语言）访问数据库时，最重复、最枯燥、因此最容易出错的任务之一就是编写 SQL 命令。当存在显式 SQL 时，无论它是在存储过程中还是在代码内部，都必须对其进行维护。例如，如果向数据库添加了一个列，那么该列必须添加到所有四个命令（INSERT、SELECT、UPDATE、DELETE - 也称为 CRUD）中，而且通常会有多个带有不同过滤条件的 SELECT。当然，有时你必须编写一些 SQL，但如果机器能尽可能多地生成它们，那就更好了。

本文将介绍编写一些可以完成这项工作的代码。它专为 SQL Server 和 ADO.NET SqlClient 提供程序而设计。

更新 2005/1/15： 我已将其修改为支持 OLEDB。在文本中引用 SqlCommandBuilder 的地方，也适用于 OleDbCommandBuilder。你不会遇到太多问题来定制它以适应其他数据库和数据提供程序。

ADO.NET 已包含的内容

ADO.NET 为此问题提供了一个解决方案，即 SqlCommandBuilder 类。你仍然需要提供 SELECT 命令，但 INSERT、UPDATE 和 DELETE 是为你生成的。在后台，CommandBuilder 会访问数据库以获取所需的元数据。CommandBuilder 有很多问题，William Vaughn 的文章“让开发人员戒掉 CommandBuilder”对此进行了讨论。在我看来，最大的问题是，如果出于某种原因，它生成的代码不符合你的需求，你就无法自定义或修复它——你必须完全放弃它。例如，如果你想在 SQL Server 中在 INSERT 之后找出自动递增列的值，那你就不走运了。另一个严重的问题是它需要往返数据库来获取元数据，因此即使微软也不建议在生产代码中使用它。

另一个可能的解决方案是使用 Data Adapter Configuration Wizard (DACW)，正如 Vaughn 的文章中所建议的那样。它的工作方式类似于 SqlCommandBuilder，但在设计时。因此，你可以看到生成的代码并根据需要进行调整。问题是，你必须在每次生成 SQL 时进行相同的调整（例如，在 INSERT 命令中添加自动生成列的检索）。一旦你调整了它，你就进入了手动维护模式——计算机无法为你向这些命令添加新列。

关于强类型数据集的一两句话

在使用普通的、无类型的 DataSet 时，需要大量的转换和按名称查找列。这些活动都无法在编译时进行检查。如果你喜欢在编译器中尽可能多地发现代码中的错误，那么你必须使用强类型数据集 (typed DS)。这是我最喜欢的 .NET 功能之一。如果数据库中发生任何更改，只需更新 typed DS，然后编译，让编译器找到你的代码与新版本的数据库不兼容的地方。另外，当你使用 typed DS 时，你拥有智能感知（这很难舍弃）。虽然它会影响性能，但收益通常会弥补这种差异。如果你的应用程序能够承受使用 typed datasets，你在这里就会看到它们如何减少需要手动编写的 SQL 量。

Typed DS 是从数据库创建的，方式与 CommandBuilder 的工作方式类似——通过查询数据库的元数据。主要区别在于它发生在设计时而不是运行时。这本质上意味着，在运行时，typed DS 中已经存在足够的元数据来构建命令——你可以找出表的名称、列列表、它们的类型、哪些列是主键等等。如果 SqlCommandBuilder 知道了这些，它就不必往返数据库了。此外，SqlCommandBuilder 无法知道你真正需要哪些列：如果某个 typed DS 只代表数据库表的一部分，那么命令中就应该只包含该 DS 中表示的列。

CustomCommandBuilder

此处提供的代码将类似于 SqlCommandBuilder，但由我们控制。因此，我们可以进行自定义更改。对于 SELECT 命令，我们可以添加过滤或排序，并且仍然拥有自动生成的列列表。对于 INSERT，我们可以添加对自动增量字段的支持。对于 UPDATE 命令，SqlCommandBuilder 使用“乐观锁定”支持，即只将自我们获取以来未更改的行写入数据库。我们可以选择其他类型的并发控制，例如使用时间戳或根本不进行并发控制。

所有相关类都在 Cogin.Data 和 Cogin.Data.DataAccess 命名空间中。CustomCommandBuilder 类负责生成 SQL。它由 SqlDataAccess 类使用，该类包含低级 ADO.NET 代码。这两个类不是应用程序特定的，可以在不同项目中重用。有两个类似的类用于 OleDb：OleDbCustomCommandBuilder 和 OleDbDataAccess。DataAccess 类不直接使用，而是通过 IDataAccess 接口使用，以便应用程序特定的类在我们从 SqlClient 切换到 OleDb 数据提供程序时不必更改。它们只需要使用 DataAccessFactory 来获取适当的版本，该版本会检查配置文件以查看将使用哪个数据提供程序。

数据访问类的示例是 CustomersDataAccess 和 OrdersViewDataAccess。这些类处理示例应用程序的所有 CRUD 操作，然而其中没有任何 SQL！这就是我们的目标。你需要编写的方法非常简单，例如

    public void FillOrdersForCustomer( string customerId )
    {
        dataAccess.SelectWithFilter( dataSet.Orders, "CustomerID=@customerId", 
            new GenericSqlParameter( "@customerId", customerId ) );
    }

    public void updateCustomers()
    {
        dataAccess.fullUpdate( dataSet.Customers );
    }

示例 Win Forms 应用程序已包含在内。它使用 Northwind 数据库，你应该已经拥有该数据库并安装在 SQL Server 上。它显示了客户及其订单（已过滤并仅在需要时加载），并允许更新、插入和删除。不幸的是，Northwind 没有级联更新/删除，因此你只能成功地处理你创建的行。由于这不是本文的重点，我没有费心编写代码来将更改级联到所有受影响的表，例如“订单详细信息”。

即使你想使用存储过程而不是纯 SQL，也可以使用 CustomCommandBuilder。它可以创建对存储过程的调用，也可以创建通用过程。在数据库中创建这些自动生成的存储过程可以在手动完成，也可以在应用程序启动时自动完成。

性能考虑

在处理数据库时，瓶颈通常是数据库本身。但是，我仍然对 CustomCommandBuilder 引入的性能影响很感兴趣。为了最大限度地减少数据库开销，测试数据库与测试程序在同一台机器上（没有网络延迟）。最快的方法是使用存储过程，因此将替代方案与该方法进行比较。所有测试用例都使用 typed DS。我没有尝试测量 typed DS 与 DataReader 或其他方法的比较。我试图通过此测试回答以下问题：

• 每次都生成 SQL 命令是否很慢？这就是 CustomCommandBuilder 所做的，如果命令生成太慢，则应使用某种缓存机制。
• 为每个请求重新创建数据访问和 typed DS 类是否很慢？
• 使用 SqlParameter 创建过滤器与纯文本相比是否有区别？

最后一个问题很重要，因为你可以通过连接字符串来创建过滤器，例如：

    SelectWithFilter( dataSet.Orders, "CustomerId='" + customerId + "'" );

这之所以慢，不是因为字符串连接，而是因为 SQL Server 总是接收不同的命令，必须每次都对其进行编译（解析和确定执行顺序）。创建参数化过滤器的更好方法是：

    SelectWithFilter( dataSet.Orders, "CustomerId=@customerId",
        new GenericSqlParameter ( "@customerId", customerId ) );

SQL Server 可以缓存此类命令，因为参数是单独从命令字符串传递的。正如你将从基准测试结果中看到的，这几乎和使用存储过程一样快，但更灵活。使用 SqlParameter 不仅更快，而且更安全，因为你不必担心参数是否包含某些非法字符，例如撇号。这里的 GenericSqlParameter 只是一个外观，它允许我们将它用于 SqlClient 和 OleDb。

我创建了一个小型基准测试，该测试也使用 Northwind 数据库，用于从 Orders 表中随机获取 1000 行。将存储过程 CustOrdersOrders（Northwind 中已存在）与使用为每个请求重新生成的 SQL 命令进行选择进行比较。测量了是否重复使用数据访问和 typed DS 类的变体。测试配置：Athlon XP 1800+，512MB RAM，7200rpm HDD，Win XP SP1，SQL Server 2000。数据库运行在同一台机器上。所有测试都执行三次。结果：

这个例子非常简化和有限，但它给出了一些提示。你应该在你的设置上进行测试，模拟典型的真实负载，以获取与你的情况相关的数据。

如你所见，差异很小，最差情况仅比最佳情况慢 25%。我认为 25% 是一个很小的差异，因为在现实世界中，数据库位于单独的服务器上，查询更耗费磁盘 I/O（在此基准测试期间，磁盘活动几乎不存在，因为很快整个 Orders 表就被缓存到系统内存中），因此差异应该小得多。你应该注意到“纯文本过滤器”具有最大的偏差。这是因为它们高度依赖于所需 CustomerID 的“随机性”，即每当重复相同的过滤条件时，就会使用先前编译的 SQL 命令。这也意味着这种过滤会破坏 SQL Server 的已编译命令缓存。

如果这种性能差异对你的情况很重要，或者你无法承担 typed DS 额外的内存开销，你可以选择 DataReader/存储过程方式，但这需要更多的工作，并且维护起来要困难得多。最好的建议是在进行任何优化之前进行测量。

如果性能是一个问题，我宁愿关注 typed datasets 通常的使用方式——作为一组直接映射到数据库表的几个数据表，即不使用 JOINs 进行查询。每个 DataTable 对象都用单独的 SQL 命令填充/更新，每个命令都意味着与服务器进行一次往返。这可以通过将多个命令打包在一起进行改进——多个结果集将通过一次往返接收。

结论

CustomCommandBuilder 使你无需编写通用 SQL 代码，同时允许你选择其生成方式。在现实生活中，性能影响通常很小。唯一的要求是你正在使用 typed data sets，如果你可以牺牲一些性能来提高生产力，我无论如何都会推荐它们。如果你使用的是 SQL Server，所有东西都已经提供，但与 ADO.NET 的 SqlCommandBuilder 不同，你可以为其他服务器进行更改。