SQL Server CLR函数的使用教程

本文将解释如何在不创建SQL Server数据库项目的情况下创建一个存储在C#类库中的SQL Server CLR函数。我们将首先简要地解释.NET通用语言运行时（CLR）的概念，然后使用.NET框架建立一个C#类库。然后，我们将使用C#库在SQL Server中创建用户定义的CLR函数。

简介

通用 语言 运行时（CLR）是微软.NET框架的核心。它为所有的.NET框架代码提供了执行环境。在CLR中运行的代码被称为托管代码。

通用语言为执行程序提供了一些服务，如及时（JIT）编译、分配和管理内存、执行类型安全、异常处理、线程管理和安全。

使用.NET开发的程序的执行是由即时编译器（JIT）管理的，无论它是用哪种.NET编程语言创建的。特定语言的编译器将源代码转换为通用中间语言（CIL）。这种语言然后由即时编译器（JIT）转换为机器代码。 这种机器代码是特定于JIT编译器运行的计算机环境。

图 - 使用JIT编译器工作（图片来源：GeeksForGeeks）

使用SQL Server CLR，我们可以在托管代码中定义几个对象，如存储过程、用户定义的函数、触发器、用户定义的类型和用户定义的聚合。

SQL Server CLR的好处之一是，由于托管代码在执行前会编译成本地代码，因此在某些方面可以实现显著的性能提升。此外，几个复杂的操作在.NET中实现起来比SQL Server更容易。

下一节将解释如何使用.NET框架创建一个基本的C#库。

侧面说明。**.NET核心和.NET标准不能用于CLR集成。

创建一个.NET类库

我们的第一步是创建一个.NET类库，其中包含我们需要在SQL Server中使用的C#函数。要做到这一点，请打开Microsoft Visual Studio并创建一个新项目。

图 - 创建一个新项目

为了创建一个SQL Server CLR类库，我们应该使用一个.NET框架类库项目。

图 - 选择.Net框架类库项目

在本文中，我们将使用.NET框架4.6来建立我们的类库，并且我们将设置 "SQLExternalFunctions"作为项目名称。

图 - 配置项目名称和框架版本

一旦Visual Studio IDE的主窗口显示出来，进入右边的解决方案资源管理器标签，将 "Class1.cs "的名字改为 "SQLExternalFunctions.cs"。

图 - 重命名主类

现在，我们需要编写我们需要在SQL Server中使用的函数。首先，让我们删除该类中不必要的部分。双击SQLExternalFunctions.cs类。

在C#类编辑器中，删除SQLExternalFunctions命名空间的大括号和不必要的导入库（只保留System和System.Linq），如下图所示。

图--删除不必要的代码

我们需要在类定义前添加static关键字，因为SQL Server CLR函数必须定义在一个静态类中。

public static class SQLExternalFunctions

删除不必要的部分后，我们要添加以下三个函数。

第一个字符出现的次数。

• 输入：一个字符串值
• 输出：一个整数值=输入字符串中第一个字符出现的次数。
• 代码。

public static int FirstCharacterOccurence(string str)
{
if (string.IsNullOrWhiteSpace(str))
return 0;
 
char ch = str[0];
return str.Where(f => f == ch).Count();
}

字符出现次数的计算。

• 输入。
  • 一个字符串值
  • 一个字符
• 输出：一个整数值=输入字符在输入字符串中出现的次数
• 代码。

public static int GetCharacterOccurence(string str, char chr)
{
if (string.IsNullOrWhiteSpace(str))
return 0;
 
return str.Where(f => f == chr).Count();
}

获取ISO格式的当前日期。

• 输入：这个函数需要任何输入
• 输出：一个ISO格式的当前日期的字符串值(yyyy-MM-dd HH:mm:ss)
• 代码。

public static string GetCurrentDateISO()
{
return DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}

写完函数后，我们应该给我们的类添加一些元数据，让SQL Server知道它包含CLR函数。这个元数据就是我们在C#中所说的 "属性"。如果感兴趣，你可以在下面的官方文档中了解更多关于C#属性的信息。Attributes (C#) | Microsoft Docs。

在每个函数定义之前，我们需要添加以下属性，如下图所示。

[Microsoft.SqlServer.Server.SqlFunction]

图--在C#函数前添加SqlFunction属性

正如Visual Studio工具提示中提到的，SQLFunction属性*"用于将作为用户定义的聚合体的方法定义标记为SQL Server中的一个函数。该属性上的属性反映了该类型与SQL Server注册时使用的物理特性"。*

图--SqlFunciton属性描述

一旦完成，我们应该建立我们的类库。要做到这一点，在解决方案资源管理器中右键单击项目，然后按 "构建解决方案"。

图 - 构建解决方案

构建解决方案后，我们应该将创建的DLL文件从项目目录（SQLExternalFunctions\bin\Debug\）中复制到我们想要存储它的位置（在本文中，我们将把它放在（D:\CLR Functions\）中。

图 - 打开项目目录

图--类库位置

创建一个SQL Server CLR用户定义的函数

在创建了.NET类库之后，下一步是创建一个SQL Server CLR用户定义的函数，以调用.NET类库中的函数。

首先，打开SQL Server Management Studio，打开一个新的查询编辑器。

图 - 打开一个新的查询编辑器

SQL服务器配置

在定义SQL Server CLR函数之前，我们应该配置服务器以允许CLR集成。首先，我们需要使用以下命令启用高级选项配置。

EXEC sp_configure 'show advanced options' , 1;
RECONFIGURE;

接下来，我们应该使用下面的命令启用CLR集成选项。

EXEC sp_configure 'clr enable' ,1;
    RECONFIGURE;

由于本文并不关注CLR安全，我们将使用以下命令禁用SQL Server已经设置的严格的CLR集成安全规则。

    EXEC sp_configure 'clr strict security', 0;
    RECONFIGURE;

警告。完全不建议打开CLR严格安全，使SQL Server变得脆弱。你可以在下面的文章中阅读更多关于签署CLR程序集和CLR严格安全的影响。SQL Server 2017中CLR严格安全配置设置的影响。

创建一个程序集对象

为了映射CLR类库或应用程序，我们应该在SQL Server中创建一个程序集对象，并将其映射到类库文件路径，如下所示。

CREATE ASSEMBLY SQLCLRDemo
FROM 'D:\CLR Functions\SQLExternalFunctions.dll';
GO

创建用户定义的函数

对于我们在C#类库中定义的每个.NET函数，我们应该定义一个相关的SQL Server CLR函数，我们必须指定相同的输入参数和输出数据类型。此外，我们应该提到原始函数的名称，如下所示。

CREATE FUNCTION GetFirstCharacterOccurence(<input parameters>) RETURNS <output data type>
    EXTERNAL NAME <clr assembly>.<class name>.<Function name>;
                GO

在这篇文章中，我们应该定义以下函数。

CREATE FUNCTION GetFirstCharacterOccurence(@str nvarchar(255)) RETURNS INT
EXTERNAL NAME SQLCLRDemo.StringLibrary.FirstCharacterOccurence;
GO
 
CREATE FUNCTION GetCharacterOccurence(@str nvarchar(255), @chr nchar(1)) RETURNS INT
EXTERNAL NAME SQLCLRDemo.StringLibrary.GetCharacterOccurence;
GO
 
CREATE FUNCTION GetIsoDate() RETURNS nvarchar(255)
EXTERNAL NAME SQLCLRDemo.StringLibrary.GetCurrentDateISO;
GO

使用这些函数

现在，让我们试试下面的查询来测试我们创建的函数。

SELECT dbo.GetFirstCharacterOccurence('alialawiye'), dbo.GetFirstCharacterOccurence('hadifadlallah')
 
SELECT dbo.GetCharacterOccurence('hadifadlallah','a')
 
SELECT dbo.GETISODATE()

下面的图片显示了查询的执行结果。

图 - 查询结果

总结

在这篇文章中，我们简要介绍了.NET通用语言运行时（CLR），以及如何在用.NET框架开发的C#类库基础上创建用户定义的SQL Server CLR函数。

创建和部署SQL Server CLR函数的另一个选择是在SQL Server数据库项目中定义它们，它们将在数据库部署操作中被创建。