Dela via

Självstudie: Sök efter en sträng med reguljära uttryck (regex) i C#

Gäller för: SQL Server 2019 (15.x) och senare versioner

Den här självstudien visar hur du använder SQL Server Language Extensions för att skapa en C#-klass som tar emot två kolumner (ID och text) från SQL Server och ett reguljärt uttryck (regex) som indataparameter. Klassen returnerar två kolumner tillbaka till SQL Server (ID och text).

För en viss text i textkolumnen som skickas till C#-klassen kontrollerar koden om det angivna reguljära uttrycket uppfylls och returnerar texten tillsammans med det ursprungliga ID:t.

Den här exempelkoden använder ett reguljärt uttryck som kontrollerar om en text innehåller ordet C# eller c#.

Förutsättningar

Kommandoradskompilering med hjälp av dotnet build räcker för den här självstudien.

Skapa exempeldata

Skapa först en ny databas och fyll i en testdata tabell med ID och text kolumner.

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

Skapa huvudklassen

I det här steget skapar du en klassfil med namnet RegexSample.cs och kopierar följande C#-kod till filen.

Den här huvudklassen importerar SDK:n, vilket innebär att C#-filen som laddades ned i det första steget måste kunna identifieras från den här klassen.

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

Kompilera och skapa en DLL-fil

Paketera dina klasser och beroenden i en DLL. Du kan skapa en .csproj fil med namnet RegexSample.csproj och kopiera följande kod till filen.

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

Gå till projektmappen och kör dotnet build, vilket genererar följande fil:

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

Mer information finns i Skapa en .NET DLL från ett C#-projekt.

Skapa externt språk

Du måste skapa ett externt språk i databasen. Det externa språket är ett databasomfattningsobjekt, vilket innebär att externa språk som C# måste skapas för varje databas som du vill använda det i.

  1. Skapa en .zip fil som innehåller tillägget.

    Som en del av SQL Server-installationen i Windows installeras .NET-tilläggsfilen .zip på den här platsen: <SQL Server install path>\MSSQL\Binn>\dotnet-core-CSharp-lang-extension.zip. Den här zip-filen innehåller nativecsharpextension.dll.

  2. Skapa ett externt språk dotnet från .zip filen:

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

Ange behörigheter

Om du vill köra .NET C#-kod måste användaren SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) beviljas läsbehörighet till \MSSQL mappen.

  1. Högerklicka på mappen och välj Egenskaper>säkerhet
  2. Välj Redigera
  3. Välj Lägg till
  4. I Välj användare, dator, tjänstkonton eller grupper:
    1. Välj Objekttyper och kontrollera att inbyggda säkerhetsprinciper och grupper har valts
    2. Välj Platser för att välja namnet på den lokala datorn överst i listan
    3. Ange ALL APPLICATION PACKAGES, kontrollera namnet och välj OK att lägga till. Om namnet inte matchar går du tillbaka till steget Platser . Systemidentifieraren (SID) är lokal för datorn.

Mer information finns i SKAPA EXTERNT SPRÅK.

Skapa externa bibliotek

Använd SKAPA EXTERNT BIBLIOTEK för att skapa ett externt bibliotek för dina DLL-filer. SQL Server har åtkomst till .dll filerna och du behöver inte ange några särskilda behörigheter till classpath.

Skapa ett externt bibliotek för RegEx-koden.

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

Anropa C#-klassen

Anropa den lagrade proceduren sp_execute_external_script för att anropa C#-koden från SQL Server. I skriptparametern definierar du vilket libraryname;namespace.classname du vill anropa. Du kan också definiera vilket namespace.classname du vill anropa utan att ange biblioteksnamnet. Tillägget hittar det första biblioteket som har matchat namespace.classname. I följande kod tillhör klassen ett namnområde med namnet UserExecutor och en klass med namnet CSharpRegexExecutor.

Koden definierar inte vilken metod som ska anropas. Som standard Execute anropas metoden. Det innebär att du måste följa SDK-gränssnittet och implementera en Execute metod i C#-klassen om du vill kunna anropa klassen från SQL Server.

Den lagrade proceduren tar en indatafråga (indatauppsättning) och ett reguljärt uttryck och returnerar de rader som uppfyllde det angivna reguljära uttrycket. Det använder ett reguljärt uttryck [Cc]# som kontrollerar om en text innehåller ordet C# eller 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;

Results

När du har kört anropet bör du få en resultatuppsättning med två av raderna.

Skärmbild av resultat från C#-exempel.

Relaterat innehåll