Share via

演练：使用托管代码创建存储过程

  • 项目

您可以通过 .NET Framework 语言（例如 Visual Basic 和 Visual C#），用托管代码编写 Microsoft SQL Server 2005 和 SQL Server 2008 数据库的存储过程。 用托管代码编写的存储过程称为 SQL Server 公共语言运行时存储过程（或 SQL CLR 存储过程）。

通过将**“存储过程”**项添加到 SQL CLR 数据库项目，可以创建 SQL 存储过程。 部署了用托管代码创建的存储过程之后，可以像调用和执行任何其他存储过程一样调用和执行这些过程。

本演练演示以下任务：

  • 创建新的 SQL CLR 数据库项目

  • 用托管代码创建一个存储过程

  • 将该存储过程部署到 SQL Server 2008 数据库

  • 创建一个脚本，以便对数据库测试存储过程

  • 查询数据库中的数据，以确认存储过程是否执行正确

系统必备

若要完成本演练，您需要：

  • 与运行于 SQL Server 2008 之上的 AdventureWorks2008 示例数据库的连接。 有关更多信息，请参见 Microsoft 网站上的 Microsoft SQL Server Product Samples（Microsoft SQL Server 产品示例）页。

创建项目

创建新的 SQL CLR 数据库项目

  1. 从**“文件”**菜单创建一个新的项目。

  2. 选择**“SQL CLR 数据库项目”，将项目命名为 SQLCLRStoredProcedure 并单击“确定”**。 有关更多信息，请参见如何：为使用 SQL Server 公共语言运行时集成的数据库对象创建项目。

连接到 SQL Server 2008 数据库

此演练需要连接到运行于 SQL Server 2008 之上的 AdventureWorks2008 示例数据库。 如果**“服务器资源管理器”**中提供了与 AdventureWorks2008 示例数据库的连接，该连接将列在“添加数据库引用”对话框中。

连接到 AdventureWorks 示例数据库

配置 SQL CLR 数据库项目

创建 SQL CLR 数据库项目之后，您可能需要修改若干项目属性，然后才能成功部署项目。

为 SQL CLR 数据库项目配置属性

  1. 在“解决方案资源管理器”中，选择要为其配置属性的项目。

  2. 在**“项目”菜单上，单击“ProjectName 属性”**，其中 ProjectName 是您为 SQL CLR 数据库项目赋予的名称。

  3. 在**“应用程序”选项卡上的“程序集”**名称中，指定要为从 SQL CLR 数据库项目中生成的程序集赋予的名称。

  4. 在**“应用程序”选项卡上的“目标 Framework”**中，单击 SQL Server 的目标版本支持的 .NET Framework 的版本。

    重要说明重要事项

    SQL Server 2005 和 SQL Server 2008 只支持使用 .NET Framework 2.0 生成的 SQL Server 项目。 如果您尝试部署SQL Server项目，SQL Server 2005或SQL Server 2008，将显示错误消息： Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database（在进行校验是您要部署的程序集的名称）。

  5. 在**“数据库”选项卡上的“程序集所有者”**中，键入作为程序集所有者的用户或角色的名称。

    重要说明重要事项

    此值必须是当前用户所属的角色的名称，或者是必须具有 IMPERSONATE 权限的当前用户的名称。 如果未指定程序集所有者，则所属权将授予当前用户。 此设置对应于 SQL Server CREATE ASSEMBLY 语句的 AUTHORIZATION 所有者名称 参数。 有关更多信息，请参见 Microsoft 网站上的 CREATE ASSEMBLY (Transact-SQL)。

  6. 在**“文件”菜单上，单击“全部保存”**。

创建 SQL Server 存储过程

创建了 SQL CRL 数据库项目后，向其中添加一个存储过程。

创建 SQL Server 存储过程

  1. 在**“项目”菜单上，单击“添加新项”**。

  2. 在**“添加新项”对话框中，选择“存储过程”**。

  3. 键入 InsertCurrency 作为新存储过程的**“名称”**。

  4. 单击**“添加”**。

  5. 使用以下代码替换代码编辑器中的代码：

    Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <SqlProcedure()>
    Public Shared Sub InsertCurrency(
        ByVal currencyCode As SqlString, ByVal name As SqlString)

        Using conn As New SqlConnection("context connection=true")

            Dim InsertCurrencyCommand As New SqlCommand()
            Dim currencyCodeParam As New SqlParameter("@CurrencyCode", SqlDbType.NVarChar)
            Dim nameParam As New SqlParameter("@Name", SqlDbType.NVarChar)

            currencyCodeParam.Value = currencyCode
            nameParam.Value = name


            InsertCurrencyCommand.Parameters.Add(currencyCodeParam)
            InsertCurrencyCommand.Parameters.Add(nameParam)

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" &
                " VALUES(@CurrencyCode, @Name, GetDate())"

            InsertCurrencyCommand.Connection = conn

            conn.Open()
            InsertCurrencyCommand.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub
End Class

    using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [SqlProcedure()]
    public static void InsertCurrency_CS(
        SqlString currencyCode, SqlString name)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand InsertCurrencyCommand = new SqlCommand();
            SqlParameter currencyCodeParam = new SqlParameter("@CurrencyCode", SqlDbType.NVarChar);
            SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);

            currencyCodeParam.Value = currencyCode;
            nameParam.Value = name;

            InsertCurrencyCommand.Parameters.Add(currencyCodeParam);
            InsertCurrencyCommand.Parameters.Add(nameParam);

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
                " VALUES(@CurrencyCode, @Name, GetDate())";

            InsertCurrencyCommand.Connection = conn;

            conn.Open();
            InsertCurrencyCommand.ExecuteNonQuery();
            conn.Close();
        }
    }
}

部署、执行和调试存储过程

创建新的存储过程后，可以通过按 F5 生成该存储过程，将其部署到 SQL Server 并进行调试。 首先，在位于项目的**“TestScripts”文件夹中的“Test.sql”**文件中，添加执行和测试存储过程的代码。 有关如何创建测试脚本的更多信息，请参见如何：编辑 Test.sql 脚本以运行使用 SQL Server 公共语言运行时集成的对象。

有关如何调试 SQL 的更多信息，请参见Debugging SQL Database Objects。

部署并运行 InsertCurrency 存储过程

  1. 在**“解决方案资源管理器”中展开“TestScripts”**文件夹，并双击 Test.sql 文件。

    提示

    您可以将其他脚本指定为默认调试脚本。 有关更多信息，请参见如何：编辑 Test.sql 脚本以运行使用 SQL Server 公共语言运行时集成的对象。

  2. 用下面的代码替换 Test.sql 文件中的代码：

    'VB
EXEC InsertCurrency 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

//C#
EXEC InsertCurrency_CS 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

  3. 按 F5 生成、部署并调试该存储过程。 有关如何不进行调试而直接部署的信息，请参见如何：将 SQL CLR 数据库项目项部署到 SQL Server。

    查看所示的结果输出 窗口，然后选择 显示输出： 数据库输出。

请参见

任务

如何：通过使用公共语言运行时集成创建和运行 SQL Server 存储过程

如何：通过使用公共语言运行时集成创建和运行 SQL Server 触发器

如何：通过使用公共语言运行时集成创建和运行 SQL Server 聚合

如何：通过使用公共语言运行时集成创建和运行 SQL Server 用户定义的函数

如何：通过使用公共语言运行时集成创建和运行 SQL Server 用户定义的类型

如何：调试 SQL CLR 存储过程

参考

SQL CLR 数据库项目和数据库对象的特性

概念

SQL Server CLR 集成简介 (ADO.NET)

使用托管代码创建数据库对象的好处