SQL Server 2005中的CLR集成(1)
2015-08-28来源:易贤网

随着技术的不断升级,微软的王牌数据库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存储过程返回表格式结果集

推荐信息