更新 TableAdapter 以使用 JOIN (VB)

作者：Scott Mitchell

下載 PDF

使用資料庫時，通常會要求分佈在多個表中的資料。 要從兩個不同的表中擷取資料，我們可以使用相關子查詢或 JOIN 操作。 在本教學課程中，我們將比較相關子查詢和 JOIN 語法，然後了解如何建立在其主查詢中包含 JOIN 的 TableAdapter。

簡介

對於關聯式資料庫，我們感興趣的資料通常分佈在多個表中。 例如，當顯示產品資訊時，我們可能希望列出每個產品對應的類別和供應商名稱。 Products 表格有 CategoryID 和 SupplierID 值，但實際類別和供應商名稱分別在 Categories 和 Suppliers 表中。

要從另一個相關表擷取信息，我們可以使用相關子查詢 或 JOINs。 相關子查詢是引用外部查詢中的列的巢狀 SELECT 查詢。 例如，在建立資料存取層教學中，我們在 ProductsTableAdapter 主查詢中使用兩個相關子查詢來傳回每個產品的類別和供應商名稱。 A JOIN 是一個 SQL 構造，用來合併兩個不同表中的相關行。 我們在使用 JOIN SqlDataSource 控制項查詢資料教學中使用了 來顯示每個產品旁邊的類別資訊。

我們放棄在 TableAdapter 中使用 JOINs 的原因是因為 TableAdapter s 精靈在自動產生對應的 INSERT、UPDATE 和 DELETE 陳述式方面有限制。 更具體地說，如果 TableAdapter 的主查詢包含任何 JOIN s，則 TableAdapter 無法為其 InsertCommand、UpdateCommand 和 DeleteCommand 屬性自動建立即席 SQL 陳述式或預存程序。

在本教學課程中，我們將簡要比較和對比相關子查詢和 JOINs，然後探索如何建立在其主查詢中包含 JOINs 的 TableAdapter。

比較和對照相關子查詢和子查詢 JOIN

回想一下，第一個教學課程 Northwind 中 ProductsTableAdapter 建立的資料集使用相關子查詢來傳回每個產品對應的類別和供應商名稱。 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

兩個相關子查詢 (SELECT CategoryName FROM Categories WHERE Categories.CategoryID = Products.CategoryID) - (SELECT CompanyName FROM Suppliers WHERE Suppliers.SupplierID = Products.SupplierID) 和 SELECT - SELECT 是為每個產品傳回單一值作為外部陳述式列清單中的附加列的查詢。

或者，可以使用 JOIN 傳回每個產品的供應商和類別名稱。 以下查詢傳回與上面查詢相同的輸出，但使用 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

A JOIN 根據某些條件將一個表格中的記錄與另一表格中的記錄合併。 例如，在上面的查詢中，LEFT JOIN Categories ON Categories.CategoryID = Products.CategoryID 指示 SQL Server 將每個產品記錄與 CategoryID 值與產品 CategoryID 值相符的類別記錄合併。 合併的結果使我們能夠使用每個產品的相應類別欄位 (例如 CategoryName)。

注意

從關聯式資料庫查詢資料時通常使用 JOIN。 如果您不熟悉 JOIN 語法或需要溫習一下其用法，我建議您閱讀 W3 Schools 的 SQL Join 教學課程。 同樣值得閱讀的還有 SQL 線上叢書的JOIN基礎知識和子查詢基礎知識部分。

由於 JOIN 和相關子查詢都可以用於從其他表中擷取相關資料，因此許多開發人員都摸不著頭腦，不知道該使用哪種方法。 我交談過的所有 SQL 專家都說了大致相同的話，即效能並不重要，因為 SQL Server 將產生大致相同的執行計劃。 那麼，他們的建議是使用您和您的團隊最熟悉的技術。 值得注意的是，在給予這個建議後，這些專家立即表達了他們對 JOIN 的偏好而不是相關子查詢。

使用類別型化資料集建立資料存取層時，這些工具在使用子查詢時效果更好。 特別是，如果主查詢包含任何 INSERT，TableAdapter 精靈將不會自動產生對應的 UPDATE、DELETE 和 JOIN 陳述式，但在使用相關子查詢時會自動產生這些陳述式。

若要探索此缺點，請在 ~/App_Code/DAL 資料夾中建立臨時類別型化資料集。 在 TableAdapter 設定精靈中，選擇使用即席 SQL 陳述式並輸入下列 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 陳述式。 如果您按一下「進階」按鈕，您可以看到此功能已啟用。 儘管進行了此設置，TableAdapter 將無法建立 INSERT、UPDATE 和 DELETE 陳述式，因為主查詢包含 JOIN。

圖 2：輸入包含 JOIN 的主查詢

按一下 [完成] 以完成程序。 此時，您的 DataSet 設計器將包含一個 TableAdapter，其中包含一個 DataTable，其中包含 SELECT 查詢列清單中傳回的每個欄位的列。 這包括 CategoryName 和 SupplierName，如圖 3 所示。

圖 3：資料表包含列清單中傳回的每個欄位的列

雖然 DataTable 具有適當的列，但 TableAdapter 缺少其 InsertCommand 、UpdateCommand 和 DeleteCommand 屬性的值。 若要確認這一點，請按一下設計器中的 TableAdapter，然後前往「屬性」視窗。 在那裡您將看到 InsertCommand、UpdateCommand 和 DeleteCommand 屬性設定為 (None) 。

圖 4：InsertCommand、UpdateCommand 和 DeleteCommand 屬性設定為 (無) (點擊看大圖)

為了解決這個缺點，我們可以透過「屬性」視窗手動為 InsertCommand、UpdateCommand 和 DeleteCommand 屬性提供 SQL 陳述式和參數。 或者，我們可以先將 TableAdapter 的主查詢設定為不包含任何 JOIN。 這將允許為我們自動產生 INSERT、UPDATE 和 DELETE 陳述式。 完成精靈後，我們可以從「屬性」視窗手動更新 TableAdapter SelectCommand 以使其包含 JOIN 語法。

雖然此方法有效，但在使用即席 SQL 查詢時非常脆弱，因為每當透過精靈重新設定 TableAdapter 的主查詢時，都會重新建立自動產生的 INSERT、UPDATE 和 DELETE 陳述式。 這意味著，如果我們右鍵點擊 TableAdapter，從上下文選單中選擇設定，然後再次完成精靈，那麼我們稍後進行的所有自訂都會遺失。

幸運的是，TableAdapter 自動產生的 INSERT、UPDATE、DELETE 和 陳述式的脆弱性僅限於即席 SQL 陳述式。 如果您的 TableAdapter 使用預存程序，您可以自訂 SelectCommand、InsertCommand、UpdateCommand 或 DeleteCommand 預存程序並重新執行 TableAdapter 設定精靈，而不必擔心預存程序會被修改。

在接下來的幾個步驟中，我們將建立一個 TableAdapter，它最初使用省略任何 JOIN 的主查詢，以便自動產生對應的插入、更新和刪除預存程序。 然後我們將更新 SelectCommand 使用從相關表返回附加列的 JOIN。 最後，我們將建立對應的業務邏輯層類別並在 ASP.NET 網頁中示範如何使用 TableAdapter。

步驟 1：使用簡化的主查詢建立 TableAdapter

在本教學課程中，我們將為 NorthwindWithSprocs DataSet 中的 Employees 表新增 TableAdapter 和強類別型 DataTable。 此 Employees 表包含一個指定員工經理的 EmployeeID ReportsTo 欄位。 例如，員工 Anne Dodsworth 的 ReportTo 值為 5，這是 Steven Buchanan 的 EmployeeID 值。 因此，安妮向她的經理史蒂文報告。 除了報告每位員工的 ReportsTo 價值之外，我們可能還想擷取他們經理的姓名。 這可以使用 JOIN。 但在最初建立 TableAdapter 時使用 JOIN 會阻止精靈自動產生對應的插入、更新和刪除功能。 因此，我們先建立一個主查詢不包含任何 JOIN 的 TableAdapter。 然後，在步驟 2 中，我們將更新主查詢預存程序以透過 JOIN。

首先開啟 ~/App_Code/DAL 資料夾中的 NorthwindWithSprocs 資料集。 右鍵點選設計器，從上下文選單中選擇新增選項，然後選擇 TableAdapter 選單項目。 這將啟動 TableAdapter 設定精靈。 如圖 5 所示，讓精靈建立新的預存程序並按一下 Next。 如需透過 TableAdapter 精靈建立新預存程序的複習，請參閱為類別型化資料集 TableAdapter 建立新預存程序教學課程。

圖 5：選擇「建立新預存程序」選項 (點選查看大圖)

對 TableAdapter 的主查詢使用下列 SELECT 陳述式：

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

由於此查詢不包含任何 JOIN，TableAdapter 精靈將自動建立具有對應 INSERT、UPDATE 和 DELETE 陳述式的預存程序，以及用於執行主查詢的預存程序。

以下步驟可讓我們命名 TableAdapter 的預存程序。 使用名稱 Employees_Select、Employees_Insert、Employees_Update 和 Employees_Delete，如圖 6 所示。

圖 6：命名 TableAdapter 的預存程序 (點選看大圖)

最後一步提示我們命名 TableAdapter 的方法。 使用 Fill 和 GetEmployees 作為方法名稱。 另請務必勾選「建立方法以將更新直接傳送至資料庫 (GenerateDBDirectMethods)」複選框。

圖 7：命名 TableAdapter 的方法並 FillGetEmployees (點擊看大圖)

完成精靈後，請花一些時間檢查資料庫中的預存程序。 您應該看到四個新的：Employees_Select、Employees_Insert、Employees_Update 和 Employees_Delete。 接下來，檢查剛剛建立的 EmployeesDataTable 和 EmployeesTableAdapter。 DataTable 包含主查詢傳回的每個欄位的列。 按一下 TableAdapter，然後前往「屬性」視窗。 在那裡您將看到 InsertCommand、UpdateCommand 和 DeleteCommand 屬性已正確設定為呼叫對應的預存程序。

圖 8：TableAdapter 包括插入、更新和刪除功能 (點擊看大圖)

自動建立插入、更新和刪除預存程序並正確設定 InsertCommand、UpdateCommand 和 DeleteCommand 屬性後，我們就可以自訂 SelectCommand 預存程序以傳回有關每個員工的經理的附加資訊。 具體來說，我們需要更新預存程序以使用 Employees_SelectJOIN 並傳回 manager FirstName 和 LastName 值。 更新預存程序後，我們需要更新 DataTable 以使其包含這些附加列。 我們將在步驟 2 和 3 中解決這兩項任務。

步驟 2：自訂預存程序以包含 JOIN

首先前往伺服器資源管理器，深入查看 Northwind 資料庫的「Employees_Select 預存程序」資料夾，然後開啟該預存程序。 如果您沒有看到此預存程序，請右鍵點擊預存程序資料夾並選擇重新整理。 更新儲存過程，以便它使用 LEFT JOIN 傳回經理的名字和姓氏：

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 陳述式後，透過前往「檔案」選單並選擇「儲存」來儲存變更 Employees_Select。 或者，您可以點擊工具列中的「儲存」圖示或按 Ctrl+S。 儲存變更後，在伺服器資源管理器中右鍵點擊 Employees_Select 預存程序並選擇執行。 這將執行預存程序並在「輸出」視窗中顯示其結果 (請參閱圖 9)。

圖 9：預存程序結果顯示在輸出視窗中 (按一下查看全尺寸影像)

步驟 3：更新 DataTable 的列

此時，Employees_Select 預存程序傳回 ManagerFirstName 和 ManagerLastName 值，但 EmployeesDataTable 缺少欄位。 可以透過以下兩種方式之一將這些缺失的欄位新增至資料表：

• 手動 - 右鍵點擊資料集設計器中的資料表，然後從新增功能表中選擇列。 然後，您可以命名該列並相應地設定其屬性。
• 自動 - TableAdapter 設定精靈將更新 DataTable 的欄位以反映 SelectCommand 預存程序傳回的欄位。 使用即席 SQL 陳述式時，精靈也會刪除 InsertCommand、UpdateCommand 和 DeleteCommand 屬性，因為現在 SelectCommand 包含 JOIN。 但是當使用預存程序時，這些命令屬性保持不變。

我們在之前的教學課程中探索過手動加入 DataTable 列，包括使用主記錄的項目符號列表和詳細資料列表以及上傳文件的主/詳細信息，我們將在下一個教學課程中再次更詳細地介紹此過程。 然而，在本教學中，我們將透過 TableAdapter 設定精靈使用自動方法。

首先右鍵點擊 EmployeesTableAdapter 並從上下文選單中選擇設定。 這將開啟 TableAdapter 設定精靈，其中列出了用於選擇、插入、更新和刪除的預存程序及其傳回值和參數 (如有)。 圖 10 顯示了該精靈。 在這裡我們可以看到 Employees_Select 預存程序現在回傳 ManagerFirstName 和 ManagerLastName 欄位。

圖 10：精靈顯示 Employees_Select 預存程序的更新列清單 (按一下查看全尺寸影像)

按一下完成完成精靈。 返回資料集設計器後，EmployeesDataTable 包含兩個附加列：ManagerFirstName 和 ManagerLastName。

圖 11：包含兩個新欄位 (EmployeesDataTable點選查看全尺寸影像)

為了說明更新的 Employees_Select 預存程序有效且 TableAdapter 的插入、更新和刪除功能仍然有效，讓我們建立一個允許使用者查看和刪除員工的網頁。 然而，在建立這樣的頁面之前，我們需要先在業務邏輯層中建立一個新類別，以便與 NorthwindWithSprocs 資料集中的員工一起工作。 在步驟 4 中，我們將建立一個 EmployeesBLLWithSprocs 類別。 在步驟 5 中，我們將從 ASP.NET 頁面使用此類別。

第四步：實作業務邏輯層

在名為 EmployeesBLLWithSprocs.vb 的 ~/App_Code/BLL 資料夾中建立一個新的類別檔案。 EmployeesBLL 類別模仿現有類別的語義，只是這個新類別提供了更少的方法並使用 NorthwindWithSprocs DataSet (而不是 Northwind DataSet)。 將下列程式碼加入 EmployeesBLLWithSprocs 類別。

Imports NorthwindWithSprocsTableAdapters
<System.ComponentModel.DataObject()> _
Public Class EmployeesBLLWithSprocs
    Private _employeesAdapter As EmployeesTableAdapter = Nothing
    Protected ReadOnly Property Adapter() As EmployeesTableAdapter
        Get
            If _employeesAdapter Is Nothing Then
                _employeesAdapter = New EmployeesTableAdapter()
            End If
            Return _employeesAdapter
        End Get
    End Property
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, True)> _
    Public Function GetEmployees() As NorthwindWithSprocs.EmployeesDataTable
        Return Adapter.GetEmployees()
    End Function
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Delete, True)> _
    Public Function DeleteEmployee(ByVal employeeID As Integer) As Boolean
        Dim rowsAffected = Adapter.Delete(employeeID)
        'Return true if precisely one row was deleted, otherwise false
        Return rowsAffected = 1
    End Function
End Class

EmployeesBLLWithSprocs 類別 Adapter 屬性傳回 NorthwindWithSprocsDataSet sEmployeesTableAdapter 的實例。 這是由類別 GetEmployees 和 DeleteEmployee 方法使用的。 GetEmployees 方法呼叫 EmployeesTableAdapter 對應的方法，GetEmployees 方法呼叫 Employees_Select 預存程序並將其結果填入 EmployeeDataTable。 DeleteEmployee 方法類似地呼叫 EmployeesTableAdapterDelete 方法，後者呼叫 Employees_Delete 預存程序。

步驟 5：處理表示層中的資料

EmployeesBLLWithSprocs 課程完成後，我們就可以透過 ASP.NET 頁面處理員工資料。 打開 AdvancedDAL 資料夾中的 JOINs.aspx 頁面並將 GridView 從工具箱拖曳到設計器上，將 ID 屬性設為 Employees。 接下來，從 GridView s 智慧標記將網格綁定到名為 EmployeesDataSource 的新 ObjectDataSource 控制項。

設定 ObjectDataSource 以使用 EmployeesBLLWithSprocs 類別，並從 SELECT 和 DELETE 標籤中確保從下拉清單中選擇 GetEmployees 和 DeleteEmployee 方法。 按一下完成以完成 ObjectDataSource 的設定。

圖 12：設定 ObjectDataSource 以使用 EmployeesBLLWithSprocs 類別 (按一下查看大圖)

圖 13：讓 ObjectDataSource 使用 GetEmployees 和 DeleteEmployee 方法 (按一下檢視全尺寸影像)

Visual Studio 將為每個 EmployeesDataTable 欄位新增一個 BoundField 到 GridView。 刪除 Title、LastName、FirstName、ManagerFirstName 和 ManagerLastName 之外的所有這些 BoundField，並將最後四個 BoundField 的 HeaderText 屬性分別重新命名為 Last Name、First Name、Manager s First Name 和 Manager s Last Name。

為了允許使用者從此頁面刪除員工，我們需要做兩件事。 首先，透過勾選智慧標記中的「啟用刪除」選項來指示 GridView 提供刪除功能。 其次，將 ObjectDataSource 的 OldValuesParameterFormatString 屬性從 ObjectDataSource 精靈設定的值 (original_{0}) 變更為其預設值 ({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 所示，該頁面將列出每位員工及其經理的姓名 (假設他們有)。

圖 14：JOIN 儲存過程中的 Employees_Select 返回經理的姓名 (點擊查看大圖)

點擊「刪除」按鈕將啟動刪除工作流程，最終執行 Employees_Delete 預存程序。 但是，在儲存過程中嘗試的 DELETE 陳述式由於違反外鍵限製而失敗 (參見圖 15)。 具體來說，每個 Orders 員工表中都有一筆或多筆記錄，導致刪除失敗。

圖 15：刪除具有相應訂單的員工會導致外鍵約束違規 (點擊查看大圖)

若要允許刪除員工，您可以：

• 更新外鍵約束以級聯刪除，
• 從 Orders 表中手動刪除要刪除的員工的記錄，或者
• 更新 Employees_Delete 預存程序以先從 Orders 表中刪除相關記錄，然後再刪除 Employees 記錄。 我們在使用類別型化資料集的 TableAdapter 的現有預存程序教學中討論了此技術。

我將其作為練習留給讀者。

摘要

使用關聯式資料庫時，查詢通常會從多個相關表中提取資料。 相關子查詢和 JOIN 相關子查詢提供了兩種不同的技術來存取查詢中相關表中的資料。 在先前的教學中，我們最常使用相關子查詢，因為 TableAdapter 無法為涉及 JOIN 的查詢自動產生 INSERT、UPDATE 和 DELETE 陳述式。 雖然可以手動提供這些值，但在使用臨時 SQL 陳述式時，當 TableAdapter 設定精靈完成時，所有自訂設定都會被覆寫。

幸運的是，使用預存程序建立的 TableAdapter 不會像使用即席 SQL 陳述式建立的那樣遭受相同的脆弱性。 因此，使用預存程序時建立主查詢使用 JOIN 的TableAdapter是可行的。 在本教學中，我們了解如何建立這樣的 TableAdapter。 我們首先對 TableAdapter 的主查詢使用 JOIN-less SELECT 查詢，以便自動建立對應的插入、更新和刪除預存程序。 TableAdapter 的初始設定完成後，我們擴充了 SelectCommand 預存程序以使用 JOIN 並重新執行 TableAdapter 設定精靈來更新 EmployeesDataTable 列。

重新執行 TableAdapter 設定精靈會自動更新 EmployeesDataTable 欄位以反映 Employees_Select 預存程序傳回的資料欄位。 或者，我們可以手動將這些列新增到資料表中。 我們將在下一個教學課程中探索手動向資料表新增列。

快樂程式！

關於作者

Scott Mitchell 是七本 ASP/ASP.NET 書籍的作者和 4GuysFromRolla.com 的創始人，自 1998 年以來一直致力於 Microsoft Web 技術。 史考特是一名獨立顧問、培訓師和作家。 他的最新著作是 Sams Teach Yourself ASP.NET 2.0 in 24 Hours。 您可以撥打 mitchell@4GuysFromRolla.com 聯絡他。或者透過他的部落格， http://ScottOnWriting.NET部落格可以在以下位置找到。

特別感謝

本教學系列得到了許多有用的審閱者的審閱。 本教學的主要審查員是 Hilton Geisenow、David Suru 和 Teresa Murphy。 有興趣查看我即將發表的 MSDN 文章嗎？ 如果是這樣，請留言給我 mitchell@4GuysFromRolla.com。

上一個下一個