Tutorial: Pesquisar uma sequência usando expressões regulares (regex) em C#

Aplica-se a: SQL Server 2019 (15.x) e versões posteriores

Este tutorial mostra como usar Extensões de Linguagem do SQL Server e criar uma classe C# que recebe duas colunas (ID e texto) do SQL Server e uma expressão regular (regex) como um parâmetro de entrada. A classe retorna duas colunas de volta para SQL Server (ID e texto).

Para um determinado texto na coluna de texto enviado para a classe C#, o código verifica se determinada expressão regular é atendida e retorna esse texto com a ID original.

Este código de exemplo usa uma expressão regular que verifica se um texto contém a palavra C# ou c#.

Pré-requisitos

• Instância do Mecanismo de Banco de Dados no SQL Server 2019 (15.x) e versões posteriores com a estrutura de extensibilidade e a extensão de programação .NET no Windows. Para obter mais informações, consulte O que são Extensões de Linguagem no SQL Server?. Para obter mais informações sobre requisitos de codificação, confira Como chamar o runtime do .NET nas Extensões de Linguagem do SQL Server.

• SQL Server Management Studio ou Azure Data Studio para executar T-SQL.

• .NET 6 ou SDK posterior no Windows.

• O arquivo dotnet-core-CSharp-lang-extension-windows-release.zip do SDK de Extensibilidade da Microsoft para C# para SQL Server.

A compilação da linha de comando usando dotnet build é suficiente para este tutorial.

Criar dados de exemplo

Primeiro, crie um banco de dados e preencha uma tabela testdata com as colunas ID e 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

Criar a classe principal

Nesta etapa, crie um arquivo de classe chamado RegexSample.cs e copie o seguinte código C# para esse arquivo.

Essa classe principal importará o SDK, o que significa que o arquivo C# baixado na primeira etapa precisa ser detectável nessa classe.

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

Compilar e criar um arquivo DLL

Empacote suas classes e dependências em um arquivo DLL. Você pode criar um arquivo de .csproj chamado RegexSample.csproj e copiar o seguinte código para esse arquivo.

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

Vá para a pasta do projeto e execute dotnet build para gerar o seguinte arquivo:

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

Para obter mais informações, consulte Criar uma DLL .NET de um projeto C#.

Criar linguagem externa

Você precisa criar uma linguagem externa no banco de dados. A linguagem externa é um objeto com escopo de banco de dados, o que significa que linguagens externas como C# precisam ser criadas para cada banco de dados no qual você deseja usá-las.

1. Crie um arquivo .zip que contém a extensão.

   Como parte da configuração do SQL Server no Windows, o arquivo .zip da extensão .NET é instalado neste local: <SQL Server install path>\MSSQL\Binn>\dotnet-core-CSharp-lang-extension.zip. Este arquivo zip contém o nativecsharpextension.dll.

2. Crie uma linguagem dotnet externa usando o arquivo .zip:

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

Definir permissões

Para executar o código .NET C#, o usuário SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) precisa receber permissões de leitura na pasta \MSSQL.

1. Clique com o botão direito do mouse na nova pasta e escolha Propriedades > Segurança
2. Selecione Editar
3. Selecione Adicionar
4. Em Selecionar Usuários, Computador, Contas de Serviço ou Grupos:
   1. Selecione Tipos de Objeto e verifique se as opções Princípios de segurança interna e Grupos estão selecionadas
   2. Selecione Locais para selecionar o nome do computador local na parte superior da lista
   3. Insira ALL APPLICATION PACKAGES, verifique o nome e selecione OK para adicionar. Se o nome não resolver, acesse novamente a etapa Locais. O identificador do sistema (SID) é local para sua máquina.

Para obter mais informações, confira CREATE EXTERNAL LANGUAGE.

Criar bibliotecas externas

Use CREATE EXTERNAL LIBRARY para criar uma biblioteca externa para seus arquivos DLL. O SQL Server terá acesso aos arquivos .dll e você não precisará definir nenhuma permissão especial para o classpath.

Crie uma biblioteca externa para o código RegEx.

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

Chame a classe C#

Chame o procedimento armazenado sp_execute_external_script para invocar o código C# do SQL Server. No parâmetro script, defina qual libraryname;namespace.classname você deseja chamar. Você também pode definir qual namespace.classname deseja chamar sem especificar o nome da biblioteca. A extensão encontrará a primeira biblioteca que tem o namespace.classname correspondente. No código a seguir, a classe pertence a um namespace chamado UserExecutor e uma classe chamada CSharpRegexExecutor.

O código não define qual método chamar. Por padrão, o método Execute será chamado. Isso significa que você precisará seguir a interface do SDK e implementar um método Execute em sua classe C# se desejar poder chamar a classe do SQL Server.

O procedimento armazenado usa uma consulta de entrada (conjunto de dados de entrada) e uma expressão regular e retorna as linhas que cumpriram a expressão regular fornecida. Ele usa uma expressão regular [Cc]# que verifica se um texto contém a palavra C# ou 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;

Resultados

Após executar a chamada, você deve obter um conjunto de resultados com duas das linhas.

Conteúdo relacionado

• Como chamar o runtime do .NET nas Extensões de Linguagem do SQL Server
• O que são Extensões de Linguagem do SQL Server?
• Tipos de dados com suporte no C# .NET e no SQL Server