SQL Server中使用CLR调用.NET方法

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

我们来通过一个案例来分析下：

     第一，   新建类库，引用命名空间，using Microsoft.SqlServer.Server;

     第二，   编写一个公共类，含有一个公共的静态函数，并且具有特性：[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.None)] ，其中的DataAccess=DataAccessKind.None ，意味该函数不访问用户数据，DataAccessKind.Read 枚举值表示需要访问。现在2010中也可以简写[SqlFunction]

     第三，   写一个方法进行操作，如：

  [SqlFunction]
        public static string Say(string strName)
        {
            return strName + "_beijing";
        }
   [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None)]
        public static String Say()
        {
            return "hello,world";
        }

    第四， 编译此类库。找出相关的类库中的dll文件。

    第五，   登陆sql,编写进行创建程序集：

CREATE ASSEMBLY CRLServer 
FROM 'D:\项目\Project\Demo\CRLServer2008\CRLServerDemo\CRLServer2008Library\bin\Debug\CRLServer2008Library.dll'
WITH permission_set = Safe; 

    注意：（1）这段代码的意思就是从本地一个dll中创建一个程序集，并且设置权限，默认有三种选择：SAFE | EXTERNAL_ACCESS | UNSAFE，

                    Safe模式就是最具限制性的权限集。由具有 SAFE 权限的程序集所执行的代码将无法访问外部系统资源，例如文件、网络、环境变量或注册表。

                    EXTERNAL_ACCESS 使程序集可以访问某些外部系统资源，例如文件、网络、环境变量以及注册表。

                    UNSAFE 可使程序集不受限制地访问资源，无论是 SQL Server 实例内部还是外部的资源都可以访问。从 UNSAFE 程序集内运行的代码可调用未托管代码。

           （2） dll版本和数据库版本有一定对应关系，Sql Server2008 使用请请用3.5的版本 dll不能是.net4.0

第六，创建一个函数变量来调用类库中的方法

CREATE FUNCTION CLR_ServerSay
(
@paymentinfo [nvarchar](max)
)
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS 
EXTERNAL NAME CRLServer.[CRLServer2008Library.SayHello].[Say]

 注意：程序集名.[类名].方法名；

         CRLServer属于程序集名称，CRLServer2008Library.SayHello 为命名空间名称 Say为方法名称；程序集名.[类名].方法名

第七：

   在第一次使用 sql server clr集成功能的时候，需要开启一个clr选项，执行以下语句：

EXEC sp_configure 'clr enabled', '1';
GO
RECONFIGURE;
GO

 第八：在SQL用使用此函数

select dbo.CLR_ServerSay('感谢你的使用')

 重要的注意事项：

   [SqlFunction]
        public static SqlBoolean RegexSearch(SqlChars input, string pattern)
        {
            return Regex.Match(new string(input.Value), pattern, RegexOptions.Compiled).Success;
        }

CREATE FUNCTION CLR_ServerRegexSearch
(
@strchar nvarchar(max), @paymentinfo [nvarchar](max)
)
RETURNS bit WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME CRLServer.[CRLServer2008Library.SayHello].[RegexSearch]

 

SqlString 数据类型转换成 nvarchar(4,000)，而 SqlChars 转换成 nvarchar(max)。尽可能使用 nvarchar(max) 并且最大程度地保证灵活性。然而，如果所有相关字符串包含的字符都少于 4,000 个，使用 nvarchar(4,000) 则性能可得到显著改善。

CLR里返回的bool对应SQL Server里的bit,即：1/0/Null,而不是true/false,所以，没法直接用dbo.ContainsOne(txtContent,'柳永法')实现bool形，而得这么用：dbo.ContainsOne(txtContent,'柳永法')=1