第八步:從表現層調用Managed Stored Procedures
當對數據訪問層和業務邏輯層進行擴充以支持調用GetDiscontinuedProducts 和 GetProductsWithPriceLessThan這2種managed stored procedures後,我們可以在一個ASP.NET頁面裡展示這些存儲過程的結果了.
打開AdvancedDAL文件夾裡的ManagedFunctionsAndSprocs.aspx頁面,從工具箱拖一個GridView控件到設計器,設其ID為DiscontinuedProducts,在其智能標簽裡綁定到一個名為DiscontinuedProductsDataSource的ObjectDataSource控件,設置其調用ProductsBLLWithSprocs class類的GetDiscontinuedProducts方法.
圖20:調用ProductsBLLWithSprocs Class類
圖21:在SELECT標簽裡調用GetDiscontinuedProducts方法
由於我們只需要展示產品信息,在UPDATE, INSERT,和DELETE標簽裡選 “(None)”,再點Finish完成配置.完成後Visual Studio會為ProductsDataTable表的列自動的添加BoundField列 或 CheckBoxField列. 將除ProductName和Discontinued以外的列全部刪除.這樣你的GridView 和 ObjectDataSource的聲明代碼看起來和下面的差不多:
<asp:GridView ID="DiscontinuedProducts" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductID" DataSourceID="DiscontinuedProductsDataSource"> <Columns> <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" /> <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" SortExpression="Discontinued" /> </Columns> </asp:GridView> <asp:ObjectDataSource ID="DiscontinuedProductsDataSource" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetDiscontinuedProducts" TypeName="ProductsBLLWithSprocs"> </asp:ObjectDataSource>
花點時間在浏覽器裡登錄該頁面。當登錄時,ObjectDataSource控件將調用ProductsBLLWithSprocs class類的 GetDiscontinuedProducts方法.就像我們在第七步看到的那樣,該方法又調用DAL層的ProductsDataTable class類的GetDiscontinuedProducts方法,該方法又調用存儲過程GetDiscontinuedProducts.該存儲過程返回那些處於“discontinued”狀態的產品. 存儲過程返回的結果填充到DAL層的一個ProductsDataTable,進而返回給BLL,再返回給表現層並綁定到一個GridView控件展現出來.
圖22:“Discontinued”的產品被列出來了
我們可以繼續加強練習,比如在頁面上再放置一個TextBox控件和一個GridView控件。在TextBox控件裡輸入一個數,而GridView控件調用ProductsBLLWithSprocs class類的GetProductsWithPriceLessThan方法將價格低於該數的產品展示出來.
第九步:創建並調用T-SQL UDFs
用戶自定義函數——簡稱UDF,是一種數據庫對象,與編程語言裡的函數定義很相仿.與C#裡面的函數類似,UDF可以包含一系列的輸入參數並返回一個特定類型的值.一個UDF要麼返回標量數據(scalar data)——比如一個string, 一個integer等等;要麼返回一個表列數據(tabular data).讓我們先快速的考察一下這2種類型的UDF,先從標量數據類型開始.
下面的UDF用於計算某個特定產品的總價.其有3個輸入參數——UnitPrice, UnitsInStock,Discontinued.其返回一個money類型的值.它通過以UnitPrice乘以UnitsInStock來得到總價,如是處於“discontinued”狀態,則總價減半.
CREATE FUNCTION udf_ComputeInventoryValue ( @UnitPrice money, @UnitsInStock smallint, @Discontinued bit ) RETURNS money AS BEGIN DECLARE @Value decimal SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0) IF @Discontinued = 1 SET @Value = @Value * 0.5 RETURN @Value END
將該UDF添加到數據庫後,我們打開Management Studio,打開Programmability文件夾,再打開Functions文件夾,再打開Scalar-value Functions文件夾,就可以看到該UDF.我們可以在一個SELECT查詢裡這樣來使用:
SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue (UnitPrice, UnitsInStock, Discontinued) as InventoryValue FROM Products ORDER BY InventoryValue DESC
我已經將該udf_ComputeInventoryValue用戶函數添加到了Northwind數據庫。圖23就是在Management Studio裡調用上述SELECT查詢得到的輸出結果.
圖23:列出了每個產品的總價
UDF也可以返回表列數據.比如,我們可以創建一個UDF返回屬於某個category的所有產品:
CREATE FUNCTION dbo.udf_GetProductsByCategoryID ( @CategoryID int ) RETURNS TABLE AS RETURN ( SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE CategoryID = @CategoryID )
該udf_GetProductsByCategoryID用戶函數接受一個@CategoryID輸入參數,返回SELECT查詢的結果.一旦創建之後,該UDF就可以在SELECT查詢的FROM (或 JOIN)之句裡引用.下面的示例返回飲料類所屬的每個產品的ProductID, ProductName,CategoryID值:
SELECT ProductID, ProductName, CategoryID FROM dbo.udf_GetProductsByCategoryID(1)
我已經將該udf_GetProductsByCategoryID用戶函數添加到Northwind數據庫。圖24顯示的是在Management Studio運行上述SELECT查詢的結果.返回表列數據的UDF放在Table-value Functions文件夾裡.
圖24:飲料類產品的ProductID, ProductName,CategoryID都列出來了
注意:關於創建和使用UDF的更多詳情,請參閱文章《Intro to User-Defined Functions》和《dvantages and Drawbacks of User-Defined Functions》
第十步:創建一個Managed UDF
上面示例裡創建的udf_ComputeInventoryValue和 udf_GetProductsByCategoryID用戶函數都是T-SQL數據庫對象.SQL Server 2005同樣支持managed UDF,我們可以將其添加到ManagedDatabaseConstructs工程,就像在第三和第五步做的那樣.在這一步,我們將用managed code執行udf_ComputeInventoryValue用戶函數.
在解決資源管理器裡右鍵單擊,選擇“Add a New Item”,在對話框裡選User-Defined Function模板,將新UDF文件命名為udf_ComputeInventoryValue_Managed.cs.
圖25:向ManagedDatabaseConstructs工程添加一個Managed UDF
該User-Defined Function模板將創建一個名為UserDefinedFunctions的partial class類,同時還有一個方法,該方法的名字與類文件的名字一樣(就本例而言,為udf_ComputeInventoryValue_Managed)。該方法有一個SqlFunction特性, 這就標明了該方法是一個managed UDF.
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString udf_ComputeInventoryValue_Managed() { // Put your code here return new SqlString("Hello"); } }
該udf_ComputeInventoryValue方法目前返回一個SqlString對象,且不接受任何的輸入參數.我們將對其進行更新以包含3個參數——UnitPrice, UnitsInStock,和Discontinued,並返回一個SqlMoney對象.該方法用到邏輯與上面的T-SQL類型的udf_ComputeInventoryValue用戶函數的一樣.
[Microsoft.SqlServer.Server.SqlFunction] public static SqlMoney udf_ComputeInventoryValue_Managed (SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued) { SqlMoney inventoryValue = 0; if (!UnitPrice.IsNull && !UnitsInStock.IsNull) { inventoryValue = UnitPrice * UnitsInStock; if (Discontinued == true) inventoryValue = inventoryValue * new SqlMoney(0.5); } return inventoryValue; }
我們注意到UDF方法的輸入參數就是其對應的SQL類型:UnitPrice的類型為SqlMoney、UnitsInStock的類型為SqlInt16、Discontinued的類型為SqlBoolean.這些類型反映了這些列在Products表裡定義的類型:UnitPrice列的類型為money、UnitsInStock列的類型為smallint、Discontinued列的類型為bit.
代碼首先創建了一個SqlMoney類型的名為inventoryValue的實例,並賦值為0.由於Products表允許UnitsInPrice 和 UnitsInStock列的值為NULL,因此我們首先通過SqlMoney對象的IsNull屬性來檢查這2列是否包NULL值。如果這2列的值都不為NULL,那麼UnitPrice乘以UnitsInStock就得到了inventoryValue的值,另外如果Discontinued為true的話,inventoryValue的值減半.
注意:由於SqlMoney對象只允許2個SqlMoney實例相乘,它不允許一個SqlMoney實例與一浮點數(literal floating-point)相乘,所以在代碼裡我們用一個值為0.5的SqlMoney實例與inventoryValue相乘.
第11步驟:配置Managed UDF
現在我們已經創建了一個managed UDF,我們將把它配置給Northwind數據庫.就像我們在第四步看到的那樣,在解決資源管理器裡,在工程名上右鍵單擊選“Deploy”.
完成後,返回到SQL Server Management Studio,刷新Scalar-valued Functions文件夾.你就會看到2個實體:
.dbo.udf_ComputeInventoryValue——在第九步創建的T-SQL UDF
.dbo.udf ComputeInventoryValue_Managed——我們在第10步剛剛創建的managed UDF
對該managed UDF進行測試,在Management Studio裡執行如下的查詢:
SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue_Managed( UnitPrice, UnitsInStock, Discontinued ) as InventoryValue FROM Products ORDER BY InventoryValue DESC
該命令使用的是udf ComputeInventoryValue_Managed函數而不是udf_ComputeInventoryValue函數,但是輸出結果都一樣,可以查看圖23的截屏.
第12步:調試Managed Database Objects
在第72章我們探討了通過Visual Studio調試SQL Server的3種模式:直接數據庫調試、應用程序調試、通過SQL Server Project調試.Managed database objects不能用直接數據庫模式調試,不過可以從一個客戶端程序和SQL Server Project來調試.為了使調試正常工作,SQL Server 2005 數據庫要求必須允許SQL/CLR調試.記得當我們最初創建ManagedDatabaseConstructs工程時,Visual Studio詢問我們是否激活SQL/CLR調試(見第2步的圖6).我們可以在Server Explorer窗口裡在數據庫上右鍵單擊,以對該配置進行修改.
圖26:確保數據庫激活SQL/CLR調試
設想我們想調試GetProductsWithPriceLessThan存儲過程.我們首先要在GetProductsWithPriceLessThan方法的代碼裡設置斷點.
圖27:在GetProductsWithPriceLessThan方法裡設置斷點
首先我們考察從SQL Server Project裡調試managed database objects.
由於我們的解決資源管理器裡包含2個工程——ManagedDatabaseConstructs SQL Server Project以及我們的website.為了從SQL Server Project進行調試,當調試時我們需要引導Visual Studio開啟ManagedDatabaseConstructs SQL Server Project.在解決資源管理器裡的ManagedDatabaseConstructs project上點擊右鍵,選“Set as StartUp Project”項.
當從調試器打開ManagedDatabaseConstructs project時,它執行Test.sql文件的SQL statements,該文件位於Test Scripts文件夾.比如,要測試GetProductsWithPriceLessThan存儲過程的話,將Test.sql文件的內容替換為下面的statement,這些statement調用GetProductsWithPriceLessThan存儲過程,其輸入參數@CategoryID的值為14.95:
exec GetProductsWithPriceLessThan 14.95
一旦將上面的腳本鍵入Test.sql文件,點Debug菜單裡的“Start Debugging”項,或按F5或是工具欄上的綠色圖標啟動調試.這將在資源管理器裡構建工程,將該managed database objects配置給Northwind數據庫,然後執行Test.sql腳本.此時,將會遇到斷點,我們可以進入GetProductsWithPriceLessThan方法,檢查輸入參數的值等等.
圖28:碰到GetProductsWithPriceLessThan方法裡的斷點
為了從客戶端程序調試一個SQL database object,數據庫務必要配置為支持應用程序調試.在服務器資源管理器裡,在數據庫上右鍵單擊,確保選中“Application Debugging”項。另外,我們還要將ASP.NET應用程序與SQL Debugger結合起來,而且關閉連接池.這些步驟我們在第74章的第2步裡詳細探討過了.
一旦你配置完ASP.NET應用程序和數據庫.設置ASP.NET website為啟動方案.如果你登錄一個調用設置了斷點的managed objects的頁面的話,該程序就會碰到斷點,並轉換到調試器,在調試器裡你可以進入代碼,就像圖28那樣.
第13步:手動編譯並配置Managed Database Objects
使用SQL Server Projects,我們可以很容易的創建、編譯、配置managed database objects.不過遺憾的是,只有在Visual Studio的Professional 和 Team Systems這2個版本才可以使用SQL Server Projects.如果你使用的是Visual Web Developer 或 Standard Edition版本,並且打算使用managed database objects的話,你需要手動創建並配置它們.這將包括4個步驟:
1.創建一個文件來存放managed database object的源代碼
2.將object進行編譯
3.將編譯文件注冊到SQL Server 2005數據庫
4.在SQL Server裡創建一個數據庫對象,並指向編譯文件裡的相應的方法
為便於演示,我們將創建一個新的managed stored procedure,返回那些UnitPrice值高於指定值的產品.在你的電腦上創建一個名為GetProductsWithPriceGreaterThan.cs 的新文件,並鍵入如下的代碼(你可以使用Visual Studio, Notepad或任何的文本編輯器來進行):
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void GetProductsWithPriceGreaterThan(SqlMoney price) { // Create the command SqlCommand myCommand = new SqlCommand(); myCommand.CommandText = @"SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE UnitPrice > @MinPrice"; myCommand.Parameters.AddWithValue("@MinPrice", price); // Execute the command and send back the results SqlContext.Pipe.ExecuteAndSend(myCommand); } }
這些代碼與我們在第五步創建的GetProductsWithPriceLessThan方法的代碼很相似.唯一的不同在於:方法名不同、WHERE字句不同、以及查詢使用的參數名不同.返回到GetProductsWithPriceLessThan方法,其WHERE字句為“WHERE UnitPrice < @MaxPrice”. 而在這裡,GetProductsWithPriceGreaterThan方法裡,代碼為“WHERE UnitPrice > @MinPrice”.
我們現在需要將該類進行編譯.在命令行裡導航到你存放GetProductsWithPriceGreaterThan.cs文件的根目錄,並使用C#編譯器(csc.exe)來進行編譯:
csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs
如果包含csc.exe的文件夾沒有位於系統路徑,那你將必須完全引用其路徑,%WINDOWS%/Microsoft.NET/Framework/version/,比如:
C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs
圖29:對GetProductsWithPriceGreaterThan.cs文件進行編譯
其中,/t標記指定將C# class類編譯為一個DLL(而不是可執行文件)。而 /out標記指定了編譯後文件的名稱.
注意:除了用命令行來編譯GetProductsWithPriceGreaterThan.cs class類外,我們還可以使用Visual C# Express Edition或在Visual Studio Standard Edition版裡創建一個單獨的Class Library project.S?ren Jacob Lauritsen為我們提供了一個Visual C# Express Edition project,它包含了GetProductsWithPriceGreaterThan存儲過程,以及我們在第3、5和10步裡創建的那2個managed stored procedures 和 UDF.此外還包含了添加相應數據庫對象必需的T-SQL commands.
將代碼編譯完後,我們需要將其注冊到SQL Server 2005數據庫.可以通過T-SQL,使用命令CREATE ASSEMBLY,或通過SQL Server Management Studio. 我們來看使用Management Studio的情況.
在Management Studio裡,展開Northwind數據庫裡的Programmability文件夾,其內有一個Assemblies文件夾。在該文件夾上右鍵單擊,選“New Assembly”.這將開啟New Assembly對話框(見圖30),點擊Browse按鈕,選擇我們剛剛編譯的ManuallyCreatedDBObjects.dll文件,再點OK完成添加.在Object Explorer裡你應該可以看到ManuallyCreatedDBObjects.dll文件.
圖30:將ManuallyCreatedDBObjects.dll添加到數據庫
圖31:ManuallyCreatedDBObjects.dll展示在Object Explorer裡
完成後,我們要將一個存儲過程與編譯文件裡的GetProductsWithPriceGreaterThan方法聯系起來.為此,打開一個new query窗口,執行下面的腳本:
CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan] ( @price [numeric](18, 0) ) WITH EXECUTE AS CALLER AS EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan] GO
這將在Northwind數據庫裡創建一個名為etProductsWithPriceGreaterThan的新存儲過程,並將其與GetProductsWithPriceGreaterThan方法聯系起來(該方法屬於編譯文件ManuallyCreatedDBObjects)
執行完腳本後,在Object Explorer裡刷新Stored Procedures文件夾。你將看到一個新的存儲過程——GetProductsWithPriceGreaterThan,在該存儲過程旁邊有一個鎖的圖標.測試該存儲過程,在查詢窗口鍵入並執行如下的腳本:
exec GetProductsWithPriceGreaterThan 24.95
如圖32所示,上述命令將那些價格高於24.95的產品展示出來.
圖32:在對象資源管理器的顯示的列表
結語:
Microsoft SQL Server 2005整合了Common Language Runtime (CLR),它允許用managed code來創建數據庫對象.在以前,要創建數據庫對象,我們只能使用T-SQL, 但是現在我們可以使用.NET編程語言,比如C#來進行創建. 在本文我們創建了2個managed stored procedures以及一個managed User-Defined Function.
Visual Studio的SQL Server Project類型可以很容易的創建、編譯、配置managed database objects,此外還支持多種調試.遺憾的是,SQL Server Project類型只有在Visual Studio的Professional 和 Team Systems版本才有。對於Visual Web Developer 或Standard版的用戶而言,要手工完成這些步驟,就想我們在第13步看到的那樣.
祝編程快樂!
作者簡介
本系列教程作者 Scott Mitchell,著有六本ASP/ASP.NET方面的書,是4GuysFromRolla.com的創始人,自1998年以來一直應用 微軟Web技術。大家可以點擊查看全部教程《[翻譯]Scott Mitchell 的ASP.NET 2.0數據教程》,希望對大家的學習ASP.NET有所幫助。