在ASP.NET 2.0中操作數(shù)據(jù)之六十七：在TableAdapters中使用JOINs

導(dǎo)言：

　　在關(guān)系數(shù)據(jù)庫里，我們處理的數(shù)據(jù)通常跨越了幾個數(shù)據(jù)表。舉例：當(dāng)展示產(chǎn)品信息時我們很可能想列出每個產(chǎn)品相應(yīng)的category以及供應(yīng)商的名稱等.誠然，Products表里包含有CategoryID 和SupplierID值，但是事實上的category以及supplier names分別定義在Categories表和Suppliers表里. 要從其它的相關(guān)表里獲取信息，我們可以使用correlated subqueries或JOINs.一條correlated subquerie就是一個鑲套的SELECT，引用外部查詢(outer query)的列.比如在第一章《創(chuàng)建一個數(shù)據(jù)訪問層》里我們在ProductsTableAdapter的主查詢里使用2條correlated subqueries來返回每個產(chǎn)品的category 以及supplier names.而JOIN是一SQL構(gòu)造，將2個不同的表的相關(guān)聯(lián)的rows進行合并.在第46章《使用SqlDataSource控件檢索數(shù)據(jù)》里，我們使用JOIN來顯示每個產(chǎn)品的category信息.

　　我們避免在TableAdapters里使用JOIN是由于TableAdapter向?qū)ё詣由傻腎NSERT, UPDATE,以及DELETE statements有其局限性.具體來說，如果TableAdapter的主查詢里包含了任何的JOIN,那么TableAdapter就不能為它的InsertCommand, UpdateCommand,以及DeleteCommand屬性自動地創(chuàng)建 ad-hoc SQL statements或存儲過程.在開始之前，我們先簡要地對correlated subqueries和JOIN進行比較.

比較Correlated Subqueries和JOINs

　　我們知道在第一章的Northwind DataSet數(shù)據(jù)集里創(chuàng)建的ProductsTableAdapter使用correlated subqueries來返回每個產(chǎn)品對應(yīng)的category 和 supplier name。該ProductsTableAdapter的主查詢?nèi)缦拢?/p>

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來返回每個產(chǎn)品的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基于某種標(biāo)準(zhǔn)將一個表的記錄與另一個表的記錄合并起來.比如上述代碼中，“LEFT JOIN Categories ON Categories.CategoryID = Products.CategoryID”就指示SQL Server將每一條product記錄與category記錄合并起來，標(biāo)準(zhǔn)是category記錄的CategoryID值與product記錄CategoryID值相吻合.在合并的結(jié)果里，我們可以對每個產(chǎn)品相應(yīng)的category fields進行處理(比如CategoryName).

　　注意：JOIN通常用來從相關(guān)的數(shù)據(jù)庫查詢數(shù)據(jù).如果你對JOIN語法比較陌生或者對其用法復(fù)習(xí)提高，我推薦你閱讀W3 Schools論壇上的文章《SQL Join tutorial》（http://www.w3schools.com/sql/sql_join.asp）；此外你還可以閱讀SQL Books Online的《JOIN Fundamentals》和《Subquery Fundamentals》部分.

　　當(dāng)使用類型化的數(shù)據(jù)集(Typed DataSets)來構(gòu)建數(shù)據(jù)訪問層時，使用correlated subqueries要好一些。具體來說，如果主查詢里包含任何的JOIN時，TableAdapter的設(shè)置向?qū)Ь筒粫詣由上鄳?yīng)的INSERT, UPDATE, 以及DELETE statements.相反，使用correlated subqueries的話就可以.

　　為驗證這一點，我們在~/App_Code/DAL文件夾里創(chuàng)建一個臨時的類型化的數(shù)據(jù)集.在TableAdapter設(shè)置向?qū)Ю镞x擇使用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的主查詢

　　默認(rèn)情況下，TableAdapter在主查詢的基礎(chǔ)上自動地創(chuàng)建INSERT, UPDATE, 以及DELETE statements.如果你點擊“Advanced”按鈕的話，你將看到該功能是激活的.不理會這些設(shè)置的話，TableAdapter將不能創(chuàng)建INSERT, UPDATE,以及DELETE statements因為主查詢包含了JOIN.

圖2：鍵入一個包含JOIN的主查詢

　　點Finish完成向?qū)А４藭r在DataSet設(shè)計器里將只包含一個TableAdapter，其包含的DataTable列出了SELECT查詢返回的列.包括CategoryName 和 SupplierName,如圖3所示.

圖3：DataTable包含了返回的列

　　此外，TableAdapter的InsertCommand, UpdateCommand, 和DeleteCommand屬性為空。你可以在設(shè)計器里選中TableAdapter，查看屬性窗口.你將看到InsertCommand, UpdateCommand, 和DeleteCommand屬性設(shè)置為“(None)”.

圖4： InsertCommand, UpdateCommand,DeleteCommand屬性為“(None)”

　　為了驗證該缺點，我們可以通過屬性窗口為InsertCommand, UpdateCommand,以及 DeleteCommand屬性手動寫入SQL statements以及參數(shù).最開始我們可以設(shè)置TableAdapter的主查詢不包含任何JOIN，這將允許自動生成INSERT, UPDATE,以及DELETE statements.完成向?qū)гO(shè)置后，我們可以通過屬性窗口手動修改TableAdapter的SelectCommand以包含JOIN語法.

　　雖然這種方法工作正常，但很脆弱.因為我們可以在任何時候通過向?qū)гO(shè)置重新設(shè)置主查詢，重新自動生成INSERT, UPDATE,以及DELETE statements.這意味著我們剛剛進行的用戶定制可以很容易地就被丟失了.

　　好在TableAdapter自動生成的INSERT, UPDATE,以及DELETE statements的脆弱性僅僅針對ad-hoc SQL statements而言.如果你的TableAdapter使用的是存儲過程的話，你可以自定義SelectCommand, InsertCommand, UpdateCommand,或DeleteCommand存儲過程.重新運行TableAdapter設(shè)置向?qū)r不用擔(dān)心存儲過程會被修改.

　　在接下來的幾個步驟里我們將創(chuàng)建一個TableAdapter，最初我們使用一個不含JOIN的主查詢，以便自動生成相應(yīng)的insert, update,和delete存儲過程.接著，我們將更新該SelectCommand以使用JOIN來從相關(guān)表返回額外的列. 最后，我們將創(chuàng)建一個對應(yīng)的Business Logic Layer class類，在ASP.NET頁面上使用該TableAdapter.

第1步：使用簡單的主查詢創(chuàng)建一個TableAdapter

　　在本文，我們將為NorthwindWithSprocs DataSet數(shù)據(jù)集的Employees表添加一個TableAdapter以及一個強類型的DataTable.該Employees表包含一個ReportsTo列，它指定了該雇員的經(jīng)理的EmployeeID值.比如：雇員Anne Dodsworth的ReportTo值為5，也就是Steven Buchanan的EmployeeID值.因此，雇員Anne Dodsworth的經(jīng)理就是Steven Buchanan.除了返回每個雇員的ReportsTo值外，我們也想返回他們經(jīng)理的名字.為此，我們可以使用JOIN.但是我們知道，在最初創(chuàng)建TableAdapter時使用JOIN的話向?qū)⒉荒軌蜃詣由上鄳?yīng)的insert, update,delete屬性. 因此，我們在最初創(chuàng)建 TableAdapter的時候不在其主查詢里包含任何的JOIN.在第2步里，我們將對主查詢存儲過程進行更新，通過使用JOIN來獲取經(jīng)理的名字.

　　我們打開~/App_Code/DAL文件夾里的NorthwindWithSprocs DataSet數(shù)據(jù)集.在設(shè)計器里單擊右鍵，選擇“Add”項，再選" TableAdapter",這將打開TableAdapter設(shè)置向?qū)?如圖5所示，讓向?qū)?chuàng)建一個新的存儲過程，再點Next.具體的相關(guān)細(xì)節(jié)請參閱第65章《在TableAdapters中創(chuàng)建新的存儲過程》

圖5：選擇“Create new stored procedures”項

該TableAdapter的主查詢的SELECT statement如下：

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

　　由于該查詢沒有包含任何的JOIN,因此TableAdapter向?qū)⒂孟鄳?yīng)的INSERT, UPDATE, DELETE statements來創(chuàng)建存儲過程.

　　接下來向?qū)б覀優(yōu)榇鎯^程命名。用Employees_Select, Employees_Insert, Employees_Update, and Employees_Delete來命名，如圖6所示。

圖6：對TableAdapter的存儲過程命名

　　最后向?qū)б覀優(yōu)門ableAdapter的方法命名，我們命名為Fill 和 GetEmployees.同時選中“Create methods to send updates directly to the database (GenerateDBDirectMethods)”選項.

圖7：將TableAdapter的方法命名為Fill和GetEmployees

　　完成設(shè)置后，花點時間檢查數(shù)據(jù)庫里的存儲過程，你可以看到4個新的存儲過程：Employees_Select, Employees_Insert, Employees_Update,Employees_Delete.接下來，考察我們剛剛創(chuàng)建創(chuàng)建的EmployeesDataTable 和 EmployeesTableAdapter.該DataTable包含了主查詢返回的每列。選中TableAdapter并進入屬性窗口，你將看到InsertCommand, UpdateCommand,DeleteCommand屬性調(diào)用相應(yīng)的存儲過程.

圖8：TableAdapter包含Insert, Update,Delete屬性

　　當(dāng)自動的生成insert, update,delete存儲過程，并恰當(dāng)?shù)脑O(shè)置好InsertCommand, UpdateCommand,DeleteCommand屬性后我們就可以對SelectCommand的存儲過程進行用戶定制，以返回雇員的經(jīng)理這些信息.具體來說，我們需要更新Employees_Select存儲過程，使用JOIN返回經(jīng)理的FirstName 和 LastName值。完成后，我們要更新DataTable以使其包含這些額外的列.我們將在第2和3步實現(xiàn).

第2步：用JOIN定制存儲過程

　　在服務(wù)器資源管理器里，展開Northwind數(shù)據(jù)庫的存儲過程文件夾，打開存儲過程Employees_Select。如果你沒有找到該存儲過程，右擊存儲過程文件夾選“刷新”.更新該存儲過程，以使其用一個LEFT JOIN來返回經(jīng)理的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”來保存所做的修改. 當(dāng)然，你也可以點擊工具欄的保存圖標(biāo)或按下Ctrl+S鍵.保存后，在服務(wù)器資源管理器里右擊存儲過程Employees_Select，選“執(zhí)行”。這將執(zhí)行存儲過程并在輸出窗口里顯示結(jié)果，如圖9所示。

圖9：存儲過程的結(jié)果顯示在輸出窗口里

第3步：更新DataTable的列

　　此時，Employees_Select存儲過程返回ManagerFirstName 和ManagerLastName值。但在EmployeesDataTable里并不包含這2列.可以通過下面的方法來進行添加：

.手動——在設(shè)計器里右鍵單擊DataTable，在“Add”菜單里選“Column”.然后對列命名并設(shè)置其屬性.

.自動——TableAdapter設(shè)置向?qū)翫ataTable的列以映射SelectCommand存儲過程返回的列(field).如果使用的是ad-hoc SQL statements的話，向?qū)瞥齀nsertCommand, UpdateCommand,以及 DeleteCommand屬性，因為SelectCommand現(xiàn)在包含了一個JOIN. 但若使用存儲過程的話，這些command屬性將依然存在.

　　我們在前面的第35章《使用Repeater和DataList單頁面實現(xiàn)主/從報表》以及第52章《使用FileUpload上傳文件》里考察過手動添加列的情況，我們在以后的文章里也會看到該過程的更多的細(xì)節(jié)，不過在本文，我們通過使用TableAdapter設(shè)置向?qū)碜詣犹砑?

　　右鍵單擊EmployeesTableAdapter，并選擇“配置”。這將開啟TableAdapter設(shè)置向?qū)?，它列出了用于select, insert, updat,delet的存儲過程，同時還有其返回的值和參數(shù)(如果有的話).如圖10所示，我們可以看到Employees_Select存儲過程現(xiàn)在返回了ManagerFirstName 和 ManagerLastName列

圖10：向?qū)э@示了Employees_Select存儲過程更新后的列

　　點Finish完成設(shè)置，回到DataSet設(shè)計器里，該EmployeesDataTable現(xiàn)在包含了2個新添的列ManagerFirstName 和 ManagerLastName.

圖11：該EmployeesDataTable現(xiàn)在包含了2個新列

　　為了驗證更新后的Employees_Select存儲過程是否起作用，以及該TableAdapter的insert, update,delete功能，我們要創(chuàng)建一個web頁面來允許用戶查看并刪除employees.不過在此之前，我們要先在業(yè)務(wù)邏輯層里創(chuàng)建一個新類來處理NorthwindWithSprocs DataSet數(shù)據(jù)集里的employees.在第4步，我們將創(chuàng)建一個EmployeesBLLWithSprocs class類，在第5步，我們將在一個ASP.NET頁面里使用該類.

第4步：更新Business Logic Layer

　　在~/App_Code/BLL文件夾里創(chuàng)建一個名為EmployeesBLLWithSprocs.cs的類文件.該類文件與現(xiàn)有的EmployeesBLL class類文件差不多，只是方法要少一些，且使用的是NorthwindWithSprocs DataSet數(shù)據(jù)集(而不是Northwind DataSet數(shù)據(jù)集)。在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數(shù)據(jù)集的EmployeesTableAdapter的一個實例，類里面的GetEmployees 和DeleteEmployee方法將要用到該屬性.其中，GetEmployees方法調(diào)用EmployeesTableAdapter對應(yīng)的GetEmploye方法，其又再調(diào)用Employees_Select存儲過程并將結(jié)果傳遞給一個EmployeeDataTable；而DeleteEmployee方法僅僅調(diào)用EmployeesTableAdapter的Delete方法，該Delete方法調(diào)用Employees_Delete存儲過程.

第5步：在表現(xiàn)層處理數(shù)據(jù)

　　添加完EmployeesBLLWithSprocs class類后，我們將在一個ASP.NET頁面里處理 employee數(shù)據(jù)。打開AdvancedDAL文件夾里的JOINs.aspx頁面，從工具箱里拖一個GridView控件到頁面，設(shè)其ID值為Employees.接下來，從其智能標(biāo)簽里綁定到一個名為EmployeesDataSource的新的ObjectDataSource控件.設(shè)置該ObjectDataSource控件使用EmployeesBLLWithSprocs class類，在SELECT 和 DELETE標(biāo)簽里分別選擇GetEmployees 和 DeleteEmployee方法. 點Finish完成設(shè)置.

圖12：設(shè)置該ObjectDataSource使用EmployeesBLLWithSprocs Class類

圖13：設(shè)置該ObjectDataSource調(diào)用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屬性設(shè)置為默認(rèn)值{0}。完成這些后，GridView 和 ObjectDataSource控件的聲明代碼看起來應(yīng)該和下面的差不多：

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以及他們的經(jīng)理的名字.

圖14：Employees_Select存儲過程使用JOIN返回經(jīng)理的名字

　　點擊Delete按鈕將觸發(fā)deleting流程，直到執(zhí)行Employees_Delete存儲過程才結(jié)束，但是存儲過程里的DELETE statement執(zhí)行失敗，原因是有外鍵約束(如圖15所示)。因為每個employee在Orders表里都有一條到多條記錄，才導(dǎo)致刪除操作失敗.

圖15：刪除操作違背外鍵約束

如果要刪除操作執(zhí)行成功，你要：

.更新外鍵約束
.對你要刪除的employee(s)，在Orders表里刪除對應(yīng)的記錄
.更新Employees_Delete存儲過程，使其在刪除Employees記錄之前，先刪除Orders表里對應(yīng)的記錄.我們在第66章《在TableAdapters中使用現(xiàn)有的存儲過程》里探討過這個問題.

我將此作為練習(xí)留給讀者

總結(jié)：

　　當(dāng)處理關(guān)系型數(shù)據(jù)庫時，我們通常要從多個不同的但又相關(guān)的表獲取數(shù)據(jù)。Correlated subqueries 和 JOIN提供了兩種從關(guān)系表訪問數(shù)據(jù)的方法.在以前的文章里使用的是correlated subqueries，因為如果使用JOIN的話TableAdapter將不能自動生成INSERT, UPDATE,DELETE statements，不過我們可以通過手工添加.如果使用ad-hoc SQL statements的話，任何用戶定制都可能被TableAdapter設(shè)置向?qū)龅母膭铀采w.

　　幸運的是，用存儲過程構(gòu)建的TableAdapters不像用ad-hoc SQL statements構(gòu)建的TableAdapters那樣易受影響.因此，當(dāng)用存儲過程構(gòu)建TableAdapter時，在主查詢里使用JOIN是可行的.在本文，我們考察了如何創(chuàng)建這種TableAdapter.最開始我們在TableAdapter的主查詢里使用不帶JOIN的SELECT查詢，以便自動生成相應(yīng)的insert, update,delete存儲過程. 然后我們對SelectCommand存儲過程進行擴充以使用一個JOIN，并重新運行TableAdapter設(shè)置向?qū)砀翬mployeesDataTable的列.

　　重新運行TableAdapter設(shè)置向?qū)⒆詣痈翬mployeesDataTable的列以映射Employees_Select存儲過程返回的列.當(dāng)然我們也可以向DataTable手動添加這些列，這是我們下一章要考察的內(nèi)容.

　　祝編程快樂！

作者簡介

　　本系列教程作者 Scott Mitchell，著有六本ASP/ASP.NET方面的書，是4GuysFromRolla.com的創(chuàng)始人，自1998年以來一直應(yīng)用 微軟Web技術(shù)。大家可以點擊查看全部教程《[翻譯]Scott Mitchell 的ASP.NET 2.0數(shù)據(jù)教程》，希望對大家的學(xué)習(xí)ASP.NET有所幫助。