在 MS SQLServer 中使用 .NET 程序集的基础 - 用户函数

• 下载示例 - 3.97 KB

引言

SQL Server 2005 提供了一个新功能，允许从用户定义的存储过程、函数、触发器、新类型和聚合中访问 .NET 程序集。 可以从 T-SQL 查询调用 CLR 函数，并且可以像调用 T-SQL 存储过程一样，从 T-SQL 批处理调用 CLR 存储过程。 本文通过逐步完成 MSDN 上的验证示例（位于此处），说明了用于函数的 CLR 集成。

Using the Code

默认情况下，Microsoft SQL Server 中关闭了公共语言运行时 (CLR) 集成功能。 要启用 CLR 集成，请使用 sp_configure 存储过程的 clr enabled 选项。

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

步骤 1：构建程序集

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections;

public class Validation
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static bool RegExMatch(string pattern, string matchString)
    {
        Regex r1 = new Regex(pattern.TrimEnd(null));
        return r1.Match(matchString.TrimEnd(null)).Success;
    }

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString ExtractAreaCode(string matchString)
    {

        Regex r1 = new Regex("\\((?<ac>[1-9][0-9][0-9])\\)");
        Match m = r1.Match(matchString);
        if (m.Success)
            return m.Value.Substring(1, 3);
        else return SqlString.Null;
    }
    [SqlFunction(TableDefinition = "letter nchar(1)")]
    public static IEnumerable SampleTableFunction(SqlString s)
    {
         
        return new ArrayList(s.ToString().ToCharArray());
    }
    [SqlFunction(FillRowMethodName = "FillRow", 
      TableDefinition = "letter nchar(1)")]
    public static IEnumerable SampleTableFunction(SqlString s)
    {
        return new ArrayList(s.ToString().ToCharArray());
    }
    private static void FillRow(object obj, out char col1)
    {
        char row = (char)obj;
        col1 = row; 
   }
}

SqlFunction 属性指示该函数将用作用户定义的函数，该函数返回标量值或表。

TableDefinition 属性指示该函数将返回一个表。该属性是返回的表的定义的 SQL 表示形式。

FillRowMethodName 中指定的方法名称由 SQL/CLR 框架隐式调用，每次在返回的 IEnumerable 对象（或实现 IEnumerable 接口的类型）上调用 MoveNext() 方法时都会调用。 FillRow 方法必须具有如下签名

private static void FillRow(Object obj, out <col1_type> <col1>, out <col2_type> <col2>, ... )

其中第一个参数 (Object obj) 是一个 object 数组，其中包含一个输出行的值。 后续的函数参数 (out <col1_type> <col1> 等) 是 out 参数，其中包含将出现在正在构造的行的列中的值 (来自 http://blogs.msdn.com/stuartpa/archive/2005/07/21/441468.aspx)。

步骤 2：在 SQL Server 中创建程序集

为此，您可以使用 CREATE ASSEMBLY (Transact-SQL) 语句。 CREATE ASSEMBLY 上传先前编译为 .dll 文件的程序集，该文件来自托管代码，以供在 SQL Server 实例中使用。

CREATE ASSEMBLY CLRSQLExample
FROM 'C:\\CLRSQLExample.dll' 
WITH PERMISSION_SET = SAFE

程序集的本地路径或网络位置应替换为您 DLL 的路径。 SQL Server 在同一位置查找任何依赖程序集。 程序集名称在数据库中必须是唯一的。

SAFE 是限制性最强的权限集。 具有 SAFE 权限的程序集执行的代码无法访问外部系统资源，例如文件、网络、环境变量或注册表。 其他选项是 EXTERNAL_ACCES 和 UNSAFE。

步骤 3：在用户定义的函数中使用程序集方法

此示例仅处理用户定义的函数。

CREATE Function fnRegExMatch(@pattern nvarchar(max), @matchstring nvarchar(max)) returns bit
AS EXTERNAL NAME
CLRSQLExample.Validation.RegExMatch 
GO
CREATE Function fnExtractAreaCode(@matchString nvarchar(max)) returns nvarchar(max)
AS EXTERNAL NAME
CLRSQLExample.Validation.ExtractAreaCode 
GO
CREATE Function fnSampleTableFunction(@str nvarchar(max)) returns table( chars nchar)
AS EXTERNAL NAME
CLRSQLExample.Validation.SampleTableFunction

为了从程序集中标识要使用的正确类和方法，EXTERNAL NAME 使用

Assembly Name.ClassName.MethodName

注册的程序集名为 CLRSQLExample。 程序集中的类是 Validation，并且将要执行的该类中的方法是 ExtractAreaCode、SampleTableFunction 和 RegExMatch。