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

[アーティクル]
05/02/2024

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

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

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

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

前提条件

Windows 上の拡張性フレームワークと .NET プログラミング拡張機能を備えた、SQL Server 2019 (15.x) 以降のバージョン上のデータベースエンジンインスタンス。詳細については、「SQL Server 言語拡張とは?」を参照してください。コーディング要件の詳細については、「SQL Server 言語拡張で .NET ランタイムを呼び出す方法」を参照してください。
T-SQL 実行用の SQL Server Management Studio または Azure Data Studio。
Windows 上の .NET 6 以降の SDK。
Microsoft Extensibility SDK for C# for SQL Server の dotnet-core-CSharp-lang-extension-windows-release.zip ファイル。

このチュートリアルでは、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# などの外部言語を使用する場合、使用する各データベースに作成する必要があるデータベーススコープのオブジェクトです。

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

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

.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. 「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# のサンプルの結果のスクリーンショット。

次の方法で共有

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

前提条件

サンプルデータの作成

メインクラスの作成

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

外部言語の作成

アクセス許可を設定する

外部ライブラリの作成

C# クラスの呼び出し

結果

フィードバック

その他のリソース

次の方法で共有

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

前提条件

サンプル データの作成

メイン クラスの作成

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

外部言語の作成

アクセス許可を設定する

外部ライブラリの作成

C# クラスの呼び出し

結果

関連するコンテンツ

フィードバック

その他のリソース

サンプルデータの作成

メインクラスの作成