使用 SQL Server 2005 CLR 托管的一些实用函数

• 下载源代码 - 12.46 KB

引言

CLR 宿主是 SQL Server 2005 的一项新酷功能，如果使用得当，可以极大地满足我们日常的数据库需求。

要更好地理解 Microsoft SQL Server 2005 中的 CLR 宿主，请访问 此链接。

背景

在 SQL Server 的早期（Microsoft SQL Server 2005 之前），为各种实用子例程编写 用户定义函数 对开发者来说简直是场噩梦，除非对 TSQL 有非常扎实的了解。现在，随着 Microsoft SQL Server 2005 中 CLR 宿主功能的引入，我们可以借助丰富的 .NET 基类库来驾驭这些子例程。我们可以创建功能丰富的程序集和子例程，然后将它们无缝部署到企业中的任何 SQL Server 2005 数据库中，并利用这些子例程的强大功能来解决我们日常的 TSQL 编程挑战。

在本文中，我创建了一个自定义库，其中包含一些 UDF，TSQL 程序员和开发人员可以轻松使用它来执行一些非常常见的 TSQL 字符串相关任务。以下是使用此库可以完成的任务列表：

1. 根据字符串模式（例如，电子邮件地址，如 foo@bar.com）验证字符串。
2. 根据一些预定义的字符串模式（我已提供用于日常工作的模式，因此您无需重新发明模式）验证字符串，例如 EMAILFORMAT、FRENCHPHONENO、USPHONENO、USZIPCODE......
3. 使用提供的分隔符拆分字符串，并返回一个包含 RowNo 和 Data 的自定义表，以便于迭代。

这些只是此库中一些非常基本和直接的方面，但如果明智地使用，它们确实可以使我们的生活变得轻松很多。

Using the Code

第一个也是最简单的上手方法是下载代码并将其部署到 Microsoft SQL Server 2005 实例的测试数据库中。在此我不会深入探讨在 SQL Server 2005 中宿主程序集的细节，而是更愿意深入研究代码。（第一个链接将为您提供大量关于部署和安全问题的资源。）

现在，来看第一个方法，也是最简单的方法。

1.[Microsoft.SqlServer.Server.SqlFunction]
2.public static bool IsValidStringFormat_Custom(string InputString, string Pattern)
3.{
4.    Regex expression = new Regex(Pattern);
5.    return expression.IsMatch(InputString);
6.}   

代码的第一行是一个属性，它清楚地表明下面的方法就是一个 SQL 函数。这真的很酷，而且非常自解释。如果我们希望任何方法能够作为用户定义函数暴露出来，我们只需为其添加该属性即可。

在此函数中，我们使用 .NET Framework 类 System.Text.RegularExpressions.Regx 来匹配自定义提供的模式。

此实用方法在从 SQL Server 2005 调用时返回 bit 类型，因此很容易确定一个字符串是否与给定模式匹配。它可以轻松地与检查约束、存储过程或任何其他用户定义函数一起使用。

下一个函数是带有预定义模式的函数（类似于我们带有预定义模式集的旧正则表达式验证器）。

[Microsoft.SqlServer.Server.SqlFunction]
public static bool IsValidFormat(string InputString, string PatternType)
{
    bool flag = false;
    switch (PatternType)
    { 
        case "EMAILFORMAT":
            flag = IsValidStringFormat_Custom(InputString, EmaiFormat);
        break;
        case "FRENCHPHONENO":
            flag = IsValidStringFormat_Custom(InputString, FrenchPhoneNo);
        break;
        case "FRENCHPOSTALCODE":
            flag = IsValidStringFormat_Custom(InputString, FrenchPostalCode);
        break;
        case "GERMANPHONENO":
            flag = IsValidStringFormat_Custom(InputString, GermanPhoneNo);
        break;
        case "GERMANPOSTALCODE":
            flag = IsValidStringFormat_Custom(InputString, GermanPostalCode);
        break;
        case "INTERNETURL":
            flag = IsValidStringFormat_Custom(InputString, InternetURL);
        break;
        case "JAPANESEPHONENO":
            flag = IsValidStringFormat_Custom(InputString, JapanesePhoneNo);
        break;
        case "JAPANESEPOSTALCODE":
            flag = IsValidStringFormat_Custom(InputString, JapanesePostalCode);
        break;
        case "PRCPHONENO":
            flag = IsValidStringFormat_Custom(InputString, PRCPhoneNo);
        break;
        case "PRCPOSTALCODE":
            flag = IsValidStringFormat_Custom(InputString, PRCPostalCode);
        break;
        case "PRCSOCIALSECURITYNO":
            flag = IsValidStringFormat_Custom(InputString, PRCSocialSecurityNo);
        break;
        case "USPHONENO":
            flag = IsValidStringFormat_Custom(InputString, USPhoneNo);
        break;
        case "USSOCIALSECURITYNO":
            flag = IsValidStringFormat_Custom(InputString, USSocialSecurityNo);
        break;
        case "USZIPCODE":
            flag = IsValidStringFormat_Custom(InputString, USZipCode);
        break;
    }

return flag;

}

此代码没有什么花哨之处。我只使用了一些常量字符串模式，并根据提供的 PatternType（例如 'USZIPCODE'）使用我旧的 IsValidStringFormat_Custom 函数进行模式匹配。

现在来看最后一个函数（这个函数现在有点凌乱）。这个函数通过给定的分隔符拆分字符串并返回一个表。

[SqlFunction(FillRowMethodName = "FillRow", 
	TableDefinition = "Data NVARCHAR(MAX), RowNo int")]
public static System.Collections.IEnumerable Split
		(SqlString InputString, string deliminator)
{
        string[] strArray = InputString.Value.Split(deliminator.ToCharArray());
        for (int foo = 0; foo < strArray.Length; foo++)
        {
            strArray[foo] = strArray[foo] + "~" + foo.ToString();
        }
        return strArray;
}
public static void FillRow(object row, out string str, out int RowNo)
{
        str = (((string)row).Split('~'))[0];
        RowNo = int.Parse((((string)row).Split('~'))[1]);
}

最有趣的特性是这个用户定义函数返回的是一个表而不是一个 bit。为了实现这一点，我们使用了两个例程：第一个是 Split，它返回 IEnumerable（使其像 System.Collection 中的任何其他数据结构一样可枚举），第二个函数实际填充表。

在属性声明点，我们还可以看到一些非常有趣的参数，FillMethodName 和 TableDefinition。

FillMethodName 指向实际填充需要从用户定义数据类型返回的表的那个方法。

TableDefinition 定义了 UDF 返回的实际表结构。

关注点

通过将 CLR 宿主集成到 SQL Server 2005 中，微软展示了 BCL 和 SQL Server 数据库有前景的集成，这在之前是缺失的。如果得到妥善利用，它确实可以帮助我们日常繁琐的 TSQL 查询以非常高效的方式运行和管理。

与任何其他库一样，这个库最有趣的部分是它可以部署和使用在任何 SQL Server 2005 数据库中，无需依赖，并且只有一个部署和维护点。

历史

• 2009 年 8 月 3 日：初次发布