導言：
在前面的文章裡我們考察了如何讓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有所幫助。
