使用具類型資料集 Tableadapter 現有的預存程序 (VB)

由斯科特· 米切爾

下載 PDF

在上一個教學課程中，我們已瞭解如何使用 TableAdapter 精靈來產生新的預存程式。 在本教學課程中，我們將瞭解相同的 TableAdapter 精靈如何使用現有的預存程式。 我們也瞭解如何手動將新的預存程式新增至資料庫。

簡介

在上一個教學課程中，我們已瞭解如何將具類型的 DataSet s TableAdapters 設定為使用預存程式來存取數據，而不是臨機操作 SQL 語句。 特別是，我們檢查了如何讓 TableAdapter 精靈自動建立這些預存程式。 將舊版應用程式移植到 ASP.NET 2.0，或在現有數據模型周圍建置 ASP.NET 2.0 網站時，資料庫可能已經包含我們需要的預存程式。 或者，您可能偏好手動或透過 TableAdapter 精靈以外的某些工具來建立預存程式，以自動產生預存程式。

在本教學課程中，我們將探討如何設定 TableAdapter 以使用現有的預存程式。 由於 Northwind 資料庫只有一小組內建預存程式，因此我們也會查看透過 Visual Studio 環境手動將新預存程式新增至資料庫所需的步驟。 讓我們開始吧！

注意

在 交易 內的包裝資料庫修改教學課程中，我們已將方法新增至 TableAdapter，以支援交易（BeginTransaction等等 CommitTransaction）。 或者，交易可以完全在預存程式中管理，而不需要修改數據存取層程序代碼。 在本教學課程中，我們將探索用來在交易範圍內執行預存程式語句的 T-SQL 命令。

步驟 1：將預存程式新增至 Northwind 資料庫

Visual Studio 可讓您輕鬆地將新的預存程式新增至資料庫。 讓我們將新的預存程式新增至 Northwind 資料庫，以傳回數據表中具有特定CategoryID值的所有數據Products行。 從 [伺服器總管] 視窗展開 Northwind 資料庫，以顯示其資料夾 - 資料庫關係圖、數據表、檢視等等。 如上一個教學課程中所見，預存程式資料夾包含資料庫現有的預存程式。 若要新增預存程式，只要以滑鼠右鍵按兩下 [預存程式] 資料夾，然後從操作功能表選擇 [新增預存程式] 選項。

圖 1：以滑鼠右鍵按下預存程式資料夾並新增預存程式（按一下以檢視完整大小的影像）

如圖 1 所示，選取 [新增預存程式] 選項會在 Visual Studio 中開啟腳本視窗，其中包含建立預存程式所需的 SQL 腳本大綱。 這是我們的工作，以充實此腳本並加以執行，此時會將預存程式新增至資料庫。

輸入下列文稿：

CREATE PROCEDURE dbo.Products_SelectByCategoryID 
(
    @CategoryID int
)
AS
SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @CategoryID

此腳本在執行時，會將新的預存程式新增至名為 Products_SelectByCategoryID的 Northwind 資料庫。 這個預存程式會接受單一輸入參數（@CategoryID類型 int為 ），並傳回具有相符 CategoryID 值之這些產品的所有字段。

若要執行此 CREATE PROCEDURE 腳本並將預存程式新增至資料庫，請按下工具列中的 [儲存] 圖示，或按 Ctrl+S。 這麼做之後，預存程式資料夾會重新整理，顯示新建立的預存程式。 此外，視窗中的文稿會從 CREATE PROCEDURE dbo.Products_SelectProductByCategoryID 變更為 ALTER PROCEDURE dbo.Products_SelectProductByCategoryID。 CREATE PROCEDURE 將新的預存程式加入資料庫，同時 ALTER PROCEDURE 更新現有的預存程式。 由於腳本的開頭已變更為 ALTER PROCEDURE，因此變更預存程式輸入參數或 SQL 語句，然後按兩下 [儲存] 圖示將會使用這些變更來更新預存程式。

圖 2 顯示儲存預存程序之後的 Products_SelectByCategoryID Visual Studio。

圖 2：預存程式 Products_SelectByCategoryID 已新增至資料庫（按一下以檢視完整大小的影像）

步驟 2：將 TableAdapter 設定為使用現有的預存程式

Products_SelectByCategoryID既然預存程式已新增至資料庫，我們可以在叫用其中一個方法時，將數據存取層設定為使用此預存程式。 特別是，我們會在 Typed DataSet 中新增 GetProductsByCategoryID(<_i22_>categoryID)<!--_i22_--> 方法ProductsTableAdapter，以呼叫我們剛才建立的Products_SelectByCategoryID預存程式。NorthwindWithSprocs

從開啟 NorthwindWithSprocs DataSet開始。 以滑鼠右鍵按兩下 ， ProductsTableAdapter 然後選擇 [新增查詢] 以啟動 TableAdapter 查詢組態精靈。 在上述教學課程中，我們選擇讓 TableAdapter 為我們建立新的預存程式。 不過，在本教學課程中，我們想要將新的 TableAdapter 方法連線到現有的 Products_SelectByCategoryID 預存程式。 因此，從精靈的第一個步驟中選擇 [使用現有的預存程式] 選項，然後按 [下一步]。

圖 3：選擇 [使用現有的預存程序選項] （按兩下以檢視完整大小的影像）

下列畫面提供填入資料庫預存程式的下拉式清單。 選取預存程式會列出其左側的輸入參數，以及右側傳回的數據欄位（如果有的話）。 Products_SelectByCategoryID從清單中選擇預存程式，然後按 [下一步]。

圖 4：挑選 Products_SelectByCategoryID 預存程式（按兩下以檢視完整大小的影像）

下一個畫面會詢問預存程式所傳回的數據種類，而這裡的答案會決定 TableAdapter s 方法所傳回的類型。 例如，如果我們指出傳回表格式數據，則方法會傳回 ProductsDataTable 填入預存程式所傳回記錄的實例。 相反地，如果我們指出這個預存程式傳回單一值，TableAdapter 會傳回 Object ，該值會在預存程式所傳回之第一筆記錄的第一個數據行中指派值。

Products_SelectByCategoryID由於預存程式會傳回屬於特定類別的所有產品，請選擇第一個答案 - 表格式數據 -，然後按 [下一步]。

圖 5：指出預存程式傳回表格式資料（按兩下以檢視完整大小的影像）

剩下的就是指出要使用哪些方法模式，後面接著這些方法的名稱。 保留 [填滿 DataTable] 和 [傳回 DataTable] 選項已核取，但將 方法重新命名為 FillByCategoryID 和 GetProductsByCategoryID。 然後按兩下一步以檢閱精靈將執行之工作的摘要。 如果一切看起來都正確，請按兩下 [完成]。

圖 6：命名方法FillByCategoryID，然後按兩下GetProductsByCategoryID以檢視完整大小的影像）

注意

我們剛才建立的 TableAdapter 方法， FillByCategoryID 和 GetProductsByCategoryID，預期類型 Integer為 的輸入參數。 這個輸入參數值會透過其 @CategoryID 參數傳遞至預存程式。 如果您修改 Products_SelectByCategory 預存程式的參數，您也必須更新這些 TableAdapter 方法的參數。 如上一個教學課程所述，這可以透過下列兩種方式之一來完成：手動新增或移除參數集合中的參數，或重新執行 TableAdapter 精靈。

步驟 3：將方法新增GetProductsByCategoryID(categoryID)至 BLL

GetProductsByCategoryID完成 DAL 方法之後，下一個步驟是在商業規則層中提供此方法的存取權。 開啟類別 ProductsBLLWithSprocs 檔案，並新增下列方法：

<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsByCategoryID(ByVal categoryID As Integer) _
    As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsByCategoryID(categoryID)
End Function

這個 BLL 方法只會傳回ProductsDataTable從 s GetProductsByCategoryID 方法傳回的 ProductsTableAdapter 。 屬性 DataObjectMethodAttribute 會提供 ObjectDataSource s Configure Data Source 精靈所使用的元數據。 特別是，這個方法會出現在 [SELECT] 索引標籤的下拉式清單中。

步驟 4：依類別顯示產品

若要測試新加入 Products_SelectByCategoryID 的預存程式和對應的 DAL 和 BLL 方法，讓我們建立包含 DropDownList 和 GridView 的 ASP.NET 頁面。 DropDownList 會列出資料庫中的所有類別，而 GridView 會顯示屬於所選類別的產品。

注意

我們已在先前的教學課程中使用DropDownLists建立主要/詳細數據介面。 如需實作這類主要/詳細數據報表的更深入探討，請參閱 使用DropDownList 進行主要/詳細數據篩選教學課程。

ExistingSprocs.aspx開啟資料夾中的頁面AdvancedDAL，並將DropDownList 從 [工具箱] 拖曳至設計工具。 將 DropDownList 的 ID 屬性設定為 Categories ，並將其 AutoPostBack 屬性設定為 True。 接下來，從其智慧標記，將DropDownList系結至名為 CategoriesDataSource的新 ObjectDataSource。 設定 ObjectDataSource，使其從 CategoriesBLL 類別 s GetCategories 方法擷取其數據。 將 UPDATE、INSERT 和 DELETE 索引標籤中的下拉式清單設定為 [無]。

圖 7：從 類別 s GetCategories 方法擷CategoriesBLL取資料（按兩下以檢視完整大小的影像）

圖 8：將 UPDATE、INSERT 和 DELETE 索引標籤標的下拉式清單設定為 [無] （按兩下以檢視完整大小的影像）

完成 ObjectDataSource 精靈之後，將 DropDownList 設定為顯示 CategoryName 資料欄位，並使用 CategoryID 字段作為 Value 每個 ListItem的 。

此時，DropDownList 和 ObjectDataSource 的宣告式標記應該如下所示：

<asp:DropDownList ID="Categories" runat="server" AutoPostBack="True" 
    DataSourceID="CategoriesDataSource" DataTextField="CategoryName" 
    DataValueField="CategoryID">
</asp:DropDownList>
<asp:ObjectDataSource ID="CategoriesDataSource" runat="server"
    OldValuesParameterFormatString="original_{0}" 
    SelectMethod="GetCategories" TypeName="CategoriesBLL">
</asp:ObjectDataSource>

接下來，將 GridView 拖曳到設計工具上，將它放在 DropDownList 底下。 將 GridView 設定 ID 為 ProductsByCategory ，並從其智慧標記將它系結至名為 ProductsByCategoryDataSource的新 ObjectDataSource。 將 ProductsByCategoryDataSource ObjectDataSource 設定為使用 類別 ProductsBLLWithSprocs ，讓它使用 GetProductsByCategoryID(categoryID) 方法擷取其數據。 由於此 GridView 只會用來顯示資料，因此請將 UPDATE、INSERT 和 DELETE 索引標籤標的下拉式清單設定為 [無]，然後按 [下一步]。

圖 9：將 ObjectDataSource 設定為使用 ProductsBLLWithSprocs 類別 （按一下即可檢視完整大小的影像）

圖 10：從 方法擷 GetProductsByCategoryID(categoryID) 取資料 （按兩下以檢視完整大小的影像）

SELECT 索引標籤中選擇的方法需要參數，因此精靈的最後一個步驟會提示我們輸入參數的來源。 將 [參數來源] 下拉式清單設定為 [控制]，然後 Categories 從 [ControlID] 下拉式清單中選擇控件。 按一下 [完成] 以完成程序。

圖 11：使用 Categories DropDownList 作為參數的來源 categoryID （按兩下以檢視完整大小的影像）

完成 ObjectDataSource 精靈之後，Visual Studio 會為每個產品數據字段新增 BoundFields 和 CheckBoxField。 您可以視需要自定義這些欄位。

透過瀏覽器瀏覽頁面。 瀏覽頁面時，會選取 [飲料] 類別，以及方格中所列的對應產品。 將下拉式清單變更為替代類別，如圖 12 所示，會造成回傳，並重載具有新選取類別產品的方格。

圖 12：顯示 [產品類別] 中的產品 （按兩下以檢視完整大小的影像）

步驟 5：將預存程式 s 語句包裝在交易範圍內

在 交易 中的包裝資料庫修改教學課程中，我們討論了在交易範圍內執行一系列資料庫修改語句的技術。 回想一下，在交易的保護傘下執行的修改全都成功或全部失敗，保證不可部分完成性。 使用交易的技術包括：

• 使用命名空間中的 System.Transactions 類別，
• 讓資料存取層使用 ADO.NET 類別，例如 SqlTransaction、 和
• 直接在預存程式中新增 T-SQL 交易命令

在 交易 教學課程中包裝資料庫修改使用 DAL 中的 ADO.NET 類別。 本教學課程的其餘部分會檢查如何使用預存程式中的 T-SQL 命令來管理交易。

手動啟動、認可和回復交易的三個主要 SQL 命令分別是 BEGIN TRANSACTION、 COMMIT TRANSACTION和 ROLLBACK TRANSACTION。 如同 ADO.NET 方法，使用預存程式中的交易時，我們需要套用下列模式：

1. 指出交易的開頭。
2. 執行組成交易的 SQL 語句。
3. 如果步驟 2 中的任何一個語句中有錯誤，請回復交易。
4. 如果步驟 2 中的所有語句都完成且沒有錯誤，請認可交易。

您可以使用下列樣本，在 T-SQL 語法中實作此模式：

BEGIN TRY
  BEGIN TRANSACTION -- Start the transaction
  ... Perform the SQL statements that makeup the transaction ...
  -- If we reach here, success!
  COMMIT TRANSACTION
END TRY
BEGIN CATCH 
  -- Whoops, there was an error
  ROLLBACK TRANSACTION
  -- Raise an error with the 
  -- details of the exception   
  DECLARE @ErrMsg nvarchar(4000),
          @ErrSeverity int 
  SELECT @ErrMsg = ERROR_MESSAGE(), 
         @ErrSeverity = ERROR_SEVERITY() 
 
  RAISERROR(@ErrMsg, @ErrSeverity, 1) 
END CATCH

範本一開始會定義 TRY...CATCH 區塊，這是 SQL Server 2005 的新建構。 Try...Catch如同 Visual Basic 中的區塊，SQL TRY...CATCH 區塊會在 區塊中TRY執行 語句。 如果有任何語句引發錯誤，控件會立即傳送至 CATCH 區塊。

如果執行構成交易的 SQL 語句沒有任何錯誤， COMMIT TRANSACTION 語句會認可變更並完成交易。 不過，如果其中一個語句會產生錯誤， ROLLBACK TRANSACTION 則 區塊中的 CATCH 會在交易開始之前，將資料庫傳回其狀態。 預存程式也會使用 RAISERROR 命令引發錯誤，這會導致 SqlException 應用程式中引發 。

注意

由於 區塊 TRY...CATCH 是 SQL Server 2005 的新功能，因此如果您使用舊版的 sql Server Microsoft，則上述範本將無法運作。

讓我們看看具體範例。 和 Products 數據表之間Categories存在外鍵條件約束，這表示CategoryID數據表中的每個Products字段都必須對應至CategoryID數據表中的Categories值。 任何違反此條件約束的動作，例如嘗試刪除具有相關聯產品的類別，會導致外鍵條件約束違規。 若要確認這一點，請重新流覽 [使用二進位數據] 區段中的 [更新和刪除現有的二進位數據] 範例 （~/BinaryData/UpdatingAndDeleting.aspx）。 此頁面會列出系統中每個類別以及 [編輯] 和 [刪除] 按鈕（請參閱圖 13），但如果您嘗試刪除具有相關聯產品的類別，例如飲料，則刪除會因為外鍵條件約束違規而失敗（請參閱圖 14）。

圖 13：每個類別都會顯示在具有 [編輯和刪除] 按鈕的 GridView 中（按兩下以檢視完整大小的影像）

圖 14：您無法刪除具有現有產品的類別 （按兩下以檢視完整大小的影像）

不過，假設我們想要允許刪除類別，無論類別是否具有相關聯的產品。 如果刪除具有產品的類別，假設我們也想要刪除其現有的產品（雖然另一個選項是只將其產品 CategoryID 值設定為 NULL）。 這項功能可以透過外鍵條件約束的串聯規則來實作。 或者，我們可以建立接受 @CategoryID 輸入參數的預存程式，並在叫用時明確刪除所有相關聯的產品，然後刪除指定的類別。

我們第一次嘗試這類預存程式可能如下所示：

CREATE PROCEDURE dbo.Categories_Delete
(
    @CategoryID int
)
AS
-- First, delete the associated products...
DELETE FROM Products
WHERE CategoryID = @CategoryID
-- Now delete the category
DELETE FROM Categories
WHERE CategoryID = @CategoryID

雖然這肯定會刪除相關聯的產品和類別，但它不會在交易的保護傘下這樣做。 假設有一些其他外鍵條件約束 Categories 會禁止刪除特定 @CategoryID 值。 問題是，在這種情況下，我們會先刪除所有產品，再嘗試刪除類別。 淨結果是，對於這類類別，此預存程式會移除其所有產品，而該類別仍會保留，因為它在某些其他數據表中仍有相關記錄。

不過，如果預存程式已包裝在交易的範圍內， Products 則會在 上的 Categories刪除失敗時回復數據表的刪除。 下列預存程式文稿會使用交易來確保兩 DELETE 個語句之間的不可部分完成性：

CREATE PROCEDURE dbo.Categories_Delete
(
    @CategoryID int
)
AS
BEGIN TRY
  BEGIN TRANSACTION -- Start the transaction
  -- First, delete the associated products...
  DELETE FROM Products
  WHERE CategoryID = @CategoryID
  -- Now delete the category
  DELETE FROM Categories
  WHERE CategoryID = @CategoryID
  -- If we reach here, success!
  COMMIT TRANSACTION
END TRY
BEGIN CATCH 
  -- Whoops, there was an error
  ROLLBACK TRANSACTION
  -- Raise an error with the 
  -- details of the exception   
  DECLARE @ErrMsg nvarchar(4000),
          @ErrSeverity int 
  SELECT @ErrMsg = ERROR_MESSAGE(), 
         @ErrSeverity = ERROR_SEVERITY() 
 
  RAISERROR(@ErrMsg, @ErrSeverity, 1) 
END CATCH

花點時間將預存程式新增 Categories_Delete 至 Northwind 資料庫。 如需將預存程式新增至資料庫的指示，請參閱步驟 1。

步驟 6：更新CategoriesTableAdapter

雖然我們已將預存程式新增 Categories_Delete 至資料庫，但 DAL 目前已設定為使用臨機操作 SQL 語句來執行刪除。 我們需要更新 CategoriesTableAdapter ，並指示它改用 Categories_Delete 預存程式。

注意

在本教學課程稍早，我們使用 NorthwindWithSprocs DataSet。 但是，DataSet 只有單一實體， ProductsDataTable而且我們需要使用類別。 因此，在本教學課程的其餘部分，當我談到我參考 Northwind DataSet 的數據存取層時，我們會先在建立數據存取層教學課程中建立。

開啟 Northwind DataSet，選取 CategoriesTableAdapter，然後移至 屬性視窗。 屬性視窗 會列出 InsertCommandTableAdapter 所使用的、 DeleteCommandUpdateCommand和 SelectCommand ，以及其名稱和連接資訊。 DeleteCommand展開 屬性以查看其詳細數據。 如圖 15 所示， DeleteCommand s CommandType 屬性會設定為 Text，以指示將屬性中的 CommandText 文字傳送為臨機操作 SQL 查詢。

圖 15：選取 CategoriesTableAdapter 設計工具中的 以在 [屬性] 視窗中檢視其屬性

若要變更這些設定，請選取 屬性視窗 中的 [DeleteCommand] 文字，然後從下拉式清單中選擇 [新增]。 這會清除、 CommandType和 Parameters 屬性的CommandText設定。 接下來，將 CommandType 屬性設定為 StoredProcedure ，然後輸入 （dbo.Categories_Delete） 預CommandText存程式的名稱。 如果您確定以這個順序輸入屬性 - 首先 CommandType ， 然後 CommandText - Visual Studio 會自動填入 Parameters 集合。 如果您未依此順序輸入這些屬性，您必須透過Parameters集合編輯器手動新增參數。 不論是哪一種情況，按兩下Parameters屬性中的省略號即可啟動Parameters集合編輯器，以確認已進行正確的參數設定變更（請參閱圖 16）。 如果您在對話框中看不到任何參數，請手動新增 @CategoryID 參數（您不需要新增 @RETURN_VALUE 參數）。

圖 16：確定參數設定正確

當 DAL 更新之後，刪除類別會自動刪除其所有相關聯的產品，並在交易的保護傘下這樣做。 若要確認這一點，請返回 [更新和刪除現有的二進位數據] 頁面，然後按兩下其中一個類別的 [刪除] 按鈕。 按兩下滑鼠時，將會刪除類別及其所有相關聯的產品。

注意

測試 Categories_Delete 預存程式之前，會刪除一些產品以及選取的類別，最好是製作資料庫的備份複本。 如果您使用 NORTHWND.MDF 中的 App_Data資料庫，只要關閉 Visual Studio，然後將 中的 App_Data MDF 和 LDF 檔案複製到其他資料夾。 測試功能之後，您可以關閉 Visual Studio，並以備份複本取代 中 App_Data 目前的 MDF 和 LDF 檔案，以還原資料庫。

摘要

雖然 TableAdapter 精靈會自動為我們產生預存程式，但有時候我們可能已經建立這類預存程式，或想要手動或使用其他工具加以建立。 若要容納這類案例，您也可以將 TableAdapter 設定為指向現有的預存程式。 在本教學課程中，我們探討如何透過Visual Studio環境手動將預存程式新增至資料庫，以及如何將TableAdapter方法連線到這些預存程式。 我們也檢查了用於啟動、認可和回復預存程式中交易的 T-SQL 命令和腳本模式。

快樂的程序設計！

關於作者

斯科特·米切爾，七本 ASP/ASP.NET 書籍和 4GuysFromRolla.com 創始人的作者，自1998年以來一直與Microsoft Web 技術合作。 斯科特擔任獨立顧問、教練和作家。 他的最新書是 山姆斯在24小時內 ASP.NET 2.0。 他可以到達 mitchell@4GuysFromRolla.com， 或通過他的博客，可以在 找到 http://ScottOnWriting.NET。

特別感謝

本教學課程系列已由許多實用的檢閱者檢閱。 本教學課程的主要檢閱者是希爾頓·蓋塞諾、S 任雅各·勞裡森和特蕾莎·墨菲。 有興趣檢閱我即將推出的 MSDN 文章嗎？ 如果是，請將一行 mitchell@4GuysFromRolla.com放在 。

上一個下一個