SQL Server 2005中的CLR集成-交流共享-唐山网站建设

SQL Server 2005中的CLR集成

核心提示：随着技术的不断升级，微软的王牌数据库SQL Server 2000正在逐渐淡出人们的视野，而新版的SQL Server 2005正成为企业和开发职员的新宠。本文重点关注SQL Server 2005的功能，特别是CLR集成，这是开发职员常常要使用的功能之1。

随着技术的不断升级，微软的王牌数据库SQL Server 2000正在逐渐淡出人们的视野，而新版的SQL Server 2005正成为企业和开发职员的新宠。本文重点关注SQL Server 2005的功能，特别是CLR集成，这是开发职员常常要使用的功能之1。

1、SQL Server 2005简介

SQL Server 2005较SQL Server 2000有了长足进步。SQL Server 2005带来了大量新功能，新图形化用户界面（GUI）和新治理工具。下面罗列了1些简单的功能先容：

能够在数据库中托管.NET Framework中的通用语言运行时（CLR），那末开发职员便可以够使用Visual Basic 2005和C#编写程序集。这对SQL Server数据库编程职员多是件有趣的事情，之前的编程职员受限于SQL和T-SQL，而如今却有力的暗示了利用程序的构建方法。

通过完全成熟的，能够保存多个相干数据类型的XML数据类型对XML进行深进支持。开发职员可以将XML文档存储到数据库中，同时验证其有效性，并且抽取文档的部份内容。这意味着可以将半结构化数据与关系型数据相结合，同时将它们存储到同1地方，并采取相同方式处理它们。另外，服务器端还提供了XML查询（XQuery）和XML架构定义语言（XSD）标准。

完全更新的GUI治理工具SQL Server Management Studio（SSMS），该工具提供的单个集成环境可满足配配置/治理需求。
将报表框架（SQL Server Reporting Services，缩写为SSRS）作为数据库整体的1部份。

为实现异步消息传递创建新的利用程序框架Service Broker。

经过大量改进和扩大的SQL Server Integration Services（SSIS，之前是Data Transformation Services），该工具可用于提取，转换和加载数据（另外，关系型数据库治理系统使用这个功能代价昂贵）。

以上只是先容了SQL Server 2005的1些重点功能，每个功能都可让开发职员细细品味，限于篇幅，本文重点先容其中的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之前，程序集也称为受管代码，它是1组编译为.dll或.exe的文件。在.NET Framework中依然存在这个术语和特性。但是，在SQL Server 2005中，程序集术语则变得有点模糊。

在SQL Server范围内，程序集是1个援用物理程序集.dll文件的对象。受管代码是.dll文件，该文件使用.NET Framework CLR和可访问其他受管代码来创建。更确切的说，是在SQL Server内部的其他受管代码。每段受管代码都包括两个重要的片断信息。1个是描写程序集的元数据，例如程序集方法和属性，程序集版本号。第2个片断信息是实际的受管代码，组成程序集的方法和属性。通常，使用1些高级编程语言（例如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集成的另1种方法是利用SQL Server外围利用配置器工具。为打开这个工具，可在Microsoft SQL Server 2005配置工具菜单当选择“SQL Server外围利用配置器”。要配置CLR集成，可单击对话框底部的“功能的外部利用配置器”。在弹出的对话框中，选择左侧Database Engine之下的CLR集成，然后选中右侧的CLR集成复选框。单击肯定按钮，从而启用该选项，同时封闭窗口。接下来便可以够使用受管代码创建SQL Server中的对象了，例如创建存储进程。

4、使用受管代码创建存储进程

第1个示例很简单，其用于帮助读者初步理解在程序集中使用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)：使用该方法可将消息发送给调用方利用程序。

1旦创建了类，下1步是创建受管代码。为此，通过开始→所有程序→Microsoft .NET Framework SDK v2.0→SDK命令提示符，可打开命令提示符窗口。

在命令提示符窗口下，访问C:\Projects\sample1目录，接着履行以下命令：
CSC /target:library C:\Projects\sample1\HelloWorldStoredProcedures.cs

此时，在sample1目录会看到1个新文件HelloWorldStoredProcedures.dll。

下1步是在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文件具有不同的版本号。

需要留意的1件事情是CREATE ASSEMBLY语句是以WITH PERMISSION_SET子句结尾。该子句设置了当SQL Server使用和访问程序集时，对程序集的访问权限。该子句的可用值是：

SAFE：这是默许级别，具有最大限制性。这意味着，代码不需要使用任何外部资源，在SQL Server内部可以完全控制操纵。SAFE代码能够访问本地SQL Server数据库中的数据，或履行计算，和处理不包括访问本地数据库之外资源的业务逻辑。阶乘计算是1个好例子。阶乘计算只需要整型输进，然后返回另外1个整数。计算阶乘无需打开磁盘上的文件。

EXTERNAL_ACCESS：该级别表示可使用某些外部资源，例如文件、网络、Web服务、环境变量和注册表。因此，假设代码计划将某些结果写进磁盘文件中，那末需要在SQL Server的EXTERNAL_ACCESS安全种别下注册这些代码。

UNSAFE：这个级别设置代码能够完成任何事情，因此应当尽可能避免使用。换言之，开发职员要求不做任何级别的控制，因此代码与外部存储进程具有相同权限。即使取得了与外部存储进程相同的权限，CLR依然会为开发职员代码1些方便。但是，这样在逻辑上可能存在漏洞，那末黑客便可以够随便访问系统的关键部份。因此，应当避免在SQL Server中使用UNSAFE代码。

本示例代码通过使用HelloWorldStoredProcedures.dll，同时将权限设置为SAFE创建了名为HelloWorld的程序集。
在创建程序集以后，下1步是创建1个使用该程序集的简单T-SQL存储进程。以下DDL语句为程序集创建了进口点：

CREATE PROCEDURE HelloWorld AS EXTERNAL NAME HelloWorld.HelloWorldStoredProcedures.HelloWorld 在履行存储进程之前，查看1下CREATE PROCEDURE语句中的EXTERNAL NAME语法，其设置了.NET程序集的方法，其语法格式以下：
Assembly_name.Class_name.Method_name

当使用前面的示例时，程序集名称来自CREATE ASSEMBLY，在此处是HelloWorld。第2部份是类名称，其来自HelloWorldStoredProcedures.cs文件代码。第3部份是方法名称，它也来自HelloWorldStoredProcedures.cs，在本示例中是HelloWorld。

将这些片断信息1起置于EXTERNAL NAME子句，其告知存储进程所履行的内容。

此时，可以豫备测试程序集和获得返回数据。为了测试示例，通过运行以下语句来履行存储进程：

EXEC HelloWorld

固然本示例很简单，但是它说明了创建和部署程序集的基本步骤。下1个示例将在此代码之上，说明触及访问SQL Server数据的更加复杂的功能。

读者肯定在想：“必须有1种更加简单的方法来天生和部署这些程序集”。实际上是存在这类方法的。但是，通过这些步骤手动天生和部署程序集的缘由是，帮助读者理解程序集天生进程产生的内幕。下面将说明如何使用Visual Studio 2005专业版创建CLR存储进程。

1 2 3 下1页

4.1 创建复杂CLR存储进程

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

当选择启用CLR存储进程调试和项目建立后，在菜单当选择“项目”→“添加存储进程”项，接着设置类名称为GetProducts.cs。1旦创建了类，可根据示例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代码的同1上下文中履行SQL语句。为了启用上下文连接，可将连接字符串设置为“context connection=True”。

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

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

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

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

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

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

上1页 1 2 3 下1页

以下为援用的内容：

Enter Category ID:

AutoGenerateColumns="false" DataSourceID="productSource">

SelectCommand="GetProducts" SelectCommandType="StoredProcedure"
ConnectionString="">

ControlID="txtCategoryID"
PropertyName="Text" />

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

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

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

留意，开发职员应当将SQLCLR作为1种没法使用T-SQL显式表达逻辑的备选解决方案——也不能作为实现业务层逻辑的替换品。那末根据这个规则，开发职员应当首先使用T-SQL解决题目。

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

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

将这样的责任都拜托给宿主(SQL Server)反而会带来有趣的挑战。就本质而言，这意味着运行在SQL Server内部的个别操纵此时可能被以为具有1定讹诈性，是1个潜伏的安全线程，或可能下降服务器安全性。因此，当自由使用CLR及其各种操纵时，则变得有些危险，那末SQL Server利用程序则以1定方式正确的自己实现，即个别利用程序不应当以任何方式忽视或暗中侵害服务器。

通过对SQL Server内部能够履行的.NET代码操纵集合的粒度控制能够强迫SQL Server实现以上内容。这类机制构建于代码访问安全(Code Access Security，缩写为CAS)之上，它是CLR的1部份。微软开发职员要花费长时间艰巨的查看.NET Framework中的每个类，将它们分为3种种别(SAFE，EXTERNAL_ACCESS和UNSAFE)，这些种别在上面的内容中已先容了。换言之，开发职员需要告知SQL Server，根据所设置操纵，代码将依照3种种别之1履行。假设代码试图进行1些初始化设置之外的事情，那末SQL Server会禁止其履行。

小结

本文重点对SQL Server 2005中的CLR集成功能进行了讲授。这的确是1种非常吸引开发职员的功能，能够大幅进步开发效率，减低利用程序的保护修改强度。希看开发职员能够熟练的使用这项功能。

上1页 1 2 3 http://www.fw8.net/

TAG:程序,进程,代码,示例,职员

评论加载中...