在ASP.NET 2.0中操纵数据67:在TableAdapters中使用联接

导言：

在关系数据库中，我们处理的数据通常跨越几个数据表。示例：在显示产品信息时，我们可能希望列出每个产品对应的类别和供应商的名称。“产品”表确实包含类别标识和供应商标识值。但实际上，类别和供应商名称分别在类别表和供应商表中定义。为了从其他相关的表中获取信息，我们可以使用相关的子查询或JOINs。相关子查询是嵌套的选择。引用外部查询的列。例如，在第1章《创建一个数据访问层》中，我们在ProductsTableAdapter的主查询中使用了两个相关的子查询来返回每个产品的类别和供应商名称。JOIN是一个SQL构造。合并与两个不同表关联的行。在第46章《使用SqlDataSource控件检索数据》中，我们使用JOIN来显示每个产品的类别信息。

我们避免在TableAdapter中使用JOIN的原因是TableAdapter向导自动生成的INSERT、UPDATE和DELETE语句存在局限性。具体来说，如果TableAdapter的主查询包含任何JOIN，那么TableAdapter无法为其InsertCommand、UpdateCommand和DeleteCommand属性自动创建即席SQL语句或存储过程。在开始之前，我们将简要比较相关子查询和JOIN。

将相关子查询与联接进行比较。

我们知道，在第1章的Northwind数据集数据集中创建的ProductsTableAdapter使用相关的子查询来返回每个产品对应的类别和供应商名称。ProductsTableAdapter的主要查询如下：

选择产品标识、产品名称、供应商标识、类别标识、数量类型运行信息、单价、单位库存、单位订单、重组级别、停产(从类别中选择类别名称，类别在哪里。类别标识=产品。类别名称)作为类别名称，(从供应商处的供应商中选择公司名称。供应商ID=产品。SupplierID)作为SupplierNameFROM Products，我们关注这两个相关的子查询——”(从类别所在的类别中选择类别名称。categoryid=产品。categoryid)。”和“(从供应商处选择公司名称。supplierid=产品。所有这些都是一个选择查询，返回一个值，并作为外部select语句的一个额外的列。

此外，我们可以使用JOIN返回每个产品的供应商和类别名称。下面的查询与上面的代码具有相同的效果，但是它使用了JOIN:

选择产品标识、产品名称、产品。供应商ID，产品。类别编号、数量类型运行时间、单位价格、单位库存、单位订单、重组级别、停产、类别。类别名称，供应商。公司名称作为供应商名称来自产品左连接类别在类别上。类别标识=产品。类别左连接供应商在供应商S. supplierid=产品上。supplierid连接根据某种标准将一个表的记录与另一个表的记录组合在一起。例如，在上面的代码中，“在类别上左连接类别。categoryid=产品。categoryid”指示SQL Server将每个产品记录与类别记录合并。标准类别记录的CategoryID值与产品记录的CategoryID值一致。在合并后的结果中，我们可以处理每个产品对应的类别字段(比如CategoryName)。

注意：JOIN通常用于查询相关数据库中的数据。如果你不熟悉JOIN语法或复习它的用法，我推荐你阅读W3学校论坛上的文章《SQL Join tutorial》(http://www.w3schools.com/sql/sql_join.asp)。此外，您还可以阅读SQL Books Online的《JOIN Fundamentals》和《Subquery Fundamentals》部分。

使用类型化数据集构建数据访问层时，最好使用相关的子查询。具体来说，如果主查询包含任何JOIN，TableAdapter的设置向导将不会自动生成相应的INSERT、UPDATE和DELETE语句。相反，使用相关子查询是可以的。

为了验证这一点，我们在文件夹~/App_Code/DAL中创建了一个临时的类型化数据集。在TableAdapter设置向导中，选择使用特定的SQL语句，并键入以下选择查询(如图1所示):

选择产品标识、产品名称、产品。供应商ID，产品。类别编号、数量类型运行时间、单位价格、单位库存、单位订单、重组级别、停产、类别。类别名称，供应商。公司名称作为供应商名称来自产品左连接类别在类别上。类别标识=产品。类别左连接供应商上的供应商。供应商ID=产品。供应商id

图1:键入一个包含JOIN的主查询。

默认情况下，TableAdapter根据主查询自动创建INSERT、UPDATE和DELETE语句。如果你点击“高级”按钮，你会看到这个功能被激活。如果忽略这些设置，TableAdapter将无法创建INSERT、UPDATE和DELETE语句，因为主查询包含JOIN。

图2:键入一个包含JOIN的主查询。

单击“完成”完成向导。此时，数据集设计器中只包含一个TableAdapter，它的DataTable列出了SELECT查询返回的列，包括CategoryName和SupplierName，如图3所示。

图3:数据表包含返回的列。

此外，TableAdapter的InsertCommand、UpdateCommand和DeleteCommand属性为空。您可以在设计器中选择TableAdapter并查看属性窗口。您将看到InsertCommand、UpdateCommand和DeleteCommand属性被设置为“(无)”。

图insertcommand、updatecommand和deletecommand的属性是(无)。

为了验证这个缺点，我们可以通过属性窗口为InsertCommand、UpdateCommand和DeleteCommand属性手动编写SQL语句和参数。首先，我们可以将TableAdapter的主查询设置为不包含任何JOIN，这将允许自动生成INSERT、UPDATE和DELETE语句。完成向导设置后，我们可以通过属性窗口手动修改TableAdapter的SelectCommand，以包含JOIN语法。

虽然这种方法工作正常，但是非常脆弱，因为我们可以随时通过向导设置重置主查询，并再次自动生成INSERT、UPDATE和DELETE语句。这意味着我们刚刚做的用户定制很容易丢失。

幸运的是，TableAdapter自动生成的INSERT、UPDATE和DELETE语句的漏洞仅适用于特定的SQL语句。如果TableAdapter使用存储过程，则可以自定义SelectCommand、InsertCommand、UpdateCommand或DeleteCommand存储过程。不要担心在重新运行TableAdapter安装向导时存储过程会被修改。

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

步骤1:用一个简单的主查询创建一个TableAdapter。

在本文中，我们将向NorthwindWithSprocs数据集的Employees表中添加一个TableAdapter和一个强类型数据表。“员工”表包含“报告给”列。它指定员工经理的员工标识值。例如，员工安妮多兹沃斯的报告值为5，这是史蒂文布坎南的员工标识值。因此，员工安妮多兹沃斯的经理是史蒂文布坎南。除了返回每个员工的报告值之外，我们还希望返回他们经理的姓名。为此，我们可以使用JOIN。但是，我们知道，在最初创建TableAdapter时，向导将无法自动生成相应的插入、更新、删除属性。因此，在最初创建TableAdapter时，我们不会在其主查询中包含任何JOIN。在步骤2中，我们将更新主查询存储过程，并通过使用JOIN获取管理器的名称。

我们在~/App_Code/DAL文件夹中打开NorthwindWithSprocs数据集。在设计器中右键单击，选择“添加”项，然后选择“TableAdapter”，这将打开TableAdapter安装向导。如图5所示，让向导创建一个新的存储过程，然后单击下一步。详见第65章，《在TableAdapters中创建新的存储过程》。

图5:选择项目“创建新的存储过程”。

这个TableAdapter的主查询的SELECT语句如下：

从员工中选择employeeid、lastname、firstname、title、hiredate、reports to、country。因为查询不包含任何JOIN，所以TableAdapter向导将使用相应的INSERT、UPDATE、DELETE语句来创建存储过程。

接下来，向导要求我们命名存储过程。使用employees _ select、employees _ insert、employees _ update和employees _ delete，如图6所示。

图6:命名TableAdapter的存储过程。

最后，向导要求我们命名TableAdapter的方法，我们将它们命名为Fill和GetEmployees。同时，我们选择了“创建直接向数据库发送更新的方法(生成的b直接方法)”选项。

图7:命名TableAdapter的方法Fill和GetEmployees。

设置之后，花一些时间检查数据库中的存储过程。您可以看到四个新的存储过程：employees _ select、employees _ insert、employees _ update、employees _ delete。接下来，我们将检查我们刚刚创建的EmployeesDataTable和EmployeesTableAdapter。数据表包含主查询返回的每一列。选择TableAdapter并进入属性窗口，您将看到insertcommand、updatecommand和deletecommand属性调用相应的存储过程。

图8: TableAdapter包含插入、更新、删除属性。

在自动生成插入、更新、删除存储过程并正确设置insertcommand、updatecommand和deletecommand属性后，我们可以自定义SelectCommand的存储过程，以返回员工经理的信息。具体来说，我们需要更新Employees_Select的存储过程，并使用JOIN返回经理的名字和姓氏值。完成后，我们将更新数据表以包含这些额外的列。我们将在步骤2和3中实现它们。

步骤2:使用JOIN自定义存储过程。

在服务器资源管理器中，展开罗斯文数据库的存储过程文件夹，并打开存储过程Employees_Select。如果找不到存储过程，请右键单击存储过程文件夹，然后选择刷新。更新存储过程，使其返回带有LEFT JOIN的经理的名和姓：

选择员工。员工标识，员工。姓氏，员工。名字，员工。标题，员工。雇佣日期，员工。向员工报告。国家，经理。名字作为经理名字，经理。姓氏作为经理姓氏从员工离开加入员工作为经理对员工。报告给=经理。员工在更新SELECT语句后，选择“文件”菜单中的“保存员工_选择”来保存更改。当然，您也可以单击工具栏中的保存图标或按ctrl+s，保存后，在服务器资源管理器中右键单击存储过程Employees_Select，然后选择“执行”。这将执行存储过程，并在输出窗口中显示结果，如图9所示。

图9:存储过程的结果显示在输出窗口中。

步骤3:更新数据表的列。

此时，Employees_Select存储过程返回ManagerFirstName和ManagerLastName值。但是，这两列不包含在EmployeesDataTable中。它们可以通过以下方法添加：

手动——右键单击设计器中的数据表，并在“添加”菜单中选择“列”。然后命名该列并设置其属性。自动——TableAdapter安装向导将更新数据表的列，以映射SelectCommand存储过程返回的列。如果使用即席SQL语句，向导将删除InsertCommand、UpdateCommand和DeleteCommand属性，因为SelectCommand现在包含一个JOIN，但是如果使用存储过程，这些命令属性将仍然存在。

我们在前面的第35章《使用Repeater和DataList单页面实现主/从报表》 以及第52章《使用FileUpload上传文件》 里考察过手动添加列的情况，我们在以后的文章里也会看到该过程的更多的细节，不过在本文，我们通过使用表适配器设置向导来自动添加。

右键单击员工适应者，并选择"配置"。这将开启表适配器设置向导，它列出了用于选择、插入、更新、删除的存储过程，同时还有其返回的值和参数（如果有的话).如图10所示，我们可以看到员工_选择存储过程现在返回了ManagerFirstName和ManagerLastName列

图10:向导显示了员工_选择存储过程更新后的列

点结束完成设置，回到资料组设计器里，该员工数据表现在包含了2个新添的列ManagerFirstName和经理姓名.

图11:该员工数据表现在包含了2个新列

为了验证更新后的员工_选择存储过程是否起作用，以及该表适配器的插入、更新、删除功能，我们要创建一个网页面来允许用户查看并删除员工。不过在此之前，我们要先在业务逻辑层里创建一个新类来处理NorthwindWithSprocs数据集数据集里的员工。在第四步，我们将创建一个员工bllwith存储过程类类，在第5步，我们将在一个ASP .网页面里使用该类。

第四步：更新业务逻辑层

在~/App_Code/BLL文件夹里创建一个名为EmployeesBLLWithSprocs.cs的类文件。该类文件与现有的员工BLL类类文件差不多，只是方法要少一些，且使用的是NorthwindWithSprocs数据集数据集（而不是北风数据集数据集)。在员工bllwith存储过程类里添加如下的代码：

使用系统；使用系统。数据；使用系统。配置；使用系统网络.使用系统。网络安全；使用系统网络。用户界面使用系统。网络控件；使用系统。网页组件；使用系统网络。UI。HtmlControls使用northwindwithprostableadapters[系统组件模型。DataObject]公共类EmployeesBLLWithSprocs { private EmployeesTableAdapter _ employeesAdapter=null；受保护的EmployeesTableAdapter { get { if(_ employeesAdapter==null)_ employeeseadapter=new EmployeesTableAdapter()；return _ employeesAdapter} }[系统。组件模型组件模型。DataObjectMethodType。选择，真])公共诺斯温得斯公司.EmployeesDataTable GetEmployees(){ 0返回适配器GetEmployees()；}[系统。组件模型组件模型。DataObjectMethodType。删除，真)]公共bool deleteEmployeeid(int employeeID){ int rowsAffected=Adapter .删除（员工标识);//如果恰好删除了一行，则返回没错，否则返回false rowsAffected==1；}}该员工bllwith存储过程类类的适配器属性返回NorthwindWithSprocs数据集数据集的员工适应者的一个实例，类里面的获取员工和删除员工方法将要用到该属性。其中，GetEmployees方法调用员工适应者对应的getemployee方法，其又再调用员工_选择存储过程并将结果传递给一个员工状态表而删除员工方法仅仅调用员工适应者的删除方法，该删除方法调用员工_删除存储过程。

第5步：在表现层处理数据

添加完员工bllwith存储过程类类后，我们将在一个ASP .网页面里处理雇员数据。打开高级数据文件夹里的JOINs.aspx页面，从工具箱里拖一个显示数据表格（一种控件)控件到页面，设其身份值为员工。接下来，从其智能标签里绑定到一个名为员工数据源的新的对象数据源控件。设置该对象数据源控件使用员工bllwith存储过程类类，在挑选和删除标签里分别选择获取员工和删除员工方法。点结束完成设置。

图12:设置该对象数据源使用员工BLLWithSprocs存储过程类类

图13:设置该对象数据源调用获取员工和删除员工方法

Visual Studio将为EmployeesDataTable中的每一列添加一个BoundField。删除除标题、姓氏、名字、经理名字和经理姓名之外的所有列。并将这些列的HeaderText属性分别重命名为“姓氏”、“名字”、“经理的名字”和“经理的姓氏”。

为了让用户删除页面上的员工，我们必须做两件事。首先，启用GridView的删除功能，然后将ObjectDataSource控件的oldvaluesparametertformatstring属性设置为默认值{0}。完成后，GridView和ObjectDataSource控件的声明代码应该如下所示：

asp3360 GridView ID=' Employees ' runat=' server ' AutoGenerateColumns=' False ' DataKeyNames=' EmployeeID ' DataSourceID=' Employees data source ' Columns as 3336 0 command field showdelete button=' True '/asp3360 boundfield data field=' Title ' header text=' Title ' sort expression=' Title '/asp3360 boundfield data field=' Last Name ' header text=' Last Name ' sort expression='该页面列出了每个员工及其经理的姓名。

图14:14:Employees _ Select存储过程使用JOIN返回经理的姓名。

单击Delete按钮将触发删除过程，直到Employees_Delete存储过程被执行，但是由于外键约束，存储过程中Delete语句的执行失败(如图15所示)。因为每个员工在“订单”表中都有一条或多条记录，所以删除操作会失败。

图15:删除操作违反了外键约束。

如果删除操作成功，您应该：

更新外键约束。对于要删除的员工，删除“订单”表中的相应记录。在删除员工记录之前，更新Employees_Delete存储过程以删除Orders表中的相应记录。我们在第66章《在TableAdapters中使用现有的存储过程》中讨论了这个问题。

我把这个留给读者做练习。

总结：

在处理关系数据库时，我们通常需要从许多不同但相关的表中获取数据。相关子查询和JOIN提供了两种从关系表中访问数据的方法。在以前的文章中，使用了相关的子查询，因为如果使用了JOIN，TableAdapter将不会自动生成INSERT、UPDATE、DELETE语句，但是，我们可以手动添加它。如果我们使用即席SQL语句，任何用户自定义都可能被TableAdapter安装向导所做的更改覆盖。

幸运的是，用存储过程构建的tableadapter不像用即席SQL语句构建的tableadapter那样容易受到影响。因此，在用存储过程构建tableadapters时，在主查询中使用JOIN是可行的。在本文中，我们研究了如何创建这个TableAdapter。首先，我们在TableAdapter的主查询中使用不带JOIN的SELECT查询，自动生成相应的插入、更新、删除的存储过程。然后，我们扩展了SelectCommand存储过程以使用JOIN，并重新运行TableAdapter安装向导来更新EmployeesDataTable的列。

再次运行TableAdapter安装向导将自动更新EmployeesDataTable的列，以映射Employees_Select存储过程返回的列。当然，我们也可以手动将这些列添加到DataTable中，这是我们将在下一章中研究的内容。

编程快乐！

作者简介

Scott Mitchell，本系列教程的作者，也是关于ASP/ASP的六本书的作者。NET，是4GuysFromRolla.com的创始人，自1998年以来一直使用微软的网络技术。你可以点击查看所有教程《[翻译]Scott Mitchell 的ASP.NET 2.0数据教程》，希望能帮助你学习ASP.NET。