SQL Server 2005中的CLR集成
1、SQL Server 2005简介
SQL Server 2005较SQL Server 2000有了长足进步。SQL Server 2005带来了大量新功能，新图形化用户界面（GUI）和新管理工具。下面列举了一些简单的功能介绍：
能够在数据库中托管.NET Framework中的通用语言运行时（CLR），那么开发人员就可以使用Visual Basic 2005和C#编写程序集。这对于SQL Server数据库编程人员可能是件有趣的事情，以前的编程人员受限于SQL和T-SQL，而如今却有力的暗示了应用程序的构建方法。
通过完全成熟的，能够保存多个相关数据类型的XML数据类型对XML进行深入支持。开发人员可以将XML文档存储到数据库中，同时验证其有效性，并且抽取文档的部分内容。这意味着可以将半结构化数据与关系型数据相结合，同时将它们存储到同一地方，并采取相同方式处理它们。另外，服务器端还提供了XML查询（XQuery）和XML架构定义语言（XSD）标准。
完全更新的GUI管理工具SQL Server Management Studio（SSMS），该工具提供的单个集成环境可满足配配置/管理需求。
将报表框架（SQL Server Reporting Services，缩写为SSRS）作为数据库整体的一部分。
为实现异步消息传递创建新的应用程序框架Service Broker。
经过大量改进和扩展的SQL Server Integration Services（SSIS，以前是Data Transformation Services），该工具可用于提取，转换和加载数据（另外，关系型数据库管理系统使用这个功能代价昂贵）。
以上只是介绍了SQL Server 2005的一些重点功能，每个功能都可以让开发人员细细品味，限于篇幅，本文重点介绍其中的CLR集成功能。
在SQL Server 2005中可使用通用语言运行时（CLR）集成，这意味着在SQL Server实例中提供对于.NET Framework编程模型的访问是很有必要的。为此，在SQL Server 2005中引入了程序集概念。程序集是.NET编译和托管的DLL文件。SQL Server使用程序集部署对象，例如存储过程、用户定义类型、触发器和用户定义函数。这些对象通常使用T-SQL编写，但是而今使用多种受管语言代码（例如VB.NET或者C#）也能够创建和编写这些对象。SQL Server 2005的这种新功能还提供了在数据库对象（例如存储过程，函数和类型）中，访问经过改进的.NET Framework编程模型的能力。.NET Framework 2.0版本具有多方面增强功能，这些经过改进的功能有很多已经可以为CLR集成使用。
2、受管程序集的概念
在SQL Server 2005之前，程序集也称为受管代码，它是一组编译为.dll或者.exe的文件。在.NET Framework中仍然存在这个术语和特性。然而，在SQL Server 2005中，程序集术语则变得有点模糊。
在SQL Server范围内，程序集是一个引用物理程序集.dll文件的对象。受管代码是.dll文件，该文件使用.NET Framework CLR和可访问其他受管代码来创建。更确切的说，是在SQL Server内部的其他受管代码。每段受管代码都包括两个重要的片段信息。一个是描述程序集的元数据，例如程序集方法和属性，程序集版本号。第二个片段信息是实际的受管代码，组成程序集的方法和属性。通常，使用一些高级编程语言（例如C#或者Visual Basic.NET）编写受管代码，这些代码共享类库，同时被编译为中间语言（Intermediate Language，缩写为IL）。
程序集中的受管代码实现SQL Server对象的功能，例如存储过程、UDT、CLR函数和CLR触发器。更为重要的是，程序集自身控制受管代码访问内部和外部资源的权限级别。当在SQL Server中利用CREATE ASSEMBLY语句创建程序集时，.dll文件会物理的加载到SQL Server中，这样SQL Server引擎就能够引用和使用程序集。SQL Server 2005中有两个说明所创建程序集的表，它们是sys.assemblies和sys.assembly_files。
3、启用CLR集成的方法
当开发人员开始在SQL Server 2005中使用程序集之前，需要告知SQL Server已经准备好在SQL Server中与CLR交互。默认情况下，禁用CLR集成功能，必须启用CLR集成才能SQL Server中访问.NET对象。
为了启用CLR集成，在SQL Server Management Studio的查询窗口中执行以下代码：
EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE GO
要启用CLR集成，必须具有ALTER SETTINGS服务器权限。该权限由sysadmin和serveradmin固定服务器角色的成员显式持有。
启用CLR集成的另一种方法是利用SQL Server外围应用配置器工具。为打开这个工具，可在Microsoft SQL Server 2005配置工具菜单中选择“SQL Server外围应用配置器”。要配置CLR集成，可单击对话框底部的“功能的外部应用配置器”。在弹出的对话框中，选择左边Database Engine之下的CLR集成，然后选中右边的CLR集成复选框。单击确定按钮，从而启用该选项，同时关闭窗口。接下来就可以使用受管代码创建SQL Server中的对象了，例如创建存储过程。
4、使用受管代码创建存储过程
第一个示例很简单，其用于帮助读者初步理解在程序集中使用SQL Server 2005的方法。首先，在C:\Projects目录下创建名为sample1的文件夹。打开编辑器，输入以下代码，接着将其保存为HelloWorldStoredProcedures.cs：
using System; using System.Data; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; public class HelloWorldStoredProcedures { public static void HelloWorld() { SqlContext.Pipe.Send("Hello World"); } }
在HellpWorld()方法中，通过调用SqlContext类的Pipe属性能够获取指向SqlPipe对象的引用。当获取指向SqlPipe对象的引用后，接着可将表格式结果和消息发送到客户端。这个工作可由SqlPipe类的Send()方法完成。SqlPipe对象类似于ASP.NET中的Response对象。通过调用Send()的多种重载方法，开发人员可通过管道将数据发送给调用方应用程序。Send()的各种重载方法如下所示：
Send(SqlDataReader)：允许以SqlDataReader对象格式发送表格式结果。
Send(SqlDataRecord)：允许以SqlDataRecord对象格式发送结果。
Send(String)：使用该方法可将消息发送给调用方应用程序。
一旦创建了类，下一步是创建受管代码。为此，通过开始→所有程序→Microsoft .NET Framework SDK v2.0→SDK命令提示符，可打开命令提示符窗口。
在命令提示符窗口下，访问C:\Projects\sample1目录，接着执行以下命令：
CSC /target:library C:\Projects\sample1\HelloWorldStoredProcedures.cs
此时，在sample1目录会看到一个新文件HelloWorldStoredProcedures.dll。
下一步是在SQL Server中注册程序集。在SQL Server Management Studio中打开查询窗口，确认选中AdventureWorks数据库，接着执行以下T-SQL语句：
CREATE ASSEMBLY HelloWorld FROM 'C:\Projects\sample1\HelloWorldStoredProcedures.dll' WITH PERMISSION_SET = SAFE
CREATE ASSEMBLY语句将程序集加载到SQL Server中，此时可以在SQL Server内部引用该程序集。在SQL Server中，可以以相同文件名称存储程序集的多个版本，只要每个.dll文件具有不同的版本号。
需要注意的一件事情是CREATE ASSEMBLY语句是以WITH PERMISSION_SET子句结尾。该子句设置了当SQL Server使用和访问程序集时，对程序集的访问权限。该子句的可用值是：
SAFE：这是默认级别，具有最大限制性。这意味着，代码不需要使用任何外部资源，在SQL Server内部可以完全控制操作。SAFE代码能够访问本地SQL Server数据库中的数据，或者执行计算，以及处理不包括访问本地数据库之外资源的业务逻辑。阶乘计算是一个好例子。阶乘计算只需要整型输入，然后返回另外一个整数。计算阶乘无需打开磁盘上的文件。
EXTERNAL_ACCESS：该级别表示可以使用某些外部资源，例如文件、网络、Web服务、环境变量和注册表。因此，如果代码计划将某些结果写入磁盘文件中，那么需要在SQL Server的EXTERNAL_ACCESS安全类别下注册这些代码。
UNSAFE：这个级别设置代码能够完成任何事情，因此应该尽量避免使用。换言之，开发人员要求不做任何级别的控制，因此代码与外部存储过程具有相同权限。即使获得了与外部存储过程相同的权限，CLR仍然会为开发人员代码一些方便。然而，这样在逻辑上可能存在漏洞，那么黑客就能够随意访问系统的关键部分。因此，应该避免在SQL Server中使用UNSAFE代码。
本示例代码通过使用HelloWorldStoredProcedures.dll，同时将权限设置为SAFE创建了名为HelloWorld的程序集。
在创建程序集之后，下一步是创建一个使用该程序集的简单T-SQL存储过程。以下DDL语句为程序集创建了入口点：
CREATE PROCEDURE HelloWorld AS EXTERNAL NAME HelloWorld.HelloWorldStoredProcedures.HelloWorld 在执行存储过程之前，查看一下CREATE PROCEDURE语句中的EXTERNAL NAME语法，其设置了.NET程序集的方法，其语法格式如下：
Assembly_name.Class_name.Method_name
当使用前面的示例时，程序集名称来自CREATE ASSEMBLY，在此处是HelloWorld。第二部分是类名称，其来自HelloWorldStoredProcedures.cs文件代码。第三部分是方法名称，它也来自HelloWorldStoredProcedures.cs，在本示例中是HelloWorld。
将这些片段信息一起置于EXTERNAL NAME子句，其告知存储过程所执行的内容。
此时，可以准备测试程序集和获取返回数据。为了测试示例，通过运行以下语句来执行存储过程：
EXEC HelloWorld
虽然本示例很简单，但是它说明了创建和部署程序集的基本步骤。下一个示例将在此代码之上，说明涉及访问SQL Server数据的更为复杂的功能。
读者肯定在想：“必须有一种更为简单的方法来生成和部署这些程序集”。实际上是存在这种方法的。然而，通过这些步骤手动生成和部署程序集的原因是，帮助读者理解程序集生成过程发生的内幕。下面将说明如何使用Visual Studio 2005专业版创建CLR存储过程。