CommandBuilder深入分析

William Vaughn
Beta V Corporation

2003年3月

对应:
   Microsoft® ADO.NET

摘要 : 分析 ADO.NET CommandBuilder 原理, 并演示如何使用Visual Studio .NET DataAdapter 配置向导 (DACW) 构建查询命令. (11 页)

目录

CommandBuilder所支持的操作  
从基于COM 的 ADO 升级
ADO.NET CommandBuilder 如何工作s
其他的方法?
创建查询动作命令
检察生成的代码
调整 UpdateCommand
调整 DeleteCommand
总结

我参加了本周微软主持的一次技术交流
 (http://communities2.microsoft.com/home/chatsevents.aspx). 我意外的与微软 .NET Framework 开发团队及其他一些勇于尝试新技术的人士进行了交流. 这次谈话包含了其他开发者对CommandBuilder的研讨. 在公众新闻组每天也能见到许多相关话题, 因此我认为是时候将相关话题做一并解答了.

CommandBuilder所支持的操作

我们首先从基础开始, 即Microsoft® ADO.NET. ADO.NET 使用新技术更新行集. 如果熟悉Microsoft® Visual Basic 6.0 Data Object Wizard（VB6数据对象向导），这并不算全新的概念. (Homer Simpson 如此表达, "D'Oh!") 如同Data Object Wizard, ADO.NET DataAdapter 用来管理四种命令对象. 其中一种命令用来获取行集 其它三种命令分别用来增加、更新 删除行集 。

• SelectCommand.指定SQL 代码和参数 ，使用一或多条 SELECT 查询获取行集
• InsertCommand. 指定SQL 代码和参数  于数据库中插入新行.
• DeleteCommand. 指定SQL 代码和参数 于数据库表中删除指定行集
• UpdateCommand. 指定SQL 代码和参数 于数据库表中更新指定行集

如你所见, ADO.NET 开发者们可以随意使用所有 SQL 代码获取或更改数据库中指定表中的数据. 与经典的ADO 操作不同他（DataAdapter）将在运行时创建查询操作.如是，我们可以使用 Microsoft® Visual Studio® .NET DataAdapter 配置向导(DACW) 来创建一些或所有这些 命令匹配. 这是一点更大的不同. 当你比较 .NET 开发和基于COM的 Visual Studio 6.0 开发时, 客户端处理的 "黑盒" 概念削弱了，因为更多的内容被包含在源文件中. 这意味着如果你不喜欢向导生成的标准设计代码, 你可以更改它(具有风险).

CommandBuilder 能够自动生成SQL代码减轻你编写查询命令的工作, 将ADO.NET Command 对象, 和他们关联的Parameters（参数集合）赋予 SelectCommand. 很好. 他可以工作了, 但只是在非常有限的情况下, 他是有代价的, 本文将论述这一问题. 不同于 DACW, 代码和命令由CommandBuilder 在背后创建—在另一个黑盒中.

从基于 COM的ADO升级

为了更好的对比 ADO.NET 与标准 ADO的不同, 让我们进一步探讨标准 ADO . 基于COM的标准ADO (ADOc) 不存在CommandBuilder, 或者至少不存在于表面上. 他好像一个黑盒 处理查询动作. ADOc 预先用过初始化 SELECT 查询来获取返回结果集所有描述的源数据 . 他降低了服务器端查询产生速度并使更新结果集的对象臃肿 但他不得不如此. 因此ADOc 是一个 OSFA (one size fits all 通用尺寸; 一个为各种情况设计的标准接口) OLE DB 接口,其他一些提供者无法提供这种能力 来提供 充分的, 相容的, 或正确的源数据. 这意味着他（ADOc）是 一个健壮的应用程序并能为更多支持者服务.

ADOc 具有 ADO.NET CommandBuilder 不具备的Update Criteria 属性 (参看 Microsoft Knowledge Base article 190727). 以我的观点, 这是CommandBuilder一项重要的性能削弱. Update Criteria 允许开发者 调整 ADOc 构造SQL命令动作 测试当前冲突.在此, 基于Update Criteria 属性已经设置, SQL 查询动作根据如果服务器端数据行是否已经由于更新而改变来决定是否完成这个动作. 例如, 下面Update Criteria 设置 决定你选择 的SQL命令如何运行:

• AdCriteriaKey. 只用于主键. 他表示如果行存在，更新或删除他.
• adCriteriaAllCols. 构造SQL 代码比较Recordset（结果集）与服务器端所所有列数据. 这与CommandBuilder 很相似.
• adCriteriaUpdCols. (默认值) 构造SQL 代码比较，只对应结果集中更改的部分.
• adCriteriaTimeStamp. 构造SQL代码比较，应用于timestamp（时间戳） 列 (如果可用).

如你所见, ADOc 和 Update Criteria 属性 允许你 决定是否 导入那些从未触及（或无法触及）的列集. 这是非常重要的，因为这有许多情形下，在其他更新行集中你取了只读的列. 例如, 你的用户被允许读取CurrentSalary 列, 但 不允许更改它. 没有类似的途径预防ADO 中UPDATE 声明的SET字句,你的应用程序将不得不使用其他方法. 许多有力的并发验证技术几乎都 使用一个服务器准时间戳列来发现 发送－接受 更改. 典型的时间戳测试, 服务器能够从你最后读取后行发生更改时立即告知你. CommandBuilder 不能使用时间戳列管理并发.

好了, 结束冗长的论述, ADO.NET CommandBuilder 不支持 Update Criteria属性.他简单的使用粗糙的方法 (类似于adCriteriaAllCols)来测试服务器端行的改变. 这一问题使习惯于使用 ADO并使用聪明方案的ADOc 开发者感到失望.

赋予ADOc更多的对获取源数据的控制和帮助, 并且他为一些提供者性能更好, Microsoft  寻找出ADO.NET骨架的雏形. 这样可以理解为什么许多开发者不期望 使用ADO 来只作他们的查询操作 总之—他们已经准备转移他们的查询操作逻辑到新的产品. 使用 ADO.NET他们必须要做很多 并绝对需要转换他们的角色: 做到更轻、更快、更简单. 构造.NET 数据提供者也是一个重点, 因此他无异于对OLE DB 作一次脑外科手术. 这个策略将为ADO.NET 开发者减轻SQL动作查询的工作.

ADO.NET CommandBuilder如何工作

CommandBuilder 需要你提供一个 正确的, 可执行的, 和标准的 SelectCommand 来与一个DataAdapter相关联.他也需要一个可行的连接. 这是因为CommandBuilder 打开连接与 DataAdapter 一同往返于服务器两端并且进行查询语句的构造. 当完成时它会关闭连接. 在你的应用程序中用代码构建一个 CommandBuilder 如下所示:

清单 1. 编制CommandBuilder

Dim cn As SqlConnection

Dim da As SqlDataAdapter

Dim cb As SqlCommandBuilder

cn = New SqlConnection("data source=demoserver…")

da = New SqlDataAdapter("SELECT Au_ID, au_lname, City FROM authors", cn)

cb = New SqlCommandBuilder(da)        ' 创建新的 CommandBuilder

从这段代码中你不知道背后作了什么. 此时, 如果你打开 Microsoft® SQL Server™ 日志 (当使用新编写的代码时这是一个好主意),你无法知道 构造CommandBuilder 作了些什么, 从始至终. 这是因为 ADO.NET 直到被应用程序调用前没有构造任何命令动作. 例如, 我在清单 1中添加下列代码 (它为 UpdateCommand获取SQL命令):

MsgBox(cb.GetUpdateCommand.CommandText.ToString)

ADO.NET 执行:

exec sp_executesql N' SET FMTONLY OFF; SET NO_BROWSETABLE ON;_

   SET FMTONLY ON;SELECT au_ID, au_lname, city FROM authors'

这个操作返回SELECT语句得到的源数据结果集. 他配合DataAdapter来构造另外三个查询命令 (InsertCommand,UpdateCommand, 和 DeleteCommand). 这里, 除非 SelectCommand 比标准SELECT命令复杂. 是的,如果SelectCommand太复杂或包含JOIN操作 CommandBuilder 则无法构建其他命令. 这里列举其他一些限制:

• 你的 SelectCommand 必须在查询部分返回至少一个 PrimaryKey（主键） 或unique（唯一标示）列 . 特别指出使用 TimeStamp 列而不是 PrimaryKey 等，均无法工作.
• SelectCommand 不能涉及存在JOIN 操作的SQL 查询, 存储过程,或视图. 这意味着你的 SELECT 语句必须对应单张表.
• SelectCommand 涉及的列必须同时可以进行读写操作.你不能导入那些由于某种原因不可写入的列或表达式.
• CommandBuilder 不尝试 也不提供任何机制来从查询操作中获取输出提示 (例如Identity（标识）变量).
• 如果SelectCommand的 CommandText, Connection, CommandTimeout 或 Transaction 属性发生改变,你必须通知 CommandBuilder 执行 RefreshSchema 方法, 这个操作引起另一个往返（与服务器端）.
• CommandBuilder生成 UpdateCommand 或 DeleteCommand 经不会改变最后读取之后的任何行 . 这意味着你将不得不书写你自己的代码强制执行 UPDATE 或 DELETE 操作.
• CommandBuilder是为单张不相干的表所设计. 你不要期待通过主键/外键关联来更新（一组）表.
• 如果你的SelectCommand SQL 列包含特殊字符, 如空格, 句号, 引号 或非字母数字的标示, 你无法使用CommandBuilder.

当 .NET 框架 1.1 推出 (携带于 Visual Studio .NET 2003)我发觉CommandBuilder 增加了对 Oracle客户端 和 Odbc 的.NET框架数据提供者 . 不,  CommandBuilder无法支持所有 .NET 框架数据提供者, 因此当你的数据提供者出现问题时你不要感到惊讶.

其它的方法?

好,当你的SELECT 略微有些复杂时除了坚持使用 CommandBuilder. 有没有其他替代方案?好, 这里有一些其他方法. 我们使用身边的 DACW 来帮助解决这一问题. 开始前,我通常使用存储过程来执行SELECT 和查询动作. 现在创建一个Command 对象来执行存储过程,设置 CommandText 属性为你的存储过程名称，设置CommandType 为.StoredProcedure类型—非常类似ADOc.

现在 Parameters 集合 (他用来描述 input, RETURN 变量, 和 OUTPUT 参数) 是另外一个话题. 这一点DACW 也可以提供帮助. 我们从头完成向导来生成全部参数集合. 不, 我不会象这样做.你可以, 但是我通常会对查询操作做一些必须的修改. 清单2 显示了如何初始化SelectCommand.

清单 2. 设置 SelectCommand input, OUTPUT, 和RETURN 参数变量

cn = New SqlConnection("server=betav10;database=biblio; " )

& " integrated security=sspi")

cmd = New SqlCommand("PriceByTitle", cn)

cmd.CommandType = CommandType.StoredProcedure

With cmd.Parameters

   .Add("@TitleWanted", SqlDbType.VarChar, 20)

   .Add("@TitlesFound", SqlDbType.Int).Direction =  _ 

ParameterDirection.Output

   .Add("@MaxPriceFound", SqlDbType.Decimal).Direction = _

  ParameterDirection.Output

   .Add("@MinPriceFound", SqlDbType.Decimal).Direction = _

ParameterDirection.Output

   .Add("@AvgPriceFound", SqlDbType.Decimal).Direction = _

ParameterDirection.Output

   .Add("@RETURN", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

End With

创建查询动作命令

的确, SelectCommand 相当简单. 查询命令动作却使你头疼. 我们在程序中经常遇到需要花上一整天去讨论在机器上构造复杂的连接查询, 但我们将只有篇幅去展示你如何在 DACW 基础上生成你所需SQL操作的途径. 我们也将关注如何解决典型问题—并发和标识管理 . 即,如果当ADO.NET 知晓行在你最后读取之后发生改变如何通知你的应用程序. 和 你如何知道最后服务器端生成的标识（Identity）变量是多少?

下面的示例阐述了关于 时间戳并发, 假设 你正想这么做 (你可以). 他假设你运行 SQL Server (任何版本). 对于其他DBMS（数据库管理系统）后台 Oracle 或 DB2, 你将不得不做一些 细小的代码更改以适应你的 DBMS. 不, 通常我们使用DACW 来初始化构建SQL和 Command 对象, 我们不需（手工）对代码进行任何修改.

首先, 你需要首先创建 DemoTSConcurrency 表 和 GetDemoTSRows 存储过程 以便 Visual Studio .NET 和DACW 可以检测到他们. 编译完成版本的示例程序包含在包含在目录中为你完成这些. 确认在 Server Explorer 窗格右击 Tables and Stored Procedures 图标，然后点击 Refresh 此时 IDE（集成开发环境） 将能够看到 新近创建的存储过程和表. GetDemoTSRows stored procedure 如下所示:

清单3. GetDemoTSRows

CREATE PROCEDURE dbo.GetDemoTSRows

(@StateWanted Char(2) = 'WA')  

AS SELECT PID, Name, State, TimeStamp 

FROM DemoTSConcurrency    

WHERE State = @StateWanted  

RETURN

当表和存储过程被建立, 启动DACW. 点击 Toolbox, 点击 Data 选项卡, 并且点击 SqlDataAdapter. 这将启动DACW 并请你指定一个使用的连接. 选择 或创建一个到你 DBMS 的连接(连接到到你刚刚创建的示例表和存储过程的数据库).

下一步, 告诉DACW 你需要指定的SQL 语句的代码. 我们将使用这一途径这个示例中我们将为SelectCommand使用存储过程. 是的,你可以为所用命令使用存储过程, 但这是另外一个话题了. 我们将花一点时间编辑SelectCommand 使他指向我们的存储过程.

点击 Query Builder. 工具将帮助你以托拽式的操作创建SQL 命令.从列表里选择 DemoTSConcurrency表  并点击 Add 之后点击 Close. 被选中的表现在显示在Query Builder顶端的窗格中.

选择 DemoTSConcurrency表的所有列. 不, 不要简单的点击 *—他会让你难以掌握代码. 现在你点选所有列, SQL 语句便生成显示在查询窗格. 在 SELECT命令的criteria 列输入模仿存储过程输入参数 进行筛选，格式形如 "= @StateWanted" ， Criteria 列在Query Builder的第二块面板中. 确认生成的SQL导入了一个参数，你将传递他给存储过程. 完成后, 你应该填充查询生成器  如图1. 点击Okay 回到 DACW. 点击 Next, 和 Finish—你已经完成了!

 

图1. DACW 查询生成器 填充SelectCommand 查询

检查生成的代码

此时 DACW 所做的好像魔术,你的代码看起来没有什么变化—直到你展开 "Windows Form Designer generated code" 区域. 点击标签旁边的+标志并检查DACW插入你项目的代码. 我们将删除其中一些代码, 不用有任何紧张.

第一步我们要做的是从生成代码中构建我们的SqlDataAdapter . 展开 "Windows Form Designer generated code" 区域 知道你找到SqlSelectCommand1 如清单 4所示.

清单 4. DACW-生成 SelectCommand

'

'SqlSelectCommand1

'

Me.SqlSelectCommand1.CommandText = "SELECT PID, Name, State, _

  TimeStamp FROM DemoTSConcurrency WHERE (State = " & _

  @StateWanted)"

Me.SqlSelectCommand1.Connection = Me.SqlConnection1

Me.SqlSelectCommand1.Parameters.Add(New _

 System.Data.SqlClient.SqlParameter("@StateWanted", _

 System.Data.SqlDbType.VarChar, 2, "State"))

        '

提出这些行有些奇怪, 但我们将截取他.我们主要感兴趣的是 DACW-生成的Parameters（参数）集合.但首先我们要使用他, 我们需要创建一个子函来生成我们的DataAdapter (如清单5所示).

清单5. GenerateDataAdapter 子程序

Private Sub GenerateDataAdapter()

End Sub

从DACW-generated 中剪切 SqlSelectCommand1 编码(清单 4中演示的) 到这个 GenerateDataAdapter 子程序. 在这点我将清理代码并作一些小的更改. 首先, 我们要实例化 SqlDataAdapter. 例子使用 "da" 作为对象变量. 清单 6第一行 实例化DataAdapter 并使用 New 构造子 来创建 SelectCommand, 设置他的 CommandText (用来指向我们的存储过程), 并指向工作的数据库连接. 其余的部分用来构造 Parameters（参数） 集合. 这些代码通过DACW啐手可得 .

在此环节，我们简单的对 Parameters 集合的位置做一些调整 并使用 DACW-生成的代码. 它不光导入参数名 (@StateWanted),更带入了源列的数据类型和长度. 但是等等, State 列在数据库中被定义为 CHAR 型, 而非 VarChar. 显然, DACW 这一点没有做正确 因此我们需要修正他. 清单 6 展示了我们最终的编辑结果.

清单 6. 调整代码汇同DACW-标准代码

 Private Sub GenerateDataAdapter()

        '

        'SqlSelectCommand1

        '

 da = New SqlDataAdapter("GetDemoTSRows", cn)

 da.SelectCommand.CommandType = CommandType.StoredProcedure

 With da.SelectCommand.Parameters

  .Add(New System.Data.SqlClient.SqlParameter("@StateWanted", _ 

    System.Data.SqlDbType.Char, 2, "State"))

 End With

End Sub

这一步结束后还有三个任务要做. 重新构建 InsertCommand, DeleteCommand 和 UpdateCommand 代码块, 我们需要重复这个过程—但是使用的技术有微妙的不同. 为了这个示例的目的, 我们将使用 DACW-生成 SQL, 但不是所有的参数因为我们将使用TimeStamp检测并发. 获取三块代码来创建查询操作, 我们最终的代码显示在清单 7. 我们将逐步在不同地方分别先后展示这些操作.

首先, InsertCommand 在命令中不需要时间戳列. DACW 知道何时做最好 因为服务器创建时间戳变量—标识（Identity） 列也一样. 这意味着 DACW-生成代码并非总能正确执行. 我们无需担心INSERT的并发检测 因为我们使用标识列来确认插入行是唯一的. 此时, 我们需要在行被插入后返回一个新的标识变量 . DACW 为此目的添加额外的SELECT 命令.

清单 7. DataAdapter InsertCommand 调整

' InsertCommand

' 创建一个 DA InsertCommand实例

da.InsertCommand = New SqlCommand(Nothing, cn)   

With da.InsertCommand

  .CommandText = "INSERT INTO DemoTSConcurrency(Name, State) " _

  & "VALUES (@Name, @State)" _

  & " SELECT PID, Name, State, TimeStamp FROM DemoTSConcurrency " _

  & " WHERE (PID = SCOPE_IDENTITY())"

' 设置 Parameters 集合. 均为输入参数

  .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", _

     System.Data.SqlDbType.VarChar, 50, "Name"))

  .Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", _

     System.Data.SqlDbType.Char, 2, "State"))     ' 从 VarChar变为Char

End With

调整 UpdateCommand

UpdateCommand 提出了一点新的挑战. 在这个例子中我们通过原始TimeStamp (托管于 ADO.NET DataRowVersion.Original名称空间) 同服务器端TimeStamp列的新版本测试并发. 如果测试不匹配, 我们就能知道该行已经在你获取之后又发生了更改. 和InsertCommand一样, UpdateCommand 导入了 第二个 SELECT 命令来返回当前行的数据. 坦白的讲,这非常的无聊, 他必定与我们刚刚所做的操作结果匹配. 在 (非常特殊) 的情况下你没有写入所有列, 这样做可能有用, 但这样做无疑浪费时间. 我从 DACW生成代码中消去几个额外参数获得下面清单 8的结果.

清单 8. DataAdapter UpdateCommand 调整

  '

  'SqlUpdateCommand1

   '

' 创建 DA UpdateCommand实例

da.UpdateCommand = New SqlCommand(Nothing, cn) 

  With da.UpdateCommand

    .CommandText = "UPDATE DemoTSConcurrency SET Name = " _

& "@Name, State = @State " _

     & " WHERE (PID = @PID) AND (TimeStamp = @Original_TimeStamp); " _

     .Parameters.Add(New System.Data.SqlClient.SqlParameter("@PID", _

        System.Data.SqlDbType.Int, 4, "PID"))

     .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", _

        System.Data.SqlDbType.VarChar, 50, "Name"))

     .Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", _

        System.Data.SqlDbType.Char, 2, "State"))

     .Parameters.Add(New System.Data.SqlClient.SqlParameter( _

       "@Original_TimeStamp", _

        System.Data.SqlDbType.VarBinary, 8, _

         System.Data.ParameterDirection.Input, _

        False, CType(0, Byte), CType(0, Byte), "TimeStamp", _

        System.Data.DataRowVersion.Original, Nothing))

   End With

调整 DeleteCommand

DeleteCommand 非常优美简捷. 在这个例子中我们简单的通过 原始PID (行主键)  和原始 TimeStamp 来对一个实际的行进行（删除）演示.

清单 9. DataAdapter DeleteCommand 调整

        'SqlDeleteCommand1

        '

da.DeleteCommand = New SqlCommand(Nothing, cn) ' Instantiate new DA DeleteCommand

With da.DeleteCommand

  .CommandText = "DELETE FROM DemoTSConcurrency " _

    & " WHERE (PID = @Original_PID) AND (TimeStamp = @Original_TimeStamp)"

   .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_PID", _

   System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _

   CType(0, Byte), CType(0, Byte), "PID",  _

   System.Data.DataRowVersion.Original, Nothing))

.Parameters.Add(New System.Data.SqlClient.SqlParameter( _

   "@Original_TimeStamp", _

   System.Data.SqlDbType.VarBinary, 8, _

 System.Data.ParameterDirection.Input, False, _

   CType(0, Byte), CType(0, Byte), "TimeStamp",  _

   System.Data.DataRowVersion.Original, Nothing))

End With

总结

论文包含的示例实现了 DataAdapter 的命令组. 我希望这个指南能够使你摆脱对 DACW 和 CommandBuilder的依赖. 通过这十二页多的提纲说明,你可以尝试一些三栏式的查询生成器帮助你和你的团组提升学习曲线.

关于作者

William (Bill) Vaughn是 Beta V Corporation Redmond, Washington的会长. 他为全球客户提供培训, 顾问, 和服务咨询, 专长Visual Basic 和SQL Server 数据库构架与实施. William's最近所著图书有《 ADO.NET and ADO Examples and Best Practices for Visual Basic Programmers—2nd Edition》和 C# 版《 ADO.NET Examples and Best Practices for C# Programmers》.在今后都是非常值得借鉴. William创作了许多论文组织培训讲座在世界范围的电脑论坛他还是优秀的演讲家.你可以通过billva@nwlink.com与他联系.