在ASP.NET 2.0中操作數(shù)據(jù)之六十六：在TableAdapters中使用現(xiàn)有的存儲過程

導(dǎo)言：

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

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

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

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

圖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

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

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

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

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

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

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

圖3：選“Use existing stored procedure”項

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

圖4：選Products_SelectByCategoryID存儲過程.

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

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

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

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

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

第三步：在BLL層添加一個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()方法來返回ProductsDataTable。由于使用了DataObjectMethodAttribute屬性，我們使用ObjectDataSource的設(shè)置數(shù)據(jù)源向?qū)r，該方法會出現(xiàn)在SELECT標(biāo)簽的下拉列表中.

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

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

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

　　打開AdvancedDAL文件夾里的ExistingSprocs.aspx頁面，從工具箱里拖一個DropDownList控件到頁面，設(shè)置其ID為Categories，AutoPostBack屬性為true.接下來，在其智能標(biāo)簽里將其綁定到一個名為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ū)Ш?，我們設(shè)置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控件，該控件調(diào)用ProductsBLLWithSprocs class類的GetProductsByCategoryID(categoryID)方法。由于該GridView控件僅僅用來展示數(shù)據(jù)，因此，在UPDATE, INSERT, 和DELETE標(biāo)簽里選“(None)”并點Next.

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

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

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

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

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

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

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

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

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

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

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

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

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

可以用T-SQL syntax來執(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

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

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

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

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

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

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

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

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

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

　　如果該存儲過程置身于一個事務(wù)里的話，對Categories表的刪除操作失敗將導(dǎo)致對Products表的刪除操作回滾.下面的存儲過程腳本使用一個事務(wù)來確保對這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數(shù)據(jù)庫添加一個名為Categories_Delete的存儲過程，具體步驟可參考第一步.

第六步：更新CategoriesTableAdapter

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

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

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

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

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

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

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

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

結(jié)語：

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

　　祝編程快樂！

作者簡介

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