正确利用 ADO.NET

核心提示：表格报表以行的格式列出信息，这些行就像数据库表的行一样，正确利用 ADO.NET，表格报表中的列标头与表的列名相关联，表格相交叉的报表（简称交叉表报表）是二维矩阵或电子表格，因此，只要我在 .NET 框架下工作，电子表格的查找标准是沿列标头的顶部向右以及沿行标头的左侧向下进行查找，您查找的数据 — 通常由诸

表格报表以行的格式列出信息，这些行就像数据库表的行一样。表格报表中的列标头与表的列名相关联。表格相交叉的报表（简称交叉表报表）是二维矩阵或电子表格，电子表格的查找标准是沿列标头的顶部向右以及沿行标头的左侧向下进行查找。您查找的数据 — 通常由诸如 SUM()、AVERAGE() 或 COUNT() 的聚合函数进行汇总 — 占据了矩阵的内部单元。

例如，假设您为全球知名的 Pubs 连锁书店工作，而该公司的首席执行官希望知道该公司的作者所著的书籍在公司每个书店的销售情况。您可以通过使用与 sqlmag02/html/ontargetwithado_netListing_01.txt" target="_blank">清单 1（第 22 页）所创建的视图相类似的视图来创建一个列数为 3 的表格报表。此视图名为 vwSales，它列出了按书店和作者分组的图书总销售额，其中，每个作者—书店—销售额元组列在一行中。sqlserver/art/ontargetwithado_netfigure_01.gif" target="_blank">图 1 （第 22 页）显示了清单 1 的视图生成的表格报表。不过，如果您将报表呈现为交叉表报表（例如，第 22 页的sqlserver/art/ontargetwithado_netfigure_02.gif" target="_blank">图 2 显示的报表），则报表会更直观，并可以传递更多的信息。交叉表报表将书店名称在列标头中水平地列出，而不是沿着各行中的作者姓名列出。因此，要查找作者 Stearns MacFeather 在书店 Bookbeat 的销售额，应该看 Stearns MacFeather 行和 Bookbeat 列的交叉部分。

交叉表报表分为两类：宽度固定的报表和宽度可变的报表。对于宽度固定的报表，您在设计时就知道报表中的列数和列名。使用 T-SQL 查询来生成宽度固定的交叉表报表非常简单，因为您可以对聚合函数中嵌入的 CASE 表达式进行硬编码，从而对输出的每一列进行求值。sqlmag02/html/ontargetwithado_netListing_02.txt" target="_blank">清单 2显示了针对我们举例的 Pubs 书店应用程序在宽度固定的交叉表查询中使用 CASE 表达式的示例。清单 2 中的第一个 CASE 表达式的含义是：如果 Store 列中的值等于 "Barnum's"，则返回 Sales 列中的值；否则，返回 0。交叉表查询在其列表达式中使用 SUM() 函数，因此无需预先聚合它用于其数据源的视图中的数据。所以，在其 FROM 子句中，清单 2 中的交叉表查询使用 sqlmag02/html/ontargetwithado_netListing_03.txt" target="_blank">清单 3 创建的 vwSales2 视图，而不是 vwSales。如果您使用 COUNT() 或 AVG() 作为聚合函数，则需要从 CASE 表达式中去掉 "Else 0"；否则，答案将对其销售值为 0 的交易进行平均和计数，而这样的交易实际上并不存在。当您从 CASE 表达式中去掉 "Else 0" 后，对于不存在任何记录的作者—书店组合，将得到 NULL 值，而不是零。

但我们假设 Pubs 有一百家书店，每个月都有一些新书店开张，同时也有其他一些书店关张。由于这个原因，您需要一个宽度可变的交叉表报表，该报表从数据中动态地读取书店名称，无论当月存在多少书店，都为每个名称生成一个列。现在我们来探讨用于生成宽度可变的交叉表报表的两个完全不同的方法。第一个示例在存储过程中使用动态 SQL 来创建包含 CASE 表达式的交叉表查询字符串。EXEC 命令执行该查询字符串，以便返回报表。第二个示例不使用 CASE 表达式或动态 SQL，它使用 ADO.NET 中新的关系特性 — Visual Studio .NET 中的数据访问组件 — 对数据进行交叉制表。下面我将顺便指出每种方法的优点和缺点。

使用动态 SQL

sqlmag02/html/ontargetwithado_netListing_04.txt" target="_blank">清单 4显示的存储过程 procXTabDSQL 使用 vwSales2 视图以及在整个书店名称列表中循环的本地游标生成交叉表查询字符串。该查询字符串以行标头作为第一列。然后，该过程在书店名称列表上打开游标。该游标将各个书店名称放到变量 @StoreName 中。每个迭代生成包含围绕 CASE 表达式的 SUM() 函数的字符串，然后将该字符串连接到 varchar 变量 @strSQL。每个 SUM() 函数在最终报表中生成一列输出。

第一个 SUM() 函数中的 CASE 表达式用于报表的 Barnum's 书店列。sql server(WINDOWS平台上强大的数据库平台) 将嵌在 "Barnum's" 中的单引号解释为比较字符串的分隔符，并尝试将 stor_name 中存储的数据与 Barnum 进行比较，而不是与 Barnum's 进行比较。要解决此问题，则必须将代码中名称的一个单引号替换为两个单引号。双单引号序列中的第一个引号是第二个引号的转义符；它告诉 sql server(WINDOWS平台上强大的数据库平台) 字符串分析器将第二个引号解释为字符而不是字符串分隔符。列标志 [Barnum's] 中的单引号不会引起问题，因为该列标志是嵌入在中括号内的。清单 2 包含用于报表的 Barnum's 列的正确 CASE 表达式。

当您尝试从其自身用单引号分隔的字符串来生成 SQL 查询时，难度便增加了。您必须为比较字符串的前导单引号和尾随单引号提供转义序列。比较字符串包含在变量中，因此您必须使用 REPLACE() 函数以编程方式用两个单引号来替换它的一个单引号。但是，REPLACE() 函数也使用单引号作为其字符串型参数的字符串分隔符。因此，要在 @StoreName 变量中用两个单引号替换一个单引号，必须使用下面这个比较麻烦的语法

REPLACE(@StoreName,'',''')

单引号的 ASCII 编码为 39。为了提高清单 4 的代码中的 CASE 表达式的可读性，我有选择地使用 CHAR(39) 代替其中的某些单引号。

当代码完成书店名称的循环后，它会将 SQL 字符串的其余部分连接到 varchar 变量。当交叉表查询的 SQL 字符串完成后，代码将调用 EXECUTE 过程来生成交叉表结果集。

使用 ADO.NET

即使您已经克服了单引号的问题，还是必须与 varchar 变量的 8000 字符的限制作斗争。如果动态 SQL 字符串的长度超过 8000 个字符，查询就会崩溃。您可能认为 8000 个字符的长度对于您所要生成的任何查询都是足够的，但根据列数和每个 CASE 表达式的字符长度，最终的 SQL 字符串可能会变得很长。在我们的示例中，只要有大约 100 列，查询就会崩溃。您可以通过编程方式限制列数，这样查询就不会崩溃，但如果您需要超出这个限制，就没有办法了。

克服此限制的一个方法是利用 ADO.NET 的关系特性。下面的示例生成一个宽度可变的交叉表报表，该报表可以根据您的需要包括任意数量的列，而且无需使用 SQL 游标、动态 SQL 或 CASE 表达式。

sqlmag02/html/ontargetwithado_netListing_05.txt" target="_blank">清单 5（第 24 页）显示了此示例的 Visual Basic .NET 代码，该代码描述的是一个名为 XTabDotNetDS 的类。请注意，标注 A 中的 Inherits DataSet 语句在类的开头。如果您不熟悉 ADO.NET，则可以将数据集理解为一个小型的内存中数据库。DataSet 对象有一个 Tables 集合。ADO.NET 表被称为数据表。每个数据表有一个 Rows 集合，它包含 DataRow 对象和 Columns 集合，Columns 集合包含 DataColumn 对象。DataSet 对象还具有包含 DataRelation 对象的 Relations 集合，DataRelation 对象描述了数据表之间的引用关系。Inherits DataSet 语句确保自定义 DataSet 类的开头是 ADO.NET DataSet 基类的所有集合和功能。创建 XTabDotNetDS 对象时，该类的构造函数调用三个过程：Get3Tables()、AddXTabTable() 和 FillXTabTable()。

Get3Tables()。 清单 5 中的标注 B 显示的 Get3Tables() 过程说明使用 SqlDataAdapter 对象向数据集填充从sqlmag02/html/ontargetwithado_netListing_06.txt" target="_blank">清单 6（第 24 页）创建的存储过程返回的记录的操作。.NET 中的 SqlDataAdapter 对象可用作 sql server(WINDOWS平台上强大的数据库平台) 数据源和 DataSet 对象之间的桥梁。清单 6 创建的存储过程返回三个源表。当 SqlDataAdapter 从 sql server(WINDOWS平台上强大的数据库平台) 将多个表调入新创建的数据集时，ADO.NET 将为新的数据表分配一般名称 Table、Table1 和 Table2 等等。此时最好将这些表重命名为比较有意义的名称，这样您在使用这些表时就不必引用索引或一般名称了。我选择使用 Stores、Authors 和 Sales 作为表名。请注意，使用 SqlDataAdapter 时，您无需编写显式打开和关闭 SQL 连接对象的代码；SqlDataAdapter 会在后台为您完成这些工作。添加表后，代码将在 au_name 列上创建 Authors 表和 Sales 表之间的数据关系。最后，代码将在整个 Authors 表中进行迭代，以创建交叉表报表的行标头。对于每个 Author 行标头，代码使用这个 DataRelation 对象来查找销售额数据，以便填充交叉表中该作者的数据行中的其余各列。

AddXTabTable()。 标注 C 中的 AddXTabTable() 过程将第四个数据表 XTab 添加到 Tables 集合。此表将保存最终交叉表报表。添加该数据表后，代码会通过向数据表的 Columns 集合添加列来创建数据表的架构。XTab 中的第一列 Column(0) 用于行标头，行标头将包含 Author 姓名。其他列名是来自 Stores 数据表的书店名称。要获得书店名称，您可以创建 DataView。

DataView 构造函数接受 Stores 数据表作为其参数。DataView 是环绕 DataTable 对象的包装。代码通过 DataView 仍然可以访问数据表的所有属性，但 DataView 还增加了一些独一无二的重要方法。在此示例中，DataView.Sort 需要完成的任务是将书店按字母顺序排序。当代码在 DataView 中对书店进行排序后，代码会对所有行进行循环，并为每个书店向 XTab 数据表追加一列。Store(0), Barnum's, 成为 Column(1) 的列名。请注意，该代码将新列的数据类型设置为 System.Single 以接受货币数据，并将默认值设置为 0。

FillXTabTable()。 剩下的工作是，通过使用标注 D 中的代码显示的 FillXTabTable() 过程来用数据填充 XTab 数据表。该过程首先对对象变量求值，然后对 Authors 表中的所有行进行循环。对于 Authors 表中的每一行，该代码为 XTab 数据表创建一个新的数据行，然后将作者的姓名放在第一列 — Column(0)。默认情况下，新行中其他列的内容为零。

接下来，该代码对 DataRelation 对象调用 Author DataRow 的 GetChildRows 方法，以便从 Sales 表检索一个链接到该作者的 DataRow 的数组。对于该作者有销售额的每个书店，该数组 (arowSales) 包含有关该书店的一个数据行。例如，对于作者 Sheryl Hunter（该作者只在 Barnum's 书店有销售额），GetChildRows 返回一个只具有一个元素的数组。

然后，该代码对该作者的销售额数据行进行循环，并将每个销售额总和放于在该销售额数据行的 stor_name 字段中标识的 XTab 列下。该作者没有销售额的书店的默认值仍然是 0。当 XTab 行完成后，该代码将它添加到数据表的 Rows 集合，然后返回到循环的开头，以便处理下一个作者。当处理完所有作者后，也就完成了 XTab 数据表，如sqlserver/art/ontargetwithado_netfigure_03.gif" target="_blank">图 3 所示。

要在 .NET Windows 窗体 DataGrid 中查看结果，可以将 XTab 数据表分配给 DataGrid 的数据源。如果您希望在 DataGrid 第一次出现时对其进行排序，则可以使用sqlmag02/html/ontargetwithado_netListing_07.txt" target="_blank">清单 7 显示的代码将该表封装在 DataView 中，对 DataView 排序，然后将 DataView 分配给 DataGrid 的数据源。

两种方法间的差异

上面讨论的两种创建动态交叉表报表的方法产生的结果很类似，但又不完全相同。ADO.NET 版本的方法在 Authors 表中针对每个作者包括一行，而动态 SQL 版本的方法则不包括在所有书店中均没有销售额的作者。根据报表的用途，这种不同会显得非常重要。ADO.NET 版本的方法通过代码为您提供了排除在 Sales 表中没有子记录的作者的灵活性。一种方式是，只检查 Author DataRow 的 GetChildRows 方法返回的行数。如果不存在销售额记录，该代码就不向交叉表数据表添加新行。相反，使 SQL 交叉表查询返回没有销售额的作者的行则更加困难，但这是可以做到的。为了在动态 SQL 版本的方法中包括所有作者，您可能会决定首先至少将一个虚销售额记录添加到源数据，对于每个没有销售额的作者记录，虚销售额记录的销售额值为 0。但是，如果您使用 COUNT() 作为汇总函数，则此方法不起作用，因为那些虚销售额也会被计数，而这正是您所不希望的。解决此问题的办法是，通过使用您需要的汇总函数对源数据进行预聚合，然后添加虚记录，只要每个作者至少获得一个记录。之后，在交叉表查询中，始终使用 SUM() 函数。sqlmag02/html/ontargetwithado_netListing_08.txt" target="_blank">清单 8 显示的代码创建函数 dbo.fn_Sales()，该函数将来自 vwSales 的预聚合的记录与一组虚记录联合起来 — 每个组合都是针对相应的作者—书店对的，该组合是使用交叉联接生成的。如果您用 dbo.fn_Sales() 替换清单 4 的交叉表查询中的 dbo.vwSales2，则最终结果与 ADO.NET 版本的方法的结果相同：所有作者都出现在报表中。

动态 SQL 方法的一个优点是它不需要 Microsoft .NET 框架。例如，如果您使用 Visual Studio 6.0，则可以很轻松地从动态 SQL 交叉表创建 ADO 记录集，并将它绑定到 flex-grid 控件。另一方面，ADO.NET 方法不一定要绑定到 sql server(WINDOWS平台上强大的数据库平台)。您可以很轻松地修改这种方法，使它可以用于不支持 CASE 表达式的数据库，例如，Microsoft Access。

sql server(WINDOWS平台上强大的数据库平台) 中游标的运行速度很慢，而且又要占用很多资源，因此，只有在其他方法都不可行的时候，才使用这种方案。动态 SQL 的效率也比较低，因为 sql server(WINDOWS平台上强大的数据库平台) 不重用它从直接执行的 SQL 字符串生成的查询计划。执行 SQL 字符串时，还需要考虑安全问题。我尽可能避免使用游标和动态 SQL，ADO.NET 解决方案使我可以做到这一点。当我使用 ADO.NET 时，我不担心 varchar 变量的 8000 字符限制，相比前面提到的 T-SQL，我可以更好地控制输出。因此，只要我在 .NET 框架下工作，我就会使用 ADO.NET 解决方案。