使用SQL Server的CLR集成

公共语言运行库 (CLR) 是 Microsoft .NET Framework 的核心，为所有 .NET Framework 代码提供执行环境。在 CLR 中运行的代码称为托管代码。CLR 提供执行程序所需的各种函数和服务，包括实时 (JIT) 编译、分配和管理内存、强制类型安全性、异常处理、线程管理和安全性。

通过在 Microsoft SQL Server 中托管 CLR（称为 CLR 集成），可以在托管代码中编写存储过程、触发器、用户定义函数、用户定义类型和用户定义聚合函数。因为托管代码在执行之前会编译为本机代码，所以，在有些方案中可以大大提高性能。

托管代码使用代码访问安全性 (CAS)、代码链接和应用程序域来阻止程序集执行某些操作。SQL Server 2005 使用 CAS 帮助保证托管代码的安全，并避免操作系统或数据库服务器受到威胁。

一、部署和编译CLR集成

CLR集成功能在system.data.dll的程序集中公开，该程序集是.Net Framework的一部分，在全局程序集缓存（GAC）以及.Net Framework中可以找到它。

System.data.dll程序集包括以下命名空间，这些命名空间是编译CLR数据库所必须的：

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

1、编写一个类文件，以helloWord.cs实现存储过程为例，

编写文件名为helloWord.cs的类文件，实现在SQL中“Hello world!20080102”输出，其代码如下：

using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

 

public class HelloWorldProc

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void HelloWorld()

    {

        SqlContext.Pipe.Send("Hello World!/n20080102/n");

    }

}

2、编译上述“helloWord”存储过程

运行cmd.exe，在.NET Framework的安装路径下，使用 /target 选项启动 Visual C# 或 Visual Basic 编译器，以指定生成一个库 DLL。例如编译保存在E盘根目录下的“helloWord.cs”，

则执行语句如下：

C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727>csc /target:library e:/helloWord.cs

如下图所示：

编译成功后，系统将在.Net Framework的安装路径下，创建一个名为helloWord.dll的库。

二、在SQL Server中运行CLR

1、在SQL Server中启用CLR

默认情况下，Microsoft SQL Server 中禁用公共语言运行库 (CLR) 集成功能，必须启用才能使用通过 CLR 集成实现的对象。

通过系统存储过程“SP_Configure”启用CLR：

EXEC SP_CONFIGURE 'clr enabled',1

GO

RECONFIGURE

GO

2、在SQL Server中加载和运行上述“helloWord”存储过程

在“helloWord”存储过程编译成功后，通过“CREATE ASSEMBLY”创建名称为“helloword”的程序集，SQL语句如下：

CREATE ASSEMBLY helloword from 'C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/helloWord.dll'

WITH PERMISSION_SET=SAFE

注：

SQL Server主机策略级权限集有三种：

（1）       SAFE：只允许内部计算和本地数据访问。SAFE是强制性最强的权限集。通过具有SAFE权限的程序集执行的代码无法访问外部系统资源，例如文件、网络、环境变量或注册表。

（2）       EXTERNAL_ACCESS：与SAFE权限集类似，但是可以访问外部系统资源，例如文件、网络、环境变量和注册表。

（3）   UNSAFE：允许程序集对资源进行不受限制的访问，无论在SQL SERVER内部还是外部，从UNSAFE程序集中执行的代码可以调用非托管代码。

在“helloword”程序集创建成功后，通过“CREATE PROCEDURE”创建名称为“hello”的存储过程来访问“helloWord”方法，SQL语句如下：

CREATE PROCEDURE hello

AS

EXTERNAL NAME helloword.HelloWorldProc.HelloWorld

在“hello”存储过程创建成功后，运行“EXEC hello”，系统将SQL Server Management Studio 消息窗口中输出如下信息：

Hello World!

20080102

2、从测试数据库中删除刚才创建的“helloWord”存储过程示例

DROP PROCEDURE hello         --删除存储过程

GO

DROP ASSEMBLY helloword     --删除程序集

GO