通过

教程：在 C# 中使用正则表达式 (regex) 搜索字符串

适用于： SQL Server 2019 (15.x) 及更高版本

本教程演示如何使用 SQL Server 语言扩展创建一个 C# 类，该类接收来自 SQL Server 的两列（ID 和 text），并接收一个正则表达式 (regex) 作为输入参数。 该类会将两列返回到 SQL Server（ID 和 text）。

对于发送到 C# 类的 text 列中的给定文本，代码会检查是否满足给定正则表达式，并将该文本与原始 ID 一起返回。

此示例代码使用可检查文本是否包含单词 C# 或 c# 的正则表达式。

先决条件

使用 dotnet build 的命令行编译足以满足本教程的要求。

创建示例数据

首先创建新数据库，并使用 testdata 和 ID 列填充 text 表。

CREATE DATABASE csharptest;
GO

USE csharptest;
GO

CREATE TABLE testdata
(
    [id] INT,
    [text] VARCHAR (100)
);
GO

INSERT INTO testdata (id, "text") VALUES (4, 'This sentence contains C#');
INSERT INTO testdata (id, "text") VALUES (1, 'This sentence does not');
INSERT INTO testdata (id, "text") VALUES (3, 'I love c#!');
INSERT INTO testdata (id, "text") VALUES (2, NULL);
GO

创建主类

在此步骤中，创建名为 RegexSample.cs 的类文件，并将以下 C# 代码复制到该文件中。

此 main 类会导入 SDK，这意味着需要可从此类发现第一步中下载的 C# 文件。

using System;
using System.Runtime.InteropServices;
using System.Collections.Generic;
using Microsoft.Data.Analysis;
using Microsoft.SqlServer.CSharpExtension.SDK;
using System.Text.RegularExpressions;

namespace UserExecutor
{
    /// <summary>
    /// This class extends the AbstractSqlServerExtensionExecutor and uses
    /// a regular expression that checks if a text contains the word "C#" or "c#"
    /// </summary>
    public class CSharpRegexExecutor: AbstractSqlServerExtensionExecutor
    {
        /// <summary>
        /// This method overrides the Execute method from AbstractSqlServerExtensionExecutor.
        /// </summary>
        /// <param name="input">
        /// A C# DataFrame contains the input dataset.
        /// </param>
        /// <param name="sqlParams">
        /// A Dictionary contains the parameters from SQL server with name as the key.
        /// </param>
        /// <returns>
        /// A C# DataFrame contains the output dataset.
        /// </returns>
        public override DataFrame Execute(DataFrame input, Dictionary<string, dynamic> sqlParams){
            // Drop NULL values and sort by id
            //
            input = input.DropNulls().OrderBy("id");

            // Create empty output DataFrame with two columns
            //
            DataFrame output = new DataFrame(new PrimitiveDataFrameColumn<int>("id", 0), new StringDataFrameColumn("text", 0));

            // Filter text containing specific substring using regex expression
            //
            DataFrameColumn texts = input.Columns["text"];
            for(int i = 0; i < texts.Length; ++i)
            {
                if(Regex.IsMatch((string)texts[i], sqlParams["@regexExpr"]))
                {
                    output.Append(input.Rows[i], true);
                }
            }

            // Modify the parameters
            //
            sqlParams["@rowsCount"]  = output.Rows.Count;
            sqlParams["@regexExpr"] = "Success!";

            // Return output dataset as a DataFrame
            //
            return output;
        }
    }
}

编译和创建 DLL 文件

将类和依赖项打包到 DLL 中。 你可以创建名为 .csproj 的 RegexSample.csproj 文件，并将以下代码复制到该文件中。

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <AllowUnsafeBlocks>true</AllowUnsafeBlocks>
    <EnableDynamicLoading>true</EnableDynamicLoading>
  </PropertyGroup>
  <PropertyGroup>
    <OutputPath>$(BinRoot)/$(Configuration)/</OutputPath>
    <AppendTargetFrameworkToOutputPath>false</AppendTargetFrameworkToOutputPath>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.Analysis" Version="0.4.0" />
  </ItemGroup>
  <ItemGroup>
    <Reference Include="Microsoft.SqlServer.CSharpExtension.SDK">
      <HintPath>[path]\Microsoft.SqlServer.CSharpExtension.dll</HintPath>
    </Reference>
  </ItemGroup>
</Project>

转到项目文件夹并运行 dotnet build，这将生成以下文件：

path\to\project\bin\Debug\RegexSample.dll

有关详细信息，请参阅从 C# 项目创建 .NET DLL。

创建外部语言

需要在数据库中创建外部语言。 外部语言是数据库范围内的对象，这意味着需要为要在其中使用外部语言（如 C#）的每个数据库创建外部语言。

  1. 创建包含扩展名的 .zip 文件。

    作为 Windows 上 SQL Server 安装程序的一部分，.NET 扩展 .zip 文件安装在以下位置：<SQL Server install path>\MSSQL\Binn>\dotnet-core-CSharp-lang-extension.zip。 此 zip 文件包含 nativecsharpextension.dll。

  2. 通过 dotnet 文件创建外部语言 .zip：

    CREATE EXTERNAL LANGUAGE [dotnet]
FROM (
    CONTENT = N'<path>\dotnet-core-CSharp-lang-extension.zip',
    FILE_NAME = 'nativecsharpextension.dll'
);
GO

设置权限

若要执行 .NET C# 代码，需要向用户 SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) 授予对 \MSSQL 文件夹的读取权限。

  1. 右键单击新文件夹，然后选择“属性”>“安全”
  2. 选择编辑
  3. 选择“添加”
  4. 在“选择用户、计算机、服务帐户或组”中执行以下操作：
    1. 选择“对象类型”并确保选择“内置安全原则和组”
    2. 选择“位置”以在列表顶部选择本地计算机名称
    3. 输入 ALL APPLICATION PACKAGES，检查名称，然后选择“确定”以添加。 如果名称未解析，则请重新访问“位置”步骤。 系统标识符 (SID) 是计算机的本地标识符。

有关详细信息，请参阅 CREATE EXTERNAL LANGUAGE。

创建外部库

使用 CREATE EXTERNAL LIBRARY 为 DLL 文件创建外部库。 SQL Server 有权访问 .dll 文件，无需对 classpath 设置任何特殊访问权限。

创建 RegEx 代码的外部库。

CREATE EXTERNAL LIBRARY [regex.dll]
FROM (CONTENT = N'<path>\RegexSample.dll')
WITH (LANGUAGE = 'Dotnet');
GO

调用 C# 类

调用存储过程 sp_execute_external_script 以从 SQL Server 中调用 C# 代码。 在 script 参数中，定义要调用的 libraryname;namespace.classname。 还可以定义要在不指定库名称的情况下调用的 namespace.classname。 该扩展将找到具有匹配的 namespace.classname 的第一个库。 在以下代码中，类属于名为 UserExecutor 的命名空间和名为 CSharpRegexExecutor 的类。

代码未定义要调用的方法。 默认情况下，会调用 Execute 方法。 这意味着，如果希望能够从 SQL Server 调用类，则需要遵循 SDK 接口并在 C# 类中实现 Execute 方法。

存储过程采用输入查询（输入数据集）和正则表达式，并返回满足给定正则表达式的行。 其使用可检查文本是否包含单词 [Cc]# 或 C# 的正则表达式 c#。

DECLARE @rowsCount AS INT;
DECLARE @regexExpr AS VARCHAR (200);

SET @regexExpr = N'[Cc]#';

EXECUTE sp_execute_external_script
    @language = N'dotnet',
    @script = N'regex.dll;UserExecutor.CSharpRegexExecutor',
    @input_data_1 = N'SELECT * FROM testdata',
    @params = N'@regexExpr VARCHAR(200) OUTPUT, @rowsCount INT OUTPUT',
    @regexExpr = @regexExpr OUTPUT,
    @rowsCount = @rowsCount OUTPUT
    WITH RESULT SETS
(
        (id INT, TEXT VARCHAR (100))
);

SELECT @rowsCount AS rowsCount, @regexExpr AS message;

结果

执行调用之后，应获得包含两行的结果集。

C# 示例中的结果的屏幕截图。

相关内容