本文英文原版及代碼下載：

http://www.asp.net/learn/data-access/tutorial-75-cs.aspx

Scott Mitchell 的ASP.NET 2.0數據教程之75:用Managed Code創建存儲過程和用戶自定義函數

導言：

數據庫，比如Microsoft’s SQL Server 2005使用Transact-Structured Query Language (T-SQL)來插入、修改、檢索數據.絕大多數數據庫系統都包含constructs來對一系列的SQL statements進行分組，這些statements可以作為單獨的單元來執行.存儲過程就是一個例子，另一個例子是用戶自定義函數(UDFs), 我們將在第9步進行詳細的探討.

SQL是設計來處理一系列數據的. SELECT,UPDATE,和DELETE statements適用于相應表的所有記錄，且通過WHERE字句來進行篩選.也有很多的特性被設計來一次處理一條記錄，或操作標量數據(scalar data).比如CURSORs允許一次遍歷所有的記錄.字符串操作功能，比如LEFT, CHARINDEX, 以及PATINDEX用來處理標量數據.SQL也包含了控制流聲明，比如IF和WHILE.

在Microsoft SQL Server 2005之前，存儲過程和用戶自定義函數UDFs只能當做一個T-SQL statements集來創建，而SQL Server 2005設計時包含Common Language Runtime (CLR)。因此，對一個SQL Server 2005數據庫里的存儲過程和用戶定義方法，我們可以用managed code來進行創建。那就也說你可以在一個C#類里創建一個存儲過程或用戶定義函數.這樣一來我們就可以在.NET Framework或你自己定義的類里面使用這些存儲過程或方法.

在本文我們將考察如何創建存儲過程和用戶定義函數，以及如何將它們整合進數據庫Northwind.讓我們開始吧。

注意：
管理數據庫對象(Managed database objects)與SQL數據庫里包含的相對應的數據庫對象比較起來有一些優勢，主要體現在：使用的語言更豐富、熟悉；可以使用現有的代碼和邏輯.但是在處理那些并不包含很多邏輯的一系列數據時，其效率可能要低一些.關與managed code相較T-SQL而言的優勢，請參閱文章《Advantages of Using Managed Code to Create Database Objects》( http://msdn2.microsoft.com/en-us/library/k2e1fb36(VS.80).aspx )

第一步：將Northwind數據庫移出App_Data文件夾

本教程到目前為止使用的是放在App_Data文件夾里的Microsoft SQL Server 2005 Express版本的數據庫.

然而在本教程，我們將Northwind數據庫移出App_Data文件夾，再使用一個被注冊為SQL Server 2005 Express版本數據庫的實例。雖然我們不移出的話也可以執行本文的這些步驟，不過將其注冊為一個SQL Server 2005 Express版本數據庫的實例的話，這些步驟就要簡單的多了。

本文下載代碼里包含2個數據庫文件:NORTHWND.MDF 和 NORTHWND_log.LDF，將它們放在一個叫DataFiles的文件夾里，先關閉Visual Studio，再將NORTHWND.MDF 和 NORTHWND_log.LDF文件從根目錄的App_Data文件夾里移出到根目錄以外的其它文件夾里。完成后，我們需要將Northwind數據庫注冊為SQL Server 2005 Express版本數據庫的實例。為此我們要用到SQL Server Management Studio.如果你沒有安裝的話，可以在此下載并安裝：( http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796 )。

打開SQL Server Management Studio，如圖1所示，Management Studio首先問我們連接什么服務器，在server name里鍵入“localhost\SQLExpress” ，在Authentication下拉列表里選“Windows Authentication” 。點Connect.

圖1：連接恰當的數據庫實例

一旦連接后，Object Explorer窗口將會把SQL Server 2005 Express版本數據庫實例的信息顯示出來，比如databases, security information, management options等.

我們需要將DataFiles文件夾里的Northwind數據庫作為SQL Server 2005 Express版本數據庫實例。在Databases文件夾里右鍵單擊，選“Attach”項。這將打開Attach Databases對話框.點 Add 按鈕，找到NORTHWND.MDF文件，再點OK。這樣，你的屏幕看起來和圖2差不多：

圖2：連接到合適的數據庫實例

注意：
當通過Management Studio連接到SQL Server 2005 Express版本的數據庫實例時，Attach Databases對話框不允許你瀏覽用戶私人文件目錄(user profile directories),比如My Documents.因此，一定要將NORTHWND.MDF 和 NORTHWND_log.LDF文件放在一個非用戶私人文件目錄里.

點OK完成后，Attach Databases對話框將會關閉，Object Explorer將會將新添加的數據庫列出來。問題來了，它的名字可能是這樣的：9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF，我們將其重命名為“Northwind”，方法是在該數據庫右鍵單擊，選“Rename”.

圖3：將數據庫重命名為“Northwind”

第二步：在Visual Studio里創建一個新的解決方案和SQL Server Project

要在SQL Server 2005里創建管理存儲過程或用戶定義函數，我們要在一個類里用C# 代碼寫這些存儲過程和用戶定義函數。一旦寫完后，我們需要將該類編譯為一個 .dll文件，將該編譯文件注冊到一個SQL Server 數據庫，然后在數據庫里創建一個存儲過程或用戶定義函數以指向編譯文件里對應的方法。這些步驟要手工完成。我們可以在文本編輯器里寫代碼，在命令行里用C#編譯器 (csc.exe)對其進行編譯；用CREATE ASSEMBLY 命令或從Management Studio里將其注冊到數據庫.添加存儲過程或用戶定義函數的方法類似.幸運的是 Visual Studio的Professional 和 Team Systems 版本包含一個SQL Server Project類型，它可以自動的完成這些工作.在本文，我們將使用SQL Server Project類型來創建一個管理存儲過程和用戶定義函數。

注意：
如果你使用的是Visual Studio的Visual Web Developer 或 Standard版本，你就要手動完成了。在Step 13，我們將詳細介紹手動完成的細節.我們鼓勵你從Steps 2 看到Steps 12 ，再看Step 13.因為Steps 2 到Steps 12包含了很重要的SQL Server 配置說明，不管你用的是什么版本.

打開Visual Studio. 從 File 菜單，選New Project以打開New Project對話框(見圖4).點到Database工程類型，在右邊的Templates列表，選擇創建一個新的SQL Server Project.我將其命名為ManagedDatabaseConstructs并放在一個名為Tutorial75的解決方案里.

圖4：創建一個新的SQL Server Project

在New Project對話框里點OK按鈕，創建該解決方案和SQL Server Project.

一個SQL Server Project依賴于一個具體的數據庫.因此，接下來我們要指定該信息。如圖5所示，New Database Reference對話框里指向了Northwind數據庫，也就是我們在第一步里注冊的SQL Server 2005 Express版本數據庫實例.

圖5：將SQL Server Project與Northwind數據庫聯系起來

為了對我們將要在本工程創建的管理存儲過程和用戶自定義函數進行調試，我們需要激活SQL/CLR調試支持.無論什么時候將一個SQL Server Project與新的數據庫聯系起來時(就像我們在圖5做的那樣)，Visual Studio會詢問我們是否激活SQL/CLR調試(如圖6)，選Yes.

圖6：激活SQL/CLR調試

此時，這個新的SQL Server Project已經添加到解決方案里了。其包含一個Test Scripts文件夾，文件夾里是一個Test.sql文件。它用來對本工程添加的管理數據庫對象進行調試，我們將在第12步考察調試.

我們現在可以對該工程添加新的管理存儲過程和用戶自定義函數.不過在此之前，我們要將現有的web應用程序包含進解決方案。在 File 菜單里選Add項，再選Existing Web Site.瀏覽到相應的文件夾，點OK.如圖7所示，這將更新解決方案以包含2個工程：即website和名為ManagedDatabaseConstructs的SQL Server Project.

圖7：該解決方案現在包含2個工程

Web.config文件里的NORTHWNDConnectionString值當前引用的是App_Data文件夾里的NORTHWND.MDF。由于我們已經將其從App_Data文件夾移出了，其注冊為SQL Server 2005 Express版本數據庫實例，因此我們需要相應的更新NORTHWNDConnectionString值。打開Web.config文件，改動NORTHWNDConnectionString值，像這樣：“Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True”.

完成后，你的Web.config文件的<connectionStrings>節點看起來和下面的差不多：

<connectionStrings>
<add name="NORTHWNDConnectionString" connectionString=
"Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
Integrated Security=True;Pooling=false"
providerName="System.Data.SqlClient" />
</connectionStrings>

注意：
就像在上一章探討的一樣，當從一個客戶端程序——比如一個ASP.NET website，調試一個SQL Server對象時，我們需要關閉連接池。上面的連接字符串里我們關閉了連接池(“Pooling=false”). 如果你不打算從ASP.NET website調試管理存儲過程和用戶自定義函數的話，激活連接池.

第三步：創建一個Managed Stored Procedure

要向Northwind數據庫添加一個管理存儲過程的話，我們首先要創建一個存儲過程作為該SQL Server Project里的一個方法。從解決資源管理器里，右鍵單擊 ManagedDatabaseConstructs工程，選“添加新項”，這將展示Add New Item 對話框，其列出了可以添加到該工程的各種管理數據庫對象的類型，如圖8所示，包括stored procedures 、User-Defined Functions等.我們來創建一個存儲過程，用來簡單的將那些處于discontinued狀態的產品返回，將該存儲過程文件命名為GetDiscontinuedProducts.cs.

圖8：添加一個新的存儲過程，名為GetDiscontinuedProducts.cs

這將創建一個新的C# class類文件，如下：

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 GetDiscontinuedProducts()
{
// Put your code here
}
};

我們注意到該存儲過程作為一個static方法來執行，且位于一個名為StoredProcedures的部分類(partial class)文件之內. 此外，該 GetDiscontinuedProducts方法有一個SqlProcedure特性, 這就標明了該方法是一個存儲過程.

下面的代碼創建了一個SqlCommand對象，設其CommandText為一個SELECT查詢，以返回Products table表里所有Discontinued列為1的記錄.它然后執行該命令并將結果返回給客戶端程序.添加這些代碼到GetDiscontinuedProducts方法.

// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText =
@"SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE Discontinued = 1";

// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);

所有的管理數據庫對象都可以使用SqlContext對象，該對象展示“調用者”(caller)的內容；而SqlContext又可以通過其Pipe屬性來訪問一個SqlPipe對象，該對象用來在SQL Server數據庫和調用程序之間傳遞信息；而ExecuteAndSend方法，就像其名字暗示的那樣，執行傳入的SqlCommand對象，并將結果返回給客戶端程序.

注意：
管理數據庫對象最適合做這種存儲過程和用戶定義函數——使用procedural logic邏輯而不是set-based logic邏輯.所謂Procedural logic邏輯包括處理一系列一行行(on a row-by-row basis)的數據或者處理標量數據(scalar data).然而，我們剛剛創建的GetDiscontinuedProducts方法，并未使用Procedural logic邏輯。其實該方法最理想的是作為一個T-SQL存儲過程來執行.之所以作為一個管理存儲過程來執行，是為了示范創建和配置管理存儲過程所必要的步驟.

步驟4：配置Managed Stored Procedure

代碼完成后我們準備將其配置給Northwind數據庫.“Deploy”項執行的具體步驟我們將在第13步講明白。進入解決資源管理器，在ManagedDatabaseConstructs工程名上右鍵單擊，選“Deploy”項，然而，可能會出現如下的錯誤消息：“Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.”

該出錯信息發生試圖將編譯文件注冊到Northwind數據庫時.為了將一個編譯文件注冊到一個SQL Server 2005數據庫，該數據庫的compatibility level必須設置為90.默認下，一個新的SQL Server 2005數據庫的compatibility level為90.而Microsoft SQL Server 2000使用的數據庫的默認compatibility level為80.由于使用的Northwind數據庫最初是一個Microsoft SQL Server 2000數據庫，其compatibility level被設置為80，因此需要設置為90以便于進行注冊.要更新數據庫的compatibility level，在Management Studio里打開一個New Query窗口，輸入：exec sp_dbcmptlevel 'Northwind', 90點擊工具欄上的執行圖標以運行上述查詢.

圖9：更新Northwind數據庫的Compatibility Level

更新后重新部署該SQL Server Project，這次應該不會出錯了.返回SQL Server Management Studio，在Object Explorer里的Northwind 數據庫上右鍵單擊，選“刷新”.接下來，找到Programmability文件夾，再展開Assemblies文件夾。如圖10所示，Northwind數據庫現在包含了一個由ManagedDatabaseConstructs工程生成的編譯文件.

圖10：該ManagedDatabaseConstructs編譯文件現在注冊到Northwind數據庫

也來打開Stored Procedures文件夾。你將會看到名為GetDiscontinuedProducts的存儲過程。該存儲過程是在部署的時候創建的，它指向ManagedDatabaseConstructs編譯文件里的GetDiscontinuedProducts方法.當執行GetDiscontinuedProducts存儲過程時，它反過來執行GetDiscontinuedProducts方法.由于它是一個管理存儲過程(managed stored procedure),不能通過Management Studio來對其進行編輯(因此，在存儲過程名旁邊有一個鎖的圖標)

圖11：GetDiscontinuedProducts存儲過程展示在Stored Procedures文件夾

還有一個障礙喲克服：該數據庫被配置為阻止執行managed code.我們來做一個實驗。打開一個new query窗口，執行GetDiscontinuedProducts存儲過程.你將會收到如下的錯誤信息：“Execution of user code in the .NET Framework is disabled. Enable ‘clr enabled’configuration option.”

讓我們檢查Northwind數據庫的配置信息，在查詢窗口鍵入并運行命令“exec sp_configure”。其顯示“clr enabled”目前設置為0.

圖12：“clr enabled”目前設置為0.

我們注意到每條配置(如圖12)都有4個值：“minimum”、“maximum”、“config”、“run” 值.要更新“clr enabled”配置的“config”值，執行如下的命令：exec sp_configure 'clr enabled', 1

如果你再運行“exec sp_configure”的話，你將看到上述聲明將“clr enabled”配置的“config”值設為1，而“run”值仍然為0.因此，我們需要執行RECONFIGURE命令,它將會把“ run”值設為目前的“config”值.在查詢窗口輸入“RECONFIGURE”，再點工具欄上的執行圖標.如果你運行“exec sp_configure”的話，現在你可以看到“clr enabled”配置的“config” 和 “run”值都為1.

完成“clr enabled”配置后，我們準備運行GetDiscontinuedProducts存儲過程.在查詢窗口鍵入并運行命令“exec GetDiscontinuedProducts”.調用該存儲過程將導致執行GetDiscontinuedProducts方法里相應的managed code.代碼發出一個SELECT查詢并返回所有處于discontinued狀況的產品，并將數據返回給調用程序——具體到本例，就是SQL Server Management Studio.Management Studio將接收到的數據展示在Results窗口.

圖13：GetDiscontinuedProducts存儲過程返回所有處于Discontinued狀態的產品

第五步：創建接收輸入參數的Managed Stored Procedures

我們在本教程創建的很多查詢和存儲過程都使用參數.比如，在第67章，我們創建了一個名為GetProductsByCategoryID的存儲過程，它接收一個名為@CategoryID的輸入參數.該存儲過程返回那些其CategoryID值與@CategoryID吻合的產品.

要創建接收輸入參數的managed stored procedure，僅僅在定義方法時指定這些參數即可.我們來做個演示，在ManagedDatabaseConstructs工程里添加一個名為GetProductsWithPriceLessThan的另一個managed stored procedure.該managed stored procedure接收一個指定了價格的參數，返回所有的其UnitPrice列低于參數值的產品.

我們來進行添加.在ManagedDatabaseConstructs工程名上右鍵單擊，選“添加新存儲過程”.將文件命名為GetProductsWithPriceLessThan.cs.就像我們在圖3看到的那樣，這將創建一個新的C# class類文件.

更新GetProductsWithPriceLessThan方法以使其接收一個名叫price的SqlMoney類型的輸入參數.代碼如下：

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceLessThan(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 < @MaxPrice";

myCommand.Parameters.AddWithValue("@MaxPrice", price);

// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);
}

GetProductsWithPriceLessThan方法的生命代碼和我們在第三步創建的GetDiscontinuedProducts方法的聲明代碼很相似.唯一的不同在于GetProductsWithPriceLessThan方法接收一個輸入參數(price), 且SqlCommand的查詢里也包含了一個參數(@MaxPrice).

完成代碼添加后，重新部署SQL Server Project.接下來，返回到SQL Server Management Studio并刷新Stored Procedures文件夾.你將會看到一個新的查詢——GetProductsWithPriceLessThan.從查詢窗口，鍵入并執行命令“exec GetProductsWithPriceLessThan 25”，它會將所有價格低于25的產品顯示出來，如圖14所示.

圖14：價格低于25的產品都顯示出來

第六步：從數據訪問層調用Managed Stored Procedure

此時，我們已經向ManagedDatabaseConstructs工程添加了GetDiscontinuedProducts 和 GetProductsWithPriceLessThan這2個managed stored procedures，并把它們注冊到Northwind SQL Server數據庫.我們同樣從SQL Server Management調用它們(見圖13和14)。為了使我們的ASP.NET應用程序能調用這些managed stored procedures,因此我們需要將它們添加到體系的數據訪問層和業務邏輯層.在這一步，我們向類型化數據集NorthwindWithSprocs的ProductsTableAdapter添加2個新的方法.在第七步，我們將添加相應的方法到業務邏輯層.

在Visual Studio里打開類型化數據集NorthwindWithSprocs，向ProductsTableAdapter添加一個名為GetDiscontinuedProducts的方法.
注意：
由于我們已經將Northwind數據庫移出了App_Data文件夾，所以我們應該在Web.config文件里對連接字符串進行相應的更新。在第二步我們探討了更新Web.config文件里的NORTHWNDConnectionString的值.如果你忘記了更新的話，當你試圖向TableAdapter添加新方法的時候，你將看到這樣的錯誤信息：“Failed to add query. Unable to find connection ‘NORTHWNDConnectionString’ for object ‘Web.config’”.要克服這個問題， 需要更新Web.config文件的NORTHWNDConnectionString值.就像在第二步探討的那樣，再重新向TableAdapter添加新方法，這次就不會出錯了.

添加新方法的時候，TableAdapter查詢配置向導首先詢問我們如何訪問數據庫。由于我們已經創建并注冊了GetDiscontinuedProducts存儲過程，因此選“Use existing stored procedure”項，點Next.

圖15：選“Use existing stored procedure” 項

接下來要我們選要調用的存儲過程。在左邊下拉列表里選存儲過程GetDiscontinuedProducts.

圖16：選GetDiscontinuedProducts存儲過程

由于GetDiscontinuedProducts將返回一系列的記錄行，我們選第一項(“Tabular data”) ，再點Next.

圖17: 選“Tabular Data” 項

最后，向導要我們為方法命名，選中這2項并分別命名為FillByDiscontinued 和 GetDiscontinuedProducts.點Finish完成向導.

圖18：將方法命名為FillByDiscontinued 和 GetDiscontinuedProducts

采用同樣的方法，為名為GetProductsWithPriceLessThan的managed stored procedure在ProductsTableAdapter里添加FillByPriceLessThan和 GetProductsWithPriceLessThan這2個方法.

圖19顯示的是為GetDiscontinuedProducts 和 GetProductsWithPriceLessThan這2個managed stored procedures在ProductsTableAdapter里添加各種方法后的界面.

圖19: ProductsTableAdapter包含了新添加的方法

第七步：向業務邏輯層添加相應的方法

現在我們已經更新了數據訪問層，我們還需要向業務邏輯層添加相應的方法.向ProductsBLLWithSprocs class類添加如下的2種方法：

[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
return Adapter.GetDiscontinuedProducts();
}

[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable
GetProductsWithPriceLessThan(decimal priceLessThan)
{
return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}

這2種方法都簡單的調用數據訪問層對應的方法，并返回ProductsDataTable實例.而2種方法上面附加的DataObjectMethodAttribute標記的作用是將方法包含到 ObjectDataSource控件的配置數據源向導的SELECT標簽里的下拉列表項里. （上部分完）

Scott Mitchell 的ASP.NET 2.0數據教程之75:用Managed Code創建存儲過程和用戶自定義函數(上部分)