更新 TableAdapter 以使用 JOIN (C#)

作者 ：Scott Mitchell

下载 PDF

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

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

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

我们在 TableAdapters 中使用 JOIN s 的原因是 TableAdapter 向导中存在自动生成相应和INSERTUPDATEDELETE语句的限制。 更具体地说，如果 TableAdapter 的主查询包含任何 JOIN s，则 TableAdapter 无法为表 InsertCommandUpdateCommandSQL 语句或存储过程和DeleteCommand属性自动创建临时 SQL 语句或存储过程。

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

回想一下，DataSet 的第一个教程Northwind中创建的ProductsTableAdapter子查询使用相关子查询来收回每个产品的相应类别和供应商名称。 下面 ProductsTableAdapter 显示了主查询。

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 语句列列表中的附加列。

或者，可以使用 a 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 和相关子查询都可用于从其他表中检索相关数据，因此许多开发人员会暂留头，并想知道使用哪种方法。 我所谈论的所有 SQL 大师都说了大致相同的事情，它并不重要，因为SQL Server将产生大致相同的执行计划。 然后，他们的建议是使用你和你的团队最熟悉的技术。 值得指出的是，在提出这一建议后，这些专家立即表示他们对相关子查询的偏好 JOIN 。

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

若要探索此缺点，请在文件夹中创建临时类型化数据集 ~/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 将自动创建INSERTUPDATE和DELETE基于主查询的语句。 如果单击“高级”按钮，可以看到此功能已启用。 尽管此设置，TableAdapter 将无法创建INSERTUPDATE和DELETE语句，因为主查询包含 aJOIN。

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

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

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

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

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

若要解决此问题，可以通过属性窗口手动提供 SQL 语句和DeleteCommand参数InsertCommandUpdateCommand以及属性。 或者，我们可以首先将 TableAdapter 的主查询配置为 不包含 任何 JOIN 查询。 这将允许自动生成和INSERTUPDATEDELETE语句。 完成向导后，我们可以从属性窗口手动更新 TableAdapter sSelectCommand，使其包含JOIN语法。

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

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

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

在本教程中，我们将为 Employees 数据集中的 NorthwindWithSprocs 表添加 TableAdapter 和强类型 DataTable。 该 Employees 表包含指定 ReportsTo 员工经理的字段 EmployeeID 。 例如，员工安妮·多兹沃思 ReportTo 的值为 5，即 EmployeeID 史蒂文·布坎南。 因此，安妮向她的经理史蒂文报告。 除了报告每个员工 ReportsTo 的值之外，我们还可能想要检索其经理的名称。 这可以使用 a JOIN. 但是，在最初创建 TableAdapter 时使用 JOIN 阻止向导自动生成相应的插入、更新和删除功能。 因此，我们将首先创建一个 TableAdapter，其主查询不包含任何 JOIN 查询。 然后，在步骤 2 中，我们将更新主查询存储过程，以通过 a JOIN检索管理器的名称。

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

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

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

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

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

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

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

最后一步会提示我们命名 TableAdapter s 方法。 使用 Fill 并 GetEmployees 用作方法名称。 此外，请确保保留 Create 方法以将更新直接发送到数据库 (GenerateDBDirectMethods) 复选框。

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

完成向导后，花点时间检查数据库中的存储过程。 应会看到四个新项： Employees_Select、 Employees_Insert、 Employees_Update和 Employees_Delete。 接下来，检查 EmployeesDataTable 并 EmployeesTableAdapter 刚刚创建。 DataTable 包含主查询返回的每个字段的列。 单击 TableAdapter，然后转到属性窗口。 你将在那里看到InsertCommandUpdateCommand正确配置了和DeleteCommand属性以调用相应的存储过程。

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

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

首先转到服务器资源管理器，向下钻取到 Northwind 数据库的“存储过程”文件夹，然后打开 Employees_Select 存储过程。 如果未看到此存储过程，请右键单击“存储过程”文件夹，然后选择“刷新”。 更新存储过程，以便它使用 a 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：存储过程结果显示在输出窗口中 (单击以查看全尺寸图像)

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

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

我们已在前面的教程中手动添加 DataTable 列，包括 主记录项目符号列表和详细信息 DataList 和 上传文件，我们将在下一教程中更详细地查看此过程。 但是，对于本教程，让我们通过 TableAdapter 配置向导使用自动方法。

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

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

单击“完成”完成向导。 返回到数据集设计器后，包括 EmployeesDataTable 两个附加列： ManagerFirstName 和 ManagerLastName。

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

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

Create a new class file in the ~/App_Code/BLL folder named 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 s Adapter 属性返回数据集的EmployeesTableAdapter实例NorthwindWithSprocs。 类 GetEmployees 和 DeleteEmployee 方法使用此属性。 该方法 GetEmployees 调用 EmployeesTableAdapter 相应的 GetEmployees 方法，该方法调用 Employees_Select 存储过程并在其中 EmployeeDataTable填充其结果。 该方法DeleteEmployee同样调用EmployeesTableAdapterDelete调用存储过程的方法Employees_Delete。

完成 EmployeesBLLWithSprocs 课堂后，我们便可以通过 ASP.NET 页处理员工数据。 JOINs.aspx打开文件夹中的页面AdvancedDAL，将 GridView 从工具箱拖到设计器上，将其ID属性设置为 Employees。 Next, from the GridView s smart tag, bind the grid to a new ObjectDataSource control named EmployeesDataSource.

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

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

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

Visual Studio 将为每个 EmployeesDataTable 列将 BoundField 添加到 GridView。 Remove all of these BoundFields except for Title, LastName, FirstName, ManagerFirstName, and ManagerLastName and rename the HeaderText properties for the last four BoundFields to Last Name, First Name, Manager s First Name, and Manager s Last Name, respectively.

若要允许用户从此页面中删除员工，我们需要执行两项操作。 首先，通过从其智能标记中检查“启用删除”选项，指示 GridView 提供删除功能。 其次，将 ObjectDataSource 属性从 ObjectDataSource OldValuesParameterFormatString 向导设置的值 (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记录。 我们在 Typed DataSet s TableAdapters 教程的“使用现有存储过程 ”中讨论了此方法。

我把这作为读者的练习。

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

幸运的是，使用存储过程创建的 TableAdapters 与使用即席 SQL 语句创建的表Adapters 不相同。 因此，创建一个 TableAdapter，其主查询在使用存储过程时使用 JOIN 表Adapter是可行的。 在本教程中，我们了解了如何创建此类 TableAdapter。 我们首先对 TableAdapter 的主查询使用 JOIN-less SELECT 查询，以便自动创建相应的插入、更新和删除存储过程。 完成 TableAdapter 的初始配置后，我们增强了 SelectCommand 存储过程，以使用 JOIN 并重新运行 TableAdapter 配置向导来更新 EmployeesDataTable s 列。

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

快乐编程！

斯科特·米切尔，七本 ASP/ASP.NET 书籍和 4GuysFromRolla.com 创始人，自1998年以来一直在与 Microsoft Web 技术合作。 斯科特担任独立顾问、教练和作家。 他的最新书是 山姆斯教自己在24小时内 ASP.NET 2.0。 他可以到达 mitchell@4GuysFromRolla.com。 或通过他的博客，可以在其中 http://ScottOnWriting.NET找到。

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