更新 TableAdapter 来使用 JOIN 68

简介

在使用关系数据库时，我们需要的数据经常散布在多个表中。例如，在显示产品信息时，我们也许希望列出每个产品相应的类别和供应商的名称。Products 表拥有CategoryID 和 SupplierID 的值，但实际的类别和供应商名称分别位于Categories 和 Suppliers 表中。

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

我们放弃结合使用JOIN 和 TableAdapter 的原因是，TableAdapter 的向导在自动生成相关INSERT 、UPDATE 和DELETE 语句时存在限制。具体地说，如果 TableAdapter 的主查询中包含 JOIN 语句，TableAdapter 就无法自动为InsertCommand 、UpdateCommand 和DeleteCommand 属性创建 ad-hoc SQL 语句或存储过程。

在本教程中，在探讨如何创建一个在主查询中包含JOIN 语句的TableAdapter 之前，我们将对相关子查询和 JOIN 进行简单的比较和对比。

对相关子查询和JOIN进行比较和对比

回想一下，我们在第一篇教程中在 Northwind Dataset 中创建的 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 语句的 column 列表中一个额外的列。

或者，我们也可以使用 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 通常用于在关系数据库中查询数据。如果你不熟悉 JOIN 语法，或者需要复习JOIN 的用法，我建议你阅读 W3 学校论坛上的文章 SQL Join 教程。此外，你还可以阅读在线 SQL 书籍的 JOIN 基础知识和子查询基础知识部分。

由于 JOIN 和相关子查询都可以用于从其它表中获取相关数据，许多开发人员都对到底使用哪种方法感到迷惑不解。所有与我交谈过的SQL 专家都持基本相同的观点，即无论选择哪种方法， SQL Server 都将产生大致相同的执行计划，因此性能所受的影响不大。他们的建议是，使用你和你的团队最熟悉的技术。但在提出上述建议之后，这些专家又会立即表示他们更偏爱JOIN （而非相关子查询）。

在使用强类型 Dataset 构建数据访问层时，使用子查询的效果更好。具体来说，如果主查询中包含JOIN ，那么 TableAdapter 的向导不会自动生成相关的 INSERT 、UPDATE 和 DELETE 语句，而在使用相关子查询时，该向导可以自动生成这些语句。

为了研究 JOIN 的缺点，我们在 ~/App_Code/DAL 文件夹中创建一个临时的强类型 Dataset 。在 TableAdapter Configuration Wizard 中，选择使用 ad-hoc 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 将基于主查询自动创建 INSERT、UPDATE 和 DELETE 语句。如果单击 Advanced 按钮，你可以看到此功能是激活的。但尽管进行了此设置，由于主查询中包含JOIN，TableAdapter 无法创建 INSERT、UPDATE 和DELETE 语句。

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

单击 Finish 完成向导。此时，Dataset 的设计器将包含一个 TableAdapter，其 DataTable 中包含 SELECT 查询的 column 列表返回的每个字段的列。其中包括 CategoryName 和SupplierName，如图 3 所示。

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

尽管 DataTable 中有相应的列，但 TableAdapter 的 InsertCommand、UpdateCommand 和 DeleteCommand 属性的值为空。要验证这一点，我们在设计器中单击 TableAdapter，然后转至 Properties 窗口。在这里，你将看到 InsertCommand、UpdateCommand 和DeleteCommand 属性的值设置为 “(None) ”。

图 4：InsertCommand、UpdateCommand 和 DeleteCommand 属性的值设置为 “(None) ”

要克服此缺点，我们可以通过 Properties 窗口手动地为 InsertCommand、UpdateCommand 和 DeleteCommand 属性提供SQL 语句和参数。或者，我们也可以在开始时配置 TableAdapter 的主查询，使其不包含 JOIN。这将使 TableAdapter 为我们自动生成 INSERT、UPDATE 和DELETE 语句。在完成此向导后，我们可以从 Properties 窗口手动更新 TableAdapter 的 SelectCommand，使主查询中包含JOIN 语法。

尽管此方法有效，但它在使用 ad-hoc SQL 查询时非常脆弱，因为 TableAdapter 的主查询随时可通过向导重新配置，并重新创建自动生成的 INSERT、UPDATE 和 DELETE 语句。这意味着，如果我们右键单击 TableAdapter，从上下文菜单中选择 Configure 并重新完成向导，那么我们进行的所有定制都将丢失。

幸运的是，TableAdapter 的自动生成的 INSERT、UPDATE 和 DELETE 语句的脆弱性仅限于 ad-hoc SQL 语句。如果 TableAdapter 使用存储过程，那么你可以定制 SelectCommand、InsertCommand、UpdateCommand 或DeleteCommand 存储过程。在重新运行 TableAdapter Configuration Wizard 时，我们不必担心对存储过程的定制会丢失。

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

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

在本教程中，我们将为 NorthwindWithSprocs Dataset 中的 Employees 表添加一个 TableAdapter 和强类型 DataTable。Employees 表中包含一个ReportsTo 字段，该字段指定员工经理的EmployeeID。例如，员工 Anne Dodsworth 的 ReportTo 值为 5，同时这也是 Steven Buchanan 的 EmployeeID 值。因此，Anne 的经理是 Steven。除了报告每位员工的ReportsTo 值，我们也想获得他们经理的名字。这可以通过一个JOIN 语句实现。但如果在最初创建 TableAdapter 的时候使用 JOIN，向导将不能自动生成相应的插入、更新和删除功能。因此，我们最初创建的 TableAdapter 的主查询中不包含 JOIN。然后，在步骤 2 中，我们将更新主查询存储过程，通过JOIN 获得经理的名字。

首先，打开 ~/App_Code/DAL 文件夹中的 NorthwindWithSprocs DataSet。右键单击设计器，从上下文菜单中选择 Add 选项，然后选择 TableAdapter 菜单项。这将启动 TableAdapter Configuration 向导。如图 5 所示，让向导创建新存储过程，并单击 Next。有关从 TableAdapter 的向导创建新存储过程的最新资料，请参考为强类型 DataSet 的 TableAdapters 创建新的存储过程教程。

图5：选择 “Create new stored procedures ” 选项

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

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

由于此查询不包含 JOIN，TableAdapter 向导将自动创建带有相关 INSERT、UPDATE 和 DELETE 语句的存储过程，以及执行主查询的存储过程。

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

图6：为 TableAdapter 的存储过程命名

最后一步是提醒我们为 TableAdapter 的方法命名。使用 Fill 和 GetEmployees 作为方法名称。同时，确保选中 “Create methods to send updates directly to the database (GenerateDBDirectMethods) ” 复选框。

图7：将 TableAdapter 的方法命名为 Fill 和 GetEmployees

完成此向导后，花些时间检查数据库中的存储过程。你应该会看到四个新的存储过程：Employees_Select、Employees_Insert、Employees_Update 和Employees_Delete。然后，检查刚刚创建的EmployeesDataTable 和EmployeesTableAdapter。DataTable 中包含由主查询返回的每个字段的列。单击 TableAdapter，然后转至 Properties 窗口。在这里，你将看到 InsertCommand、UpdateCommand 和DeleteCommand 属性的配置正确，能够调用相应的存储过程。

图8：TableAdapter 包含插入、更新和删除功能

在自动创建插入、更新和删除存储过程并正确配置了 InsertCommand、UpdateCommand 和 DeleteCommand 属性之后，我们可以对 SelectCommand 的存储过程进行定制，以返回每位员工经理的其它信息。具体来说，我们需要更新Employees_Select 存储过程，使用JOIN 并返回经理的FirstName 和LastName 值。更新完存储过程后，我们需要更新 DataTable，使它包含这些额外的列。我们将在步骤 2 和 3 中实施这两项任务。

步骤2：定制存储过程来包含JOIN

首先转至 Server Explorer ，展开 Northwind 数据库的 Store Procedures 文件夹，然后打开 Employees_Select 存储过程。如果你没有看到此存储过程，则右键单击Stored Procedures 文件夹，然后选择 Refresh 。更新存储过程，使其使用 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 语句后，转至 File 菜单并选择 “Save Employees_Select ” 来保存更改。你也可以单击工具栏上的 Save 图标或按下 Ctrl+S。保存完更改后，右键单击 Server Explorer 中的 Employees_Select 存储过程，选择 Execute。这将执行存储过程并在 Output 窗口中显示执行结果（参见图 9）。

图9：存储过程结果显示在 Output 窗口中

步骤 3：更新 DataTable 的列

此时，Employees_Select 存储过程返回了 ManagerFirstName 和 ManagerLastName 的值，但 EmployeesDataTable 中不包含这两列。你可以通过以下两种方法之一将这两个列添加到 DataTable：

手动添加 - 右键单击 DataSet 设计器中的 DataTable，从 Add 菜单中选择 Column。然后，为新添加的列命名，并设置该列的属性。
自动添加 - TableAdapter Configuration Wizard 将更新 DataTable 的列，从而映射 SelectCommand 存储过程返回的字段。在使用 ad-hoc SQL 语句时，由于现在 SelectCommand 中包含 JOIN，向导将删除 InsertCommand 、 UpdateCommand 和 DeleteCommand 属性。但在使用存储过程时，这些 Command 属性依然存在。

我们已经在前面的教程（包括使用有详细 DataList 主要记录项目符号列表的主/ 明细报表和上载文件）中考察了手动添加 DataTable 列的情况。在下一篇教程中，我们将更仔细地探讨此过程。但在本教程中，我们将通过 TableAdapter Configuration Wizard 来自动添加。

首先，右键单击 EmployeesTableAdapter，从上下文菜单中选择 Configure。这将开启 TableAdapter Configuration Wizard。该向导列出了用于查询、插入、更新和删除的存储过程，以及这些存储过程的返回值和参数（如果有）。图 10 显示了该向导。在图中，我们可以看到，Employees_Select 存储过程现在返回了ManagerFirstName 和ManagerLastName 字段。

图10：向导显示了 Employees_Select 存储过程的更新的 Column 列表

单击 Finish 完成向导。返回到 DataSet 设计器。现在，EmployeesDataTable 中包含两个新列：ManagerFirstName 和 ManagerLastName。

图11：EmployeesDataTable 中包含两个新列

要阐明更新后的 Employees_Select 存储过程仍然有效，并且该 TableAdapter 仍具有插入、更新和删除功能，我们来创建一个网页，允许用户浏览和删除员工。但在创建网页之前，我们首先要在业务逻辑层创建一个新类，以便处理NorthwindWithSprocsDataSet 中的员工数据。在步骤 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 属性返回 NorthwindWithSprocs DataSet 的 EmployeesTableAdapter 的一个实例。该实例用在新类的 GetEmployees 和 DeleteEmployee 方法中。GetEmployees 方法调用EmployeesTableAdapter 的对应的GetEmployees 方法，而该方法又调用Employees_Select 存储过程并将结果填充到EmployeeDataTable 中。类似地，DeleteEmployee 方法调用EmployeesTableAdapter 的Delete 方法，而该方法又调用Employees_Delete 存储过程。

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

添加完 EmployeesBLLWithSprocs 类之后，我们可以通过 ASP.NET 页面处理员工数据了。打开 AdvancedDAL 文件夹中的 JOINs.aspx 页面，将一个 GridView 控件从工具栏拖放到设计器，并将其 ID 属性设为 Employees。然后，从 GridView 的智能标签中，将该控件绑定到名为 EmployeesDataSource 的新 ObjectDataSource 控件。

配置该 ObjectDataSource 使用 EmployeesBLLWithSprocs 类，并确保 SELECT 和 DELETE 标签的下拉列表中选中了 GetEmployees 和 DeleteEmployee 方法。单击 Finish 完成 ObjectDataSource 的配置。

图12：配置 ObjectDataSource 使用 EmployeesBLLWithSprocs 类

图13：配置 ObjectDataSource 使用 GetEmployees 和 DeleteEmployee 方法

对于 EmployeesDataTable 的每一列，Visual Studio 都会向 GridView 添加一个 BoundField。删除 Title、LastName、FirstName、ManagerFirstName 和ManagerLastName 之外的所有 BoundField，将后四个 BoundField 的 HeaderText 属性分别重命名为 “Last Name ”、 “First Name ”、 “Manager ’s First Name ” 和 “Manager ’s Last Name ”。

要允许用户从此页面删除员工，我们需要做两件事。首先，在 GridView 的智能标签上选中 “Enable Deleting ” 选项，允许 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：Employees_Select 存储过程中的 JOIN 返回经理的名字

单击 Delete 按钮将启动删除工作流程，该流程直到执行 Employees_Delete 存储过程才结束。但是，由于外键约束违例，存储过程中的DELETE 语句失败（参见图 15）。具体地说，由于每位员工在 Orders 表中有一条或多条记录，因此删除操作失败。

图15：删除拥有相关订单的员工数据导致外键约束违例

要删除一个员工，你应该：

将外键约束更新为级联删除，
对于用户希望删除的员工，我们可以手动删除 Orders 表中与这些员工相关的记录，或者
更新 Employees_Delete 存储过程，使该存储过程在删除员工记录之前，首先删除 Orders 表中与这些员工相关的记录。我们已经在对强类型 DataSet 的 TableAdapter 使用现有存储过程教程中讨论了此技术。

我将此作为练习留给读者完成。

小结

在处理关系数据库时，查询经常需要从多个相关的表中获取数据。相关子查询和JOIN 提供的两种不同的方法都允许我们在查询中访问相关表的数据。在前面的教程中，我们常用相关子查询，因为当查询中包含 JOIN 时，TableAdapter 无法自动生成 INSERT、UPDATE 和DELETE 语句。尽管我们可以手动设置这些值，但如果使用了 ad-hoc SQL 语句，当 TableAdapter Configuration Wizard 完成时，所有定制都会丢失。

幸运的是，使用存储过程创建的 TableAdapter 不像使用 ad-hoc SQL 语句创建的 TableAdapter 那样脆弱。因此，在使用存储过程构建 TableAdapter 时，我们可以在主查询中使用 JOIN。在本教程中，我们讨论了如何创建这种 TableAdapter。首先，我们使用一个不含 JOIN 的SELECT 查询作为 TableAdapter 的主查询，以便自动创建相应的插入、更新和删除存储过程。TableAdapter 的初始配置完成后，我们在 SelectCommand 存储过程中添加JOIN 语句，然后重新运行 TableAdapter Configuration Wizard 来更新 EmployeesDataTable 的列。

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

快乐编程！

posted @ 2016-05-02 00:35 迅捷之风阅读(456) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

迅捷之风