共用方式為

教學課程：在 C# 中使用規則運算式 (regex) 搜尋字串

  • 發行項

適用於：SQL Server 2019 (15.x) 和更新版本

本教學課程示範如何使用 SQL Server 語言延伸模組來建立一個會從 SQL Server 接收兩個資料行 (ID 和 text) 的 C# 類別，以及一個作為輸入參數的規則運算式。 此類別會將兩個資料行傳回 SQL Server (ID 和 text)。

針對文字資料行中傳送至 C# 類別的指定文字，此程式碼會檢查指定的規則運算式是否已完成，並連同原始識別碼一起傳回該文字。

此範例程式碼會使用可檢查文字是否包含 C# 或 c# 一字的規則運算式。

必要條件

使用 dotnet build 的命令列編譯對於本教學課程就已經足夠。

建立範例資料

首先，建立新資料庫，並用 ID 和 text 資料行填入 testdata 資料表。

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 中。 建立名為 .csproj 的 RegexSample.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. 從 dotnet 檔案建立外部語言 .zip：

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

設定權限

若要執行 .NET C# 程式碼，使用者 SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) 必須被授與 \MSSQL 資料夾的讀取權限。

  1. 以滑鼠右鍵按一下資料夾，然後選擇 [屬性]>[安全性]。
  2. 選取編輯
  3. 選取新增
  4. 在 [選取使用者、電腦、服務帳戶或群組] 中：
    1. 按一下 [物件類型]，並確定已選取 [內建安全性原則和群組]。
    2. 按一下 [位置]，選取清單頂端的本機電腦名稱。
    3. 輸入 ALL APPLICATION PACKAGES，檢查名稱，然後選取 [確定] 以新增。 如果名稱無法解析，請重新瀏覽 [位置] 步驟。 系統識別碼 (SID) 是機器的本機識別碼。

如需詳細資訊，請參閱 CREATE EXTERNAL LANGUAGE。

建立外部程式庫

使用 CREATE EXTERNAL LIBRARY 為您的 DLL 檔案建立外部程式庫。 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。 您也可以定義想要呼叫哪些 namespace.classname，而無需指定程式庫名稱。 延伸模組會尋找具有相符 namespace.classname 的第一個程式庫。 在下列程式碼中，類別屬於名為 UserExecutor 的命名空間，以及名為 CSharpRegexExecutor 的類別。

程式碼不會定義要呼叫的方法。 根據預設，將呼叫 Execute 方法。 也就是說，如果您想要能夠從 SQL Server 呼叫類別，則必須遵循 SDK 介面，並在您的 C# 類別中實作 Execute 方法。

預存程序會採用輸入查詢 (輸入資料集) 和規則運算式，並傳回滿足指定規則運算式的資料列。 它會使用可檢查文字是否包含 C# 或 c# 字詞的規則運算式 [Cc]#。

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;

結果

執行呼叫之後，您應該會獲得包含其中兩個資料列的結果集。

C# 範例結果的螢幕擷取畫面。

相關內容