C#和SQLCLR为SQL Server编写用户定义函数，

CLR 

摘要： 文档阐述使用 C#和SQL CLR为SQL Server编写用户定义函数，并演示用户定义函数在T-SQL中的应用。文档中实现的 Base64 编码解码函数和正则表达式函数属于标量值函数，字符串分割函数属于表值函数，而平方平均数函数属于聚合函数。 环境要求：SQL Server 2005/2008，Visual Studio 2005/2008，C# 2.0。 概述 微软在推出SQL Server 2005后，实现了对.NET CLR的集成，使得.NET代码可在SQL Server服务器进程中执行。开发人员通过C#和SQLCLR可轻松创建 存储过程、用户定义函数、触发器和用户定义类型等功能，改变了以前只能通过T- SQL 语言来实现这些功能的局面。作为SQLCLR的典型应用，本文将通过C#编写Base64编码解码函数、正则表达式函数、字符串分割函数以及 平方平均数函数 来演示如何为SQL Server编写 标量值函数、表值函数和聚合函数。 启用SQLCLR 在讲解具体函数之前，我们先来了解一下如何启用SQLCLR，并通过 Visual Studio 2005/2008创建数据库项目，最后部署.NET程序集到SQL Server的过程。 默认情况下，SQL Server的SQLCLR是禁用的，要使用SQLCLR需要通过sp_configure系统存储过程设置服务器 配置选项来启用，下面显示了默认情况下的设置。 sp_configure 'clr enabled' ; name                                minimum    maximum    config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- clr enabled                        0          1          0            0 下面用T-SQL来启用SQLCLR，并查看修改后的设置信息。 sp_configure 'clr enabled' , 1; GO RECONFIGURE ; GO sp_configure 'clr enabled' ; name                                minimum    maximum    config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- clr enabled                        0          1          1            1 Visual Studio 提供了一个项目 模板来建立用于数据库开发的项目，在新建项目中选择Visual C#，然后选择数据库项目，在对话框中设置项目名称为SqlServer.SqlClr.Functions，为项目添加一个C#类文件并命名类名称为 UserDefinedFunctions，并添加一个静态公共的HelloSqlClr函数，补充代码结果如下所示。 using System; using System.Data; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public class UserDefinedFunctions {     [Microsoft.SqlServer.Server.SqlFunction (Name = "Clr_HelloSqlClr" )]     public static SqlString HelloSqlClr(SqlString input)     {         return input;     } } 复制代码 UserDefinedFunctions 类引用了System.Data.SqlTypes命名空间。 System.Data.SqlTypes 命名空间包含了SQL Server 中本地数据类型对应的类型，比如上面的SqlString类型对应于SQL Server的char、nchar、text、ntext、nvarchar、varchar数据类型。这些类提供一种比.NET Framework公共语言运行库(CLR)提供的数据类型更快更 安全的替代方案。使用此命名空间中的类有助于防止类型转换错误时出现精度损失的情况。 UserDefinedFunctions 类还引用了Microsoft.SqlServer.Server命名空间，该命名空间包含将 Microsoft .NET Framework公共语言运行库(CLR)集成到Microsoft SQL Server和SQL Server 数据库引擎进程执行环境时所要用到的类、 接口和枚举。上面的代码我们为 HelloSqlClr 指定了SqlFunction特性，并设置Name属性，这使得通过Visual Studio轻松实现托管用户定义函数在SQL Server中的部署。 如果采用非自动化方式部署需要通过C#编译器编译源代码，并通过T-SQL语言进行程序集 安装，最后进行函数注册等步骤。 下面通过CSC进行C#代码的编译，输出 SqlServer.SqlClr.Functions.dll 程序集。 csc /target:library /out:SqlServer.SqlClr.Functions.dll UserDefinedFunctions.cs 复制代码 通过SQL Server Management Studio执行T-SQL加载SqlServer.SqlClr.Functions.dll程序集到SQL Server中。 CREATE ASSEMBLY [SqlServer.SqlClr.Functions] FROM 'D:\SqlServer SqlClr Solution\SqlServer.SqlClr.Functions\SqlServer.SqlClr.Functions.dll' WITH PERMISSION_SET = SAFE 复制代码 当程序集安装到SQL Server中后，可以通过以下T-SQL来查看程序集。 SELECT * FROM sys . assemblies 复制代码 下面的T-SQL注册程序集中包含的 HelloSqlClr 函数。 CREATE FUNCTION [dbo] . [Clr_HelloSqlClr] ( @input [nvarchar] ( 128)) RETURNS [nvarchar] ( 128) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlServer.SqlClr.Functions] . [UserDefinedFunctions] . [HelloSqlClr] 复制代码 以上工作完成后，就可以通过T-SQL来调用用户定义函数了。 SELECT dbo . Clr_HelloSqlClr ( 'Hello sqlclr' ); --------------------------- Hello sqlclr 本节简单介绍了通过C#实现用户定义函数并部署应用的一般流程，接下去的章节来具体实现几个常用的功能函数，其中 Base64 编码解码函数和正则表达式函数属于标量值函数，字符串分割函数属于表值函数，而平方平均数函数属于聚合函数。