Tutorial: Mencari string menggunakan ekspresi reguler (regex) di C#

Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru

Tutorial ini menunjukkan kepada Anda cara menggunakan Ekstensi Bahasa SQL Server untuk membuat kelas C# yang menerima dua kolom (ID dan teks) dari SQL Server dan ekspresi reguler (regex) sebagai parameter input. Kelas mengembalikan dua kolom kembali ke SQL Server (ID dan teks).

Untuk teks tertentu di kolom teks yang dikirim ke kelas C#, kode memeriksa apakah ekspresi reguler yang diberikan terpenuhi, dan mengembalikan teks tersebut bersama dengan ID asli.

Kode sampel ini menggunakan ekspresi reguler yang memeriksa apakah teks berisi kata C# atau c#.

Prasyarat

• Instans Mesin Database pada SQL Server 2019 (15.x) dan versi yang lebih baru, dengan kerangka kerja ekstensibilitas dan ekstensi pemrograman .NET di Windows. Untuk informasi selengkapnya, lihat Apa itu Ekstensi Bahasa SQL Server?. Untuk informasi selengkapnya tentang persyaratan pengkodian, lihat Cara memanggil runtime .NET di Ekstensi Bahasa SQL Server.

• SQL Server Management Studio atau Azure Data Studio untuk mengeksekusi T-SQL.

• .NET 6 atau SDK yang lebih baru di Windows.

• File dotnet-core-CSharp-lang-extension-windows-release.zip dari Microsoft Extensibility SDK untuk C# untuk SQL Server.

Kompilasi baris perintah menggunakan dotnet build cukup untuk tutorial ini.

Membuat data sampel

Pertama, buat database baru dan isi testdata tabel dengan ID kolom dan 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

Membuat kelas utama

Dalam langkah ini, buat file kelas yang disebut RegexSample.cs dan salin kode C# berikut ke dalam file tersebut.

Kelas utama ini mengimpor SDK, yang berarti bahwa file C# yang diunduh pada langkah pertama perlu ditemukan dari kelas ini.

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

Mengkompilasi dan membuat file DLL

Kemas kelas dan dependensi Anda ke dalam DLL. Anda dapat membuat file yang .csproj disebut RegexSample.csproj dan menyalin kode berikut ke dalam file tersebut.

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

Buka folder proyek dan jalankan dotnet build, yang menghasilkan file berikut:

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

Untuk informasi selengkapnya, lihat Membuat DLL .NET dari proyek C#.

Membuat bahasa eksternal

Anda perlu membuat bahasa eksternal dalam database. Bahasa eksternal adalah objek terlingkup database, yang berarti bahwa bahasa eksternal seperti C# perlu dibuat untuk setiap database tempat Anda ingin menggunakannya.

1. Buat file yang .zip berisi ekstensi.

   Sebagai bagian dari penyiapan SQL Server di Windows, file ekstensi .zip .NET diinstal di lokasi ini: <SQL Server install path>\MSSQL\Binn>\dotnet-core-CSharp-lang-extension.zip. File zip ini berisi nativecsharpextension.dll.

2. Buat bahasa dotnet eksternal dari .zip file:

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

Mengatur izin

Untuk menjalankan kode .NET C#, pengguna SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) perlu diberikan izin baca ke \MSSQL folder.

1. Klik kanan folder dan pilih Keamanan Properti>
2. Pilih Edit
3. Pilih Tambahkan
4. Di Pilih Pengguna, Komputer, Akun Layanan, atau Grup:
   1. Pilih Jenis Objek dan pastikan Prinsip keamanan bawaan dan Grup dipilih
   2. Pilih Lokasi untuk memilih nama komputer lokal di bagian atas daftar
   3. Masukkan ALL APPLICATION PACKAGES, centang nama, dan pilih OK untuk ditambahkan. Jika nama tidak teratasi, kunjungi kembali langkah Lokasi . Pengidentifikasi sistem (SID) bersifat lokal untuk komputer Anda.

Untuk informasi selengkapnya, lihat MEMBUAT BAHASA EKSTERNAL.

Membuat pustaka eksternal

Gunakan CREATE EXTERNAL LIBRARY untuk membuat pustaka eksternal untuk file DLL Anda. SQL Server memiliki akses ke .dll file dan Anda tidak perlu mengatur izin khusus ke classpath.

Buat pustaka eksternal untuk kode RegEx.

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

Panggil kelas C#

Panggil prosedur sp_execute_external_script tersimpan untuk memanggil kode C# dari SQL Server. Dalam parameter skrip, tentukan yang libraryname;namespace.classname ingin Anda panggil. Anda juga dapat menentukan panggilan mana yang namespace.classname ingin Anda panggil tanpa menentukan nama pustaka. Ekstensi akan menemukan pustaka pertama yang memiliki kecocokan namespace.classname. Dalam kode berikut, kelas milik namespace layanan yang disebut UserExecutor dan kelas yang disebut CSharpRegexExecutor.

Kode tidak menentukan metode mana yang akan dipanggil. Secara default, Execute metode akan dipanggil. Ini berarti Anda perlu mengikuti antarmuka SDK dan menerapkan Execute metode di kelas C# Anda, jika Anda ingin dapat memanggil kelas dari SQL Server.

Prosedur tersimpan mengambil kueri input (himpunan data input) dan ekspresi reguler dan mengembalikan baris yang memenuhi ekspresi reguler yang diberikan. Ini menggunakan ekspresi [Cc]# reguler yang memeriksa apakah teks berisi kata C# atau 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;

Hasil

Setelah menjalankan panggilan, Anda akan mendapatkan tataan hasil dengan dua baris.

Konten terkait

• Cara memanggil runtime .NET di Ekstensi Bahasa SQL Server
• Apa itu Ekstensi Bahasa SQL Server?
• Jenis data yang didukung C# .NET dan SQL Server