更新 TableAdapter 以使用 JOIN (C#)

作者 ：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 s，则 TableAdapter 无法为其 、 UpdateCommand和 DeleteCommand 属性自动创建即席 SQL 语句或存储过程InsertCommand。

在本教程中，我们将简要比较和对比相关的子查询和 JOIN s，然后探索如何创建在其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 加入教程。 同样值得一读的是 JOINSQL 联机丛书的基础知识和子查询基础知识部分。

由于 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

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

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

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

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

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

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

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

若要解决此缺点，可以通过 属性窗口手动提供 、 UpdateCommand和 属性的 SQL 语句和DeleteCommand参数InsertCommand。 或者，我们可以先将 TableAdapter main 查询配置为不包含任何 JOIN 。 这将允许 INSERT为我们自动生成 、 UPDATE和 DELETE 语句。 完成向导后，我们可以从 属性窗口手动更新 TableAdapter，SelectCommand使其包含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 s。 然后，在步骤 2 中，我们将更新 main 查询存储过程，以通过 JOIN检索管理器的名称。

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

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

将以下SELECT语句用于 TableAdapter main 查询：

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 所示。

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

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

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

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

图 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 字段。

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

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

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

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

步骤 4：实现业务逻辑层

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

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using NorthwindWithSprocsTableAdapters;
[System.ComponentModel.DataObject]
public class EmployeesBLLWithSprocs
{
    private EmployeesTableAdapter _employeesAdapter = null;
    protected EmployeesTableAdapter Adapter
    {
        get
        {
            if (_employeesAdapter == null)
                _employeesAdapter = new EmployeesTableAdapter();
            return _employeesAdapter;
        }
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Select, true)]
    public NorthwindWithSprocs.EmployeesDataTable GetEmployees()
    {
        return Adapter.GetEmployees();
    }
    [System.ComponentModel.DataObjectMethodAttribute
        (System.ComponentModel.DataObjectMethodType.Delete, true)]
    public bool DeleteEmployee(int employeeID)
    {
        int rowsAffected = Adapter.Delete(employeeID);
        // Return true if precisely one row was deleted, otherwise false
        return rowsAffected == 1;
    }
}

类 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 的配置。

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

图 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 所示，页面将列出每个员工及其经理的姓名， (假设他们有一个) 。

图 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放置一行。

上一页下一页