Sql Server 2005 CLR实例

时间：2011-02-13 来源：三桂

在VS中选中数据库模板然后新建"Visual C# SQL CLR数据库项目",再添加一个"用户定义的函数"类文件,输入如下代码:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Runtime.InteropServices;

[Serializable]
[SqlUserDefinedType(Format.Native)]
[StructLayout(LayoutKind.Sequential)]
public partial class UserDefinedFunctions
{
    [SqlFunction] 
    public static SqlString fnHelloworld()
    {
        // 在此处放置代码
        return new SqlString("Hello world!");
    }
    /// <summary>
    /// SQL CLR 使用正则表达式替换，eg:
    /// select dbo.RegexReplace('<span>博客园http://www.cnblogs.com/</span>','<.+?>',''/// update Articles set txtContent=dbo.RegexReplace(txtContent,'<.+?>','')
    /// --结果：博客园/// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="pattern">正则表达式</param>
    /// <returns>替换后结果</returns>
    [SqlFunction]
    public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
    {
        return Regex.Replace(new string(input.Value), pattern.Value, replacement.Value, RegexOptions.Compiled);
    }
     
    /// <summary>
    /// SQL CLR 使用正则表达式替换，eg:
    /// select dbo.RegexSearch('<span>博客园</span>','<.+?>','')
    /// select * from Articles where dbo.RegexSearch(txtContent,'博客园')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="pattern">正则表达式</param>
    /// <returns>查询结果，1,0</returns>
    [SqlFunction]
    public static SqlBoolean RegexSearch(SqlChars input, string pattern)
    {
        return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
    }

    /// <summary>
    /// SQL CLR 使用.net的Contains查找是否满足条件,eg:
    /// select dbo.ContainsOne('这里是博客园，','博客园');
    /// select * from Articles where dbo.ContainsOne(txtContent,'博客园')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="search">要搜索的字符串</param>
    /// <returns>返回是否匹配,1,0</returns>
    [SqlFunction]
    public static SqlBoolean ContainsOne(SqlChars input, string search)
    {
        return new string(input.Value).Contains(search);
    }
     
    /// <summary>
    /// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg:
    /// select dbo.ContainsAny('这里是博客园，','博,客,园');
    /// select * from Articles where dbo.ContainsAny(txtContent,'博,客,园')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="search">要搜索的字符串，以","分隔，自己处理空格问题</param>
    /// <returns>返回是否匹配,1,0</returns>
    [SqlFunction]
    public static SqlBoolean ContainsAny(SqlChars input, string search)
    {
        string strTemp = new string(input.Value);
        foreach (string item in search.Split(','))
        {
            if (strTemp.Contains(item))
            {
                return true;
            }
        }
        return false;
    }

    /// <summary>
    /// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg:
    /// select dbo.ContainsAll('这里是博客园，','博,客,园');
    /// select * from Articles where dbo.ContainsAll(txtContent,'博,客,园')=1;
    /// </summary>
    /// <param name="input">源串，或字段名</param>
    /// <param name="search">要搜索的字符串，以","分隔，自己处理空格问题</param>
    /// <returns>返回是否匹配,1,0</returns>
    [SqlFunction]
    public static SqlBoolean ContainsAll(SqlChars input, string search)
    {
        string strTemp = new string(input.Value);
        foreach (string item in search.Split(','))
        {
            if (!strTemp.Contains(item))
            {
                return false;
            }
        }
        return true;
    }
};)
    http://www.cnblogs.com/

编译之后成功,但是右键 > 部署的时候却提示部署失败,失败信息如下:

"正在创建 [SqlServer.SqlClr.Functions]...
D:\workspace\Project\SqlServer.SqlClr.Functions\bin\Debug\SqlServer.SqlClr.Functions.sql(39-39): Deploy error SQL01268: .Net SqlClient Data Provider: 消息 6218，级别 16，状态 3，行 1 针对 'SqlServer.SqlClr.Functions' 的 CREATE ASSEMBLY 失败，原因是程序集 'SqlServer.SqlClr.Functions' 未通过身份验证。请检查被引用程序集是否是最新的，而且是可信的(external_access 或 unsafe)，能在该数据库中执行。如果有 CLR Verifier 错误消息，将显示在此消息之后执行批处理时发生错误。"

微软官方的部署方法是编译项目之后在SqlServer2005查询分析器中输入如下代码:

exec sp_configure 'CLR ENABLED',1 --1为启用CLR,0为禁用
reconfigure

--设置数据库选型TRUSTWORTHY 为on
alter database NewPT set trustworthy on

CREATE ASSEMBLY [SqlServer.SqlClr.Functions]
FROM 'D:\workspace\Project\SqlServer.SqlClr.Functions\bin\Debug\SqlServer.SqlClr.Functions.dll'
WITH PERMISSION_SET = SAFE
GO

执行之后同样得到错误消息"针对 'SqlServer.SqlClr.Functions' 的 CREATE ASSEMBLY 失败，原因是程序集 'SqlServer.SqlClr.Functions' 未通过身份验证。请检查被引用程序集是否是最新的，而且是可信的(external_access 或 unsafe)，能在该数据库中执行。如果有 CLR Verifier 错误消息，将显示在此消息之后"

如果能正常部署,则可以在对应的数据库下这样写sql语句:

select dbo.fnHelloworld();  //Hello world!

select dbo.RegexReplace('<span>博客园http://www.cnblogs.com/</span>','<.+?>','')
update Articles set txtContent=dbo.RegexReplace(txtContent,'<.+?>','')
--结果：博客园http://www.cnblogs.com/

select dbo.RegexSearch('<span>博客园</span>','<.+?>','')
select * from Articles where dbo.RegexSearch(txtContent,'博客园')=1;

select dbo.ContainsOne('这里是博客园，','博客园');
select * from Articles where dbo.ContainsOne(txtContent,'博客园')=1;

select dbo.ContainsAny('这里是博客园，','博,客,园');
select * from Articles where dbo.ContainsAny(txtContent,'博,客,园')=1;

select dbo.ContainsAll('这里是博客园，','博,客,园');
select * from Articles where dbo.ContainsAll(txtContent,'博,客,园')=1;

可以使用.net的语法来实现Sqlserver函数,很是强大...