Руководство. Поиск строки с помощью регулярных выражений (regex) в C#

Область применения: SQL Server 2019 (15.x) и более поздних версий

В этом руководстве показано, как использовать расширения языка SQL Server для создания класса C#, который получает два столбца (идентификатор и текст) из SQL Server и регулярное выражение (regex) в качестве входного параметра. Класс возвращает в SQL Server два столбца (идентификатор и текст).

Для заданного текста в текстовом столбце, отправленном классу C#, код проверяет, выполняется ли данное регулярное выражение, и возвращает этот текст вместе с исходным идентификатором.

В этом примере кода используется регулярное выражение, которое проверяет, содержит ли текст слово C# или c#.

Необходимые компоненты

• ядро СУБД экземпляре SQL Server 2019 (15.x) и более поздних версиях с расширением расширяемости и расширения программирования .NET в Windows. Дополнительные сведения см. в разделе "Что такое расширения языка SQL Server?". Дополнительные сведения о требованиях к программированию см. в статье "Как вызвать среду выполнения .NET в расширениях языка SQL Server".

• SQL Server Management Studio или Azure Data Studio для выполнения T-SQL.

• Пакет SDK для .NET 6 или более поздней версии в Windows.

• Файл dotnet-core-CSharp-lang-extension-windows-release.zip из пакета SDK расширяемости Майкрософт для C# для SQL Server.

Для работы с этим руководством достаточно использовать 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# в этот файл.

Этот основной класс импортирует пакет 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

Дополнительные сведения см. в разделе "Создание библиотеки DLL .NET" из проекта C#.

Создание внешнего языка

Необходимо создать внешний язык в базе данных. Внешний язык — это объект с областью действия базы данных, что означает, что для каждой базы данных необходимо создать внешние языки, такие как C#.

1. .zip Создайте файл, содержащий расширение.

   В рамках установки SQL Server в Windows файл расширения .zip .NET устанавливается в этом расположении: <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 , чтобы вызвать код C# из SQL Server. В параметре скрипта определите, какое libraryname;namespace.classname необходимо вызвать. Вы также можете определить, какие namespace.classname вызовы необходимо вызвать, не указывая имя библиотеки. Расширение найдет первую библиотеку, которая соответствует.namespace.classname В следующем коде класс принадлежит пространству UserExecutor имен и классу CSharpRegexExecutor.

Код не определяет, какой метод следует вызывать. По умолчанию Execute метод будет вызываться. Это означает, что необходимо следовать интерфейсу ПАКЕТА SDK и реализовать Execute метод в классе C#, если вы хотите иметь возможность вызывать класс из SQL Server.

Хранимая процедура принимает входной запрос (входной набор данных) и регулярное выражение и возвращает строки, которые выполнили данное регулярное выражение. Он использует регулярное выражение [Cc]# , которое проверяет, содержит ли текст слово C# или c#.

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

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

EXEC 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;

Результаты

После выполнения вызова необходимо получить результирующий набор с двумя строками.

Связанный контент

• Как вызвать среду выполнения .NET в расширениях языка SQL Server
• Что такое расширения языка SQL Server?
• Поддерживаемые типы данных C# .NET и SQL Server