欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-11 19:15:40
导言:   在关系数据库里,我们处理的数据通常跨越了几个数据表。举例:当展示产品信息时我们很可能想列出每个产品相应的category以及供应商的名称等.诚然,produc...

导言:

  在关系数据库里,我们处理的数据通常跨越了几个数据表。举例:当展示产品信息时我们很可能想列出每个产品相应的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

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图1:键入一个包含join的主查询

  默认情况下,tableadapter在主查询的基础上自动地创建insert, update, 以及delete statements.如果你点击“advanced”按钮的话,你将看到该功能是激活的.不理会这些设置的话,tableadapter将不能创建insert, update,以及delete statements因为主查询包含了join.

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图2:键入一个包含join的主查询

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

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图3:datatable包含了返回的列

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

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图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中创建新的存储过程

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图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所示。

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图6:对tableadapter的存储过程命名

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

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图7:将tableadapter的方法命名为fill和getemployees

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

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图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所示。

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图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列

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图10:向导显示了employees_select存储过程更新后的列

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

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图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完成设置.

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图12:设置该objectdatasource使用employeesbllwithsprocs class类

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图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以及他们的经理的名字.

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图14:employees_select存储过程使用join返回经理的名字

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

在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
图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有所帮助。

上一篇:

下一篇: