SQL Server CLR全攻略.docVIP

SQL Server CLR全攻略 CLR介绍和配置 Microsoft SQL Server 现在具备与 Microsoft Windows .NET Framework 的公共语言运行时 (CLR) 组件集成的功能。CLR 为托管代码提供服务，例如跨语言集成、代码访问安全性、对象生存期管理以及调试和分析支持。对于 SQL Server 用户和应用程序开发人员来说，CLR 集成意味着您现在可以使用任何 .NET Framework 语言（包括 Microsoft Visual Basic .NET 和 Microsoft Visual C#）编写存储过程、触发器、用户定义类型、用户定义函数（标量函数和表值函数）以及用户定义的聚合函数。 下面列出了这一集成的其中一些主要优点： 更好的编程模型。 改进了安全和安全性。 能够定义数据类型和聚合函数。 通过标准化环境简化了开发。 具备改善性能和可扩展性的潜力。 既然CLR这么灵活，那么我们如何在T-SQL和CLR之间做出选择呢？一般情况下，我们当以T-SQL为主，在下面几种情况下，应该考虑使用CLR： SQL中涉及大量的逻辑判断和逻辑运算。比如需要在数据库级别自定义加密算法，解密算法等。 T-SQL无法处理需求。比如需要在SQL中进行正规表达式的判断等。 逻辑判断过于复杂，需要使用大量游标进行处理。这种情况一般要进行测试，因为CLR不一定比游标快，关键是看你怎么写。 下面介绍一下如何开启CLR和两种创建CLR的方法。关于如何在VS中编写CLR程序，请看后续章节。 开启CLR。 在SQL Server2005/2008里面，CLR默认是关闭的。可以使用如下SQL语句开启CLR。 sp_configure show advanced options, 1; GO RECONFIGURE; GO sp_configure clr enabled, 1; GO RECONFIGURE; GO 创建CLR。 方法一：使用DLL文件进行创建 CREATE ASSEMBLY AssemblyName from ‘DLLPath’ 例如：CREATE ASSEMBLY AssemblyName from ‘c:\hello.dll’ 方法二:使用文件16进制流进行创建 CREATE ASSEMBLY AssemblyName from 文件十六进制流 例如:CREATE ASSEMBLY AssemblyName from 0x123344…… 其中0x12334……是文件c:\hello.dll的十六进制流。可以使用UltraEdit等编辑器把相关dll文件的十六进制流copy出来。 相比较，方法一简单，属于外部引用，通过创建就可以知道是哪个文件。但是dll文件路径必须可以被SQLServer所访问，且部署时dll要随SQLServer变化而变化。 方法二是把流写进SQLServer中，从一定程度上屏蔽了代码。如果此dll不大的话，推荐使用方法二来创建CLR。 CLR存储过程 从这一节开始呢，我们就要开始CLR的编程之旅了。在这之前，我先把本节中需要了解的两个新类SqlDataRecord和SqlMetaData，及五个新方法SqlContext.Pipe.SendResultsStart，SqlContext.Pipe.SendResultsRow，SqlContext.Pipe.SendResultsEnd，SqlContext.Pipe.Send和SqlContext.Pipe.ExecuteAndSend进行一下必要的说明，方便大家阅读后续的代码。 首先SqlDataRecord和SqlMetaData是数据集合和原数据的意思。可以简单的把SqlDataRecord理解成DataTable，把SqlMetaData理解成DataColumn。我们再向SqlDataRecord里面填充数据之前要先执行SqlContext.Pipe.SendResultStart()方法，告诉数据库下面开始填充数据，使用SqlContext.Pipe.SendResultRow方法来填充数据，填充结束后使用SqlContext.Pipe.SendResultEnd方法来结束填充。这些都是基本流程，没什么好解释的，只要照着去做就可以了。 SqlContext.Pipe.Send是向客户端发送一条结果，SqlContext.Pipe.ExecuteAndSend是执行一条语句。 下面我将用几个实际的简单例子来说明如何使用这几个方法。 1.使用SqlContext.Pipe.Send构建无参无返回值的存储过程 [Microsoft.SqlServer.Ser