導言：

　　在前面的文章里我們考察了如何讓TableAdapters向導自動的創建存儲過程.而在本文，我們將考察如何讓TableAdapter使用現有的存儲過程。由于Northwind數據庫現有的存儲過程很少，我們也需要考察如何在Visual Studio環境里手動向數據庫添加新的存儲過程.

　　注意：在第61章《在事務里對數據庫修改進行封裝》里我們向TableAdapter添加了一些方法以支持事務(比如 (BeginTransaction, CommitTransaction等)。我們可以在不修改數據訪問層代碼的情況下，在一個存儲過程里管理整個事務.在本文，我們還將對事務里執行存儲過程的T-SQL commands命令進行考察.

第一步：向Northwind數據庫添加存儲過程

　　我們很容易通過Visual Studio向數據庫添加存儲過程.讓我們向Northwind數據庫添加一個新存儲過程，它返回Products表里特定CategoryID值的產品.在服務器資源管理窗口，展開Northwind數據庫，就像我們在前面的文章看到的一樣，存儲過程文件夾包含了現有的存儲過程。要添加新的存儲過程的話，只需要右鍵單擊存儲過程文件夾，選“添加新存儲過程”項，

圖1：右擊Stored Procedures文件夾選“Add a New Stored Procedure”

如圖1所示，選“Add a New Stored Procedure”項后，將在Visual Studio里打開一個腳本窗口.輸入如下的腳本：

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的新存儲過程，該存儲過程接受一個輸入參數(@CategoryID, 類型為int)并將與CategoryID值匹配的所有產品返回.

　　執行該CREATE PROCEDURE腳本，將向數據庫添加存儲過程，點工具欄的保存按鈕或按Ctrl+S。如此之后，刷新存儲過程文件夾以顯示最近添加的存儲過程，如此一ilai部分由“CREATE PROCEDURE dbo.Products_SelectProductByCategoryID” 轉變為“ALTER PROCEDURE dbo.Products_SelectProductByCategoryID”. CREATE PROCEDURE用于添加新存儲過程，而ALTER PROCEDURE用于更新現有的存儲過程。由于腳本開頭部分已經轉變為ALTER PROCEDURE, 我們可以通過改動輸入參數或SQL statements并點擊保存按鈕，即可完成對存儲過程的更新.圖2顯示的是保存Products_SelectByCategoryID存儲過程后的畫面.

圖2：Products_SelectByCategoryID存儲過程已經添加到數據庫

第二步：設置TableAdapter使用現有的存儲過程

　　現在存儲過程Products_SelectByCategoryID已經添加到數據庫，我們將設置數據訪問層使用該存儲過程。具體說，我們將向ProductsTableAdapter添加GetProducstByCategoryID(categoryID)方法， 該方法將調用我們剛剛創建的存儲過程Products_SelectByCategoryID.

　　打開NorthwindWithSprocs數據集，在ProductsTableAdapter上右鍵單擊，選“添加查詢”以啟用TableAdapter Query Configuration wizard.我們將使用剛剛創建的存儲過程Products_SelectByCategoryID，因此選“Use existing stored procedure”項，然后點Next.

圖3：選“Use existing stored procedure”項

　　接下來的畫面為一個下拉列表框，列出了數據庫現有的所有存儲過程，當選擇某個存儲過程的話，左邊將列出其輸入參數，右邊將列出其返回列(如果有的話).在下拉列表里選Products_SelectByCategoryID存儲過程，再點Next.

圖4：選Products_SelectByCategoryID存儲過程.

　　接下來的畫面詢問我們存儲過程返回的是哪種類型的數據，以及TableAdapter的方法返回的類型.比如，如果我們指定返回tabular data(表列數據)的話，該方法將返回一個ProductsDataTable instance實例；如果我們指定存儲過程返回一個單一值(a single value)的話，TableAdapter將返回一個object(對象)，該對象由存儲過程返回的第一行的第一列來賦值.由于存儲過程Products_SelectByCategoryID將返回某個category的所有產品，選第一項“Tabular data”，再點Next.

圖5：指定存儲過程返回Tabular Data

　　然后需要指定采用的方法模式以及方法的名稱.同時選中Fill a DataTable” 和 “Return a DataTable”項.將這2個方法重命名為FillByCategoryID和 GetProductsByCategoryID. 點Next，確認無誤的話，再點Finish完成設置。

圖6：將方法命名為FillByCategoryID 和 GetProductsByCategoryID

　　注意：我們剛才添加FillByCategoryID 和 GetProductsByCategoryID方法,執行一個int類型的輸入參數，它由@CategoryID傳遞進來。如果你要改動Products_SelectByCategory存儲過程的參數的話，你也必須更新這些TableAdapter方法的參數.就像在前一篇文章探討的一樣，要么手動添加或刪除參數集里的參數，要么再次運行TableAdapter向導.

第三步：在BLL層添加一個GetProductsByCategoryID(categoryID)方法

設置完DAL層的GetProductsByCategoryID方法后，下一步我們將在業務邏輯層添加方法以調用該方法.打開ProductsBLLWithSprocs class類的文件，添加如下方法：

[System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetProductByCategoryID(int categoryID)
{
 return Adapter.GetProductsByCategoryID(categoryID);

}

　　該BLL層方法僅僅通過ProductsTableAdapter的 GetProductsByCategoryID()方法來返回ProductsDataTable。由于使用了DataObjectMethodAttribute屬性，我們使用ObjectDataSource的設置數據源向導時，該方法會出現在SELECT標簽的下拉列表中.

第四步：展示產品

　　為測試新添加的Products_SelectByCategoryID存儲過程，以及DAL 和 BLL層里的對應的方法， 我們將創建一個ASP.NET頁面，該頁面包含一個DropDownList控件以及一個 GridView控件.DropDownList控件列出數據庫里所有的category，當選定某個category時，我們將在GridView里將屬于該category的所有product展示出來.

　　注意：我們在前面的文章里用DropDownList控件創建過主/從報表，更多細節請參考第7章《使用DropDownList過濾的主/從報表》

　　打開AdvancedDAL文件夾里的ExistingSprocs.aspx頁面，從工具箱里拖一個DropDownList控件到頁面，設置其ID為Categories，AutoPostBack屬性為true.接下來，在其智能標簽里將其綁定到一個名為CategoriesDataSource的ObjectDataSource控件.設置該控件調用CategoriesBLL class類的GetCategories方法，而在UPDATE, INSERT, 以及DELETE標簽里選“(None)”.

圖7：調用CategoriesBLL Class類的GetCategories方法

圖8：在UPDATE, INSERT,和DELETE標簽里選“(None)”

完成ObjectDataSource向導后，我們設置DropDownList控件顯示的是CategoryName列，而傳遞的Value值為CategoryID列.此時，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>

　　接下來，在DropDownList控件下面放一個GridView控件，社其ID為ProductsByCategory ，并將其綁定到一個名為ProductsByCategoryDataSource的ObjectDataSource控件，該控件調用ProductsBLLWithSprocs class類的GetProductsByCategoryID(categoryID)方法。由于該GridView控件僅僅用來展示數據，因此，在UPDATE, INSERT, 和DELETE標簽里選“(None)”并點Next.

圖9：設置ObjectDataSource控件ProductsBLLWithSprocs Class類

圖10：調用GetProductsByCategoryID(categoryID)方法

接下來要選擇參數來源，我們在Parameter source下拉列表里選“Control”；在ControlID下拉列表里選“Categories” 。點Finish完成設置.

圖11：設參數categoryID來源于ID為Categories的DropDownList控件

完成ObjectDataSource向導后，Visual Studio會自動的添加BoundFields列和一個 CheckBoxField列。你可以對其外觀盡情定制.

在瀏覽器里登錄該頁面，當登錄時選取的是Beverages(飲料類)，該類的產品將會顯示出來.如果我們選擇其它種類的話，對應的所有產品將顯示出來.如下圖： 

圖12： Produce類的所有產品都顯示出來了

第五步：用事務封裝存儲過程命令

在第61章《在事務里對數據庫修改進行封裝》里我們探討了用事務對數據庫修改命令進行封裝的技術，這些修改操作要么都成功要么都失敗。使用事務的技術包括：

.使用System.Transactions命名空間里的類
.在Data Access Layer層調用ADO.NET classes類，比如SqlTransaction
.直接在存儲過程里添加T-SQLtransaction commands事務命令

在63章我們在DAL層使用ADO.NET classe類，而在本文剩余部分，我們將在一個存儲過程里運用T-SQL command命令來對一個事務進行管理.

用來手動啟動、提交、回滾事務的3個主要SQL command命令分別是BEGIN TRANSACTION, COMMIT TRANSACTION, 以及ROLLBACK TRANSACTION.與使用ADO.NET方法類似，在一個存儲過程里使用事務時，應采用如下的模式：

1.指出事務已經開啟
2.執行事務包含的SQL statements
3.如果第二步的任何一個statement出錯，則回滾事務
4.如果第二步的所有statement執行無誤，則提交事務

可以用T-SQL syntax來執行該模式，如下：

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 Server2005新增的結構.就像C#里的try...catch模式一樣，該SQL TRY...CATCH模式在TRY區域執行statement，如果任何一個statement出錯，則立即轉到CATCH區域.

　　如果執行無誤，COMMIT TRANSACTION將提交更改并完成事務；如果執行出錯，那么CATCH區域的ROLLBACK TRANSACTION將數據庫返回到開始前的狀態。存儲過程也會通過RAISERROR command命令拋出一個SqlException異常.
注意：上面代碼的的TRY...CATCH模式是SQL Server 2005里新添加的，如果你使用的是Microsoft SQL Server稍微舊點的版本的話，上面的代碼不會成功執行。不過你可以參考這篇文章《Managing Transactions in SQL Server Stored Procedures》（http://www.4guysfromrolla.com/webtech/080305-1.shtml）以尋求幫助.

　　讓我們看一個實實在在的例子。在Categories表和Products表之間有一個外鍵約束，這意味著，Products表里的CategoryID列必須要與Categories表里的CategoryID值吻合.如果某個category有對應的product，而我們試圖刪除該category時將會導致違背外鍵約束.我們來進行演示，登錄這個頁面(~/BinaryData/UpdatingAndDeleting.aspx)，該頁面列出了系統里的所有category，且每行都包含Edit和Delete按鈕(如圖13)，如果你嘗試刪除一個有對應product的category時，比如Beverages——刪除失敗，因為違背了外鍵約束(如圖14所示).

圖13：每條Category記錄都包含Edit 和 Delete按鈕

圖14：你無法刪除有對應產品的Category

　　我們希望可以刪除任何一個category，不管其是否有對應的產品.當刪除category時，我們同樣希望刪除其對應的產品(盡管我們可以簡單的將這些產品的CategoryID值設置為NULL).為此，我們可以創建一個存儲過程，它接受一個輸入參數@CategoryID。當調用它時明確的將所有對應的product刪除，然后再將這個category刪掉.

人們的第一反應是創建類似下面的存儲過程：

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

　　上述代碼明白無誤的將相關的product以及該category刪除，只是沒有置身于一個事務內.假設還有其它的基于Categorie表CategoryID值的外鍵約束，那么在這種情況下問題就出來了：對該category來說，其相關的product都刪除掉了，而這個category因與其它表還有外鍵約束而仍然保留在數據庫.

　　如果該存儲過程置身于一個事務里的話，對Categories表的刪除操作失敗將導致對Products表的刪除操作回滾.下面的存儲過程腳本使用一個事務來確保對這2個DELETE statement的原子操作：

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

花點時間向Northwind數據庫添加一個名為Categories_Delete的存儲過程，具體步驟可參考第一步.

第六步：更新CategoriesTableAdapter

　　一旦我們添加完Categories_Delete存儲過程后，DAL層就可以使用ad-hoc SQL statements來執行刪除操作了.不過我們需要更新CategoriesTableAdapter，使其使用Categories_Delete存儲過程.

　　注意：在前幾章我們處理的是NorthwindWithSprocs數據集，該數據集只有一個實體——ProductsDataTable，但是我們將會遇到處理categories的情況。因此，在本文后面部分，當我提到數據訪問層(Data Access Layer)時，我指的是Northwind數據集，也就是我們在第1章《創建一個數據訪問層》里創建的那個.

　　打開Northwind數據集，選中CategoriesTableAdapter并打開其屬性窗口，該窗口列出了該TableAdapter用到的InsertCommand, UpdateCommand, DeleteCommand, 以及SelectCommand，以及name和數據庫連接信息.展開DeleteCommand屬性查看其細節.如圖15所示，DeleteCommand的ComamndType屬性被設置為Text， 其文本信息作為一個ad-hoc SQL查詢.

圖15：在CategoriesTableAdapter的屬性窗口查看其屬性信息

　　讓我們來作一些修改.選中“(DeleteCommand)”文本，然后在下拉列表里選“(New)”，這將清除掉CommandText, CommandType,Parameters屬性的設置。接著將CommandType屬性設置為StoredProcedure，然后在CommandText屬性里輸入存儲過程的名稱(即dbo.Categories_Delete).如果你是按照先設置CommandType屬性再設置CommandText屬性的順序的話，Visual Studio將自動生成Parameters collection(參數集).如果你沒有按照這個順序來的話，你就只能點擊Parameters屬性里的一個橢圓型的區域來打開Parameters Collection Editor對話框，手動添加參數.不管是自動的還是手動添加參數，我們都應該打開Parameters Collection Editor對話框以檢查參數是否正確(如圖16).如果你在對話框里沒看到任何的參數，那么就手動添加參數@CategoryID，(你不需要添加參數@RETURN_VALUE).

圖16：確保參數設置正確

　　當DAL完成更新后，刪除一個category將自動的刪除所有其對應的product，這些操作都置身于一個事務里.我們來做個驗證，重返剛才那個頁面，當單擊某個category的Delete按鈕時，該category及其所有的product都會被刪除.

　　注意：在測試Categories_Delete存儲過程前，最好對數據庫做個備份，因為該存儲過程將刪除選中的category及其對應的product.如果你用的是App_Data文件夾里的NORTHWND.MDF數據庫的話，你只需要關閉Visual Studio并將文件夾里的MDF和LDF文件拷貝到其它文件夾.測試完畢后，關閉Visual Studio，再用備份的MDF和LDF文件覆蓋掉App_Data文件夾的對應文件.

結語：

　　雖然TableAdapter向導可以自動的生成存儲過程，但是在某些時候我們需要使用現有的存儲過程。在本文，我們考察了如何在Visual Studio環境里手動添加存儲過程，并引導TableAdapter的方法使用這些存儲過程。另外我們還考察了在存儲過程里用來開啟、提交、回滾事務的T-SQL commands 和script腳本模式.

　　祝編程快樂！

作者簡介

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