使用托管代码创建存储过程和用户定义的函数 (VB)

作者 ：斯科特·米切尔

下载 PDF

Microsoft SQL Server 2005 与 .NET 公共语言运行时集成，使开发人员能够通过托管代码创建数据库对象。 本教程演示如何使用 Visual Basic 或 C# 代码创建托管存储过程和托管用户定义函数。 我们还了解这些版本的 Visual Studio 如何允许你调试此类托管数据库对象。

介绍

Microsoft SQL Server 2005 等数据库使用 Transact-结构化查询语言（T-SQL）来插入、修改和检索数据。 大多数数据库系统都包含用于对一系列 SQL 语句进行分组的构造，这些语句随后可以作为单个可重用单元执行。 存储过程是一个示例。 另一个是 用户定义的函数（UDF），这是我们将在步骤 9 中更详细地检查的构造。

SQL 的核心是为处理数据集而设计的。 和SELECTUPDATEDELETE语句本质上适用于相应表中的所有记录，并且仅受其WHERE子句的限制。 然而，有许多语言功能设计用于一次处理一条记录和操作标量数据。 CURSOR允许一组记录一次循环访问一个。 字符串操作函数，例如 LEFT， CHARINDEX处理 PATINDEX 标量数据。 SQL 还包括控制流语句，例如 IF 和 WHILE。

在Microsoft SQL Server 2005 之前，存储过程和 UDF 只能定义为 T-SQL 语句的集合。 但是，SQL Server 2005 旨在提供与 公共语言运行时（CLR）的集成，这是所有 .NET 程序集使用的运行时。 因此，可以使用托管代码创建 SQL Server 2005 数据库中的存储过程和 UDF。 也就是说，可以在 Visual Basic 类中创建存储过程或 UDF 作为方法。 这使这些存储过程和 UDF 能够利用 .NET Framework 和你自己的自定义类中的功能。

本教程介绍如何创建托管存储过程和用户定义的函数，以及如何将它们集成到 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位于名为 <a0/a0> 的文件夹。 如果遵循本教程自己的实现，请关闭 Visual Studio 并将网站文件夹中的文件移动到NORTHWND.MDFNORTHWND_log.LDF网站App_Data外部的文件夹。 将数据库文件移动到另一个文件夹后，需要向 SQL Server 2005 Express Edition 数据库实例注册 Northwind 数据库。 这可以通过 SQL Server Management Studio 完成。 如果计算机上安装了非 Express Edition 的 SQL Server 2005，则可能已安装 Management Studio。 如果计算机上只有 SQL Server 2005 Express Edition，请花点时间下载并安装 Microsoft SQL Server Management Studio。

启动 SQL Server Management Studio。 如图 1 所示，Management Studio 首先询问要连接到的服务器。 输入服务器名称的 localhost\SQLExpress，在“身份验证”下拉列表中选择“Windows 身份验证”，然后单击“连接”。

图 1：连接到适当的数据库实例

连接后，对象资源管理器窗口将列出有关 SQL Server 2005 Express Edition 数据库实例的信息，包括其数据库、安全信息、管理选项等。

我们需要将文件夹中的 Northwind 数据库 DataFiles （或可能已移动到的位置）附加到 SQL Server 2005 Express Edition 数据库实例。 右键单击“数据库”文件夹，然后从上下文菜单中选择“附加”选项。 此时会显示“附加数据库”对话框。 单击“添加”按钮，向下钻取到相应的 NORTHWND.MDF 文件，然后单击“确定”。 此时，屏幕应类似于图 2。

图 2：连接到相应的数据库实例（单击以查看完整大小的图像）

注意

通过 Management Studio 连接到 SQL Server 2005 Express Edition 实例时，“附加数据库”对话框不允许向下钻取用户配置文件目录，例如“我的文档”。 因此，请确保将 NORTHWND.MDF 这些文件 NORTHWND_log.LDF 放在非用户配置文件目录中。

单击“确定”按钮附加数据库。 “附加数据库”对话框将关闭，对象资源管理器现在应列出刚刚附加的数据库。 Northwind 数据库的名称可能如下所示 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF。 右键单击数据库并选择“重命名”，将数据库重命名为 Northwind。

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

步骤 2：在 Visual Studio 中创建新的解决方案和 SQL Server 项目

若要在 SQL Server 2005 中创建托管存储过程或 UDF，我们将在类中将存储过程和 UDF 逻辑编写为 Visual Basic 代码。 编写代码后，我们需要将此类编译为程序集（文件 .dll ），将程序集注册到 SQL Server 数据库，然后在指向程序集中相应方法的数据库中创建存储过程或 UDF 对象。 这些步骤都可以手动执行。 我们可以在任何文本编辑器中创建代码，使用 Visual Basic 编译器从命令行编译代码（vbc.exe），使用 CREATE ASSEMBLY 命令或 Management Studio 将其注册到数据库，并通过类似的方式添加存储过程或 UDF 对象。 幸运的是，Visual Studio 的 Professional 和 Team Systems 版本包括自动执行这些任务的 SQL Server 项目类型。 在本教程中，我们将逐步讲解如何使用 SQL Server 项目类型创建托管存储过程和 UDF。

注意

如果使用 Visual Web 开发人员或 Visual Studio 标准版，则必须改用手动方法。 步骤 13 提供了手动执行这些步骤的详细说明。 建议在阅读步骤 13 之前阅读步骤 2 到 12，因为这些步骤包括必须应用的重要 SQL Server 配置说明，无论使用哪个版本的 Visual Studio。

首先打开 Visual Studio。 在“文件”菜单中，选择“新建项目”以显示“新建项目”对话框（请参阅图 4）。 向下钻取到数据库项目类型，然后从右侧列出的模板中选择创建新的 SQL Server 项目。 我已选择命名此项目 ManagedDatabaseConstructs ，并将其放置在名为 Tutorial75“解决方案”中的解决方案中。

图 4：创建新的 SQL Server 项目（单击以查看全尺寸图像）

单击“新建项目”对话框中的“确定”按钮以创建解决方案和 SQL Server 项目。

SQL Server 项目绑定到特定数据库。 因此，创建新的 SQL Server 项目后，我们立即被要求指定此信息。 图 5 显示了“新建数据库引用”对话框，该对话框已填写，指出我们在 SQL Server 2005 Express Edition 数据库实例中注册的 Northwind 数据库，该数据库已在步骤 1 中重新注册。

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

为了调试将在此项目中创建的托管存储过程和 UDF，我们需要为连接启用 SQL/CLR 调试支持。 每当将 SQL Server 项目与新数据库（如图 5 所示）相关联时，Visual Studio 会询问我们是否要在连接上启用 SQL/CLR 调试（请参阅图 6）。 单击 “是” 。

图 6：启用 SQL/CLR 调试

此时，新的 SQL Server 项目已添加到解决方案。 它包含一个名为文件的文件夹Test ScriptsTest.sql，该文件夹用于调试在项目中创建的托管数据库对象。 我们将在步骤 12 中查看调试。

现在，我们可以向此项目添加新的托管存储过程和 UDF，但在此之前，让我们先在解决方案中包含现有的 Web 应用程序。 从“文件”菜单中选择“添加”选项，然后选择“现有网站”。 浏览到相应的网站文件夹，然后单击“确定”。 如图 7 所示，这将更新解决方案以包含两个项目：网站和 ManagedDatabaseConstructs SQL Server 项目。

图 7：解决方案资源管理器现在包括两个项目

当前 NORTHWNDConnectionString 中的 Web.config 值引用 NORTHWND.MDF 文件夹中的文件 App_Data 。 由于我们从 SQL Server 2005 Express Edition 数据库实例中删除并显式注册了该数据库 App_Data ，因此需要相应地更新 NORTHWNDConnectionString 该值。 Web.config在网站中打开文件并更改NORTHWNDConnectionString值，以便连接字符串读取： Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True。 此更改后，你的 <connectionStrings> 分区 Web.config 应如下所示：

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

注意

如前面的教程中所述，从客户端应用程序（如 ASP.NET 网站）调试 SQL Server 对象时，需要禁用连接池。 上面所示的连接字符串将禁用连接池（ Pooling=false ）。 如果不打算从 ASP.NET 网站调试托管存储过程和 UDF，请启用连接池。

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

若要将托管存储过程添加到 Northwind 数据库，首先需要在 SQL Server 项目中创建存储过程作为方法。 在解决方案资源管理器中，右键单击ManagedDatabaseConstructs项目名称并选择添加新项。 此时将显示“添加新项”对话框，其中列出了可添加到项目的托管数据库对象的类型。 如图 8 所示，这包括存储过程和用户定义的函数等。

让我们首先添加一个存储过程，该存储过程只返回所有已停用的产品。 将新的存储过程文件 GetDiscontinuedProducts.vb命名为 。

图 8：添加新存储过程（GetDiscontinuedProducts.vb单击以查看全尺寸图像）

这将创建包含以下内容的新 Visual Basic 类文件：

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub  GetDiscontinuedProducts ()
        ' Add your code here
    End Sub
End Class

请注意，存储过程作为名为 Shared /a0> 的 Partial 类文件中的方法 StoredProcedures实现。 此外，该方法 GetDiscontinuedProducts 使用特性进行修饰 SqlProcedure ，该特性将该方法标记为存储过程。

下面的代码创建一个 SqlCommand 对象并将其设置为 CommandText 一个 SELECT 查询，该查询返回 Products 表中所有列，其 Discontinued 字段等于 1 的产品。 然后，它会执行该命令并将结果发送回客户端应用程序。 将此代码添加到 GetDiscontinuedProducts 方法中。

' Create the command
Dim myCommand As 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。 过程逻辑涉及逐行处理数据集或处理标量数据。 GetDiscontinuedProducts但是，我们刚刚创建的方法不涉及过程逻辑。 因此，最好将其实现为 T-SQL 存储过程。 它作为托管存储过程实现，用于演示创建和部署托管存储过程所需的步骤。

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

完成此代码后，我们已准备好将其部署到 Northwind 数据库。 部署 SQL Server 项目会将代码编译到程序集中，将程序集注册到数据库，并在数据库中创建相应的对象，并将其链接到程序集中的相应方法。 “部署”选项执行的确切任务集在步骤 13 中更精确。 右键单击ManagedDatabaseConstructs解决方案资源管理器中的项目名称，然后选择“部署”选项。 但是，部署失败，并出现以下错误：“EXTERNAL”附近的语法不正确。 您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。 请参阅存储过程 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 中打开“新建查询”窗口并输入：

exec sp_dbcmptlevel 'Northwind', 90

单击工具栏中的“执行”图标以运行上述查询。

图 9：更新 Northwind 数据库的兼容性级别（单击以查看全尺寸图像）

更新兼容性级别后，重新部署 SQL Server 项目。 这一次，部署应完成且不会出现错误。

返回到 SQL Server Management Studio，右键单击对象资源管理器中的 Northwind 数据库，然后选择“刷新”。 接下来，向下钻取到可编程性文件夹，然后展开“程序集”文件夹。 如图 10 所示，Northwind 数据库现在包含项目生成的 ManagedDatabaseConstructs 程序集。

图 10：程序集 ManagedDatabaseConstructs 现已注册到 Northwind 数据库

此外，展开“存储过程”文件夹。 你将在那里看到名为 <a0/a0> 的存储过程。 此存储过程是由部署过程创建的，并指向 GetDiscontinuedProducts 程序集中的 ManagedDatabaseConstructs 方法。 GetDiscontinuedProducts执行存储过程时，它会依次执行GetDiscontinuedProducts该方法。 由于这是一个托管存储过程，因此无法通过 Management Studio 对其进行编辑（因此存储过程名称旁边的锁图标）。

图 11： GetDiscontinuedProducts 存储过程在存储过程文件夹中列出

在调用托管存储过程之前，我们还需要克服一个障碍：数据库配置为防止执行托管代码。 通过打开新的查询窗口并执行存储过程来 GetDiscontinuedProducts 验证这一点。 将收到以下错误消息：禁用 .NET Framework 中的用户代码执行。 启用“clr 已启用配置”选项。

若要检查 Northwind 数据库配置信息，请在查询窗口中输入并执行命令 exec sp_configure 。 这表明已启用 clr 的设置当前设置为 0。

图 12：已启用 clr 的设置当前设置为 0（单击以查看全尺寸图像）

请注意，图 12 中的每个配置设置都列出了四个值：最小值和最大值以及配置和运行值。 若要更新已启用 clr 设置的配置值，请执行以下命令：

exec sp_configure 'clr enabled', 1

如果重新运行， exec sp_configure 则会看到上述语句将已启用 clr 的设置配置值更新为 1，但运行值仍设置为 0。 要使此配置更改生效，我们需要执行 RECONFIGURE 命令，该命令会将运行值设置为当前配置值。 只需在查询窗口中输入 RECONFIGURE 并单击工具栏中的“执行”图标。 如果现在运行 exec sp_configure ，则应看到已启用 clr 的设置配置和运行值的值为 1。

完成 clr 配置后，即可运行托管 GetDiscontinuedProducts 存储过程。 在查询窗口中输入并执行命令execGetDiscontinuedProducts。 调用存储过程会导致方法中的 GetDiscontinuedProducts 相应托管代码执行。 此代码发出查询 SELECT 以返回所有已停止的产品，并将此数据返回到调用应用程序，即此实例中的 SQL Server Management Studio。 Management Studio 接收这些结果，并在“结果”窗口中显示它们。

图 13： GetDiscontinuedProducts 存储过程返回所有已停止使用的产品（单击可查看全尺寸图像）

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

在这些教程中创建的许多查询和存储过程都使用了 参数。 例如，在 “为类型化数据集 s TableAdapters 创建新存储过程”教程中，我们创建了一个名为接受名为 GetProductsByCategoryID 输入参数的 @CategoryID存储过程。 然后，存储过程返回其字段与提供@CategoryID的参数值匹配的所有产品CategoryID。

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

若要向项目添加新存储过程，请 ManagedDatabaseConstructs 右键单击项目名称，然后选择添加新存储过程。 为 GetProductsWithPriceLessThan.vb 文件命名。 正如我们在步骤 3 中看到的那样，这将创建一个新的 Visual Basic 类文件，其中包含一个名为该类StoredProcedures中Partial的方法GetProductsWithPriceLessThan。

GetProductsWithPriceLessThan更新方法定义，使其接受SqlMoney名为price输入参数并编写代码以执行并返回查询结果：

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub GetProductsWithPriceLessThan(ByVal price As SqlMoney)
    'Create the command
    Dim myCommand As 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)
End Sub

方法 GetProductsWithPriceLessThan 的定义和代码与步骤 3 中创建的方法的定义和代码 GetDiscontinuedProducts 非常相似。 唯一的区别是 GetProductsWithPriceLessThan 该方法接受为输入参数 （price）， SqlCommand s 查询包括参数 （@MaxPrice），并且将参数添加到 SqlCommand s Parameters 集合并分配变量的值 price 。

添加此代码后，重新部署 SQL Server 项目。 接下来，返回到 SQL Server Management Studio 并刷新存储过程文件夹。 应会看到一个新条目。 GetProductsWithPriceLessThan 在查询窗口中，输入并执行命令，该命令 exec GetProductsWithPriceLessThan 25将列出低于 $25 的所有产品，如图 14 所示。

图 14：显示 25 美元以下的产品（单击以查看全尺寸图像）

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

此时，我们已将 GetDiscontinuedProducts 存储过程 GetProductsWithPriceLessThan 和托管存储过程添加到 ManagedDatabaseConstructs 项目，并将其注册到 Northwind SQL Server 数据库。 我们还从 SQL Server Management Studio 调用了这些托管存储过程（请参阅图 13 和 14）。 但是，为了使 ASP.NET 应用程序使用这些托管存储过程，我们需要将它们添加到体系结构中的数据访问和业务逻辑层。 在此步骤中，我们将向 Typed DataSet 中添加两个新方法NorthwindWithSprocsProductsTableAdapter，该方法最初是在 Typed DataSet s TableAdapters 教程的“创建新存储过程”中创建的。 在步骤 7 中，我们将向 BLL 添加相应的方法。

在 NorthwindWithSprocs Visual Studio 中打开类型化数据集，首先向命名GetDiscontinuedProducts方法添加新方法ProductsTableAdapter。 若要向 TableAdapter 添加新方法，请右键单击设计器中的 TableAdapter 名称，并从上下文菜单中选择“添加查询”选项。

注意

由于我们已将 Northwind 数据库从App_Data文件夹移动到 SQL Server 2005 Express Edition 数据库实例，因此必须更新 Web.config 中的相应连接字符串以反映此更改。 在步骤 2 中，我们讨论了更新NORTHWNDConnectionString值。Web.config 如果忘记进行此更新，则会看到错误消息“无法添加查询”。 尝试向 TableAdapter 添加新方法时，无法在对话框中找到对象的Web.config连接NORTHWNDConnectionString。 若要解决此错误，请单击“确定”，然后按照步骤 2 中所述转到 Web.config 并更新 NORTHWNDConnectionString 值。 然后尝试将方法重新添加到 TableAdapter。 这一次，它应该工作而不出错。

添加新方法将启动 TableAdapter 查询配置向导，我们在过去的教程中使用了多次。 第一步要求我们指定 TableAdapter 应如何访问数据库：通过即席 SQL 语句或新的或现有的存储过程。 由于我们已经创建了托管存储过程并将其注册到 GetDiscontinuedProducts 数据库，因此请选择“使用现有存储过程”选项，然后点击“下一步”。

图 15：选择“使用现有存储过程”选项（单击可查看全尺寸图像）

下一个屏幕提示我们输入该方法将调用的存储过程。 从下拉列表中选择 GetDiscontinuedProducts 托管存储过程，然后点击“下一步”。

图 16：选择 GetDiscontinuedProducts 托管存储过程（单击以查看全尺寸图像）

然后，系统会要求指定存储过程是返回行、单个值还是不返回任何行。 由于 GetDiscontinuedProducts 返回一组已停用的产品行，请选择第一个选项（表格数据），然后单击“下一步”。

图 17：选择表格数据选项（单击以查看全尺寸图像）

最后一个向导屏幕允许我们指定使用的数据访问模式和生成的方法的名称。 选中这两个复选框，并命名方法和 FillByDiscontinued GetDiscontinuedProducts。 单击“完成”，完成向导。

图 18：命名方法FillByDiscontinued（GetDiscontinuedProducts单击以查看全尺寸图像）

重复这些步骤，为托管存储过程创建命名FillByPriceLessThan和GetProductsWithPriceLessThan传入ProductsTableAdapterGetProductsWithPriceLessThan的方法。

图 19 显示了将方法添加到ProductsTableAdapterGetDiscontinuedProducts托管GetProductsWithPriceLessThan存储过程后数据集设计器的屏幕截图。

图 19： ProductsTableAdapter 包括此步骤中添加的新方法（单击以查看全尺寸图像）

步骤 7：将相应的方法添加到业务逻辑层

现在，我们已经更新了数据访问层，以包含用于调用步骤 4 和 5 中添加的托管存储过程的方法，我们需要将相应的方法添加到业务逻辑层。 将以下两种方法添加到 ProductsBLLWithSprocs 类：

<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetDiscontinuedProducts() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetDiscontinuedProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceLessThan(ByVal priceLessThan As Decimal) _
    As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceLessThan(priceLessThan)
End Function

这两种方法只需调用相应的 DAL 方法并返回 ProductsDataTable 实例。 上述每个方法的 DataObjectMethodAttribute 标记会导致这些方法包含在 ObjectDataSource 配置数据源向导的 SELECT 选项卡中的下拉列表中。

步骤 8：从呈现层调用托管存储过程

随着业务逻辑和数据访问层的增强，包括对调用 GetDiscontinuedProducts 和管理 GetProductsWithPriceLessThan 存储过程的支持，我们现在可以通过 ASP.NET 页显示这些存储过程结果。

打开 ManagedFunctionsAndSprocs.aspx 文件夹中的页面 AdvancedDAL ，然后从工具箱将 GridView 拖到设计器上。 将 GridView 属性IDDiscontinuedProducts设置为其智能标记，并将其绑定到名为 DiscontinuedProductsDataSource 的新 ObjectDataSource。 将 ObjectDataSource 配置为从 ProductsBLLWithSprocs 类 s GetDiscontinuedProducts 方法拉取其数据。

图 20：将 ObjectDataSource 配置为使用 ProductsBLLWithSprocs 类（单击以查看全尺寸图像）

图 21： GetDiscontinuedProducts 从 SELECT 选项卡中的下拉列表中选择方法（单击以查看全尺寸图像）

由于此网格将仅用于显示产品信息，请将 UPDATE、INSERT 和 DELETE 选项卡中的下拉列表设置为“无”，然后单击“完成”。

完成向导后，Visual Studio 将自动为 中的每个 ProductsDataTable数据字段添加 BoundField 或 CheckBoxField。 请花点时间删除除 GridView 和 ObjectDataSource 声明性标记之外ProductNameDiscontinued的所有字段，此时，声明性标记应如下所示：

<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 打包为 a ProductsDataTable ，然后返回到 BLL，然后返回到呈现层，其中绑定到 GridView 并显示它们。 如预期所示，网格列出了已停用的产品。

图 22：已列出停产产品（单击以查看全尺寸图像）

若要进一步练习，请将 TextBox 和另一个 GridView 添加到页面。 通过调用 ProductsBLLWithSprocs 类的方法，让此 GridView 显示小于输入到 TextBox 中的金额的产品 GetProductsWithPriceLessThan 。

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

用户定义的函数或 UDF 是数据库对象，用于在编程语言中密切模拟函数的语义。 与 Visual Basic 中的函数一样，UDF 可以包含可变数量的输入参数并返回特定类型的值。 UDF 可以返回标量数据（字符串、整数等）或表格数据。 让我们快速了解这两种类型的 UDF，从返回标量数据类型的 UDF 开始。

以下 UDF 计算特定产品的库存估计值。 它通过采用三个输入参数（ UnitPrice特定产品的值 UnitsInStock和 Discontinued 值）来执行此操作，并返回类型值 money。 它通过将库存乘以UnitPriceUnitsInStock计算库存的估计值。 对于已停止使用的项目，此值将减半。

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 找到它，方法是展开可编程性文件夹、Functions 和 Scalar-value Functions。 它可用于查询， SELECT 如下所示：

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 查询的输出。 另请注意，UDF 列在对象资源管理器的标量值 Functions 文件夹下。

图 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 （或JOIN） 子句中FROM引用此 UDF。 以下示例将返回 ProductID每个饮料的值 ProductName和 CategoryID 值。

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

我已将 udf_GetProductsByCategoryID UDF 添加到 Northwind 数据库;图 24 显示了通过 Management Studio 查看时上述 SELECT 查询的输出。 可以在 对象资源管理器 表值 Functions 文件夹中找到返回表格数据的 UDF。

图 24： ProductID每个饮料的 ， ProductName并 CategoryID 列出 （单击以查看全尺寸图像）

注意

有关创建和使用 UDF 的详细信息，请查看 用户定义的函数简介。 另请查看 用户定义的函数的优点和缺点。

步骤 10：创建托管 UDF

udf_ComputeInventoryValue udf_GetProductsByCategoryID在上述示例中创建的 UDF 是 T-SQL 数据库对象。 SQL Server 2005 还支持托管 UDF，这可以添加到 ManagedDatabaseConstructs 项目中，就像步骤 3 和步骤 5 中的托管存储过程一样。 对于此步骤，让我们在托管代码中实现 udf_ComputeInventoryValue UDF。

若要将托管 UDF 添加到ManagedDatabaseConstructs项目，请右键单击解决方案资源管理器中的项目名称，然后选择“添加新项”。 从“添加新项”对话框中选择“用户定义的模板”，并命名新的 UDF 文件 udf_ComputeInventoryValue_Managed.vb。

图 25：向项目添加新的托管 UDF ManagedDatabaseConstructs （单击以查看全尺寸图像）

用户定义的函数模板创建一个 Partial 类，该类的名称 UserDefinedFunctions 与类文件名称相同（udf_ComputeInventoryValue_Managed在此实例中）。 此方法使用 SqlFunction 属性进行修饰，该属性将该方法标记为托管 UDF。

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function udf_ComputeInventoryValue_Managed() As SqlString
        ' Add your code here
        Return New SqlString("Hello")
    End Function
End Class

该方法 udf_ComputeInventoryValue 当前返回一个 SqlString 对象 ，不接受任何输入参数。 我们需要更新方法定义，以便它接受三个输入参数 - UnitPrice， UnitsInStock和 Discontinued - 并返回一个 SqlMoney 对象。 计算清单值的逻辑与 T-SQL udf_ComputeInventoryValue UDF 中的逻辑相同。

<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function udf_ComputeInventoryValue_Managed _
    (UnitPrice As SqlMoney, UnitsInStock As SqlInt16, Discontinued As SqlBoolean) _
    As SqlMoney
    Dim inventoryValue As SqlMoney = 0
    If Not UnitPrice.IsNull AndAlso Not UnitsInStock.IsNull Then
        inventoryValue = UnitPrice * UnitsInStock
        If Discontinued = True Then
            inventoryValue = inventoryValue * New SqlMoney(0.5)
        End If
    End If
    Return inventoryValue
End Function

请注意，UDF 方法的输入参数是其相应的 SQL 类型：SqlMoney字段UnitPrice、SqlInt16字段UnitsInStock和SqlBoolean字段。Discontinued 这些数据类型反映了表中定义的 Products 类型： UnitPrice 列的类型 money、 UnitsInStock 类型的 smallint列和 Discontinued 类型的 bit列。

代码首先创建一个名为 SqlMoney inventoryValue 0 的实例。 该Products表允许列和UnitsInPriceUnitsInStock列中的数据库NULL值。 因此，我们需要首先检查这些值是否包含 NULL s，我们通过对象属性IsNull进行检查。SqlMoney 如果同时UnitPriceUnitsInStock包含非NULL值，我们将计算inventoryValue为两者的乘积。 然后，如果 Discontinued 为 true，则我们将该值减半。

注意

该 SqlMoney 对象仅允许将两 SqlMoney 个实例相乘。 它不允许实例 SqlMoney 乘以文本浮点数。 因此，若要将其 inventoryValue 乘以值为 0.5 的新 SqlMoney 实例。

步骤 11：部署托管 UDF

创建托管 UDF 后，即可将其部署到 Northwind 数据库。 正如我们在步骤 4 中看到的，SQL Server 项目中的托管对象是通过右键单击解决方案资源管理器中的项目名称并选择上下文菜单中的“部署”选项来部署的。

部署项目后，返回到 SQL Server Management Studio 并刷新标量值 Functions 文件夹。 现在应会看到两个条目：

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

若要测试此托管 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 而不是 T-SQL udf_ComputeInventoryValue UDF，但输出相同。 请参阅图 23，查看 UDF 输出的屏幕截图。

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

在 调试存储过程 教程中，我们讨论了通过 Visual Studio 调试 SQL Server 的三个选项：从 SQL Server 项目直接数据库调试、应用程序调试和调试。 托管数据库对象不能通过直接数据库调试进行调试，但可以直接从客户端应用程序和 SQL Server 项目进行调试。 但是，为了使调试正常工作，SQL Server 2005 数据库必须允许 SQL/CLR 调试。 回想一下，当我们首次创建 ManagedDatabaseConstructs 项目时，Visual Studio 询问我们是否要启用 SQL/CLR 调试（请参阅步骤 2 中的图 6）。 可以通过右键单击服务器资源管理器窗口中的数据库来修改此设置。

图 26：确保数据库允许 SQL/CLR 调试

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

图 27：在 GetProductsWithPriceLessThan 方法中设置断点（单击可查看全尺寸图像）

让我们首先看看如何从 SQL Server 项目调试托管数据库对象。 由于我们的解决方案包含两个项目（ ManagedDatabaseConstructs SQL Server 项目和网站）才能从 SQL Server 项目进行调试，因此我们需要指示 Visual Studio 在开始调试时启动 ManagedDatabaseConstructs SQL Server 项目。 右键单击ManagedDatabaseConstructs解决方案资源管理器中的项目，然后从上下文菜单中选择“设为启动项目”选项。

ManagedDatabaseConstructs从调试器启动项目时，Test Scripts它会在位于文件夹中的Test.sql文件中执行 SQL 语句。 例如，若要测试 GetProductsWithPriceLessThan 托管存储过程，请将现有 Test.sql 文件内容替换为以下语句，该语句调用 GetProductsWithPriceLessThan 传入值 14.95 的 @CategoryID 托管存储过程：

exec GetProductsWithPriceLessThan 14.95

输入上述脚本 Test.sql后，转到“调试”菜单，选择“开始调试”或点击工具栏中的 F5 或绿色播放图标，开始调试。 这将在解决方案中生成项目，将托管数据库对象部署到 Northwind 数据库，然后执行 Test.sql 脚本。 此时，将命中断点，我们可以单步执行 GetProductsWithPriceLessThan 方法、检查输入参数的值等。

图 28：命中方法中的 GetProductsWithPriceLessThan 断点（单击以查看全尺寸图像）

为了使 SQL 数据库对象通过客户端应用程序进行调试，必须将数据库配置为支持应用程序调试。 右键单击服务器资源管理器中的数据库，并确保选中了“应用程序调试”选项。 此外，我们需要将 ASP.NET 应用程序配置为与 SQL 调试器集成并禁用连接池。 这些步骤在调试存储过程教程的步骤 2 中进行了详细讨论。

配置 ASP.NET 应用程序和数据库后，请将 ASP.NET 网站设置为启动项目并开始调试。 如果访问调用具有断点的托管对象之一的页面，应用程序将停止和控制，并会转到调试器，可在其中单步执行代码，如图 28 所示。

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

SQL Server 项目可以轻松地创建、编译和部署托管数据库对象。 遗憾的是，SQL Server 项目仅在 Visual Studio 的 Professional 和 Team Systems 版本中可用。 如果使用 Visual Web 开发人员或 Visual Studio 的标准版，并且想要使用托管数据库对象，则需要手动创建和部署它们。 这涉及四个步骤：

1. 创建包含托管数据库对象的源代码的文件，
2. 将对象编译为程序集，
3. 将程序集注册到 SQL Server 2005 数据库，并
4. 在 SQL Server 中创建指向程序集中相应方法的数据库对象。

为了说明这些任务，让我们创建一个新的托管存储过程，以返回其大于指定值的这些产品 UnitPrice 。 在计算机上创建一 GetProductsWithPriceGreaterThan.vb 个名为并输入以下代码的新文件（可以使用 Visual Studio、记事本或任何文本编辑器来完成此操作）：

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub GetProductsWithPriceGreaterThan(ByVal price As SqlMoney)
        'Create the command
        Dim myCommand As 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)
    End Sub
End Class

此代码与步骤 5 中创建的方法几乎完全相同 GetProductsWithPriceLessThan 。 唯一的区别是方法名称、 WHERE 子句和查询中使用的参数名称。 返回 GetProductsWithPriceLessThan 方法后，子 WHERE 句将读取： WHERE UnitPrice < @MaxPrice。 在这里，GetProductsWithPriceGreaterThan我们使用： WHERE UnitPrice > @MinPrice

现在，我们需要将此类编译为程序集。 在命令行中，导航到保存 GetProductsWithPriceGreaterThan.vb 文件的目录，并使用 C# 编译器 （csc.exe） 将类文件编译为程序集：

vbc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.vb

如果包含 v bc.exe 的文件夹不在系统中 PATH，则必须完全引用其路径， %WINDOWS%\Microsoft.NET\Framework\version\如下所示：

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.vb

图 29：编译 GetProductsWithPriceGreaterThan.vb 为程序集（单击以查看全尺寸图像）

该 /t 标志指定应将 Visual Basic 类文件编译为 DLL（而不是可执行文件）。 该 /out 标志指定生成的程序集的名称。

注意

你可以选择使用 Visual Basic Express Edition 或在 Visual Studio 标准版中创建单独的类库项目，而不是从命令行编译GetProductsWithPriceGreaterThan.vb类文件。 S 任 Jacob Lauritsen 提供了这样的 Visual Basic Express Edition 项目，其中包含存储过程的代码 GetProductsWithPriceGreaterThan 以及步骤 3、5 和 10 中创建的两个托管存储过程和 UDF。 S 任项目还包括添加相应数据库对象所需的 T-SQL 命令。

将代码编译为程序集后，即可在 SQL Server 2005 数据库中注册该程序集。 这可以通过 T-SQL、使用命令 CREATE ASSEMBLY或通过 SQL Server Management Studio 执行。 让我们专注于使用 Management Studio。

在 Management Studio 中，展开 Northwind 数据库中的可编程性文件夹。 其子文件夹之一是程序集。 若要手动向数据库添加新程序集，请右键单击“程序集”文件夹，然后从上下文菜单中选择“新建程序集”。 这将显示“新建程序集”对话框（请参阅图 30）。 单击“浏览”按钮，选择刚刚编译的 ManuallyCreatedDBObjects.dll 程序集，然后单击“确定”将程序集添加到数据库。 不应在对象资源管理器中看到ManuallyCreatedDBObjects.dll程序集。

图 30：将 ManuallyCreatedDBObjects.dll 程序集添加到数据库（单击以查看全尺寸图像）

图 31：ManuallyCreatedDBObjects.dll对象资源管理器中列出了

虽然我们已将程序集添加到 Northwind 数据库，但尚未将存储过程与 GetProductsWithPriceGreaterThan 程序集中的方法相关联。 为此，请打开一个新的查询窗口并执行以下脚本：

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

这将在 Northwind 数据库中创建一个新的存储过程， GetProductsWithPriceGreaterThan 并将其与托管方法 GetProductsWithPriceGreaterThan （位于程序集中的类 StoredProcedures） ManuallyCreatedDBObjects相关联。

执行上述脚本后，刷新对象资源管理器中的“存储过程”文件夹。 应会看到一个新的存储过程条目 - GetProductsWithPriceGreaterThan 旁边有一个锁图标。 若要测试此存储过程，请在查询窗口中输入并执行以下脚本：

exec GetProductsWithPriceGreaterThan 24.95

如图 32 所示，上述命令显示这些产品 UnitPrice 的信息大于 24.95 美元。

图 32：对象资源管理器ManuallyCreatedDBObjects.dll中列出了该图像（单击以查看全尺寸图像）

总结

Microsoft SQL Server 2005 提供与公共语言运行时（CLR）的集成，该运行时允许使用托管代码创建数据库对象。 以前，这些数据库对象只能使用 T-SQL 创建，但现在可以使用 Visual Basic 等 .NET 编程语言创建这些对象。 在本教程中，我们创建了两个托管存储过程和一个托管用户定义函数。

Visual Studio 的 SQL Server 项目类型有助于创建、编译和部署托管数据库对象。 此外，它还提供丰富的调试支持。 但是，SQL Server 项目类型仅在 Visual Studio 的 Professional 和 Team Systems 版本中可用。 对于使用 Visual Web 开发人员或 Visual Studio 标准版的用户，必须手动执行创建、编译和部署步骤，如步骤 13 中所述。

快乐编程！

深入阅读

有关本教程中讨论的主题的详细信息，请参阅以下资源：

• 用户定义的函数的优点和缺点
• 在托管代码中创建 SQL Server 2005 对象
• 如何：创建和运行 CLR SQL Server 存储过程
• 如何：创建和运行 CLR SQL Server 用户定义的函数
• 如何：编辑 Test.sql 脚本以运行 SQL 对象
• 用户定义函数简介
• 托管代码和 SQL Server 2005 （视频）
• Transact-SQL 参考
• 演练：在托管代码中创建存储过程

关于作者

斯科特·米切尔，七本 ASP/ASP.NET 书籍的作者和 4GuysFromRolla.com 的创始人，自1998年以来一直在与Microsoft Web 技术合作。 斯科特担任独立顾问、教练和作家。 他的最新书是 山姆斯在24小时内 ASP.NET 2.0。 他可以通过他的博客联系到mitchell@4GuysFromRolla.com他，可以在该博客中找到http://ScottOnWriting.NET。

特别感谢

本教程系列由许多有用的审阅者审阅。 本教程的主要审阅者是 S 任 Jacob Lauritsen。 除了查看本文之外，S 任还创建了本文下载中包含的 Visual C# Express Edition 项目，用于手动编译托管数据库对象。 有兴趣查看即将发布的 MSDN 文章？ 如果是这样，请把我扔一条线。mitchell@4GuysFromRolla.com

上一篇