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

作者 ：Scott Mitchell

下载 PDF

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

简介

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

SQL 的核心是用于处理数据集。 SELECT、 UPDATE和 DELETE 语句本质上适用于相应表中的所有记录，并且仅受其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中的功能，以及从你自己的自定义类。

本教程介绍如何创建托管存储过程和 User-Defined Functions，以及如何将它们集成到 Northwind 数据库中。 让我们开始吧！

注意

与 SQL 对象相比，托管数据库对象具有一些优势。 语言丰富、熟悉以及重用现有代码和逻辑的能力是main优势。 但是，使用不涉及太多过程逻辑的数据集时，托管数据库对象的效率可能较低。 若要更深入地讨论使用托管代码与 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 文件从网站文件夹 App_Data 移动到网站外部的文件夹。 将数据库文件移动到另一个文件夹后，我们需要将 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。

启动 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 Project 类型。 在本教程中，我们将演练使用 SQL Server 项目类型创建托管存储过程和 UDF。

注意

如果使用 Visual Web Developer 或 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 显示了“新建数据库引用”对话框，该对话框已填充为指向我们在步骤 1 中SQL Server 2005 Express Edition数据库实例中注册的 Northwind 数据库。

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

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

图 6：启用 SQL/CLR 调试

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

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

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

中的 NORTHWNDConnectionStringWeb.config 值当前引用 NORTHWND.MDF 文件夹中的文件 App_Data 。 由于从中删除了此数据库App_Data并将其显式注册到 SQL Server 2005 Express Edition 数据库实例中，因此需要相应地更新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 所示，这包括存储过程和 User-Defined 函数等。

让我们首先添加一个存储过程，该存储过程只是返回所有已停产的产品。 将新的存储过程文件 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

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

下面的代码创建一个 SqlCommand 对象，并将其设置为 CommandText 一个 SELECT 查询，该查询返回 Products 表中字段等于 1 的产品 Discontinued 的所有列。 然后，它会执行 命令并将结果发送回客户端应用程序。 将此代码添加到 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 对象的 属性提供对 SqlPipe 对象的Pipe访问。 此SqlPipe对象用于在SQL Server数据库和调用应用程序之间运送信息。 顾名思义， ExecuteAndSend 该方法 执行传入 SqlCommand 对象并将结果发送回客户端应用程序。

注意

托管数据库对象最适合使用过程逻辑而不是基于集的逻辑的存储过程和 UDF。 过程逻辑涉及逐行处理数据集或处理标量数据。 GetDiscontinuedProducts但是，我们刚刚创建的方法不涉及任何过程逻辑。 因此，理想情况下，它将作为 T-SQL 存储过程实现。 它是作为托管存储过程实现的，用于演示创建和部署托管存储过程所需的步骤。

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

完成此代码后，我们已准备好将其部署到 Northwind 数据库。 部署SQL Server Project 会将代码编译为程序集，向数据库注册程序集，并在数据库中创建相应的对象，并将其链接到程序集中的相应方法。 “部署”选项执行的确切任务集在步骤 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 数据库

另请展开“存储过程”文件夹。 在那里，你将看到一个名为 的 GetDiscontinuedProducts存储过程。 此存储过程由部署过程创建，指向 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：创建接受输入参数的托管存储过程

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

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

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

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) ，s SqlCommand 查询包含参数 (@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 中的 添加两个新方法ProductsTableAdapter，这最初是在为 Typed DataSet s TableAdapters 创建新的存储过程教程中创建的。NorthwindWithSprocs 在步骤 7 中，我们将向 BLL 添加相应的方法。

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

注意

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

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

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

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

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

然后，我们被要求指定存储过程是返回行、单个值还是不返回任何行。 由于 GetDiscontinuedProducts 返回停用的产品行集，请选择表格数据 ( ) 的第一个选项，然后单击“下一步”。

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

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

图 18：命名方法 FillByDiscontinued 并 GetDiscontinuedProducts (单击以查看全尺寸图像)

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

图 19 显示向 和 GetProductsWithPriceLessThan 托管存储过程添加 方法ProductsTableAdapter后 DataSet Designer的GetDiscontinuedProducts屏幕截图。

图 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 拖到Designer。 将 GridView 的 ID 属性设置为 DiscontinuedProducts ，并从其智能标记中将其绑定到名为 DiscontinuedProductsDataSource的新 ObjectDataSource。 配置 ObjectDataSource 以从 ProductsBLLWithSprocs 类 s GetDiscontinuedProducts 方法拉取其数据。

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

图 21： GetDiscontinuedProducts 从 SELECT 选项卡中 Drop-Down 列表中选择方法 (单击以查看全尺寸图像)

由于此网格仅用于显示产品信息，因此请将“更新”、“插入”和“删除”选项卡中的下拉列表设置为 (“无”) 然后单击“完成”。

完成向导后，Visual Studio 将自动为 中的每个 ProductsDataTable数据字段添加 BoundField 或 CheckBoxField。 请花点时间删除除 和 Discontinued以外的ProductName所有这些字段，此时 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 类 s GetDiscontinuedProducts 方法。 正如我们在步骤 7 中看到的，此方法调用 DAL 类 ProductsDataTable 的 GetDiscontinuedProducts 方法，该方法调用 GetDiscontinuedProducts 存储过程。 此存储过程是一个托管存储过程，执行我们在步骤 3 中创建的代码，返回停产的产品。

托管存储过程返回的结果由 DAL 打包到 中， ProductsDataTable 然后返回到 BLL，BLL 然后将结果返回到表示层，这些结果将绑定到 GridView 并显示。 与预期一样，网格会列出那些已停产的产品。

图 22：列出停产的产品 (单击以查看全尺寸图像)

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

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

User-Defined Functions 或 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 找到它，方法是依次展开“可编程性”文件夹、“函数”和“标量值函数”。 它可以在查询中使用， 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查询的结果。 创建后，可以在查询的 (或 JOIN) 子句SELECT中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 的详细信息，检查 User-Defined Functions 简介。 还检查 User-Defined 函数的优点和缺点。

步骤 10：创建托管 UDF

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

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

图 25：将新的托管 UDF 添加到 ManagedDatabaseConstructs 项目 (单击以查看全尺寸图像)

User-Defined Function 模板使用方法创建名为 PartialUserDefinedFunctions 的类，该方法的名称与类文件的名称 (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对于 字段，SqlInt16对于 ，对于 UnitsInStock，SqlBoolean对于 。DiscontinuedUnitPrice 这些数据类型反映表中定义的Products类型：UnitPrice列的类型为 money，UnitsInStock类型smallint为 的列，类型为 的Discontinuedbit列。

代码首先创建一个名为 SqlMoneyinventoryValue 的实例，该实例的分配值为 0。 表Products允许 在 UnitsInPrice 和 UnitsInStock 列中提供数据库NULL值。 因此，我们需要首先检查，查看这些值是否包含 NULL s，而我们通过对象 s IsNull 属性执行此操作SqlMoney。 如果 和 UnitsInStock 都UnitPrice包含非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 项目中调试托管数据库对象。 由于我们的解决方案包括两个项目（ManagedDatabaseConstructsSQL Server项目和网站）若要从SQL Server项目进行调试，我们需要指示 Visual Studio 在开始调试时启动ManagedDatabaseConstructsSQL Server项目。 右键单击ManagedDatabaseConstructs解决方案资源管理器中的项目，然后从上下文菜单中选择“设置为启动项目”选项。

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

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 的专业版和团队系统版中可用。 如果使用的是 Visual Web Developer 或 Visual Studio Standard Edition，并且想要使用托管数据库对象，则需要手动创建和部署它们。 这涉及四个步骤：

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 子句和查询中使用的参数名称。 回到 方法中GetProductsWithPriceLessThanWHERE，子句为：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 Standard Edition 中创建单独的类库项目，而不是从命令行编译GetProductsWithPriceGreaterThan.vb类文件。 S ren Jacob Lauritsen 为此类 Visual Basic Express Edition 项目提供了存储过程的代码 GetProductsWithPriceGreaterThan ，以及步骤 3、5 和 10 中创建的两个托管存储过程和 UDF。 S ren 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 所示，上述命令显示那些大于 $24.95 的产品 UnitPrice 的信息。

图 32：ManuallyCreatedDBObjects.dll在对象资源管理器 (单击以查看全尺寸图像)

总结

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

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

编程愉快！

深入阅读

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

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

关于作者

Scott Mitchell 是七本 ASP/ASP.NET 书籍的作者， 4GuysFromRolla.com 的创始人，自 1998 年以来一直从事 Microsoft Web 技术工作。 Scott 担任独立顾问、培训师和作家。 他的最新书是 山姆斯在24小时内 ASP.NET 2.0自学。 可以在 上联系 mitchell@4GuysFromRolla.com他， 也可以通过他的博客联系到他，该博客可在 http://ScottOnWriting.NET中找到。

特别感谢

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

上一篇