使用托管代码创建存储过程和用户定义函数74

简介

有些数据库 ，如Microsoft 的SQL Server 2005，使用 Transact-Structured Query Language (T-SQL) 来插入、修改和检索数据。大多数数据库系统都含有这样的结构：这些结构可将一系列SQL语句组成一组使其作为一个单个的、可复用的单元来执行。存储过程就是这样一个例子。另一个例子是用户定义函数(UDF) ，我们将在步骤 9中详细地讨论该结构。

SQL 的核心设计针对的是数据集。SELECT 、UPDATE 和 DELETE 本质上应用于相应表中的所有记录并且只受到WHERE 子句的限制。然而，仍有许多语言功能被设计成每次只处理一条记录并且处理标量数据。我们可以使用 指针 来每次一个地循环处理一组记录。字符串操作函数，如LEFT 、CHARINDEX 和PATINDEX ，针对的是标量数据。SQL 还包括流程控制语句，如 IF 和 WHILE 。

在Microsoft SQL Server 2005 以前，只能以一系列的 T-SQL 语句来定义存储过程和UDF 。而SQL Server 2005 旨在实现与Common Language Runtime (CLR) 的集成；CLR 是所有.NET 汇编程序所使用的运行时环境。其结果是，可以使用托管代码来在SQL Server 2005 数据库中创建存储过程和UDF 。就是说，用户可以 Visual Basic 类中的一个方法来创建存储过程或 UDF 。这使得存储过程和UDF 能够利用 .NET Framework 中以及定制类中的功能。

本教程中，我们将探讨怎样创建托管存储过程和UDF ，以及怎样将它们集成到我们的 Northwind 数据库中。让我们开始吧！

注意： 与其对应的 SQL 对象相比，使用托管数据库对象具有一定的优势。其主要优势在于：用户可以使用丰富而熟悉的编程语言，可以重用现有的代码和逻辑。但是，在处理数据集而不涉及很多过程逻辑时，托管数据库对象可能效率要低一些。对于与T-SQL 相比，使用托管代码的优点的更详细的讨论，请查阅 使用托管代码来创建数据库对象的优点 。

步骤 1：将 Northwind 数据库移出 App_Data

迄今为止，我们所有的教程使用的都是web 应用程序的 App_Data 文件夹中的一个Microsoft SQL Server 2005 Express Edition 数据库文件。将数据库存放于 App_Data 中使得这些教程易于发布和管理，其原因是，所有文件都存放于同一目录中因而对教程中的内容进行测试时不需要额外的配置步骤。

然而，在本教程中，我们将 Northwind 数据库从 App_Data 中移出并将其显式地注册到 SQL Server 2005 Express Edition 数据库实例。尽管当该数据库存放于App_Data 文件夹下时我们可以执行本教程中的步骤，但是将该数据库显式地注册到SQL Server 2005 Express Edition 数据库实例会使许多步骤更为简单。

本教程的相关下载包含两个数据库文件– NORTHWND.MDF 和 NORTHWND_log.LDF ，这两个文件存放于一个名为 DataFiles 的文件夹下。如果您一直使用的是自己对这些教程的实现，请关闭Visual Studio 后将 NORTHWND.MDF 和 NORTHWND_log.LDF 文件从website 的App_Data 文件夹移动到该 website 之外的另一个文件夹下。将这两个数据库文件移动到另一文件夹后，我们要将Northwind 数据库注册到SQL Server 2005 Express Edition 数据库实例中。可以通过 SQL Server Management Studio 来完成该注册。如果您的计算机上安装的是SQL Server 2005 的非Express 版本，您很可能已经安装有了 Management Studio 。如果您的计算机上只有SQL Server 2005 Express Edition ，则需花点时间在此下载并安装 Microsoft SQL Server Management Studio Express 。

启动SQL Server Management Studio 。如图 1 所示，Management Studio 启动时会询问连接到哪个服务器。在服务器名称处输入“localhost\SQLExpress” ，在Authentication 下拉列表中选择 “Windows Authentication” ，然后单击Connect 。

图1 ：连接到相应的数据库实例

连接完成后，Object Explorer 窗口将会列出 SQL Server 2005 Express Edition 数据库实例的有关信息，包括其数据库、安全信息、管理选项等等。

我们需要将DataFiles 文件夹下（或者您将数据库移动到的任何其它文件夹下）的Northwind 数据库附加到该SQL Server 2005 Express Edition 数据库实例。右键单击 Databases 文件夹，从关联菜单中选择Attach 选项。系统弹出 Attach Databases 对话框。单击 Add 按扭，找到相应的 NORTHWND.MDF 文件后单击OK 。此时屏幕应如图 2 所示。

图2 ：连接到相应的数据库实例

注意： 在通过 Management Studio 连接到 SQL Server 2005 Express Edition 实例后，Attach Databases 对话框不允许向下展开到用户配置文件目录，如My Documents 。因此，请确保将NORTHWND.MDF 和 NORTHWND_log.LDF 这两个文件存放到非用户配置文件目录下。

单击OK 按扭以附加该数据库。Attach Databases 对话框关闭，此时，Object Explorer 应该列出刚才附加的数据库了。Northwind 数据库可能有类似这样的名称：9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF 。右键单击该数据库并选择Rename ，将该数据库重命名为 “Northwind” 。

图3 ：将数据库重命名为 “Northwind”

步骤 2：在 Visual Studio 中创建一个新的 Solution 和 SQL Server Project

为了在SQL Server 2005 中创建托管存储过程或 UDF ，我们在一个类中用 C#代码编写存储过程或UDF 的逻辑。代码编写完成后，需要将该类编译为一个汇编文件（一个.dll 文件），将该汇编文件注册到SQL Server 数据库，然后在数据库中创建一个存储过程对象或UDF 对象以指向该汇编文件中对应方法。所有这些步骤都可手工完成。我们可以在任何一个文本编辑器中编写该代码，在命令行上用C# 编译器 (csc.exe) 编译该代码，用 CREATE ASSEMBLY 命令或从Management Studio 将其注册到数据库。添加存储过程对象和UDF 对象的方法类似。幸运的是，Visual Studio 的Professional 版本和 Team Systems 版本包含一个 SQL Server Project 类型，该类型可使这些工作自动完成。本教程中，我们将使用SQL Server Project 类型来创建一个托管存储过程和 UDF 。

注意： 而如果您使用的是 Visual Web Developer 或Visual Studio 的 Standard 版本，则只能使用手工方法了。步骤 13 将给出手工执行这些步骤的详细指导。我们鼓励大家在阅读步骤13 之前先阅读步骤 2 至 12 ，因为这些步骤含有很重要的SQL Server 配置说明，而无论使用哪个 Visual Studio 版本都必须遵循这些说明。

首先打开 Visual Studio 。从 File 菜单中选择New Project 。New Project 对话框显示出来（参见图 4 ）。向下展开到Database 项目类型，然后，从右侧所列的 Templates 选择创建一个新的SQL Server Project 。我自己将此项目命名为 ManagedDatabaseConstructs 并将其存放到了一个名为 Tutorial75 的Solution 中。

图4 ：创建一个新的 SQL Server Project

在New Project 对话框中单击 OK 按扭以创建该 Solution 和 SQL Server Project 。

每个SQL Server Project 都会连接到一个特定的数据库。因此，在创建新的SQL Server Project 后，系统会立即要求我们指定此信息。图5 显示New Database Reference 对话框，该对话框中填充好的信息指向Northwind 数据库，我们在步骤1 中已将该数据库注册到 SQL Server 2005 Express Edition 数据库实例。

图5 ：将 SQL Server Project 与Northwind 数据库相关联

为了对我们即将在此项目中创建的托管存储过程和UDF 进行调试，我们需要对此连接启用 SQL/CLR debugging 支持。每当将一个SQL Server Project 与一个新的数据库相关联时（如图 5 所示），Visual Studio 会询问是否对该连接启用SQL/CLR debugging （见图 6 ）。单击 Yes 。

图6 ：启用 SQL/CLR Debugging

此时，新的 SQL Server Project 已添加到 Solution 中。它包含一个名为 Test Scripts 的文件夹，该文件夹下有一个名为 Test.sql 的文件，在对此项目中创建的托管数据库对象进行调试时将使用这一文件。我们将在步骤12 看到该调试过程。

现在可以在此项目中添加新的托管存储过程和UDF 了。在开始之前，我们首先将现有的 web 应用程序放入Solution 中。从 File 菜单中选择 Add 选项并选择Existing Web Site 。浏览到相应的 website 文件夹后单击OK 。如图7 所示，这将会更新 Solution ，使其包含两个项目：website 和名为 ManagedDatabaseConstructs 的 SQL Server Project 。

图7 ：Solution Explorer 现在包含两个项目

Web.config 中的 NORTHWNDConnectionString 值当前引用的是 App_Data 文件夹下的 NORTHWND.MDF 文件。由于我们已将此数据库从 App_Data 文件夹中移走并将其显式地注册到 SQL Server 2005 Express Edition 数据库实例，我们需要相应地更新 NORTHWNDConnectionString 值。打开该 website 下的 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 对托管存储过程和 UDF 进行调试，启用连接池功能。

步骤3：创建一个托管存储过程

要向一个 Northwind 数据库添加一个托管存储过程，首先要在SQL Server Project 中创建一个存储过程方法。在Solution Explorer 中右键单击ManagedDatabaseConstructs 项目名称并选择添加新项。Add New Item 对话框显示出来，其中列出可以添加到该项目中的托管数据库对象的类型。如图8 所示，这些类型中包括了存储过程和用户定义函数。

首先我们添加一个存储过程，该过程只是简单地返回所有已断货产品的信息。将这个新的存储过程文件命名为GetDiscontinuedProducts.cs 。

图8 ：添加一个名为 GetDiscontinuedProducts.cs 的新的存储过程

这将创建一个新的C# 类文件，该文件有以下内容：

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 类文件中。另外，GetDiscontinuedProducts 方法前有一个 SqlProcedure属性 ，此属性标明该方法为一个存储过程。

下面的代码创建一个 SqlCommand 对象并将该对象的 CommandText 设置为一个 SELECT 查询，该查询会返回 Discontinued 字段等于 1 的产品在 Products 表中的所有列。之后，该代码执行这一命令并将结果送回客户端应用程序。将该代码添加到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 对象 ，该对象可提供调用者的环境。SqlContext 对象通过其 Pipe 属性 来访问 SqlPipe 对象 。这里的 SqlPipe 对象用于在 SQL Server 数据库和调用程序间传递信息。从其名称就可看出， ExecuteAndSend 方法 执行一个传入的 SqlCommand 对象并将结果返回客户端应用程序。

注意 ： 托管数据库对象最适合于使用过程性逻辑的存储过程和UDF ，而不是使用基于集合的逻辑的存储过程和UDF 。过程性逻辑涉及逐行处理一组组的数据或处理标量数据。而我们刚才所创建的GetDiscontinuedProducts 方法却不包含过程性逻辑。因此，该方法理想情况下应实现为一个T-SQL 存储过程。将其实现为一个托管存储过程是为了给出一个示范，说明在创建和部署托管存储过程中所需执行的步骤。

步骤4：部署托管存储过程

代码编写完成后，就可以将其部署到 Northwind 数据库中了。在部署一个 SQL Server Project 时，系统会将其代码编译到一个汇编文件中，将该汇编文件注册到数据库，然后在数据库中创建相应的对象，将这些对象链接到汇编文件中相应的方法。在步骤13 中将会详细讲解 Deploy 选项所执行的具体任务。在 Solution Explorer 中右键单击 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 数据库，该数据库的兼容性级别必须设置为90 。缺省情况下，新的 SQL Server 2005 数据库的兼容性级别是 90 。然而用 Microsoft SQL Server 2000 创建的数据库的兼容性级别为 80 。Northwind 数据库最初是一个 Microsoft SQL Server 2000 数据库，其兼容性级别当前被设置为 80 ，因此需要将其级别提高到 90 以便注册托管数据库对象。

要更新该数据库的兼容性级别，在 Management Studio 中打开一个 New Query 窗口，输入：

exec sp_dbcmptlevel 'Northwind', 90

然后，单击工具栏上的 Execute 图标以运行上述查询。

图9 ：更新 Northwind 数据库的兼容性级别

在更新了兼容性级别后，重新部署该 SQL Server Project 。此次部署应该成功不会出错了。

返回 SQL Server Management Studio ，右键单击 Object Explorer 中的 Northwind 数据库并选择 Refresh 。然后，向下展开到 Programmability 文件夹再展开 Assemblies 文件夹。如图 10 所示，Northwind 数据库现在包含了由 ManagedDatabaseConstructs 项目生成的汇编文件。

图10 ：ManagedDatabaseConstructs 汇编文件现在注册到了 Northwind 数据库

同样，展开 Stored Procedures 文件夹。在该文件夹下，可以看到一个名为GetDiscontinuedProducts 的存储过程 。 该存储过程是在部署过程中创建的，它指向ManagedDatabaseConstructs 汇编文件中的 GetDiscontinuedProducts 方法。执行 GetDiscontinuedProducts 存储过程时，该过程会转而执行 GetDiscontinuedProducts 方法。由于这是一个托管存储过程，Management Studio 不能对其进行编辑（因而在该存储过程名称旁有一个带锁的图标）。

图11 ：Stored Procedures 文件夹下列出了 GetDiscontinuedProducts 存储过程

在能够调用该托管存储过程之前，我们还需要克服一个障碍：数据库的配置禁止其执行托管代码。作为验证，我们可以打开一个新的查询窗口，执行GetDiscontinuedProducts 存储过程。此时会出现以下错误消息： "Execution of user code in the .NET Framework is disabled.Enable �clr enabled’ configuration option.”

在该查询窗口中输入并执行 “ exec sp_configure” 命令可以查看到 Northwind 数据库的配置信息。我们可以看到，当前的“clr enabled” 设置为 0 。

图12 ：当前的 “clr enabled” 设置为 0

注意，图 12 中的每条配置都有四个值，它们是：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” ，单击工具栏上的 Execute 图标。如果现在再来运行 “exec sp_configure” ，会看到 “clr enabled” 配置的 config 和 run 值都变成了1 。

完成 “clr enabled” 的配置后，就可以运行 GetDiscontinuedProducts 托管存储过程了。在查询窗口中输入并执行“ exec GetDiscontinuedProducts ” 命令。调用该存储过程使得系统执行 GetDiscontinuedProducts 方法中的相应托管代码。该代码发出一个SELECT 查询，此查询会返回所有断货产品的数据，然后代码将此数据返回给调用程序 — 具体到本例为 SQL Server Management Studio 。Management Studio 接收这些结果并将其显示于 Results 窗口中。

图13 ：GetDiscontinuedProducts 存储过程返回所有断货产品

步骤5：创建接受输入参数的托管存储过程

我们在这些教程中创建的许多查询和存储过程都使用了参数。例如，在为强类型 DataSet 的 TableAdapters 创建新的存储过程这一教程中，我们创建了一个名为 GetProductsByCategoryID 的存储过程，该过程接受一个名为 @CategoryID 的输入参数。该存储过程返回那些其 CategoryID 字段与 @CategoryID 参数值相符的所有产品。

要创建一个带有输入参数的托管存储过程，只需在其方法定义中指定这些参数即可。我们举例说明这一点。将另一个名为GetProductsWithPriceLessThan 的托管存储过程添加到 ManagedDatabaseConstructs 项目中。该过程将接受一个指示价格的输入参数并返回其UnitPrice 字段小于该参数值的所有产品。

为了将一个新的存储过程添加到该项目，右键单击ManagedDatabaseConstructs 项目名并选择添加新存储过程。将该文件命名为GetProductsWithPriceLessThan.cs 。 如步骤 3 中所述，此时会创建一个新的 C# 类文件，在该文件的 partial classStoredProcedures 中包含了一个名为 GetProductsWithPriceLessThan 的方法。

更改 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 方法与步骤 3 中创建的 GetDiscontinuedProducts 方法有着非常相似的定义和代码，它们之间只有如下不同： GetProductsWithPriceLessThan 方法包含一个输入参数 ( price) ，其 SqlCommand 的查询包含一个参数 ( @MaxPrice) ，在 SqlCommand 的 Parameters 集中添加了一个参数，该参数赋值为price 变量的值。

在添加该代码后，重新部署 SQL Server Project 。然后，返回 SQL Server Management Studio ，刷新 Stored Procedures 文件夹。此时出现一新条目：GetProductsWithPriceLessThan 。 在查询窗口中输入并执行 “exec GetProductsWithPriceLessThan 25” 命令，该命令会列出所有价格低于 $25 的产品，如图 14 所示。

图14 ：显示出价格低于 $25 的产品

步骤6：从数据访问层调用托管存储过程

至此，我们已将GetDiscontinuedProducts 和 GetProductsWithPriceLessThan 托管存储过程添加到 ManagedDatabaseConstructs 项目中并将这些过程注册到 Northwind SQL Server 数据库。我们还通过SQL Server Management Studio 调用了这些托管存储过程（参见图 13 和 14 ）。然而，为使我们的ASP.NET 应用程序可以使用这些托管存储过程，还需要将这些过程添加到软件架构中的数据访问层和业务逻辑层。在本步骤中，我们将两个新方法添加到一个名为NorthwindWithSprocs 的 Typed DataSet 的ProductsTableAdapter 中，ProductsTableAdapter 最初是我们在为强类型 DataSet的TableAdapters创建新的存储过程教程中创建的。在步骤7 中，我们会将相应方法添加到 BLL 中。

在Visual Studio 中打开名为NorthwindWithSprocs 的 Typed DataSet ，首先将一个名为 GetDiscontinuedProducts 的新方法添加到 ProductsTableAdapter 中。为了将新方法添加到一个TableAdapter 中，在Designer 中右键单击该 TableAdapter 的名称，从关联菜单中选择Add Query 选项。

注意： 我们已将 Northwind 数据库从 App_Data 文件夹移动到 SQL Server 2005 Express Edition 数据库实例，因此必须更改Web.config 中的相应连接字串以反映这一变动。步骤2 讲述了怎样更新 Web.config 中的 NORTHWNDConnectionString 值。如果忘记了进行这一更新，在向 TableAdapter 添加新方法时，系统会弹出一个对话框，其中显示这样的错误消息：“Failed to add query.Unable to find connection ‘NORTHWNDConnectionString’ for object ‘Web.config’” 。为了纠正此错误，单击OK 后转至Web.config ，在此更新 NORTHWNDConnectionString 值（参见步骤2 ），然后重新将该方法添加到 TableAdapter 。这次应该不会出错了。

添加新方法时，系统会启动 TableAdapter Query Configuration Wizard ，这个向导我们在以前的教程中曾多次使用过。向导的第一步要求我们指定TableAdapter 访问数据库的方式：或者通过一个即时SQL 语句来访问，或者通过一个新的或现有的存储过程来访问。既然我们已创建了GetDiscontinuedProducts 托管存储过程并将其注册到了数据库，在此选择“Use existing stored procedure” 选项，单击 Next 。

图15 ：选择 “Use existing stored procedure” 选项

下一屏慕提示我们指定该方法要调用的存储过程。从下拉列表中选择GetDiscontinuedProducts 托管存储过程，单击Next 。

图16 ：选择 GetDiscontinuedProducts 托管存储过程

之后，向导要求我们指定存储过程是返回记录行、单个值，还是啥也不返回。由于GetDiscontinuedProducts 返回断货产品记录行的集合，在此选择第一个选项(“Tabular data”) ，单击 Next 。

图17 ：选择 “Tabular Data” 选项

在向导的最后一个屏幕中，我们可以指定所使用的数据访问模式及最终方法的名称。这里的两个复选框是勾选上的，保持勾选状态不变，将两个方法分别命名为FillByDiscontinued 和 GetDiscontinuedProducts 。单击 Finish 完成向导。

图18 ：将两个方法分别命名为 FillByDiscontinued 和 GetDiscontinuedProducts

重复上述步骤，在ProductsTableAdapter 中为 GetProductsWithPriceLessThan 托管存储过程创建两个名为 FillByPriceLessThan 和GetProductsWithPriceLessThan 的方法。

图19 所示是为GetDiscontinuedProducts 和 GetProductsWithPriceLessThan 托管存储过程在ProductsTableAdapter 中添加相应方法之后，DataSet Designer 的一个屏幕截图。

图19 ：ProductsTableAdapter 中包含了此步骤中添加的两个新方法

步骤 7：在业务逻辑层中添加相应方法

前面我们更新了数据访问层，该层现在含有了可对步骤4 和 5 中添加的托管存储过程进行调用的方法。这里，我们需要将相应的方法添加到业务逻辑层中。将下面两个方法添加到ProductsBLLWithSprocs 类中：

[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); 
}

这两个方法只是简单地调用相应的 DAL 方法并返回 ProductsDataTable 实例。在每个方法前面使用了 DataObjectMethodAttribute 标记，该标记使得这些方法出现于 ObjectDataSource 的Configure Data Source 向导的 SELECT 选项卡的下拉列表中。

步骤8：从表示层调用托管存储过程

我们已对业务逻辑层和数据访问层进行了扩充，使其支持对GetDiscontinuedProducts 和 GetProductsWithPriceLessThan 托管存储过程的调用；现在，我们可以通过一个ASP.NET 页面来显示这些托管存储过程的调用结果。

打开AdvancedDAL 文件夹下的 ManagedFunctionsAndSprocs.aspx 页面，从工具箱中将一个GridView 控件拖放到 Designer 中。将该GridView 控件的ID 属性设置为DiscontinuedProducts ，并且通过其智能标签将其绑定到一个名为DiscontinuedProductsDataSource 的新的ObjectDataSource 。配置该 ObjectDataSource ，使其通过 ProductsBLLWithSprocs 类的 GetDiscontinuedProducts 方法获取数据。

图20 ：配置 ObjectDataSource ，使其使用 ProductsBLLWithSprocs 类

图21 ：从 SELECT 选项卡的下拉列表中选择 GetDiscontinuedProducts 方法

此grid 只用于显示产品信息，因此，将 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 类的 GetDiscontinuedProducts 方法。如步骤7 中所述，该方法会向下调用 DAL 层的 ProductsDataTable 类的 GetDiscontinuedProducts 方法，而这个方法会调用 GetDiscontinuedProducts 存储过程。此过程是一个托管存储过程，它会执行我们在步骤3 中创建的代码，结果是返回断货产品的信息。

DAL 将该托管存储过程返回的结果封装到一个ProductsDataTable 中后返回给 BLL ，接着，BLL 将其返回给表示层，在表示层中，这些结果绑定到GridView ，因此在GridView 中显示出来。如我们所料，该 grid 列出了那些已断货的产品。

图22 ：列出断货产品

作为进一步的练习，在该页面中添加一个文本框和另一个GridView 。令该GridView 调用ProductsBLLWithSprocs 类的 GetProductsWithPriceLessThan 方法，从而将价格低于文本框中输入的金额的产品展示出来。

步骤 9：创建并调用 T-SQL UDF

用户定义函数，即 UDF ，是一个数据库对象，与编程语言中的函数定义很相似。如同 C# 中的函数，UDF 可以包含数量不定的输入参数，还可以返回一个特定类型的值。UDF 要么返回一个标量数据— 字符串、整数等等，要么返回一个表格数据。我们快速了解一下这两种类型的UDF ，先从返回标量数据类型的UDF 开始。

下面的 UDF 可估算出特定产品的库存总价。为此，该UDF 包含三个输入参数 – 特定产品的 UnitPrice 、UnitsInStock 和 Discontinued 值，并返回一个类型为 money 的值。该 UDF 通过 UnitPrice 与 UnitsInStock 相乘来估算产品的库存总价。而对于断货产品，总价减半。

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 查询中象下面这样来使用此UDF ：

SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue 
    (UnitPrice, UnitsInStock, Discontinued) as InventoryValue 
FROM Products 
ORDER BY InventoryValue DESC

我已将udf_ComputeInventoryValue UDF 添加到了 Northwind 数据库中。图23 是在Management Studio 中执行上述SELECT 查询的输出结果。在该图中还可看到，在Object Explorer 的Scalar-value Functions 文件夹下列出了这个 UDF 。

图23 ：列出每个产品的库存总价

UDF 也可以返回表格数据。例如，我们可以创建一个UDF 来返回属于一个特定分类的所有产品：

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 UDF 接受一个 @CategoryID 输入参数并返回指定的 SELECT 查询的结果。创建后，可以在SELECT 查询的FROM （或 JOIN ）子句中引用该 UDF 。下面的例子将返回属于饮料类的每个产品的ProductID 、ProductName 和 CategoryID 值。

SELECT ProductID, ProductName, CategoryID FROM dbo.udf_GetProductsByCategoryID(1)

我已将udf_GetProductsByCategoryID UDF 添加到了 Northwind 数据库中，图23 是在Management Studio 中执行上述SELECT 查询的输出结果。在 Object Explorer 的Table-value Functions 文件夹下可以看到返回表格数据的 UDF 。

图24 ：列出了每个饮料类产品的 ProductID 、ProductName 和 CategoryID

注意： 有关创建和使用 UDF 的更多信息，请查阅 用户定义函数介绍 。另外，还可查阅 用户定义函数的优缺点 。

步骤10：创建一个托管UDF

在上面的例子中创建的udf_ComputeInventoryValue 和 udf_GetProductsByCategoryID UDF 都是T-SQL 数据库对象。SQL Server 2005 同样支持托管UDF ，如同步骤 3 和 5 中的托管存储过程，我们也可在 ManagedDatabaseConstructs 项目中添加托管 UDF 。在这一步，我们将用托管代码来实现udf_ComputeInventoryValue UDF 。

要在ManagedDatabaseConstructs 项目中添加一个托管 UDF ，在Solution Explorer 中右键单击该项目名称，选择 Add a New Item 。从 Add New Item 对话框中选择 User-Defined Template ，将新的UDF 文件命名为udf_ComputeInventoryValue_Managed.cs 。

图25 ：在 ManagedDatabaseConstructs 项目中添加一个新的托管UDF

该 User-Defined Function 模板创建一个名为 UserDefinedFunctions 的 partial 类，该类中有一个与类文件同名的方法，此例为udf_ComputeInventoryValue_Managed 。该方法前有一个 SqlFunction 属性 ，此属性将该方法标记为一个托管 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 对象 并且没有任何输入参数。我们需要更改该方法的定义，使其包含三个输入参数– UnitPrice 、UnitsInStock 和 Discontinued ，并且返回一个 SqlMoney 对象。计算库存总价的逻辑与 T-SQL udf_ComputeInventoryValue UDF 中的逻辑相同。

[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 类型。

该代码首先创建一个名为inventoryValue 的 SqlMoney 实例并对其赋值为0 。由于Products 表允许 UnitsInPrice 列和 UnitsInStock 列的值为数据库NULL ，我们首先需要检查这些值是否为 NULL ，为此我们使用了 SqlMoney 对象的 IsNull 属性 。如果 UnitPrice 和UnitsInStock 都不为 NULL ，则以两者相乘来计算 inventoryValue 。之后，如果 Discontinued 为真，将该值减半。

注意：SqlMoney 对象只允许两个 SqlMoney 实例相乘。它不允许一个SqlMoney 实例与一个字面上的浮点数相乘。因此，在使inventoryValue 值减半时，我们新建一个值为0.5 的SqlMoney 实例来与该值相乘。

步骤11：部署托管UDF

我们已创建了托管 UDF ，现在可以将其部署到 Northwind 数据库了。如步骤 4 所述，在 Solution Explorer 中右键单击一个项目名称，从关联菜单中选择Deploy 选项，就可以部署该 SQL Server Project 中的托管对象。

部署了该项目之后，返回 SQL Server Management Studio ，刷新 Scalar-valued Functions 文件夹。此时，在该文件夹下应可看到以下两项：

• dbo.udf_ComputeInventoryValue – 步骤 9 中创建的 T-SQL UDF ，以及
• dbo.udf ComputeInventoryValue_Managed – 步骤 10 中创建的，刚才部署的托管 UDF 。

在 Management Studio 中执行以下查询命令来对该托管 UDF 进行测试：

SELECT ProductID, ProductName,  
       dbo.udf_ComputeInventoryValue_Managed( 
                 UnitPrice,  
                 UnitsInStock,  
                 Discontinued 
              ) as InventoryValue 
FROM Products 
ORDER BY InventoryValue DESC

此命令用的是托管 UDFudf ComputeInventoryValue_Managed 而不是 T-SQL UDF udf_ComputeInventoryValue ，但输出结果却是一样的。查看前面的屏幕截图23 ，可能看到该 UDF 的输出。

步骤12：调试托管数据库对象

在 调试存储过程 教程中，我们讲述了通过Visual Studio 对 SQL Server 进行调试的三个选项：Direct Database Debugging 、Application Debugging 和 Debugging from a SQL Server Project 。对托管数据库对象的调试不能通过Direct Database Debugging 选项来进行，但可通过客户端应用程序，或者，直接通过SQL Server Project 来进行。然而，为使调试可以进行，SQL Server 2005 数据库必须允许 SQL/CLR debugging 。记得我们最初创建 ManagedDatabaseConstructs 项目时，Visual Studio 询问我们是否启用SQL/CLR debugging （参见步骤 2 中的图 6 ）。在Server Explorer 窗口中右键单击该数据库可以改变这一配置。

图26 ：确保该数据库允许 SQL/CLR Debugging

设想我们要调试GetProductsWithPriceLessThan 托管存储过程。首先我们在 GetProductsWithPriceLessThan 方法的代码中设置一个断点。

图27 ：在 GetProductsWithPriceLessThan 方法中设置断点

首先我们看看怎样通过 SQL Server Project 来调试托管数据库对象。我们的 Solution 包含有两个项目– website 和名为ManagedDatabaseConstructs 的SQL Server Project 。因此，为了通过 SQL Server Project 进行调试，需要给Visual Studio 一个指示，使其在我们启动调试时启动ManagedDatabaseConstructs SQL Server Project 。在Solution Explorer 中右键单击ManagedDatabaseConstructs ，从关联菜单中选择 “Set as StartUp Project” 选项。

调试器启动ManagedDatabaseConstructs 项目时，会执行 Test.sql 文件中的SQL 语句，该文件位于Test Scripts 文件夹下。例如，为了对 GetProductsWithPriceLessThan 托管存储过程进行测试，用下面的语句替换当前Test.sql 文件中的内容。这条语句会以 @CategoryID 传入值14.95 来调用GetProductsWithPriceLessThan 托管存储过程：

exec GetProductsWithPriceLessThan 14.95

将以上脚本输入Test.sql 后开始调试。转至 Debug 菜单，选择Start Debugging ，或者，按 F5 键或点击工具栏上的绿色 play 图标。这时，系统会build 该 Solution 中的项目，将托管数据库对象部署到 Northwind 数据库，然后执行 Test.sql 脚本。此时会遇到断点，于是我们可以逐步调试GetProductsWithPriceLessThan 方法、检查输入参数的值等等。

图28 ：触发了 GetProductsWithPriceLessThan 方法中的断点

为了通过客户端应用程序调试 SQL 数据库对象，必须将该数据库配置为支持应用程序调试。在Server Explorer 中右键单击该数据库，确保勾选上“Application Debugging” 选项。此外，还需要配置 ASP.NET 应用程序，使其与SQL Debugger 相集成并且禁用连接池功能。在调试存储过程 教程的步骤 2 中已详细讲述了这些步骤。

配置好ASP.NET 应用程序和数据库后，将 ASP.NET website 设置为启动项目然后开始调试。当你访问一个页面，而该页面调用一个含有断点的托管对象时，程序会暂停，由调试器接管程序的控制权，此时，我们可以在调试器中逐步调试代码（如图28 所示）。

步骤13：手工编译和部署托管数据库对象

使用SQL Server Projects 可以轻松地创建、编译和部署托管数据库对象。遗憾的是，只有在Visual Studio 的Professional 和 Team Systems 两个版本中才可使用 SQL Server Projects 。如果当前使用的是Visual Web Developer 或 Visual Studio Standard Edition ，并且打算使用托管数据库对象，则需要手工创建并部署这些对象。手工过程包括四个步骤：

1. 创建一个文件来存放托管数据库对象的源代码
2. 将该对象编译到一个汇编文件中。
3. 将该汇编文件注册到 SQL Server 2005 数据库，然后
4. 在 SQL Server 中创建一个数据库对象，使该对象指向汇编文件中的相应方法。

为了演示这些步骤，我们创建一个新的托管存储过程，该过程返回那些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); 
    } 
};

该代码与步骤 5 中创建的 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 的文件夹不在系统的 PATH 目录下，则必须完整地引用其路径，%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# 文件编译为一个 DLL（而不是可执行文件）。/out 标记指定了编译后得到的汇编文件的名称。

注意：除了通过命令行来编译GetProductsWithPriceGreaterThan.cs 类文件外，还可以选择使用 Visual C# Express Edition 或在 Visual Studio Standard Edition 中创建一个单独的 Class Library 项目。Søren Jacob Lauritsen 为我们提供了这样一个 Visual C# Express Edition 项目，该项目包含了 GetProductsWithPriceGreaterThan 存储过程、以及我们在步骤 3、5、10 中创建的两个托管存储过程和 UDF 的代码。此外还包含了添加相应数据库对象所需的 T-SQL 命令。

将代码编译到汇编文件后，就可以将汇编文件注册到 SQL Server 2005 数据库了。注册可以通过 T-SQL，使用命令 CREATE ASSEMBLY 来进行，也可以通过 SQL Server Management Studio 来进行。我们来看看使用Management Studio 的情况。

在 Management Studio 中，展开 Northwind 数据库中的 Programmability 文件夹。该文件夹下有一个 Assemblies 子文件夹。要将一个新的 Assembly 手工添加到数据库中，右键单击 Assemblies 文件夹，从关联菜单中选择 New Assembly。New Assembly 对话框显示出来（参见图 30）。单击 Browse 按扭，选择刚才编译得到的 ManuallyCreatedDBObjects.dll 汇编文件，然后单击 OK 将这个 Assembly 添加到数据库中。在 Object Explorer 中现在应可以看到 ManuallyCreatedDBObjects.dll 汇编文件了。

图 30：将ManuallyCreatedDBObjects.dll 汇编文件添加到数据库

图 31：Object Explorer 中列出了 ManuallyCreatedDBObjects.dll

我们已将汇编文件添加到 Northwind 数据库中，另外还需要将一个存储过程与该汇编文件中的GetProductsWithPriceGreaterThan 方法相关联。为此，打开一个新的查询窗口并执行以下脚本：

CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan]  
(  
    @price money  
)  
WITH EXECUTE AS CALLER  
AS  
EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan]  
GO

执行结果是，在 Northwind 数据库中创建了一个名为GetProductsWithPriceGreaterThan 的新的存储过程，该过程与托管方法GetProductsWithPriceGreaterThan 相关联（该方法位于汇编文件ManuallyCreatedDBObjects 的 StoredProcedures 类之中）。

执行上述脚本后，刷新 Object Explorer 中的 Stored Procedures 文件夹。将会看到一个新的存储过程条目– GetProductsWithPriceGreaterThan ，其旁边有一个带锁的图标。在查询窗口中输入并执行以下脚本来对该存储过程进行测试：

exec GetProductsWithPriceGreaterThan 24.95

如图 32 所示，上述命令显示出 UnitPrice 大于 $24.95 的产品信息。

图32：Object Explorer 中列出了 ManuallyCreatedDBObjects.dll

小结

Microsoft SQL Server 2005 实现了与 Common Language Runtime (CLR) 的集成，这使得我们可以用托管代码来创建数据库对象。以前，我们只能使用T-SQL 来创建数据库对象，而现在，我们可以使用.NET 编程语言，如 C# 来创建数据库对象了。在本教程中，我们创建了两个托管存储过程和一个托管用户定义函数。

Visual Studio 的 SQL Server Project 类型使我们能更轻松地创建、编译和部署托管数据库对象。另外，该类型还支持丰富的调试功能。然而，SQL Server Project 类型只能在Visual Studio 的 Professional 和 Team Systems 版本中使用。对于Visual Web Developer 和 Visual Studio Standard Edition 的用户来说，则只能手工完成创建、编译和部署过程，在步骤13 中可以看到这点。

快乐编程！