SQL Server 2005中的CLR集成_Mssql数据库教程

推荐：SQL SQL Server 2008中的新日期数据类型
这篇文章主要介绍了Oracle数据库到SQL Server数据库主键的迁移过程，具体内容请参考下文。 由于项目需要要将以前Oracle的数据库转化为SQL Server,今天利用SQL Server的DTD进行数据库的迁移，但导入以后发现只导入了表结构和数据，而表的一些主键约束都没导过

随着技术的不断升级，微软的王牌数据库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存储过程。

分享：解析SQL Server 2008性能和可扩展性
1.导言 现今的公司需要易访问的和可用性好的商业数据，以便他们可以在全球市场中获得一席之地。与易访问数据的这个需求相呼应的，关系数据库和分析数据库在规模方面继续发展，内嵌数据库和许多产品一起出现，并且许多公司将服务器合并来减轻管理工作。当公司