正确利用 ADO.NET

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

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

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

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

使用动态 SQLchi na it p owe r .com5FLyL1t

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

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

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

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

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

使用 ADO.NETchi na it p owe r .com5FLyL1t

标签：