Esercitazione: Cercare una stringa usando espressioni regolari (regex) in C#

Si applica a: SQL Server 2019 (15.x) e versioni successive

Questa esercitazione illustra come usare le estensioni del linguaggio di SQL Server per creare una classe C# che riceve due colonne (ID e testo) da SQL Server e un'espressione regolare (regex) come parametro di input. La classe restituisce due colonne (ID e testo) a SQL Server.

Per un determinato testo nella colonna di testo inviata alla classe C#, il codice controlla se l'espressione regolare specificata viene soddisfatta e restituisce tale testo insieme all'ID originale.

Il codice di esempio usa un'espressione regolare che controlla se un testo contiene la parola C# o c#.

Prerequisiti

• Istanza del motore di database su SQL Server 2019 (15.x) e versioni successive, con il framework di estensibilità e l'estensione di programmazione .NET su Windows. Per altre informazioni, vedere Che cosa sono le estensioni del linguaggio di SQL Server?. Per altre informazioni sui requisiti di codifica, vedere Come chiamare il runtime .NET nelle estensioni del linguaggio di SQL Server.

• SQL Server Management Studio o Azure Data Studio per l'esecuzione di T-SQL.

• SDK .NET 6 o successivo su Windows.

• File dotnet-core-CSharp-lang-extension-windows-release.zip di Microsoft Extensibility SDK per C# per SQL Server.

Per questa esercitazione è sufficiente la compilazione da riga di comando tramite dotnet build.

Creare dati di esempio

Per prima cosa, creare un nuovo database e popolare una tabella testdata con le colonne ID e 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

Creare la classe principale

In questo passaggio creare un file di classe denominato RegexSample.cs e copiare il codice C# seguente nel file.

Questa classe principale importa l'SDK, il che significa che il file C# scaricato nel primo passaggio deve essere individuabile da questa 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;
        }
    }
}

Compilare e creare un file DLL

Assemblare le classi e le dipendenze in un file DDL. È possibile creare un file .csproj denominato RegexSample.csproj e copiare il codice seguente in tale file.

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

Passare alla cartella del progetto ed eseguire dotnet build, che genera il file seguente:

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

Per altre informazioni, vedere Creare un file DLL .NET da un progetto C#.

Creare un linguaggio esterno

È necessario creare un linguaggio esterno nel database. Il linguaggio esterno è un oggetto con ambito di database, il che significa che è necessario creare linguaggi esterni come C# per ogni database in cui si vuole usarlo.

1. Creare un file .zip contenente l'estensione.

   Come parte del programma di installazione di SQL Server in Windows, il file .zip dell'estensione .NET è installato in questo percorso: <SQL Server install path>\MSSQL\Binn>\dotnet-core-CSharp-lang-extension.zip. Questo file ZIP contiene nativecsharpextension.dll.

2. Creare un linguaggio esterno dotnet dal file .zip:

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

Impostare le autorizzazioni

Per eseguire il codice .NET C#, all'utente SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) devono essere concesse le autorizzazioni di lettura per la cartella \MSSQL.

1. Fare clic con il pulsante destro del mouse sulla cartella e scegliere Proprietà>Sicurezza
2. Seleziona Modifica.
3. Seleziona Aggiungi
4. In Seleziona utenti, computer, account servizio o gruppi:
   1. Fare clic su Tipi di oggetto e assicurarsi che siano selezionati i principi di sicurezza predefiniti e i gruppi
   2. Fare clic su Percorsi per selezionare il nome del computer locale nella parte superiore dell'elenco
   3. Immettere ALL APPLICATION PACKAGES, controllare il nome e selezionare OK per aggiungere. Se il nome non viene risolto, rivedere il passaggio relativo ai Percorsi. L'identificatore di sistema (SID) è locale nel computer.

Per altre informazioni, vedere CREATE EXTERNAL LANGUAGE.

Creare librerie esterne

Usare CREATE EXTERNAL LIBRARY per creare una libreria esterna per i file DLL. SQL Server ha accesso ai file .dll e non è necessario impostare autorizzazioni speciali per il classpath.

Creare una libreria esterna per il codice RegEx.

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

Chiamare la classe C#

Chiamare la stored procedure sp_execute_external_script per richiamare il codice C# da SQL Server. Nel parametro script definire quale libraryname;namespace.classname si vuole chiamare. È anche possibile definire quale namespace.classname si vuole chiamare senza specificare il nome della libreria. L'estensione troverà la prima libreria che ha il namespace.classname corrispondente. Nel codice seguente la classe appartiene a uno spazio dei nomi denominato UserExecutor e a una classe denominata CSharpRegexExecutor.

Il codice non definisce il metodo da chiamare. Per impostazione predefinita, verrà chiamato il metodo Execute. Ciò significa che è necessario seguire l'interfaccia dell'SDK e implementare un metodo Execute nella classe C#, se si vuole essere in grado di chiamare la classe da SQL Server.

La stored procedure accetta una query di input (set di dati di input) e un'espressione regolare e restituisce le righe che hanno soddisfatto l'espressione regolare specificata. Viene usata un'espressione regolare [Cc]# che controlla se un testo contiene la parola C# o 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;

Risultati

Dopo aver eseguito la chiamata, si dovrebbe ottenere un set di risultati con due delle righe.

Contenuto correlato

• Come chiamare il runtime .NET nelle estensioni del linguaggio di SQL Server
• Cosa sono le estensioni del linguaggio di SQL Server?
• Tipi di dati C# .NET e SQL Server supportati