ASP.NET 2.0 資料教程 68：TableAdapter 使用現有 Stored Procedure、SSMS 建立 SP、事務包裝 T-SQL 命令

前言

本文示範如何讓 TableAdapter 使用資料庫現有 Stored Procedure，包含在 SSMS 建立 SP、TableAdapter 精靈對應、以及在 SP 中使用交易包裝多條 T-SQL 命令。

步驟 1：在 SSMS 建立 Stored Procedure

1. Server Explorer → 展開資料庫 → 右鍵 Stored Procedures → Add New Stored Procedure。
2. 輸入 T-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

步驟 2：TableAdapter 精靈對應現有 SP

1. 資料集設計師 → 右鍵 TableAdapter → Add Query。
2. 選 Use existing stored procedures。
3. 選取 Products_SelectByCategoryID → 參數自動對應 @CategoryID。
4. 方法命名 GetProductsByCategoryID。

進階：Stored Procedure 中使用交易

CREATE PROCEDURE DeleteCategoryWithProducts
    @CategoryID int
AS
BEGIN TRANSACTION
BEGIN TRY
    DELETE FROM Products WHERE CategoryID = @CategoryID;
    DELETE FROM Categories WHERE CategoryID = @CategoryID;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    RAISERROR('刪除失敗，交易已回滾', 16, 1);
END CATCH

• SP 內多條命令預設不在交易中，必須顯式 BEGIN TRANSACTION / COMMIT / ROLLBACK。
• 搭配 TRY...CATCH 確保原子性。

結語

使用現有 SP 讓資料庫邏輯集中管理、DBA 可獨立優化。TableAdapter 精靈支援參數自動對應，開發效率高。交易控制在 SP 內完成，確保資料一致性。