程式師世界 >> 編程語言 >> .NET網頁編程 >> ASP.NET >> ASP.NET基礎 >> 在ASP.NET 2.0中操作數據之六十七：在TableAdapters中使用JOINs

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

編輯：ASP.NET基礎

導言：

　　在關系數據庫裡，我們處理的數據通常跨越了幾個數據表。舉例：當展示產品信息時我們很可能想列出每個產品相應的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有所幫助。