CLR 集成入门

本主题概述使用与 .NET Framework 公共语言运行时 (CLR) 的 Microsoft SQL Server 集成编译数据库对象所需的命名空间和库。本主题还说明如何编写、编译和运行用 Microsoft Visual C# 编写的简单 CLR 存储过程。

所需命名空间

从 SQL Server 2005 开始,开发基本 CLR 数据库对象所需的组件随 SQL Server 一起安装。CLR 集成功能在称作 system.data.dll 的程序集中公开,该程序集是 .NET Framework 的一部分。该程序集还在全局程序集缓存 (GAC) 以及 .NET Framework 目录中提供。对此程序集的引用通常由命令行工具和 Microsoft Visual Studio 自动添加,因此无需手动添加它。

system.data.dll 程序集包含以下命名空间,这些命名空间是编译 CLR 数据库对象所必需的:

System.Data

System.Data.Sql

Microsoft.SqlServer.Server

System.Data.SqlTypes

撰写一个简单的“Hello World”存储过程

将以下 Visual C# 或 Microsoft Visual Basic 代码复制并粘贴到某一文本编辑器中,并且将其保存在名为“helloworld.cs”或“helloworld.vb”的文件中。

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

public class HelloWorldProc
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld(out string text)
    {
        SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);
        text = "Hello world!";
    }
}
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
 
Public Class HelloWorldProc
    <Microsoft.SqlServer.Server.SqlProcedure> _ 
    Public Shared  Sub HelloWorld(<Out()> ByRef text as String)
        SqlContext.Pipe.Send("Hello world!" & Environment.NewLine)
        text = "Hello world!"
    End Sub
End Class

这一简单的程序包含针对公共类的单个静态方法。此方法使用两个新类 SqlContext 和 SqlPipe,用于创建托管数据库对象以输出简单的文本消息。此方法还分配字符串“Hello world!”作为输出参数的值。此方法可以声明为 SQL Server 中的存储过程,然后采用与 Transact-SQL 存储过程相同的方式运行。

我们现在将此程序编译为一个库,将其加载到 SQL Server 中,然后将其作为存储过程运行。

编译“Hello World”存储过程

SQL Server 默认情况下将安装 Microsoft .NET Framework 再分发文件。这些文件包括 csc.exe 和 vbc.exe,它们是用于 Visual C# 和 Visual Basic 程序的命令行编译器。为了编译我们的示例,您必须修改路径变量以指向包含 csc.exe 或 vbc.exe 的目录。下面是 .NET Framework 的默认安装路径。

C:\Windows\Microsoft.NET\Framework\(version)

Version 包含已安装 .NET Framework 可再发行组件的版本号。例如:

C:\Windows\Microsoft.NET\Framework\v2.0.31113

一旦将 .NET Framework 目录添加到路径后,您可以使用以下命令将该存储过程示例编译到某一程序集中。/target 选项可用于将其编译到某一程序集中。

对于 Visual C# 源文件:

csc /target:library helloworld.cs 

对于 Visual Basic 源文件:

vbc /target:library helloworld.vb

以上命令使用 /target 选项启动 Visual C# 或 Visual Basic 编译器,以指定生成库 DLL。

在 SQL Server 中加载并运行“Hello World”存储过程

一旦该存储过程示例成功编译后,就可以在 SQL Server 中测试它。为此,打开 SQL Server Management Studio 并创建一个新查询,将其连接到适合的测试数据库(例如,AdventureWorks2008R2 示例数据库)。

在 SQL Server 中,能否执行公共语言运行时 (CLR) 代码默认设置为 OFF。通过使用 sp_configure 系统存储过程可以启用 CLR 代码。有关详细信息,请参阅 启用 CLR 集成

我们将需要创建该程序集,以便可以访问该存储过程。对于此示例,我们将假定您已在 C:\ 目录中创建了 helloworld.dll 程序集。将以下 Transact-SQL 语句添加到您的查询中。

CREATE ASSEMBLY helloworld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

一旦创建了该程序集后,我们现在就可以通过使用 create procedure 语句访问 HelloWorld 方法。我们将存储过程称为“hello”:

CREATE PROCEDURE hello
@i nchar(25) OUTPUT
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld

一旦创建该存储过程后,就可以像用 Transact-SQL 编写的普通存储过程一样运行该存储过程。执行以下命令:

DECLARE @J nchar(25)
EXEC hello @J out
PRINT @J

这将在 SQL Server Management Studio 消息窗口中导致以下输出。

Hello world!
Hello world!

删除“Hello World”存储过程示例

在您完成该存储过程示例的运行后,可以从测试数据库中删除该过程和程序集。

首先,使用 drop procedure 命令删除该存储过程。

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hello')
   drop procedure hello

一旦该存储过程删除后,就可以删除包含示例代码的程序集。

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'helloworld')
   drop assembly helloworld