更新 TableAdapter 以使用 JOIN (VB)

  • 项目

作者 ：Scott Mitchell

下载 PDF

使用数据库时，通常会请求分布在多个表中的数据。 若要从两个不同的表中检索数据，可以使用关联的子查询或 JOIN 操作。 在本教程中，我们先比较相关子查询和 JOIN 语法，然后再了解如何创建在其main查询中包含 JOIN 的 TableAdapter。

简介

使用关系数据库时，我们感兴趣的数据通常分布在多个表中。 例如，在显示产品信息时，我们可能希望列出每个产品的相应类别和供应商名称。 表 Products 具有 CategoryID 和 SupplierID 值，但实际类别和供应商名称分别位于 Categories 和 Suppliers 表中。

若要从另一个相关表中检索信息，可以使用相关子查询或 JOIN。 相关子查询是引用外部查询中的列的嵌套 SELECT 查询。 例如，在创建数据访问层教程中，我们在 main 查询中使用了两个相关子查询ProductsTableAdapter来返回每个产品的类别和供应商名称。 是 JOIN 合并两个不同表中的相关行的 SQL 构造。 JOIN我们在 SqlDataSource 控件教程的查询数据中使用了 ，以将类别信息与每个产品一起显示。

我们之所以不对 TableAdapters 使用 JOIN ，是因为 TableAdapter 向导在自动生成相应的 INSERT、 UPDATE和 DELETE 语句方面存在限制。 更具体地说，如果 TableAdapter main 查询包含任何 JOIN ，则 TableAdapter 无法为其 InsertCommand、 UpdateCommand和 DeleteCommand 属性自动创建临时 SQL 语句或存储过程。

在本教程中，我们将简要比较和对比相关子查询 和 JOIN ，然后探索如何创建在其main查询中包含 的 TableAdapterJOIN。

比较和对比相关子查询和JOIN s

回想一下， ProductsTableAdapter 在 DataSet 的第一个教程中创建的 Northwind 使用相关子查询来恢复每个产品的相应类别和供应商名称。 ProductsTableAdapter main查询如下所示。

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued,
       (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = 
            Products.CategoryID) as CategoryName, 
       (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = 
            Products.SupplierID) as SupplierName
FROM Products

两个关联的子查询和 (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID)(SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) 是 SELECT 每个产品返回单个值作为外部 SELECT 语句列列表中的附加列的查询。

或者， JOIN 可以使用 返回每个产品的供应商和类别名称。 以下查询返回与上述查询相同的输出，但使用 JOIN 代替子查询：

SELECT ProductID, ProductName, Products.SupplierID, Products.CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued,
       Categories.CategoryName, 
       Suppliers.CompanyName as SupplierName
FROM Products
    LEFT JOIN Categories ON
        Categories.CategoryID = Products.CategoryID
    LEFT JOIN Suppliers ON
        Suppliers.SupplierID = Products.SupplierID

JOIN根据某些条件，将一个表中的记录与另一个表中的记录合并。 例如，在上述查询中， LEFT JOIN Categories ON Categories.CategoryID = Products.CategoryID 指示SQL Server将每个产品记录与其值与产品 值匹配的CategoryID类别记录CategoryID合并。 合并的结果允许我们处理每个产品 (的相应类别字段，例如 CategoryName) 。

注意

JOIN 从关系数据库查询数据时，通常使用 s。 如果你不熟悉JOIN语法或需要稍微了解其用法，我建议在 W3 Schools 学习 SQL 联接教程。 SQL 联机丛书的JOIN基础知识和子查询基础知识部分也值得一读。

由于 JOIN 和相关子查询都可用于从其他表中检索相关数据，因此许多开发人员都感到不快，并想知道要使用哪种方法。 与我交谈过的所有 SQL 大师都说了大致相同的话，在性能方面并不重要，因为SQL Server会生成大致相同的执行计划。 然后，他们的建议是使用你和你的团队最熟悉的技术。 值得指出的是，在传授这一建议后，这些专家立即表示倾向于 JOIN 相关子查询。

使用类型化数据集生成数据访问层时，工具在使用子查询时效果更好。 具体而言，如果main查询包含任何 JOIN s，TableAdapter 向导将不会自动生成相应的 INSERT、 UPDATE和 DELETE 语句，而是在使用相关子查询时自动生成这些语句。

若要了解此缺点，请在 文件夹中创建临时类型化数据集 ~/App_Code/DAL 。 在 TableAdapter 配置向导中，选择使用即席 SQL 语句并输入以下 SELECT 查询 (请参阅图 1) ：

SELECT ProductID, ProductName, Products.SupplierID, Products.CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued,
       Categories.CategoryName, 
       Suppliers.CompanyName as SupplierName
FROM Products
    LEFT JOIN Categories ON
        Categories.CategoryID = Products.CategoryID
    LEFT JOIN Suppliers ON
        Suppliers.SupplierID = Products.SupplierID

显示 TableAdaptor 配置向导窗口的屏幕截图，其中输入了包含 JON 的查询。

图 1：输入包含 JOIN 的主查询 (单击以查看全尺寸图像)

默认情况下，TableAdapter 将根据main查询自动创建 INSERT、 UPDATE和 DELETE 语句。 如果单击“高级”按钮，可以看到此功能已启用。 尽管如此，TableAdapter 将无法创建 INSERT、 和 DELETE 语句，UPDATE因为main查询包含 JOIN。

显示“高级选项”窗口的屏幕截图，其中选中了“生成插入”、“更新”和“删除语句”复选框。

图 2：输入包含 JOIN 的主查询

单击“完成”以完成向导。 此时，数据集Designer将包含一个 TableAdapter，其中包含一个 DataTable，其中包含查询列列表中返回SELECT的每个字段的列。 这包括 CategoryName 和 SupplierName，如图 3 所示。

DataTable 包含列列表中返回的每个字段的列

图 3：DataTable 包含列列表中返回的每个字段的列

虽然 DataTable 具有相应的列，但 TableAdapter 缺少其 InsertCommand、 UpdateCommand和 DeleteCommand 属性的值。 若要确认这一点，请单击Designer中的 TableAdapter，然后转到属性窗口。 你将看到 InsertCommand、 UpdateCommand和 DeleteCommand 属性设置为 (None) 。

InsertCommand、UpdateCommand 和 DeleteCommand 属性设置为 (None)

图 4：、 InsertCommandUpdateCommand和 DeleteCommand 属性设置为 (无) (单击以查看全尺寸图像)

若要解决此缺点，可以通过 属性窗口手动提供 InsertCommand、 UpdateCommand和 属性的 SQL 语句和DeleteCommand参数。 或者，我们可以首先将 TableAdapter main 查询配置为不包含任何 JOIN 。 这将允许 INSERT为我们自动生成 、 UPDATE和 DELETE 语句。 完成向导后，我们可以从属性窗口手动更新 TableAdapterSelectCommand，使其包含 JOIN 语法。

虽然此方法有效，但它在使用临时 SQL 查询时非常脆弱，因为每当通过向导重新配置 TableAdapter main 查询时，都会重新创建自动生成INSERT的 、 UPDATE和 DELETE 语句。 这意味着，如果我们右键单击 TableAdapter，从上下文菜单中选择“配置”，然后再次完成向导，那么我们以后所做的所有自定义都将丢失。

幸运的是，TableAdapter 自动生成 INSERT的 、 UPDATE和 DELETE 语句的脆性仅限于临时 SQL 语句。 如果 TableAdapter 使用存储过程，则可以自定义 SelectCommand、 InsertCommand、 UpdateCommand或 DeleteCommand 存储过程并重新运行 TableAdapter 配置向导，而无需担心存储过程会被修改。

在接下来的几个步骤中，我们将创建一个 TableAdapter，它最初使用省略任何 JOIN 的 main 查询，以便自动生成相应的插入、更新和删除存储过程。 然后，我们将更新 ， SelectCommand 以便 使用 JOIN 从相关表返回其他列的 。 最后，我们将创建一个相应的业务逻辑层类，并演示如何在 ASP.NET 网页中使用 TableAdapter。

步骤 1：使用简化的主查询创建 TableAdapter

在本教程中，我们将为 Employees DataSet 中的 NorthwindWithSprocs 表添加 TableAdapter 和强类型 DataTable。 该 Employees 表包含一个 ReportsTo 字段，该字段指定 EmployeeID 了员工经理的 。 例如，员工 Anne Dodsworth ReportTo 的值为 5，即 EmployeeID Steven Buchanan 的 。 因此，安妮向她的经理史蒂文报告。 除了报告每个员工 ReportsTo 的值，我们可能还需要检索其经理的姓名。 这可以使用 来实现 JOIN。 但在最初创建 TableAdapter 时使用 JOIN 会阻止向导自动生成相应的插入、更新和删除功能。 因此，我们将首先创建一个 TableAdapter，其main查询不包含任何 JOIN 。 然后，在步骤 2 中，我们将更新 main 查询存储过程，以通过 JOIN检索管理器的名称。

首先打开 NorthwindWithSprocs 文件夹中的 ~/App_Code/DAL DataSet。 右键单击Designer，从上下文菜单中选择“添加”选项，然后选择“TableAdapter”菜单项。 这将启动 TableAdapter 配置向导。 如图 5 所示，让向导创建新的存储过程，然后单击“下一步”。 有关从 TableAdapter 向导创建新存储过程的复习，请参阅 为 Typed DataSet s TableAdapters 创建新存储过程 教程。

选择“创建新存储过程”选项

图 5：选择“创建新存储过程”选项 (单击以查看全尺寸图像)

对 TableAdapter main 查询使用以下SELECT语句：

SELECT EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, Country
FROM Employees

由于此查询不包括任何 JOIN ，TableAdapter 向导将自动创建具有相应 INSERT、 UPDATE和 DELETE 语句的存储过程，以及用于执行main查询的存储过程。

以下步骤允许我们命名 TableAdapter 存储过程。 使用名称 Employees_Select、 Employees_Insert、 Employees_Update和 Employees_Delete，如图 6 所示。

将 TableAdapter 命名为存储过程

图 6：将 TableAdapter 命名为存储过程 (单击以查看全尺寸图像)

最后一步提示我们命名 TableAdapter 方法。 使用 Fill 和 GetEmployees 作为方法名称。 此外，请确保选中“创建方法以将更新直接发送到数据库 (GenerateDBDirectMethods) ”复选框。

将 TableAdapter s 方法命名为 Fill 和 GetEmployees

图 7：将 TableAdapter 命名为方法 Fill 并 GetEmployees (单击以查看全尺寸图像)

完成向导后，请花点时间检查数据库中的存储过程。 应会看到四个新值： Employees_Select、 Employees_Insert、 Employees_Update和 Employees_Delete。 接下来，检查 EmployeesDataTable 刚刚创建的 。EmployeesTableAdapter DataTable 包含main查询返回的每个字段的列。 单击“TableAdapter”，然后转到属性窗口。 你会看到 InsertCommand、 UpdateCommand和 DeleteCommand 属性已正确配置为调用相应的存储过程。

TableAdapter 包括插入、更新和删除功能

图 8：TableAdapter 包括插入、更新和删除功能 (单击以查看全尺寸图像)

在自动创建插入、更新和删除存储过程并 InsertCommand正确配置 、 UpdateCommand和 DeleteCommand 属性后，我们可以自定义 SelectCommand 存储过程以返回有关每个员工经理的其他信息。 具体而言，我们需要更新 Employees_Select 存储过程以使用 并 JOIN 返回管理器 和 FirstNameLastName 值。 更新存储过程后，需要更新 DataTable，使其包含这些附加列。 我们将在步骤 2 和 3 中处理这两个任务。

步骤 2：自定义存储过程以包含JOIN

首先转到服务器资源管理器，向下钻取到 Northwind 数据库的“存储过程”文件夹，然后打开 Employees_Select 存储过程。 如果未看到此存储过程，请右键单击“存储过程”文件夹，然后选择“刷新”。 更新存储过程，使其使用 LEFT JOIN 返回经理的名字和姓氏：

SELECT Employees.EmployeeID, Employees.LastName, 
       Employees.FirstName, Employees.Title, 
       Employees.HireDate, Employees.ReportsTo, 
       Employees.Country,
       Manager.FirstName as ManagerFirstName, 
       Manager.LastName as ManagerLastName
FROM Employees
    LEFT JOIN Employees AS Manager ON
        Employees.ReportsTo = Manager.EmployeeID

更新 SELECT 语句后，通过转到“文件”菜单并选择“保存”来保存 Employees_Select更改。 或者，可以单击工具栏中的“保存”图标或按 Ctrl+S。 保存更改后，右键单击服务器资源管理器中的 Employees_Select 存储过程，然后选择“执行”。 这将运行存储过程，并在“输出”窗口中显示其结果， (请参阅图 9) 。

存储过程结果显示在“输出”窗口中

图 9：存储过程结果显示在输出窗口中 (单击以查看全尺寸图像)

步骤 3：更新 DataTable s 列

此时， Employees_Select 存储过程将返回 ManagerFirstName 和 ManagerLastName 值，但 EmployeesDataTable 缺少这些列。 可以通过以下两种方式之一将这些缺失列添加到 DataTable：

  • 手动 - 右键单击 DataSet Designer中的 DataTable，然后从“添加”菜单中选择“列”。 然后，可以命名列并相应地设置其属性。
  • 自动 - TableAdapter 配置向导将更新 DataTable 的列，以反映存储过程返回的 SelectCommand 字段。 使用即席 SQL 语句时，向导还将删除 InsertCommand、 UpdateCommand和 DeleteCommand 属性， SelectCommand 因为 现在包含 JOIN。 但是，使用存储过程时，这些命令属性保持不变。

我们已探索在以前的教程中手动添加 DataTable 列，包括 使用带详细信息数据列表的主记录项目符号列表 和 上传文件，我们将在下一教程中再次更详细地了解此过程。 但是，在本教程中，让我们通过 TableAdapter 配置向导使用自动方法。

首先，右键单击 ， EmployeesTableAdapter 然后从上下文菜单中选择“配置”。 此时会显示 TableAdapter 配置向导，其中列出了用于选择、插入、更新和删除的存储过程，以及返回值和参数 (（如果有任何) ）。 图 10 显示了此向导。 在这里， Employees_Select 我们可以看到存储过程现在返回 ManagerFirstName 和 ManagerLastName 字段。

向导显示Employees_Select存储过程的更新列列表

图 10：向导显示存储过程的更新列列表 Employees_Select (单击以查看全尺寸图像)

单击“完成”完成向导。 返回到 DataSet Designer后，包括EmployeesDataTable两个附加列： ManagerFirstName 和 ManagerLastName。

EmployeesDataTable 包含两个新列

图 11：包含 EmployeesDataTable 两个新列 (单击以查看全尺寸图像)

为了说明更新 Employees_Select 的存储过程已生效，并且 TableAdapter 的插入、更新和删除功能仍然有效，让我们创建一个允许用户查看和删除员工的网页。 但是，在创建此类页面之前，我们需要先在业务逻辑层中创建一个新类，以便与 DataSet 中的 NorthwindWithSprocs 员工一起工作。 在步骤 4 中，我们将创建一个 EmployeesBLLWithSprocs 类。 在步骤 5 中，我们将从 ASP.NET 页使用此类。

步骤 4：实现业务逻辑层

在 ~/App_Code/BLL 名为 EmployeesBLLWithSprocs.vb的文件夹中创建新的类文件。 此类模拟现有 EmployeesBLL 类的语义，仅此新类提供的方法较少，并使用 NorthwindWithSprocs DataSet (而不是 Northwind DataSet) 。 将以下代码添加到 EmployeesBLLWithSprocs 类。

Imports NorthwindWithSprocsTableAdapters
<System.ComponentModel.DataObject()> _
Public Class EmployeesBLLWithSprocs
    Private _employeesAdapter As EmployeesTableAdapter = Nothing
    Protected ReadOnly Property Adapter() As EmployeesTableAdapter
        Get
            If _employeesAdapter Is Nothing Then
                _employeesAdapter = New EmployeesTableAdapter()
            End If
            Return _employeesAdapter
        End Get
    End Property
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, True)> _
    Public Function GetEmployees() As NorthwindWithSprocs.EmployeesDataTable
        Return Adapter.GetEmployees()
    End Function
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Delete, True)> _
    Public Function DeleteEmployee(ByVal employeeID As Integer) As Boolean
        Dim rowsAffected = Adapter.Delete(employeeID)
        'Return true if precisely one row was deleted, otherwise false
        Return rowsAffected = 1
    End Function
End Class

类 EmployeesBLLWithSprocs 的 Adapter 属性返回 DataSet 的EmployeesTableAdapter实例NorthwindWithSprocs。 这由 类 GetEmployees 和 DeleteEmployee 方法使用。 方法 GetEmployees 调用 EmployeesTableAdapter 相应的 GetEmployees 方法，该方法调用 Employees_Select 存储过程并在 中 EmployeeDataTable填充其结果。 方法 DeleteEmployee 同样调用 EmployeesTableAdapter 调用存储过程的 s Delete 方法 Employees_Delete 。

步骤 5：处理表示层中的数据

完成课程后 EmployeesBLLWithSprocs ，我们已准备好通过 ASP.NET 页处理员工数据。 JOINs.aspx打开 文件夹中的页面AdvancedDAL，并将 GridView 从工具箱拖到Designer，并将其ID属性设置为 Employees。 接下来，从 GridView 的智能标记中，将网格绑定到名为 EmployeesDataSource的新 ObjectDataSource 控件。

将 ObjectDataSource 配置为使用 EmployeesBLLWithSprocs 类，并从 SELECT 和 DELETE 选项卡中，确保 GetEmployees 从下拉列表中选择 和 DeleteEmployee 方法。 单击“完成”以完成 ObjectDataSource 的配置。

将 ObjectDataSource 配置为使用 EmployeesBLLWithSprocs 类

图 12：将 ObjectDataSource 配置为使用 EmployeesBLLWithSprocs 类 (单击以查看全尺寸图像)

让 ObjectDataSource 使用 GetEmployees 和 DeleteEmployee 方法

图 13：让 ObjectDataSource 使用 GetEmployees 和 DeleteEmployee 方法 (单击以查看全尺寸图像)

Visual Studio 将为每个 EmployeesDataTable 列向 GridView 添加一个 BoundField。 删除除 、、LastNameFirstName、 ManagerFirstName和 ManagerLastName 以外的Title所有这些 BoundFields，并将最后四个 BoundFields 的属性分别重命名HeaderText为姓氏、名字、经理名字和经理姓氏。

若要允许用户从此页面中删除员工，我们需要执行两项操作。 首先，通过选中智能标记中的“启用删除”选项，指示 GridView 提供删除功能。 其次，将 ObjectDataSource 属性 OldValuesParameterFormatString 从 ObjectDataSource 向导 (original_{0}) 设置的值更改为) ({0} 默认值。 进行这些更改后，GridView 和 ObjectDataSource 的声明性标记应如下所示：

<asp:GridView ID="Employees" runat="server" AutoGenerateColumns="False" 
    DataKeyNames="EmployeeID" DataSourceID="EmployeesDataSource">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" />
        <asp:BoundField DataField="Title" 
            HeaderText="Title" 
            SortExpression="Title" />
        <asp:BoundField DataField="LastName" 
            HeaderText="Last Name" 
            SortExpression="LastName" />
        <asp:BoundField DataField="FirstName" 
            HeaderText="First Name" 
            SortExpression="FirstName" />
        <asp:BoundField DataField="ManagerFirstName" 
            HeaderText="Manager's First Name" 
            SortExpression="ManagerFirstName" />
        <asp:BoundField DataField="ManagerLastName" 
            HeaderText="Manager's Last Name" 
            SortExpression="ManagerLastName" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="EmployeesDataSource" runat="server" 
    DeleteMethod="DeleteEmployee" OldValuesParameterFormatString="{0}" 
    SelectMethod="GetEmployees" TypeName="EmployeesBLLWithSprocs">
    <DeleteParameters>
        <asp:Parameter Name="employeeID" Type="Int32" />
    </DeleteParameters>
</asp:ObjectDataSource>

通过浏览器访问页面来测试页面。 如图 14 所示，页面将列出每个员工及其经理的姓名， (假设他们有一个) 。

Employees_Select存储过程中的 JOIN 返回管理器名称

图 14： JOIN 存储过程中的 Employees_Select 返回管理器的名称 (单击以查看全尺寸图像)

单击“删除”按钮将启动删除工作流，最终 Employees_Delete 执行存储过程。 但是，由于外键约束冲突，存储过程中的尝试 DELETE 语句失败 (请参阅图 15) 。 具体而言，每个员工在 Orders 表中都有一个或多个记录，从而导致删除失败。

删除具有相应订单的员工会导致外键约束冲突

图 15：删除具有相应订单的员工导致外键约束冲突 (单击以查看全尺寸图像)

若要允许删除员工，可以：

  • 更新外键约束以级联删除，
  • 从表中手动删除要删除的员工 () 的记录 Orders ，或者
  • 更新 Employees_Delete 存储过程以先从表中删除相关记录， Orders 然后再删除记录 Employees 。 我们在将 现有存储过程用于类型化数据集的 TableAdapters 教程中讨论了此方法。

我把这个留作读者的练习。

总结

使用关系数据库时，查询通常会从多个相关表拉取其数据。 关联的子查询 和 JOIN 提供两种不同的技术，用于从查询中的相关表访问数据。 在前面的教程中，我们最常使用关联的子查询，因为 TableAdapter 无法为涉及 JOIN 的查询自动生成 INSERT、 UPDATE和 DELETE 语句。 虽然这些值可以手动提供，但当使用即席 SQL 语句时，当 TableAdapter 配置向导完成时，任何自定义项都会被覆盖。

幸运的是，使用存储过程创建的 TableAdapter 不会受到与使用即席 SQL 语句创建的相同脆性。 因此，使用存储过程时，创建其main查询使用 JOIN 的 TableAdapter 是可行的。 本教程介绍了如何创建此类 TableAdapter。 我们首先对 TableAdapter main 查询使用 JOIN-less SELECT 查询，以便自动创建相应的插入、更新和删除存储过程。 完成 TableAdapter 的初始配置后，我们扩充了 SelectCommand 存储过程，以使用 JOIN 并重新运行 TableAdapter 配置向导来更新 EmployeesDataTable s 列。

重新运行 TableAdapter 配置向导会自动更新 EmployeesDataTable 列，以反映存储过程返回 Employees_Select 的数据字段。 或者，我们可以手动将这些列添加到 DataTable。 在下一教程中，我们将探讨如何手动将列添加到 DataTable。

编程快乐！

关于作者

斯科特·米切尔是七本 ASP/ASP.NET 书籍的作者和 4GuysFromRolla.com 的创始人，自 1998 年以来一直在使用 Microsoft Web 技术。 Scott 担任独立顾问、培训师和作家。 他的最新一本书是 山姆斯在 24 小时内 ASP.NET 2.0。 可以在 上mitchell@4GuysFromRolla.com联系他，也可以通过他的博客（可在 中找到http://ScottOnWriting.NET）。

特别感谢

本教程系列由许多有用的审阅者审阅。 本教程的主要审阅者是希尔顿·盖塞诺、大卫·苏鲁和特蕾莎·墨菲。 有兴趣查看我即将发布的 MSDN 文章？ 如果是，请在 处mitchell@4GuysFromRolla.com放置一行。

上一页下一页