Tutorial: Búsqueda de una cadena mediante expresiones regulares (regex) en C#

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores

En este tutorial se muestra cómo usar las extensiones de lenguaje de SQL Server para crear una clase C# que reciba dos columnas (identificador y texto) de SQL Server y una expresión regular (regex) como parámetro de entrada. La clase devuelve dos columnas a SQL Server (identificador y texto).

Para un texto determinado en la columna de texto que se envía a la clase C#, el código comprueba si se cumple la expresión regular especificada y devuelve el texto junto con el identificador original.

Este código de ejemplo utiliza una expresión regular que comprueba si un texto contiene la palabra C# o c#.

Requisitos previos

• Instancia de Motor de base de datos de SQL Server 2019 (15.x) y versiones posteriores, con el marco de extensibilidad y la extensión de programación de .NET en Windows. Para obtener más información, consulte ¿Qué son las extensiones de lenguaje de SQL Server? Para obtener más información sobre los requisitos de codificación, consulte Cómo llamar al entorno de ejecución de .NET en las extensiones de lenguaje de SQL Server.

• SQL Server Management Studio o Azure Data Studio para ejecutar T-SQL.

• SDK de .NET 6 o posterior en Windows.

• El archivo dotnet-core-CSharp-lang-extension-windows-release.zip del SDK de extensibilidad de Microsoft para C# para SQL Server.

La compilación de línea de comandos con dotnet build es suficiente para este tutorial.

Creación de datos de ejemplo

En primer lugar, cree una base de datos y rellene una tabla testdatacon columnas de ID y 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

Creación de la clase principal

En este paso, cree un archivo de clase denominado RegexSample.cs y copie el siguiente código C# en ese archivo.

Esta clase principal importa el SDK, lo que significa que el archivo C# descargado en el primer paso debe ser reconocible desde esta clase.

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

Compilación y creación de un archivo DLL

Empaquete sus clases y dependencias en un archivo DLL. Puede crear un archivo .csproj denominado RegexSample.csproj y copiar el siguiente código en ese archivo.

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

Vaya a la carpeta del proyecto y ejecute dotnet build, que genera el siguiente archivo:

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

Para obtener más información, consulte Creación de un archivo DLL de .NET a partir de proyecto de C#.

Creación de un lenguaje externo

Es necesario crear un lenguaje externo en la base de datos. El lenguaje externo es un objeto con ámbito de base de datos, lo que significa que es necesario crear lenguajes externos, como C#, para cada base de datos en la que quiera usarlo.

1. Cree un archivo .zip que contenga la extensión.

   Como parte de la configuración de SQL Server en Windows, el archivo .zip de la extensión de .NET se instala en <SQL Server install path>\MSSQL\Binn>\dotnet-core-CSharp-lang-extension.zip. Este archivo ZIP contiene el nativecsharpextension.dll.

2. Cree un lenguaje dotnet externo a partir del archivo .zip:

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

Establecimiento de permisos

Para ejecutar código de C# de .NET, el usuario SID S-1-15-2-1 (<LocalMachineName>\ALL APPLICATION PACKAGES) debe conceder permisos de lectura a la carpeta \MSSQL.

1. Haga clic con el botón derecho en la carpeta y elija Propiedades>Seguridad.
2. Seleccione Editar.
3. Seleccione Agregar.
4. En Seleccionar usuarios, equipo, cuentas de servicio o grupos:
   1. Seleccione Tipos de objeto y asegúrese de que las opciones Principios de seguridad integrados y Grupos estén seleccionadas.
   2. Seleccione Ubicaciones para seleccionar el nombre del equipo local en la parte superior de la lista.
   3. Escriba ALL APPLICATION PACKAGES, compruebe el nombre y seleccione Aceptar para agregar. Si el nombre no se resuelve, vuelva al paso de las ubicaciones. El identificador del sistema (SID) es local en la máquina.

Para obtener más información, vea CREATE EXTERNAL LANGUAGE.

Creación de bibliotecas externas

Utilice CREATE EXTERNAL LIBRARY para crear una biblioteca externa para sus archivos DLL. SQL Server tiene acceso a los archivos .dll, sin que sea necesario establecer ningún permiso especial en la propiedad classpath.

Cree una biblioteca externa para el código RegEx.

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

Llame a la clase C#.

Llame al procedimiento almacenado sp_execute_external_script para invocar el código C# desde SQL Server. En el parámetro script, defina a que libraryname;namespace.classname quiere llamar. También puede definir a que namespace.classname desea llamar sin especificar el nombre de la biblioteca. La extensión encontrará la primera biblioteca que tiene la coincidencia de namespace.classname. En el código siguiente, la clase pertenece a un espacio de nombres denominado UserExecutor y a un archivo de clase denominado CSharpRegexExecutor.

El código no define el método al que se va a llamar. De forma predeterminada, se llamará al método Execute. Esto significa que, si quiere poder llamar a la clase desde SQL Server, ha de seguir la interfaz del SDK e implementar un método de Execute en la clase C#.

El procedimiento almacenado toma una consulta de entrada (conjunto de datos de entrada) y una expresión regular, y devuelve las filas que cumplen la expresión regular especificada. Utiliza una expresión regular [Cc]# que comprueba si un texto contiene la palabra C# o 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;

Results

Después de ejecutar la llamada, debería obtener un conjunto de resultados con dos de las filas.

Contenido relacionado

• Procedimiento para llamar al entorno de ejecución de .NET en las extensiones de lenguaje de SQL Server
• ¿Qué son las extensiones de lenguaje de SQL Server?
• Tipos de datos compatibles con C# .NET y SQL Server