Microsoft SQL Server CLR 数据库对象开发指南

CLR数据库对象开发指南CLR数据库对象开发指南在SQLServer2005中，最值得一提的开发特性就是与.NETFramework公共语言运行库（CommonLanguageRuntime——CLR）的整合。CLR的整合带来了一大堆新的功能，其中包括使用.Net兼容语言（C#、VB、C++）创建数据库对象。在本次技术手册中，我们将介绍SQLServer中的.NETCLR整合情况，并详细介绍如何创建数据库对象，相信在短时间内大家就可以掌握SQLServer的CLR开发。.NETframeworkCLR基础.NETFrameworkCLR与SQLServer2005数据库引擎集成的非常紧密。实际上，SQLServer数据库引擎是以CLR为基础宿主的。与同DB2和Oracle与.NET的集成相比，这一紧密的集成使得SQLServer2005具备了与.NET集成的几个明显的优势。开发CLR数据库对象：CLR架构开发CLR数据库对象：CLR程序集创建CLR数据库对象在SQLServer程序集创建以后，你可以使用SQLServerManagementStudio执行T-SQL的CREATEPROCEDURE，CREATETRIGGER，CREATEFUNCTION，CREATETYPE，或者CREATEAGGREGATE语句，使用EXTERNALNAME从句指向你之前创建的程序集。创建CLR数据库对象CLR存储过程（上）CLR存储过程（下）CLR触发器（上）TT数据库技术专题之“CLR数据库对象开发指南”Page2of46CLR触发器（下）用户定义类型（上）用户定义类型（下）用户定义函数调试CLR数据库对象在.NETFramework，VisualStudio2005和SQLServer2005的集成功能中，最酷的特性之一就是支持调试你创建的CLR数据库对象的能力。如此紧密的整合，使得SQLServer在与Oracle和DB2等同类数据库的竞争中遥遥领先。调试CLR数据库对象CLR聚合功能（上）CLR聚合功能（下）TT数据库技术专题之“CLR数据库对象开发指南”Page3of46开发CLR数据库对象：CLR架构.NETFrameworkCLR与SQLServer2005数据库引擎集成的非常紧密。实际上，SQLServer数据库引擎是以CLR为基础宿主的。与同DB2和Oracle与.NET的集成相比，这一紧密的集成使得SQLServer2005具备了与.NET集成的几个明显的优势。在下图3-1中，你可以看到SQLServer2005数据库引擎和CLR集成的概要介绍。如图3-1所示，你可以看到，CLR是SQLServer数据库引擎内的宿主在SQLServer数据库引擎内的。SQLServer数据库使用专用API或者宿主层与CLR交互，使Windows操作系统与CLR相连接。CLR宿主在SQLServer数据库内使得SQLServer数据库引擎可以控制CLR的几个重要方面，其中包括：内存管理线程垃圾回收DB2和Oracle的实现都使用CLR作为外部进程，这意味着CLR和数据库引擎都会竞争系统资源。SQLServer2005进程内宿主CLR给Oracle或者DB2使用的外部实现提供了几个重要的优势。首先，进程中宿主使得SQLServer可以控制CLR的执行，使得像内存管理，垃圾回收和线程管理这些重要的功能都在SQLServer数据库引擎的控制之下。在外部实现中，CLR将独立地管理这些功能。数据库引擎对系统需求整体上有一个更好的效果，可以比CLR本身能更好地管理内存和线程。最后，进程中宿主CLR将提供更好的性能和可扩展性。TT数据库技术专题之“CLR数据库对象开发指南”Page4of46图3-1:SQLServerCLR数据库架构启用CLR支持功能默认状态下，SQLServer数据库引擎中的CLR支持功能是处于关闭状态的。这是为了确保SQLServer的更新安装不会在管理员没有明确参与的情况下无意中启用新功能。要启用SQLServer的CLR支持，你需要使用SQLServersp_configure系统存储过程的高级选项，请看下面的列表：sp_configure'showadvancedoptions',1GORECONFIGUREGOsp_configure'clrenabled',1GORECONFIGUREGOCLR数据库对象组件TT数据库技术专题之“CLR数据库对象开发指南”Page5of46要创建.NET数据库对象，你要从编写管理代码开始，可以用任何一种.NET语言，比如VB，C#或者ManagedC++，然后把代码编译成.NET的DLL(动态链接库)。最常见的实现方式就是使用VisualStudio2005创建一个新的SQLServer项目，然后执行编译构建生成DLL。当然，你也可以使用你自己喜欢的编辑器编写.NET代码，然后使用.NETFrameworkSDK把代码编译成.NET动态库。ADO.NET是连接CLR动态库和SQLServer数据库的中间件。一旦.NET动态库创建好以后，你需要把该动态库注册到SQLServer，创建一个新的SQLServer数据库对象叫做程序集(assembly)。程序集本质上就是把.NET动态库封装了一下。然后你可以创建一个新的数据库对象(比如存储过程或者触发器)指向SQLServer程序集。在图3-2中，你可以看到创建CLR数据库对象的大致过程。图3-2:创建CLR数据库对象(作者：McGraw-Hill译者：冯昀晖来源：TT中国)TT数据库技术专题之“CLR数据库对象开发指南”Page6of46开发CLR数据库对象：CLR程序集SQLServer.NET数据提供器如果你熟悉ADO.NET，你可能想明确地了解一下CLR数据库对象怎样连接数据库。毕竟，ADO.NET使用基于.NET数据提供器(比如SQLServer.NET数据提供器)的客户端建立数据库连接，是用联网库连接的。为了一次数据库调用遍历整个系统的网络支持，对于直接运行在服务器上的代码来说，不是处理这一问题最高效的模式，但对于客户端应用来说是一件好事。为了解决这一问题，微软创建了新的SQLServer.NET数据提供器。SQLServer.NET数据提供器建立了向SQLServer数据库的内存内连接。程序集在CLR对象的编码完成以后，你可以使用该代码创建SQLServer程序集。如果你使用的是VisualStudio2005，那么你只要简单地选择“部署”选项就行了，它可以负责创建SQLServer程序集和创建目标数据库对象的处理。如果你没有使用VisualStudio2005或者你想手工执行部署步骤，那么你需要复制.NET动态库到一个指定的位置。然后，你可以使用SQLServerManagementStudio执行T-SQL的CREATEASSEMBLY语句，引用刚才路径的.NET动态库，如下面列表所示：CREATEASSEMBLYMyCLRDLLFROM'SERVERNAMECodeLibraryMyCLRDLL.dll'CREATEASSEMBLY命令有一个参数包含了动态库的路径，从该路径可以把动态库加载到SQLServer数据库。这个路径可以是一个本地路径，但是更多时候它可能是一个网络共享文件路径。在执行CREATEASSEMBLY时，动态库被复制到主数据库中。如果程序集被更新或者变成不推荐使用的了，你可以使用DROPASSEMBLY命令删除程序集。如下所示：TT数据库技术专题之“CLR数据库对象开发指南”Page7of46DROPASSEMBLYMyCLRDLL因为程序集存储在数据库中，当程序集的源代码被修改后程序集被重新编译，必须首先使用DROPASSEMBLY命令把程序集从数据库中删除掉，然后在更新从SQLServer数据库对象中反射出来之前使用CREATEASSEMBLY命令重新加载程序集。你可以使用视图sys.assemblies查看已经添加到SQLServer2005中的程序集。SQL如下：SELECT*FROMsys.assemblies既然程序集是使用外部文件创建的，你还可能想看看用来创建那些程序集的文件。你可以使用sys.assembly_files视图做到这一点。SQL如下：SELECT*FROMsys.assembly_files(作者：McGraw-Hill译者：冯昀晖来源：TT中国)TT数据库技术专题之“CLR数据库对象开发指南”Page8of46创建CLR数据库对象在SQLServer程序集创建以后，你可以使用SQLServerManagementStudio执行T-SQL的CREATEPROCEDURE，CREATETRIGGER，CREATEFUNCTION，CREATETYPE，或者CREATEAGGREGATE语句，使用EXTERNALNAME从句指向你之前创建的程序集。程序集被创建以后，动态库被复制到目标SQLServer数据库，程序集也被注册了。下面的代码展示了使用MyCLRDLL程序集创建MyCLRProc存储过程的方法。CREATEPROCEDUREMyCLRProcASEXTERNALNAMEMyCLRDLL.StoredProcedures.MyCLRProcEXTERNALNAME从句是SQLServer2005中的新特性。这里EXTERNALNAME从句表示将要使用SQLServer程序集来创建存储过程MyCLRProc。该动态库被封装在SQLServer程序集中，可以包含多个类和方法;EXTERNALNAME语句使用如下语法指定从程序集中使用正确的类和方法：AssemblyName.ClassName.MethodName在前面的例子中，注册的程序集名为MyCLRDLL。在程序集中的类是StoredProcedures，类中要执行的方法是MyCLRProc。关于怎样使用VisualStudio2005实际创建一个新的受管代码项目的具体例子会在下一节中详细阐述。创建CLR数据库对象前面的部分用一些手工操作CLR数据库对象例子的步骤展示了一个概要流程，是为了帮助你理解CLR数据库对象的创建和部署过程。然而，虽然也可以手工创建CLR数据库对象，但那肯定不是最高效的方法。VisualStudio2005专业版，企业版，团队系统版都提TT数据库技术专题之“CLR数据库对象开发指南”Page9of46供了帮助创建CLR数据库对象，并部署和调试CLR数据库对象的工具。在本章的下一部分，你会看到怎样使用VisualStudio2005创建CLR数据库的每一种对象。注意：SQLServer项目的创建只在VisualStudio2005专业版或者更高版本中才支持。在VisualStudio标准版或者VisualStudio的早期版本中是不支持的。(作者：McGraw-Hill译者：冯昀晖来源：TT中国)TT数据库技术专题之“CLR数据库对象开发指南”Page10of46CLR存储过程（上）存储过程是最常见的数据库对象之一，你可以使用某一种.NET语言创建存储过程。CLR存储过程的最大价值之一是替换现存的扩展存储过程。T-SQL只可以访问数据库的资源。为了访问外部系统资源，微软已经在SQLServer中提供了支持，叫做扩展存储过程（extendedstoredprocedure）。扩展存储过程是不受管理的动态库，运行在SQLServer进程空间，基本上可以做到任何标准可执行程序能做到的事，包括访问数据库外部的系统资源，比如读写文件系统，读写注册表和访问网络。然而，因为扩展存储过程与SQLServer数据库引擎运行在相同的进程空间，所以在扩展存储过程中的程序bug，内存非法使用和内存泄露都可能潜在地影响SQLServer数据库引擎。CLR存储过程解决了这一问题，因为它们是用受管理的代码实现的，运行在CLR的范围之内。CLR存储过程的另一个