Kurz: Hledání řetězce pomocí regulárních výrazů (regex) v jazyce C#

Platí pro: SQL Server 2019 (15.x) a novější verze

V tomto kurzu se dozvíte, jak pomocí jazykových rozšíření SQL Serveru vytvořit třídu jazyka C#, která přijímá dva sloupce (ID a text) z SQL Serveru a regulární výraz (regex) jako vstupní parametr. Třída vrátí dva sloupce zpět na SQL Server (ID a text).

Pro daný text v textovém sloupci odeslaném do třídy C# kód zkontroluje, jestli je daný regulární výraz splněný, a vrátí tento text společně s původním ID.

Tento vzorový kód používá regulární výraz, který kontroluje, zda text obsahuje slovo C# nebo c#.

Požadavky

• Instance databázového stroje na SQL Serveru 2019 (15.x) a novějších verzích s architekturou rozšiřitelnosti a rozšířením programování .NET ve Windows. Další informace naleznete v tématu Co je SQL Server Language Extensions?. Další informace o požadavcích na kódování naleznete v tématu Jak volat modul runtime .NET v sql Server Language Extensions.

• SQL Server Management Studio nebo Azure Data Studio pro spouštění T-SQL

• Sada .NET 6 nebo novější SDK ve Windows

• Soubor dotnet-core-CSharp-lang-extension-windows-release.zip ze sady Microsoft Extensibility SDK pro jazyk C# pro SQL Server.

Pro účely tohoto kurzu stačí kompilace dotnet build příkazového řádku.

Vytvoření ukázkových dat

Nejprve vytvořte novou databázi a naplňte testdata tabulku sloupciID.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

Vytvoření hlavní třídy

V tomto kroku vytvořte volaný RegexSample.cs soubor třídy a zkopírujte do tohoto souboru následující kód jazyka C#.

Tato hlavní třída importuje sadu SDK, což znamená, že soubor C# stažený v prvním kroku musí být zjistitelný z této třídy.

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

Kompilace a vytvoření souboru DLL

Zabalte třídy a závislosti do knihovny DLL. Můžete vytvořit volaný .csprojRegexSample.csproj soubor a zkopírovat do tohoto souboru následující kód.

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

Přejděte do složky projektu a spusťte dotnet buildpříkaz , který vygeneruje následující soubor:

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

Další informace naleznete v tématu Vytvoření knihovny DLL .NET z projektu jazyka C#.

Vytvoření externího jazyka

V databázi musíte vytvořit externí jazyk. Externí jazyk je objekt s vymezeným oborem databáze, což znamená, že pro každou databázi, ve které ji chcete použít, je potřeba vytvořit externí jazyky, jako je C#.

1. Vytvořte .zip soubor obsahující příponu.

   V rámci instalace SYSTÉMU SQL Server ve Windows je soubor přípony .zip .NET nainstalován v tomto umístění: <SQL Server install path>\MSSQL\Binn>\dotnet-core-CSharp-lang-extension.zip. Tento soubor ZIP obsahuje nativecsharpextension.dllsoubor .

2. Ze souboru vytvořte externí jazyk dotnet.zip :

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

Nastavení oprávnění

Pokud chcete spustit kód .NET C#, musí mít uživatel SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) udělená oprávnění ke čtení ke \MSSQL složce.

1. Klikněte pravým tlačítkem myši na složku a zvolte Zabezpečení vlastností>.
2. Výběr možnosti Upravit
3. Vyberte Přidat.
4. V části Vybrat uživatele, počítač, účty služeb nebo skupiny:
   1. Vyberte typy objektů a ujistěte se, že jsou vybrány předdefinované principy zabezpečení a skupiny .
   2. Vyberte Umístění a vyberte název místního počítače v horní části seznamu.
   3. Zadejte ALL APPLICATION PACKAGES, zaškrtněte název a vyberte OK , které chcete přidat. Pokud se název nevyřeší, přejděte ke kroku Umístění . Identifikátor systému (SID) je místní pro váš počítač.

Další informace naleznete v tématu VYTVOŘENÍ EXTERNÍHO JAZYKA.

Vytváření externích knihoven

Pomocí příkazu CREATE EXTERNAL LIBRARY vytvořte externí knihovnu pro soubory DLL. SQL Server má přístup k .dll souborům a nemusíte nastavovat žádná zvláštní oprávnění k souboru classpath.

Vytvořte externí knihovnu pro kód RegEx.

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

Volání třídy C#

Voláním uložené procedury sp_execute_external_script vyvoláte kód jazyka C# z SQL Serveru. V parametru skriptu definujte, který libraryname;namespace.classname chcete volat. Můžete také definovat, které namespace.classname chcete volat, aniž byste zadali název knihovny. Rozšíření najde první knihovnu, která má odpovídající namespace.classnameknihovnu . V následujícím kódu patří třída do oboru názvů volaného UserExecutor a třídy s názvem CSharpRegexExecutor.

Kód nedefinuje, kterou metodu se má volat. Ve výchozím nastavení Execute bude volána metoda. To znamená, že potřebujete postupovat podle rozhraní sady SDK a implementovat metodu Execute ve třídě C#, pokud chcete mít možnost volat třídu z SQL Serveru.

Uložená procedura přebírá vstupní dotaz (vstupní datovou sadu) a regulární výraz a vrací řádky, které splňují daný regulární výraz. Používá regulární výraz [Cc]# , který kontroluje, zda text obsahuje slovo C# nebo 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

Po provedení volání byste měli získat sadu výsledků se dvěma řádky.

Související obsah

• Jak volat modul runtime .NET v jazykových rozšířeních SQL Serveru
• Co jsou sql Server Language Extensions?
• Podporované datové typy C# .NET a SQL Serveru