導(dǎo)言：

　　在前面的文章里我們考察了如何讓TableAdapters向?qū)ё詣?dòng)的創(chuàng)建存儲(chǔ)過程.而在本文，我們將考察如何讓TableAdapter使用現(xiàn)有的存儲(chǔ)過程。由于Northwind數(shù)據(jù)庫(kù)現(xiàn)有的存儲(chǔ)過程很少，我們也需要考察如何在Visual Studio環(huán)境里手動(dòng)向數(shù)據(jù)庫(kù)添加新的存儲(chǔ)過程.

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

第一步：向Northwind數(shù)據(jù)庫(kù)添加存儲(chǔ)過程

　　我們很容易通過Visual Studio向數(shù)據(jù)庫(kù)添加存儲(chǔ)過程.讓我們向Northwind數(shù)據(jù)庫(kù)添加一個(gè)新存儲(chǔ)過程，它返回Products表里特定CategoryID值的產(chǎn)品.在服務(wù)器資源管理窗口，展開Northwind數(shù)據(jù)庫(kù)，就像我們?cè)谇懊娴奈恼驴吹降囊粯?，存?chǔ)過程文件夾包含了現(xiàn)有的存儲(chǔ)過程。要添加新的存儲(chǔ)過程的話，只需要右鍵單擊存儲(chǔ)過程文件夾，選“添加新存儲(chǔ)過程”項(xiàng)，

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

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

CREATE PROCEDURE dbo.Products_SelectByCategoryID
(
 @CategoryID int
)
AS

SELECT ProductID, ProductName, SupplierID, CategoryID,
 QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
 ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @CategoryID

　　當(dāng)執(zhí)行該腳本時(shí)，將會(huì)向數(shù)據(jù)庫(kù)添加一個(gè)名為Products_SelectByCategoryID的新存儲(chǔ)過程，該存儲(chǔ)過程接受一個(gè)輸入?yún)?shù)(@CategoryID, 類型為int)并將與CategoryID值匹配的所有產(chǎn)品返回.

　　執(zhí)行該CREATE PROCEDURE腳本，將向數(shù)據(jù)庫(kù)添加存儲(chǔ)過程，點(diǎn)工具欄的保存按鈕或按Ctrl+S。如此之后，刷新存儲(chǔ)過程文件夾以顯示最近添加的存儲(chǔ)過程，如此一ilai部分由“CREATE PROCEDURE dbo.Products_SelectProductByCategoryID” 轉(zhuǎn)變?yōu)椤癆LTER PROCEDURE dbo.Products_SelectProductByCategoryID”. CREATE PROCEDURE用于添加新存儲(chǔ)過程，而ALTER PROCEDURE用于更新現(xiàn)有的存儲(chǔ)過程。由于腳本開頭部分已經(jīng)轉(zhuǎn)變?yōu)锳LTER PROCEDURE, 我們可以通過改動(dòng)輸入?yún)?shù)或SQL statements并點(diǎn)擊保存按鈕，即可完成對(duì)存儲(chǔ)過程的更新.圖2顯示的是保存Products_SelectByCategoryID存儲(chǔ)過程后的畫面.

圖2：Products_SelectByCategoryID存儲(chǔ)過程已經(jīng)添加到數(shù)據(jù)庫(kù)

第二步：設(shè)置TableAdapter使用現(xiàn)有的存儲(chǔ)過程

　　現(xiàn)在存儲(chǔ)過程Products_SelectByCategoryID已經(jīng)添加到數(shù)據(jù)庫(kù)，我們將設(shè)置數(shù)據(jù)訪問層使用該存儲(chǔ)過程。具體說，我們將向ProductsTableAdapter添加GetProducstByCategoryID(categoryID)方法， 該方法將調(diào)用我們剛剛創(chuàng)建的存儲(chǔ)過程Products_SelectByCategoryID.

　　打開NorthwindWithSprocs數(shù)據(jù)集，在ProductsTableAdapter上右鍵單擊，選“添加查詢”以啟用TableAdapter Query Configuration wizard.我們將使用剛剛創(chuàng)建的存儲(chǔ)過程Products_SelectByCategoryID，因此選“Use existing stored procedure”項(xiàng)，然后點(diǎn)Next.

圖3：選“Use existing stored procedure”項(xiàng)

　　接下來(lái)的畫面為一個(gè)下拉列表框，列出了數(shù)據(jù)庫(kù)現(xiàn)有的所有存儲(chǔ)過程，當(dāng)選擇某個(gè)存儲(chǔ)過程的話，左邊將列出其輸入?yún)?shù)，右邊將列出其返回列(如果有的話).在下拉列表里選Products_SelectByCategoryID存儲(chǔ)過程，再點(diǎn)Next.

圖4：選Products_SelectByCategoryID存儲(chǔ)過程.

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

圖5：指定存儲(chǔ)過程返回Tabular Data

　　然后需要指定采用的方法模式以及方法的名稱.同時(shí)選中Fill a DataTable” 和 “Return a DataTable”項(xiàng).將這2個(gè)方法重命名為FillByCategoryID和 GetProductsByCategoryID. 點(diǎn)Next，確認(rèn)無(wú)誤的話，再點(diǎn)Finish完成設(shè)置。

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

　　注意：我們剛才添加FillByCategoryID 和 GetProductsByCategoryID方法,執(zhí)行一個(gè)int類型的輸入?yún)?shù)，它由@CategoryID傳遞進(jìn)來(lái)。如果你要改動(dòng)Products_SelectByCategory存儲(chǔ)過程的參數(shù)的話，你也必須更新這些TableAdapter方法的參數(shù).就像在前一篇文章探討的一樣，要么手動(dòng)添加或刪除參數(shù)集里的參數(shù)，要么再次運(yùn)行TableAdapter向?qū)?

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

設(shè)置完DAL層的GetProductsByCategoryID方法后，下一步我們將在業(yè)務(wù)邏輯層添加方法以調(diào)用該方法.打開ProductsBLLWithSprocs class類的文件，添加如下方法：

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

}

　　該BLL層方法僅僅通過ProductsTableAdapter的 GetProductsByCategoryID()方法來(lái)返回ProductsDataTable。由于使用了DataObjectMethodAttribute屬性，我們使用ObjectDataSource的設(shè)置數(shù)據(jù)源向?qū)r(shí)，該方法會(huì)出現(xiàn)在SELECT標(biāo)簽的下拉列表中.

第四步：展示產(chǎn)品

　　為測(cè)試新添加的Products_SelectByCategoryID存儲(chǔ)過程，以及DAL 和 BLL層里的對(duì)應(yīng)的方法， 我們將創(chuàng)建一個(gè)ASP.NET頁(yè)面，該頁(yè)面包含一個(gè)DropDownList控件以及一個(gè) GridView控件.DropDownList控件列出數(shù)據(jù)庫(kù)里所有的category，當(dāng)選定某個(gè)category時(shí)，我們將在GridView里將屬于該category的所有product展示出來(lái).

　　注意：我們?cè)谇懊娴奈恼吕镉肈ropDownList控件創(chuàng)建過主/從報(bào)表，更多細(xì)節(jié)請(qǐng)參考第7章《使用DropDownList過濾的主/從報(bào)表》

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

圖7：調(diào)用CategoriesBLL Class類的GetCategories方法

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

完成ObjectDataSource向?qū)Ш?，我們?cè)O(shè)置DropDownList控件顯示的是CategoryName列，而傳遞的Value值為CategoryID列.此時(shí)，DropDownList控件和ObjectDataSource控件的聲明代碼看起來(lái)和下面的差不多：

<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>

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

圖9：設(shè)置ObjectDataSource控件ProductsBLLWithSprocs Class類

圖10：調(diào)用GetProductsByCategoryID(categoryID)方法

接下來(lái)要選擇參數(shù)來(lái)源，我們?cè)赑arameter source下拉列表里選“Control”；在ControlID下拉列表里選“Categories” 。點(diǎn)Finish完成設(shè)置.

圖11：設(shè)參數(shù)categoryID來(lái)源于ID為Categories的DropDownList控件

完成ObjectDataSource向?qū)Ш?，Visual Studio會(huì)自動(dòng)的添加BoundFields列和一個(gè) CheckBoxField列。你可以對(duì)其外觀盡情定制.

在瀏覽器里登錄該頁(yè)面，當(dāng)?shù)卿洉r(shí)選取的是Beverages(飲料類)，該類的產(chǎn)品將會(huì)顯示出來(lái).如果我們選擇其它種類的話，對(duì)應(yīng)的所有產(chǎn)品將顯示出來(lái).如下圖： 

圖12： Produce類的所有產(chǎn)品都顯示出來(lái)了

第五步：用事務(wù)封裝存儲(chǔ)過程命令

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

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

在63章我們?cè)贒AL層使用ADO.NET classe類，而在本文剩余部分，我們將在一個(gè)存儲(chǔ)過程里運(yùn)用T-SQL command命令來(lái)對(duì)一個(gè)事務(wù)進(jìn)行管理.

用來(lái)手動(dòng)啟動(dòng)、提交、回滾事務(wù)的3個(gè)主要SQL command命令分別是BEGIN TRANSACTION, COMMIT TRANSACTION, 以及ROLLBACK TRANSACTION.與使用ADO.NET方法類似，在一個(gè)存儲(chǔ)過程里使用事務(wù)時(shí)，應(yīng)采用如下的模式：

1.指出事務(wù)已經(jīng)開啟
2.執(zhí)行事務(wù)包含的SQL statements
3.如果第二步的任何一個(gè)statement出錯(cuò)，則回滾事務(wù)
4.如果第二步的所有statement執(zhí)行無(wú)誤，則提交事務(wù)

可以用T-SQL syntax來(lái)執(zhí)行該模式，如下：

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

　　代碼開始為一個(gè)TRY...CATCH模式——SQL Server2005新增的結(jié)構(gòu).就像C#里的try...catch模式一樣，該SQL TRY...CATCH模式在TRY區(qū)域執(zhí)行statement，如果任何一個(gè)statement出錯(cuò)，則立即轉(zhuǎn)到CATCH區(qū)域.

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

　　讓我們看一個(gè)實(shí)實(shí)在在的例子。在Categories表和Products表之間有一個(gè)外鍵約束，這意味著，Products表里的CategoryID列必須要與Categories表里的CategoryID值吻合.如果某個(gè)category有對(duì)應(yīng)的product，而我們?cè)噲D刪除該category時(shí)將會(huì)導(dǎo)致違背外鍵約束.我們來(lái)進(jìn)行演示，登錄這個(gè)頁(yè)面(~/BinaryData/UpdatingAndDeleting.aspx)，該頁(yè)面列出了系統(tǒng)里的所有category，且每行都包含Edit和Delete按鈕(如圖13)，如果你嘗試刪除一個(gè)有對(duì)應(yīng)product的category時(shí)，比如Beverages——?jiǎng)h除失敗，因?yàn)檫`背了外鍵約束(如圖14所示).

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

圖14：你無(wú)法刪除有對(duì)應(yīng)產(chǎn)品的Category

　　我們希望可以刪除任何一個(gè)category，不管其是否有對(duì)應(yīng)的產(chǎn)品.當(dāng)刪除category時(shí)，我們同樣希望刪除其對(duì)應(yīng)的產(chǎn)品(盡管我們可以簡(jiǎn)單的將這些產(chǎn)品的CategoryID值設(shè)置為NULL).為此，我們可以創(chuàng)建一個(gè)存儲(chǔ)過程，它接受一個(gè)輸入?yún)?shù)@CategoryID。當(dāng)調(diào)用它時(shí)明確的將所有對(duì)應(yīng)的product刪除，然后再將這個(gè)category刪掉.

人們的第一反應(yīng)是創(chuàng)建類似下面的存儲(chǔ)過程：

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

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

　　如果該存儲(chǔ)過程置身于一個(gè)事務(wù)里的話，對(duì)Categories表的刪除操作失敗將導(dǎo)致對(duì)Products表的刪除操作回滾.下面的存儲(chǔ)過程腳本使用一個(gè)事務(wù)來(lái)確保對(duì)這2個(gè)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

花點(diǎn)時(shí)間向Northwind數(shù)據(jù)庫(kù)添加一個(gè)名為Categories_Delete的存儲(chǔ)過程，具體步驟可參考第一步.

第六步：更新CategoriesTableAdapter

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

　　注意：在前幾章我們處理的是NorthwindWithSprocs數(shù)據(jù)集，該數(shù)據(jù)集只有一個(gè)實(shí)體——ProductsDataTable，但是我們將會(huì)遇到處理categories的情況。因此，在本文后面部分，當(dāng)我提到數(shù)據(jù)訪問層(Data Access Layer)時(shí)，我指的是Northwind數(shù)據(jù)集，也就是我們?cè)诘?章《創(chuàng)建一個(gè)數(shù)據(jù)訪問層》里創(chuàng)建的那個(gè).

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

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

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

圖16：確保參數(shù)設(shè)置正確

　　當(dāng)DAL完成更新后，刪除一個(gè)category將自動(dòng)的刪除所有其對(duì)應(yīng)的product，這些操作都置身于一個(gè)事務(wù)里.我們來(lái)做個(gè)驗(yàn)證，重返剛才那個(gè)頁(yè)面，當(dāng)單擊某個(gè)category的Delete按鈕時(shí)，該category及其所有的product都會(huì)被刪除.

　　注意：在測(cè)試Categories_Delete存儲(chǔ)過程前，最好對(duì)數(shù)據(jù)庫(kù)做個(gè)備份，因?yàn)樵摯鎯?chǔ)過程將刪除選中的category及其對(duì)應(yīng)的product.如果你用的是App_Data文件夾里的NORTHWND.MDF數(shù)據(jù)庫(kù)的話，你只需要關(guān)閉Visual Studio并將文件夾里的MDF和LDF文件拷貝到其它文件夾.測(cè)試完畢后，關(guān)閉Visual Studio，再用備份的MDF和LDF文件覆蓋掉App_Data文件夾的對(duì)應(yīng)文件.

結(jié)語(yǔ)：

　　雖然TableAdapter向?qū)Э梢宰詣?dòng)的生成存儲(chǔ)過程，但是在某些時(shí)候我們需要使用現(xiàn)有的存儲(chǔ)過程。在本文，我們考察了如何在Visual Studio環(huán)境里手動(dòng)添加存儲(chǔ)過程，并引導(dǎo)TableAdapter的方法使用這些存儲(chǔ)過程。另外我們還考察了在存儲(chǔ)過程里用來(lái)開啟、提交、回滾事務(wù)的T-SQL commands 和script腳本模式.

　　祝編程快樂！

作者簡(jiǎn)介

　　本系列教程作者 Scott Mitchell，著有六本ASP/ASP.NET方面的書，是4GuysFromRolla.com的創(chuàng)始人，自1998年以來(lái)一直應(yīng)用 微軟Web技術(shù)。大家可以點(diǎn)擊查看全部教程《[翻譯]Scott Mitchell 的ASP.NET 2.0數(shù)據(jù)教程》，希望對(duì)大家的學(xué)習(xí)ASP.NET有所幫助。