在ASP.NET 2.0中操作数据之六十七：在TableAdapters中使用JOINs

导言：

　　在关系数据库里，我们处理的数据通常跨越了几个数据表。举例：当展示产品信息时我们很可能想列出每个产品相应的category以及供应商的名称等.诚然，products表里包含有categoryid 和supplierid值，但是事实上的category以及supplier names分别定义在categories表和suppliers表里. 要从其它的相关表里获取信息，我们可以使用correlated subqueries或joins.一条correlated subquerie就是一个镶套的select，引用外部查询(outer query)的列.比如在第一章《》里我们在productstableadapter的主查询里使用2条correlated subqueries来返回每个产品的category 以及supplier names.而join是一sql构造，将2个不同的表的相关联的rows进行合并.在第46章《使用sqldatasource控件检索数据》里，我们使用join来显示每个产品的category信息.

　　我们避免在tableadapters里使用join是由于tableadapter向导自动生成的insert, update,以及delete statements有其局限性.具体来说，如果tableadapter的主查询里包含了任何的join,那么tableadapter就不能为它的insertcommand, updatecommand,以及deletecommand属性自动地创建 ad-hoc sql statements或存储过程.在开始之前，我们先简要地对correlated subqueries和join进行比较.

比较correlated subqueries和joins

　　我们知道在第一章的northwind dataset数据集里创建的productstableadapter使用correlated subqueries来返回每个产品对应的category 和 supplier name。该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

　　我们注意这2个correlated subqueries——“(select categoryname from categories where categories.categoryid = products.categoryid)” 以及“(select companyname from suppliers where suppliers.supplierid = products.supplierid)，都是一个select查询，返回一个单一值，并作为外部select statement的额外的列.

　　此外，我们可以使用join来返回每个产品的supplier 以及category name，下面的查询与上面的代码效果一样，不过用的是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将每一条product记录与category记录合并起来，标准是category记录的categoryid值与product记录categoryid值相吻合.在合并的结果里，我们可以对每个产品相应的category fields进行处理(比如categoryname).

　　注意：join通常用来从相关的数据库查询数据.如果你对join语法比较陌生或者对其用法复习提高，我推荐你阅读w3 schools论坛上的文章《sql join tutorial》（http://www.w3schools.com/sql/sql_join.asp）；此外你还可以阅读sql books online的《join fundamentals》和《subquery fundamentals》部分.

　　当使用类型化的数据集(typed datasets)来构建数据访问层时，使用correlated subqueries要好一些。具体来说，如果主查询里包含任何的join时，tableadapter的设置向导就不会自动生成相应的insert, update, 以及delete statements.相反，使用correlated subqueries的话就可以.

　　为验证这一点，我们在~/app_code/dal文件夹里创建一个临时的类型化的数据集.在tableadapter设置向导里选择使用ad-hoc sql statements，并键入如下的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 statements.如果你点击“advanced”按钮的话，你将看到该功能是激活的.不理会这些设置的话，tableadapter将不能创建insert, update,以及delete statements因为主查询包含了join.

图2：键入一个包含join的主查询

　　点finish完成向导。此时在dataset设计器里将只包含一个tableadapter，其包含的datatable列出了select查询返回的列.包括categoryname 和 suppliername,如图3所示.

图3：datatable包含了返回的列

　　此外，tableadapter的insertcommand, updatecommand, 和deletecommand属性为空。你可以在设计器里选中tableadapter，查看属性窗口.你将看到insertcommand, updatecommand, 和deletecommand属性设置为“(none)”.

图4： insertcommand, updatecommand,deletecommand属性为“(none)”

　　为了验证该缺点，我们可以通过属性窗口为insertcommand, updatecommand,以及 deletecommand属性手动写入sql statements以及参数.最开始我们可以设置tableadapter的主查询不包含任何join，这将允许自动生成insert, update,以及delete statements.完成向导设置后，我们可以通过属性窗口手动修改tableadapter的selectcommand以包含join语法.

　　虽然这种方法工作正常，但很脆弱.因为我们可以在任何时候通过向导设置重新设置主查询，重新自动生成insert, update,以及delete statements.这意味着我们刚刚进行的用户定制可以很容易地就被丢失了.

　　好在tableadapter自动生成的insert, update,以及delete statements的脆弱性仅仅针对ad-hoc sql statements而言.如果你的tableadapter使用的是存储过程的话，你可以自定义selectcommand, insertcommand, updatecommand,或deletecommand存储过程.重新运行tableadapter设置向导时不用担心存储过程会被修改.

　　在接下来的几个步骤里我们将创建一个tableadapter，最初我们使用一个不含join的主查询，以便自动生成相应的insert, update,和delete存储过程.接着，我们将更新该selectcommand以使用join来从相关表返回额外的列. 最后，我们将创建一个对应的business logic layer class类，在asp.net页面上使用该tableadapter.

第1步：使用简单的主查询创建一个tableadapter

　　在本文，我们将为northwindwithsprocs dataset数据集的employees表添加一个tableadapter以及一个强类型的datatable.该employees表包含一个reportsto列，它指定了该雇员的经理的employeeid值.比如：雇员anne dodsworth的reportto值为5，也就是steven buchanan的employeeid值.因此，雇员anne dodsworth的经理就是steven buchanan.除了返回每个雇员的reportsto值外，我们也想返回他们经理的名字.为此，我们可以使用join.但是我们知道，在最初创建tableadapter时使用join的话向导将不能够自动生成相应的insert, update,delete属性. 因此，我们在最初创建 tableadapter的时候不在其主查询里包含任何的join.在第2步里，我们将对主查询存储过程进行更新，通过使用join来获取经理的名字.

　　我们打开~/app_code/dal文件夹里的northwindwithsprocs dataset数据集.在设计器里单击右键，选择“add”项，再选" tableadapter",这将打开tableadapter设置向导.如图5所示，让向导创建一个新的存储过程，再点next.具体的相关细节请参阅第65章《在tableadapters中创建新的存储过程》

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

该tableadapter的主查询的select statement如下：

select employeeid, lastname, firstname, title, hiredate, reportsto, country
from employees

　　由于该查询没有包含任何的join,因此tableadapter向导将用相应的insert, update, delete statements来创建存储过程.

　　接下来向导要我们为存储过程命名。用employees_select, employees_insert, employees_update, and employees_delete来命名，如图6所示。

图6：对tableadapter的存储过程命名

　　最后向导要我们为tableadapter的方法命名，我们命名为fill 和 getemployees.同时选中“create methods to send updates directly to the database (generatedbdirectmethods)”选项.

图7：将tableadapter的方法命名为fill和getemployees

　　完成设置后，花点时间检查数据库里的存储过程，你可以看到4个新的存储过程：employees_select, employees_insert, employees_update,employees_delete.接下来，考察我们刚刚创建创建的employeesdatatable 和 employeestableadapter.该datatable包含了主查询返回的每列。选中tableadapter并进入属性窗口，你将看到insertcommand, updatecommand,deletecommand属性调用相应的存储过程.

图8：tableadapter包含insert, update,delete属性

　　当自动的生成insert, update,delete存储过程，并恰当的设置好insertcommand, updatecommand,deletecommand属性后我们就可以对selectcommand的存储过程进行用户定制，以返回雇员的经理这些信息.具体来说，我们需要更新employees_select存储过程，使用join返回经理的firstname 和 lastname值。完成后，我们要更新datatable以使其包含这些额外的列.我们将在第2和3步实现.

第2步：用join定制存储过程

　　在服务器资源管理器里，展开northwind数据库的存储过程文件夹，打开存储过程employees_select。如果你没有找到该存储过程，右击存储过程文件夹选“刷新”.更新该存储过程，以使其用一个left join来返回经理的first 和last name：

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 statement的更新后，在“文件”菜单里选“save employees_select”来保存所做的修改. 当然，你也可以点击工具栏的保存图标或按下ctrl+s键.保存后，在服务器资源管理器里右击存储过程employees_select，选“执行”。这将执行存储过程并在输出窗口里显示结果，如图9所示。

图9：存储过程的结果显示在输出窗口里

第3步：更新datatable的列

　　此时，employees_select存储过程返回managerfirstname 和managerlastname值。但在employeesdatatable里并不包含这2列.可以通过下面的方法来进行添加：

.手动——在设计器里右键单击datatable，在“add”菜单里选“column”.然后对列命名并设置其属性.

.自动——tableadapter设置向导会更新datatable的列以映射selectcommand存储过程返回的列(field).如果使用的是ad-hoc sql statements的话，向导会移除insertcommand, updatecommand,以及 deletecommand属性，因为selectcommand现在包含了一个join. 但若使用存储过程的话，这些command属性将依然存在.

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

　　右键单击employeestableadapter，并选择“配置”。这将开启tableadapter设置向导，它列出了用于select, insert, updat,delet的存储过程，同时还有其返回的值和参数(如果有的话).如图10所示，我们可以看到employees_select存储过程现在返回了managerfirstname 和 managerlastname列

图10：向导显示了employees_select存储过程更新后的列

　　点finish完成设置，回到dataset设计器里，该employeesdatatable现在包含了2个新添的列managerfirstname 和 managerlastname.

图11：该employeesdatatable现在包含了2个新列

　　为了验证更新后的employees_select存储过程是否起作用，以及该tableadapter的insert, update,delete功能，我们要创建一个web页面来允许用户查看并删除employees.不过在此之前，我们要先在业务逻辑层里创建一个新类来处理northwindwithsprocs dataset数据集里的employees.在第4步，我们将创建一个employeesbllwithsprocs class类，在第5步，我们将在一个asp.net页面里使用该类.

第4步：更新business logic layer

　　在~/app_code/bll文件夹里创建一个名为employeesbllwithsprocs.cs的类文件.该类文件与现有的employeesbll class类文件差不多，只是方法要少一些，且使用的是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 class类的adapter属性返回northwindwithsprocs dataset数据集的employeestableadapter的一个实例，类里面的getemployees 和deleteemployee方法将要用到该属性.其中，getemployees方法调用employeestableadapter对应的getemploye方法，其又再调用employees_select存储过程并将结果传递给一个employeedatatable；而deleteemployee方法仅仅调用employeestableadapter的delete方法，该delete方法调用employees_delete存储过程.

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

　　添加完employeesbllwithsprocs class类后，我们将在一个asp.net页面里处理 employee数据。打开advanceddal文件夹里的joins.aspx页面，从工具箱里拖一个gridview控件到页面，设其id值为employees.接下来，从其智能标签里绑定到一个名为employeesdatasource的新的objectdatasource控件.设置该objectdatasource控件使用employeesbllwithsprocs class类，在select 和 delete标签里分别选择getemployees 和 deleteemployee方法. 点finish完成设置.

图12：设置该objectdatasource使用employeesbllwithsprocs class类

图13：设置该objectdatasource调用getemployees 和 deleteemployee方法

　　visual studio会为employeesdatatable里的每列添加一个boundfield。将title, lastname, firstname, managerfirstname,和managerlastname之外的列全部删除。并分别将这几个列的headertext属性重命名为“last name”, “first name”, “manager's first name”, “manager's last name”  .

　　为了让用户可以在页面删除employees，我们要做2件事情.首先启用gridview的删除功能，然后将objectdatasource控件的oldvaluesparameterformatstring属性设置为默认值{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所示，该页面列出了每一个employee以及他们的经理的名字.

图14：employees_select存储过程使用join返回经理的名字

　　点击delete按钮将触发deleting流程，直到执行employees_delete存储过程才结束，但是存储过程里的delete statement执行失败，原因是有外键约束(如图15所示)。因为每个employee在orders表里都有一条到多条记录，才导致删除操作失败.

图15：删除操作违背外键约束

如果要删除操作执行成功，你要：

.更新外键约束
.对你要删除的employee(s)，在orders表里删除对应的记录
.更新employees_delete存储过程，使其在删除employees记录之前，先删除orders表里对应的记录.我们在第66章《在tableadapters中使用现有的存储过程》里探讨过这个问题.

我将此作为练习留给读者

总结：

　　当处理关系型数据库时，我们通常要从多个不同的但又相关的表获取数据。correlated subqueries 和 join提供了两种从关系表访问数据的方法.在以前的文章里使用的是correlated subqueries，因为如果使用join的话tableadapter将不能自动生成insert, update,delete statements，不过我们可以通过手工添加.如果使用ad-hoc sql statements的话，任何用户定制都可能被tableadapter设置向导所做的改动所覆盖.

　　幸运的是，用存储过程构建的tableadapters不像用ad-hoc sql statements构建的tableadapters那样易受影响.因此，当用存储过程构建tableadapter时，在主查询里使用join是可行的.在本文，我们考察了如何创建这种tableadapter.最开始我们在tableadapter的主查询里使用不带join的select查询，以便自动生成相应的insert, update,delete存储过程. 然后我们对selectcommand存储过程进行扩充以使用一个join，并重新运行tableadapter设置向导来更新employeesdatatable的列.

　　重新运行tableadapter设置向导将自动更新employeesdatatable的列以映射employees_select存储过程返回的列.当然我们也可以向datatable手动添加这些列，这是我们下一章要考察的内容.

　　祝编程快乐！

作者简介

　　本系列教程作者 scott mitchell，著有六本asp/asp.net方面的书，是4guysfromrolla.com的创始人，自1998年以来一直应用 微软web技术。大家可以点击查看全部教程《[翻译]scott mitchell 的asp.net 2.0数据教程》，希望对大家的学习asp.net有所帮助。