.NET中创建CLR sqlServer函数

SQL Server2005 支持在.NET中创建一个存储过程或者函数。这种方式对数据库提供一种扩展，大大的增加对数据操作的方便性。例如一些对数据较复杂的操作，用纯sql实现可能相对复杂，而通过C#之类的语言实现就方便了许多。

具体操作上方便，但是第一次做起来在细节上还有好多值得注意的地方。

 

1，创建一个数据库项目，然后添加要创建的（Function or PROCEDURE也可以是Trigger，用户自定义类型），具体一看便知。

public partial class UserDefinedFunctions
{
    /// <summary>
    /// 根据正则表达判断是否匹配
    /// </summary>
    /// <param name="regexExpress">正则表达式</param>
    /// <param name="input">检查的字符</param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static bool RegExValidate(string regexExpress, string input)
    {
        Regex regex = new Regex(regexExpress, RegexOptions.IgnoreCase);
        return regex.IsMatch(input);
    }    

}; 

这是我的示例，主要功能就是通过传入的字符串和与之对应的正则表达式，检查是否匹配。成功为true,失败为false。

 

功能函数完成，然后编译。

2，在SQL Server中操作，加载程序集

 具体操作：CREATE ASSEMBLY RegexFun from 'c:\sql\SqlServerRegex.dll' WITH PERMISSION_SET = SAFE

操作的时间有权限的限制(摘自SQL Server2008教程 CREATE ASSEMBLY)：

如果指定 PERMISSION_SET = EXTERNAL_ACCESS，则 SQL Server 登录必须具有对服务器的 EXTERNAL ACCESS ASSEMBLY 权限。如果指定 PERMISSION_SET = UNSAFE，则需要 sysadmin 固定服务器角色的成员身份。如果程序集已经存在于数据库中，则用户必须是将上载的程序集所引用的所有程序集的所有者。若要使用文件路径上载程序集，则当前用户必须是经过 Windows 身份验证的登录名或 sysadmin 固定服务器角色的成员。执行 CREATE ASSEMBLY 的用户的 Windows 登录名必须对此语句中加载的共享和文件具有读取权限。 

注意： dll版本和数据库版本有一定对应关系，Sql Server08 时间 dll不能是.net4.0

 

3， 当程序集创建成功，需要在数据库创建一个（函数|存储过程）和程序集做一个映射。

 CREATE FUNCTION RegexComFun(@str nvarchar(250),@input nvarchar(250))

RETURNS bit
AS EXTERNAL NAME RegexFun.UserDefinedFunctions.[RegExValidate]

 注意： string 在T-SQL对应的类型是 nvarchar,(varchar是出现这样错误：的 T-SQL 和 CLR 类型不匹配)

 解释：RegexFun.UserDefinedFunctions.[RegExValidate] （ RegexFun 第2步创建的程序集名称，UserDefinedFunctions.[RegExValidate] 类名和方法名）

 

4，看似一切好了，迫不及待的执行吧

select dbo.RegexComFun(N'\d+',N'123')

结果发生错误：禁止在 .NET Framework 中执行用户代码。启用 "clr enabled" 配置选项 

 

5， 配置"clr enabled"

clr enabled有两个选项：0不允许在 SQL Server 上执行程序集。1允许在 SQL Server 上执行程序集。该设置将在 sp_configure 运行之后立即生效。 不需要重新启动 SQL Server 实例。 

EXEC sp_configure 'clr enabled' , '1' ;

reconfigure; 

这样配置成功！

注意： 一定要加 reconfigure ，不然执行可以成功，但设置不生效。

这样配置完毕，在执行select dbo.RegexComFun(N'\d+',N'123')返回1，成功了！