次の方法で共有


チュートリアル: C# で正規表現 (regex) を使用して文字列を検索する

適用対象: SQL Server 2019 (15.x) 以降のバージョン

このチュートリアルでは、SQL Server の言語拡張を使用し、SQL Server から 2 つの列 (ID とテキスト) と、正規表現の入力パラメーターを受け取る C# クラスを作成する方法について説明します。 このクラスでは、SQL Server に 2 つの列 (ID とテキスト) を返します。

C# クラスに送信されるテキスト列内の指定のテキストが、指定した正規表現を満たしているかどうかをコードが確認し、元の ID と共にそのテキストが返されます。

このサンプル コードでは、テキストに単語 C# または c# が含まれているかどうかを確認する正規表現を使用しています。

前提条件

このチュートリアルでは、dotnet build を使用したコマンドライン コンパイルで十分です。

サンプル データの作成

まず、新しいデータベースを作成し、testdata テーブルに ID 列と 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

メイン クラスの作成

この手順では、RegexSample.cs というクラス ファイルを作成し、次の C# コードをそのファイルにコピーします。

このメイン クラスが SDK をインポートします。つまり、最初の手順でダウンロードした C# ファイルをこのクラスで検出できるようにする必要があります。

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

DLL ファイルのコンパイルおよび作成

ご自分のクラスと依存関係を DLL ファイルにパッケージ化します。 RegexSample.csproj という .csproj ファイルを作成し、次のコードをそのファイルにコピーします。

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

プロジェクト フォルダーに移動して dotnet build を実行すると、次のファイルが生成されます。

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

詳細については、「C# プロジェクトから .NET DLL を作成する」を参照してください。

外部言語の作成

データベースには、外部言語を作成する必要があります。 外部言語とは、データベースで C# などの外部言語を使用する場合、使用する各データベースに作成する必要があるデータベース スコープのオブジェクトです。

  1. 拡張機能を含む .zip ファイルを作成します。

    Windows での SQL Server セットアップの一部として、.NET 拡張機能 .zip ファイルが <SQL Server install path>\MSSQL\Binn>\dotnet-core-CSharp-lang-extension.zip にインストールされます。 この zip ファイルには nativecsharpextension.dll が含まれています。

  2. .zip ファイルから外部言語 dotnet を作成する方法は次のとおりです。

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

アクセス許可を設定する

.NET C# コードを実行するには、\MSSQL フォルダーに対する読み取りアクセス許可をユーザー SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) に付与する必要があります。

  1. フォルダーを右クリックし、[プロパティ] > [セキュリティ] の順に選択します。
  2. 編集を選択する
  3. [追加] を選択します。
  4. [ユーザー、コンピューター、サービス アカウントまたはグループの選択] で次を実行します。
    1. [オブジェクト タイプ] を選択し、[組み込みセキュリティ原則] と [グループ] が選択されていることを確認します。
    2. [場所] を選択して、リストの先頭にあるローカル コンピューター名を選択します。
    3. ALL APPLICATION PACKAGES」と入力し、名前を確認し、[OK] をクリックして追加します。 名前が解決されない場合は、[場所] の手順を再実行します。 システム識別子 (SID) はコンピューターに対してローカルです。

詳しくは、「CREATE EXTERNAL LANGUAGE」をご覧ください。

外部ライブラリの作成

お使いの DLL ファイル用の外部ライブラリを作成するには、CREATE EXTERNAL LIBRARY を使用します。 SQL Server は .dll ファイルにアクセスできるため、classpath に特別なアクセス許可を設定する必要はありません。

RegEx コード用の外部ライブラリを作成します。

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

C# クラスの呼び出し

ストアド プロシージャ sp_execute_external_script を呼び出し、SQL Server から C# コードを呼び出します。 呼び出す libraryname;namespace.classname を、script パラメーターに定義します。 ライブラリ名を指定せずに、呼び出す namespace.classname を定義することもできます。 拡張機能は、namespace.classname が一致する最初のライブラリを検索します。 次のコードでは、クラスは UserExecutor という名前空間と CSharpRegexExecutor というクラスに属しています。

コードでは、どのメソッドを呼び出すかが定義されていません。 デフォルトでは、Execute メソッドが呼び出されます。 つまり、SQL Server からクラスを呼び出したい場合には、SDK インターフェイスに従い、Execute メソッドをお使いの C# クラスに実装する必要があります。

このストアド プロシージャは、入力クエリ (入力データセット) と正規表現を受け取り、指定された正規表現を満たす行を返します。 正規表現 [Cc]# を使用して、テキストに C# または 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;

結果

呼び出しを実行すると、2 つの行の結果セットが返されるはずです。

C# のサンプルの結果のスクリーンショット。