使用 Visual Studio Express Edition 创建 CLR 存储过程

• 下载 BooksMgr 项目 - 7.9 KB

Visual Studio Express 版本不像专业版及更高版本那样提供创建 CLR 存储过程的项目类型（模板）。但创建 CLR 存储过程的原理非常简单，我们可以使用 Express 版本来完成。

CRL 存储过程创建步骤

CLR 存储过程的创建涉及以下步骤：

1. 创建类库项目。
2. 将存储过程定义为类的静态方法。此方法使用 SqlProcedureAttribute 属性进行修饰。
3. 部署类库
• 使用 CREATE ASSEMBLY 语句在 SQL Server 中注册类库。
• 使用 CREATE PROCEDURE 语句创建引用已注册程序集的存储过程。
4. 测试存储过程。

让我们使用 Visual Studio 2010 Express 版本来完成这些步骤。

步骤 1：创建类库项目

1. 选择 文件 -> 新建项目…
2. 选择类库项目，并将其命名为 BooksMgr。
3. 点击 确定 按钮。

这将创建一个类库项目，其中包含一个名为 Class1 的默认类。将此类重命名为 StoredProcedures。此类将包含我们的存储过程（当然，您可以在此类中定义多个存储过程）。

请确保将此项目的目标框架设置为 .Net Framework 3.5，因为我们将使用 SQL Server 2008R2，它要求 SQL CLR 程序集的目标版本为 .NET Framework 的 2.0、3.0 或 3.5 版本（而不是 4 版本）。这可以在项目属性的“应用程序”选项卡下完成。

步骤 2：定义存储过程

在 StoredProcedures 类中创建一个公共静态方法，并将其命名为 AddBook。使用 SqlProcedureAttribute 属性修饰此方法。

namespace BooksMgr
{
    public class StoredProcedures
    {
        [SqlProcedure()]
        public static void AddBook(string bookName, string authorName)
        {
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = @"INSERT INTO [BooksLibrary].[dbo].[Books]
                                        ([Name],[Author])
                                        VALUES
                                        (@Name,@Author)";
 
                command.Parameters.AddWithValue("@Name", bookName);
                command.Parameters.AddWithValue("@Author", authorName);                             
                conn.Open();
                command.ExecuteNonQuery();                
            }
        }
    }
}    

此存储过程将在 Books 表中插入一本书。这个简单的表有两个列：书名和作者名。SqlProcedureAttribute 属性将 AddBook 方法标记为存储过程。编译项目。

步骤 3：部署程序集

要部署程序集，我们需要将其注册到 SQL Server 的特定数据库中。我们将使用的数据库是一个简单的数据库。以下脚本将创建一个名为 BooksLibrary 的数据库及其唯一的 Books 表，请在 SQL Server Management Studio 中运行以下脚本。

CREATE DATABASE [BooksLibrary]
Go
USE [BooksLibrary]
GO
CREATE TABLE [dbo].[Books](
[Name] [nvarchar](1000) NOT NULL,
[Author] [nvarchar](1000) NOT NULL
) ON [PRIMARY]
GO 

为了部署创建的程序集，我们需要创建一个 SQL 脚本部署文件。因此，向 BooksMgr 项目添加一个新项（文本文件）。将此新文件重命名为 Deploy.sql，并将“复制到输出目录”属性设置为 如果较新则复制。这将把脚本文件复制到输出目录，为我们提供一个完整的包（二进制文件和部署脚本）。

在脚本文件中，我们将测试此程序集及其过程是否已存在于数据库中，如果存在，则将其删除。这可以通过以下脚本完成：

-- Deploy to 'BooksLibrary' database
USE [BooksLibrary]
 
-- Drop the stored procedure 'AddBook' if it exists
IF OBJECT_ID ('AddBook') is not null
BEGIN
	DROP PROCEDURE AddBook
END
GO
 
-- Drop the assembly 'BooksMgr' if it exists
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'BooksMgr') 
BEGIN
	DROP ASSEMBLY BooksMgr
END
GO

-- Create the assembly 'BooksMgr'
CREATE ASSEMBLY BooksMgr 
FROM 'F:\Dev\Projects\BooksMgr\BooksMgr\bin\Debug\BooksMgr.dll'
GO 

-- Create the stored procedure 'AddBook' with its parameters
CREATE PROCEDURE AddBook(@bookName nvarchar(1000), @authorName nvarchar(1000))
WITH EXECUTE AS CALLER AS EXTERNAL NAME BooksMgr.[BooksMgr.StoredProcedures].[AddBook]
GO

PRINT N'Deploying BooksMgr assemply to [BooksLibrary] database'
 
-- Deply to 'BooksLibrary' database
USE [BooksLibrary]
 
-- Drop the stored procedure 'AddBook' if it exists
IF OBJECT_ID ('AddBook') is not null
BEGIN
	DROP PROCEDURE AddBook
	PRINT N'Stored procedure AddBook dropped'
END
GO
 
-- Drop the assembly 'BooksMgr' if it exists
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'BooksMgr') 
BEGIN
	DROP ASSEMBLY BooksMgr
	PRINT 'Assembly BooksMgr.dll dropped'
END
GO
 
-- Create the assembly 'BooksMgr'
CREATE ASSEMBLY BooksMgr 
FROM 'F:\Dev\Projects\BooksMgr\BooksMgr\bin\Release\BooksMgr.dll'
PRINT 'Assembly BooksMgr.dll created'
GO
 
-- Create the stored procedure 'AddBook'with its parameters
CREATE PROCEDURE AddBook(@bookName nvarchar(1000), @authorName nvarchar(1000))
WITH EXECUTE AS CALLER AS EXTERNAL NAME BooksMgr.[BooksMgr.StoredProcedures].[AddBook]
GO
PRINT 'Stored procedure AddBook created'
Go

SqlCmd.exe -S iDevHawk\SQLEXPRESS2008R2 -i $(TargetDir)\Deploy.sql

请注意，您必须根据您的 SQL Server 的实际情况调整服务器名称。

尝试现在重新生成项目，您会注意到部署跟踪信息已打印在输出窗口中。

要验证部署是否已正确完成，

1. 打开 SQL Server Management Studio。
2. 展开“数据库”节点。
3. 展开 BooksLibrary 节点（我们的数据库）。
4. 展开“存储过程”节点。

请注意，存储过程旁边有一个锁图标。这意味着它不能在 Visual Studio 项目外部进行编辑。

步骤 4：测试存储过程

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

这将打开一个要求输入参数值的窗口；在各自的字段中键入书名和作者名。

点击 确定；将创建并执行用于执行存储过程的脚本。

我们可以在 Books 表中看到插入的书籍：

结论 

使用 Visual Studio 2010 Express 版本创建包含存储过程的程序集并部署它非常简单。

历史 

• 2012 年 5 月 23 日 - 初始版本