Tutoriel : Rechercher une chaîne à l’aide d’expressions régulières (regex) en C#

S’applique à : SQL Server 2019 (15.x) et versions ultérieures

Ce tutoriel vous montre comment utiliser les extensions de langage SQL Server pour créer une classe C# qui reçoit deux colonnes (ID et text) de SQL Server et une expression régulière (regex) comme paramètre d’entrée. La classe retourne deux colonnes à SQL Server (ID et text).

Pour un texte donné dans la colonne de texte envoyée à la classe C#, le code vérifie si l’expression régulière donnée est satisfaite, puis retourne ce texte avec l’ID d’origine.

Cet exemple de code utilise une expression régulière qui vérifie si un texte contient le mot C# ou c#.

Prérequis

• Instance de moteur de base de données sur SQL Server 2019 (15.x) et versions ultérieures, avec cadre d’extensibilité et extension de programmation .NET sur Windows. Pour plus d’informations, consultez Présentation des extensions de langage SQL Server. Pour plus d’informations sur les exigences en matière de codage, consultez Comment appeler le runtime .NET dans les extensions de langage SQL Server.

• SQL Server Management Studio ou Azure Data Studio pour exécuter le code T-SQL.

• .NET 6 ou version ultérieure du kit de développement logiciel (SDK) sur Windows.

• Le fichier dotnet-core-CSharp-lang-extension-windows-release.zip du kit de développement logiciel (SDK) d’extensibilité Microsoft pour C# pour SQL Server.

Pour ce tutoriel, la compilation à partir de la ligne de commande avec dotnet build suffit.

Créer des exemples de données

Commencez par créer une base de données et remplissez une table testdata avec les colonnes ID et 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

Créer la classe principale

Dans cette étape, créez un fichier de classe appelé RegexSample.cs et copiez le code C# suivant dans ce fichier.

Cette classe principale importe le kit de développement logiciel (SDK), ce qui signifie que le fichier C# téléchargé à l’étape 1 doit être détectable à partir de cette classe.

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

Compiler et créer un fichier DLL

Empaquetez vos classes et dépendances dans un DLL. Vous pouvez créer un fichier .csproj appelé RegexSample.csproj et copier le code suivant dans ce fichier.

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

Accédez au dossier du projet et exécutez dotnet build, ce qui génère le fichier suivant :

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

Pour plus d’informations, consultez Créer un projet .NET DLL à partir d’un projet C#.

Créer un langage externe

Vous devez créer un langage externe dans la base de données. Le langage externe est un objet délimité à une base de données, ce qui signifie qu’un langage externe comme C# doit être créé pour chaque base de données où vous souhaitez l’utiliser.

1. Créez un fichier .zip contenant l’extension.

   Dans le cadre de l’installation de SQL Server sur Windows, le fichier .zip de l’extension .NET est enregistré à l’emplacement <SQL Server install path>\MSSQL\Binn>\dotnet-core-CSharp-lang-extension.zip. Ce fichier zip contient nativecsharpextension.dll.

2. Créez un langage externe dotnet à partir du fichier .zip :

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

Définir des autorisations

Pour exécuter le code C# .NET, l’utilisateur SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) doit disposer des autorisations de lecture octroyées sur le dossier \MSSQL.

1. Cliquez avec le bouton droit sur le dossier, puis sélectionnez Propriétés>Sécurité
2. Sélectionnez Modifier.
3. Sélectionnez Ajouter
4. Dans Sélectionner les utilisateurs, les ordinateurs, les comptes de service ou les groupes :
   1. Sélectionnez Types d’objets et assurez-vous que Principes de sécurité intégrés, puis Groupes est sélectionné
   2. Sélectionnez Emplacements pour choisir le nom de l’ordinateur local en haut de la liste
   3. Saisissez ALL APPLICATION PACKAGES, vérifiez le nom, puis cliquez sur OK pour l’ajouter. Si le nom n’est pas résolu, revisitez l’étape Emplacements. L’identifiant du système (SID) est local sur votre ordinateur.

Pour plus d’informations, consultez CRÉER UN LANGAGE EXTERNE.

Créer des bibliothèques externes

Utilisez CREATE EXTERNAL LIBRARY pour créer une bibliothèque externe pour vos fichiers DLL. SQL Server ayant accès aux fichiers .dll, vous n’avez pas besoin de définir d’autorisations spéciales pour classpath.

Créez une bibliothèque externe pour le code RegEx.

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

Appeler la classe C#

Appelez la procédure sp_execute_external_script stockée pour appeler le code C# à partir de SQL Server. Dans le paramètre script, définissez le libraryname;namespace.classname à appeler. Vous pouvez également définir le namespace.classname que vous souhaitez appeler sans spécifier le nom de la bibliothèque. L’extension trouvera la première bibliothèque qui a la correspondance namespace.classname. Dans le code suivant, la classe appartient à un espace de noms appelé UserExecutor et à un fichier de classe appelé CSharpRegexExecutor.

Le code ne définit pas la méthode à appeler. Par défaut, la méthode Execute est appelée. Cela signifie que vous devez suivre l’interface du kit SDK et implémenter une méthode Execute dans votre classe C# pour pouvoir appeler la classe à partir de SQL Server.

La procédure stockée prend une requête d’entrée (jeu de données d’entrée) et une expression régulière, puis retourne les lignes qui satisfont l’expression régulière donnée. Elle utilise une expression régulière [Cc]# qui vérifie si un texte contient le mot C# ou 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;

Résultats

Après l’exécution de l’appel, vous devez obtenir un jeu de résultats avec deux lignes.

Contenu connexe

• Guide pratique pour appeler le runtime .NET dans les extensions de langage SQL Server
• Présentation des extensions de langage SQL Server
• Types de données prises en charge par C# .NET et SQL Server