Tutorial: Suchen nach einer Zeichenfolge mithilfe regulärer Ausdrücke (RegEx) in C#

Gilt für: SQL Server 2019 (15.x) und höhere Versionen

In diesem Tutorial wird gezeigt, wie Sie SQL Server-Spracherweiterungen verwenden, um eine C#-Klasse zu erstellen, die zwei Spalten (ID und Text) aus SQL Server und einen RegEx als Eingabeparameter empfängt. Die Klasse gibt an SQL Server zwei Spalten (ID und Text) zurück.

Für einen bestimmten Text in der Textspalte, der an die C#-Klasse gesendet wird, prüft der Code, ob der angegebene reguläre Ausdruck erfüllt ist, und gibt diesen Text zusammen mit der ursprünglichen ID zurück.

In diesem Beispielcode wird ein RegEx verwendet, der prüft, ob ein Text das Wort C# oder c# enthält.

Voraussetzungen

• Eine Instanz der SQL Server 2019-Datenbank-Engine (15.x) und neueren Versionen mit dem Erweiterungsframework und der :NET-Programmiererweiterung unter Windows. Weitere Informationen finden Sie unter Was sind SQL Server-Spracherweiterungen?. Weitere Informationen zu Codierungsanforderungen finden Sie unter Aufrufen von .NET-Runtime in SQL Server-Spracherweiterungen.

• SQL Server Management Studio oder Azure Data Studio für die Ausführung von T-SQL.

• .NET 6 oder höheres SDK unter Windows.

• Die Datei dotnet-core-CSharp-lang-extension-windows-release.zip aus dem Microsoft-Erweiterbarkeits-SDK für C# für SQL Server.

Die Befehlszeilenkompilierung mit dotnet build ist für dieses Tutorial ausreichend.

Erstellen von Beispieldaten

Erstellen Sie zunächst eine neue Datenbank, und füllen Sie eine testdataTabelle mit den Spalten ID und text aus.

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

Erstellen der Hauptklasse

In diesem Schritt erstellen Sie eine Klassendatei namens RegexSample.cs und kopieren den folgenden C#-Code in diese Datei.

Diese Hauptklasse importiert das SDK, was bedeutet, dass die im ersten Schritt heruntergeladene C#-Datei aus dieser Klasse erkennbar sein muss.

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

Kompilieren und Erstellen einer DLL-Datei

Packen Sie Ihre Klassen und Abhängigkeiten in eine DLL-Datei. Sie können eine .csproj-Datei namens RegexSample.csproj erstellen und folgenden Code in diese Datei kopieren.

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

Wechseln Sie zum Projektordner, und führen Sie dotnet build aus, wodurch die folgende Datei generiert wird:

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

Weitere Informationen finden Sie unter Erstellen einer .NET-DLL über ein C#-Projekt.

Erstellen einer externen Sprache

Sie müssen eine externe Sprache in der Datenbank erstellen. Die externe Sprache ist ein datenbankweites Objekt, was bedeutet, dass externe Sprachen wie C# für jede Datenbank, in der Sie sie verwenden möchten, erstellt werden müssen.

1. Erstellen Sie eine .zip-Datei mit der Erweiterung.

   Im Rahmen des SQL Server-Setups unter Windows wird die .zip-Datei mit der .NET-Erweiterung an diesem Speicherort installiert: <SQL Server install path>\MSSQL\Binn>\dotnet-core-CSharp-lang-extension.zip. Diese ZIP-Datei enthält die nativecsharpextension.dll.

2. Erstellen Sie eine externe Sprache dotnet aus der .zip-Datei:

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

Festlegen von Berechtigungen

Um .NET C#-Code auszuführen, müssen dem Benutzer SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) Leseberechtigungen für den Ordner \MSSQL erteilt werden.

1. Klicken Sie mit der rechten Maustaste auf den Ordner, und wählen Sie Eigenschaften>Sicherheit aus.
2. Wählen Sie Bearbeiten aus.
3. Wählen Sie Hinzufügen aus.
4. Unter Benutzer, Computer, Dienstkonten oder Gruppen auswählen:
   1. Klicken Sie auf Objekttypen und stellen Sie sicher, dass Integrierte Sicherheitsprinzipien und Gruppen ausgewählt ist.
   2. Klicken Sie auf Speicherorte, um den Namen des lokalen Computers oben in der Liste auszuwählen.
   3. Geben Sie ALL APPLICATION PACKAGES ein, überprüfen Sie den Namen, und klicken Sie zum Hinzufügen auf OK. Wenn der Name nicht aufgelöst wird, gehen Sie zurück zum Schritt Speicherorte. Die System-ID (SID) ist lokal auf Ihrem Computer.

Weitere Informationen finden Sie unter CREATE EXTERNAL LANGUAGE.

Erstellen von externen Bibliotheken

Verwenden Sie CREATE EXTERNAL LIBRARY, um eine externe Bibliothek für Ihre DLL-Dateien zu erstellen. SQL Server wird auf die .dll-Dateien zugreifen können, sodass Sie keine speziellen Berechtigungen für den classpath festlegen müssen.

Erstellen Sie eine externe Bibliothek für den RegEx-Code.

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

Aufrufen der C#-Klasse

Rufen Sie die gespeicherte Prozedur sp_execute_external_script auf, um den C#-Code von SQL Server aufzurufen. Legen Sie im Parameter script fest, welche libraryname;namespace.classname Sie aufrufen möchten. Sie können auch definieren, welche namespace.classname Sie aufrufen möchten, ohne den Bibliotheksnamen anzugeben. Die Erweiterung findet die erste Bibliothek, die die übereinstimmende namespace.classname aufweist. Im folgenden Code gehört die Klasse zu einem Namespace namens UserExecutor und einer Klasse namens CSharpRegexExecutor.

Der Code definiert nicht, welche Methode aufgerufen werden soll. Standardmäßig wird die Execute-Methode aufgerufen. Das bedeutet, dass Sie der SDK-Schnittstelle folgen und eine Execute-Methode in der C#-Klasse implementieren müssen, wenn Sie die Klasse aus SQL Server abrufen möchten.

Die gespeicherte Prozedur übernimmt eine Eingabeabfrage (Eingabedataset) und einen regulären Ausdruck und gibt die Zeilen zurück, die den angegebenen regulären Ausdruck erfüllen. Es wird ein regulärer Ausdruck [Cc]# verwendet, der prüft, ob ein Text das Wort C# oder c# enthält.

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;

Ergebnisse

Nach der Ausführung des Aufrufs sollten Sie ein Resultset mit zwei der Zeilen erhalten.

Zugehöriger Inhalt

• Aufrufen der .NET-Runtime in SQL Server-Spracherweiterungen
• Was sind SQL Server-Spracherweiterungen?
• Von C# .NET und SQL Server unterstützte Datentypen