源自：（http://database.51cto.com/art/200804/71928.htm）

随着技术的不断升级，微软的王牌数据库SQL Server 2000正在逐渐淡出人们的视线，而新版的SQL Server 2005正成为企业和开发人员的新宠。本文重点关注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存储过程。

4.1 创建复杂CLR存储过程

在这个示例中，将创建从AdventureWorks数据库的Production.Product表返回数据的存储过程。首先，选择“文件”→“ 新建项目”，然后在弹出窗口的左边导航菜单中选择“Visual C#”→“数据库”，接着将项目模板选择为“SQL Server项目”，这样可在Visual Studio 2005中创建新项目SqlServerDataAccess。如果单击新建项目对话框中的OK按钮，那么会要求添加数据库引用。如果 AdventureWorks引用还不可用，那么可使用“添加新引用”选项来添加指向AdventureWorks数据库的引用。

当选择启用CLR存储过程调试和项目建立后，在菜单中选择“项目”→“添加存储过程”项，接着设置类名称为GetProducts.cs。一旦创建了类，可根据示例1修改代码。

示例1：由CLR存储过程返回表格式结果集

using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Data.SqlTypes; 
using Microsoft.SqlServer.Server; 
public partial class StoredProcedures 
{ 
[Microsoft.SqlServer.Server.SqlProcedure] 
public static void GetProducts(int categoryID) 
{ 
SqlConnection connection = new SqlConnection("context connection = True"); 
connection.Open(); 
SqlCommand command = new SqlCommand("SELECT ProductID, Name FROM " + 
"Production.Product WHERE ProductSubcategoryID = " + 
categoryID.ToString(), connection); 
SqlDataReader reader = command.ExecuteReader(); 
SqlContext.Pipe.Send(reader); 
} 
};

这个程序集中的代码创建了数据库连接，然后执行T-SQL语句，结果返回Product表中具有所提供类别ID的ProductID和Name列的 所有记录。注意，示例1所示的上下文连接允许在调用CLR代码的同一上下文中执行SQL语句。为了启用上下文连接，可将连接字符串设置为“context connection=True”。

为了使用在SQL Server中处理的数据，内部过程的提供程序进行了优化。使用内部过程受管的提供程序的类和方法，能够很简单的将查询提交给数据库，执行DML和DDL语句，以及将结果集和消息返回给客户端应用程序。

Microsoft.SqlServer.Server命名空间对组成内部过程提供程序的类型进行分组。这个命名空间与ADO.NET的 SqlClient命名空间共享很多相似性和接口，开发人员使用SqlClient命名空间从受管客户端和中间层应用程序访问SQL Server数据。由于具有相似性，所以能够很简单的将代码从客户端应用程序迁移到服务器和后台使用。

Microsoft.SqlServer.Server命名空间中有两个重要类，它们用于设置内部过程提供程序：

SqlContext：这个类封装了其他一些扩展。另外，该类提供了事务和数据库连接，它们是例程执行环境的一部分。
SqlPipe：这个类可使例程向客户端发送表格式结果和消息。这个类在概念上与ASP.NET中的Response类很相似，Response类也能用于向调用者发送消息。

既然已经创建了存储过程，那么就可以使用“生成”→“生成SqlServerDataAccess”菜单项生成项目。一旦项目生成，下一步是将存储 过程部署到SQL Server。与前一示例不同，前一示例必须通过很多步骤才能部署存储过程，而Visual Studio 2005只要单击按钮就能够部署存储过程。为此，可选择“生成”→“部署SqlServerDataAccess”菜单项。以上就是使用Visual Studio 2005部署CLR存储过程的全部过程。

此时，可以通过执行来测试这个存储过程。为了达成本示例目的，创建一个测试该存储过程的简单ASP.NET页面，如示例2所示。

正如这个示例所示，调用CLR存储过程与调用T-SQL存储过程没有任何不同。二者都按照相同的步骤，而没有任何改变。结果应该显示 Production.Product表中Name和ProductNumber列的所有记录。在这个示例中，返回了Name和 ProductNumber列的所有行，因为查询没有为返回单个行而设置特定ProductID。因此，返回所有行。

4.2 SQL Server中的T-SQL和受管代码

一种很吸引人的想法是认为，开发人员能够避免编写业务层，而在SQL Server中使用受管代码编写所有代码。然而，重要的是记住SQLCLR不是用于代替业务层。因此，正由于能够在SQL Server中编写C#或者VB.NET代码，所以不希望将SQL Server作为托管所有.NET代码的应用程序服务器。SQLCLR是专用于在SQL Server中实现特定目的，而此时T-SQL可能不是正确的选择。

注意，开发人员应该将SQLCLR作为一种无法使用T-SQL显式表达逻辑的备选解决方案——也不能作为实现业务层逻辑的替代品。那么根据这个规则，开发人员应该首先使用T-SQL解决问题。

T-SQL比较适合基于集合的操作，例如表格式数据，SQLCLR比较适合实现过程性代码和递归操作。然而，真正是否更加适合取决于多种因素。通过 以下三条规则可很好的比较总结T-SQL和SQLCLR：（1）基于集合的操作最好使用T-SQL。（2）使用程序性和递归代码最好使用 SQLCLR。（3）存在多个有关因素可能影响以上两条规则，例如编译CLR代码和解释性的T-SQL代码，在SQL Server中加载时的开销，过程中所需的数据访问，辅助函数库等等。这种情况由存在多种原因造成，其中多数由于数据库内部的CLR与Windows计算 机中的CLR受到了不同的约束限制。Windows计算机中的CLR由操作系统来运行，其与SQL Server中运行的同一CLR不同。主要的不同点是SQL Server所担负的责任，管理线程调用，同步，锁定和内存位置等方面。

Windows计算机中的CLR和SQL Server中的CLR之间的另一个重要不同是，用于加载CLR的引导机制。SQL Server 2005除非在需要的情况下，否则不会加载CLR。这是因为SQL Server遵循了保护内存，尽量加载任何所需资源的规则。根据除非需要，否则不加载CLR的原则，可节约CLR本来要占据的数兆字节内存空间。因此，如 果有少量使用CLR的代码片段，同时加载CLR，那么由此可能会影响特定计算机的其他操作。

将这样的责任都委托给宿主（SQL Server）反而会带来有趣的挑战。就本质而言，这意味着运行在SQL Server内部的个别操作此时可能被认为具有一定欺诈性，是一个潜在的安全线程，或者可能降低服务器安全性。因此，当*使用CLR及其各种操作时，则 变得有些危险，那么SQL Server应用程序则以一定方式正确的自己实现，即个别应用程序不应该以任何方式忽略或者暗中损害服务器。

通过对SQL Server内部能够执行的.NET代码操作集合的粒度控制能够强制SQL Server实现以上内容。这种机制构建于代码访问安全（Code Access Security，缩写为CAS）之上，它是CLR的一部分。微软开发人员要花费长时间艰难的查看.NET Framework中的每个类，将它们分为三种类别（SAFE，EXTERNAL_ACCESS和UNSAFE），这些类别在上面的内容中已经介绍了。换 言之，开发人员需要告诉SQL Server，根据所设置操作，代码将按照三种类别之一执行。如果代码试图进行一些初始化设置之外的事情，那么SQL Server会阻止其执行。

小结

本文重点对SQL Server 2005中的CLR集成功能进行了讲解。这的确是一种非常吸引开发人员的功能，能够大幅提高开发效率，减低应用程序的维护修改强度。希望开发人员能够熟练的使用这项功能。

转载于:https://www.cnblogs.com/AlanGan/archive/2008/11/11/1905784.html