Home > Articoli > CLR User-Defined Function for Check Constraint

CLR User-Defined Function for Check Constraint

Come possiamo scrivere l’espressione logica che verifica un vincolo di tipo CHECK ? Possiamo farlo con:

  • T-SQL
  • User-Defined Function
  • CLR User-Defined Function

Abbiamo apprezzato l’utilizzo del linguaggio CLR nel post SQLCLR instead of OLE Automation (sp_OA* method), ora vediamo come è possibile implementare e fare il deploy di una funzione CLR da utilizzare per verificare l’espressione logica di un vincolo CHECK, definito ad esempio su una colonna di tipo float.

Il linguaggio che utilizzeremo per implementare la nostra CLR User defined function sarà Microsoft Visual C#.

Procediamo quindi con l’implementazione di una funzione CLR in grado di verificare se il valore di tipo float passato come parametro è NaN (Not a Number), NegativeInfinity o PositiveInfinity. In uno di questi casi, il valore restituito dalla funzione dovrà essere –1 (True), negli altri casi il valore restituito dovrà essere 0 (False). La funzione CLR verrà utilizzata per verificare l’espressione logica di un vincolo CHECK implementato per rafforzare l’integrità di dominio di una colonna di tipo float.

Le figure seguenti illustrano come creare il progetto SQLCLRUtils e la funzione CLR udf_float_isnan().

Nell’IDE di Visual Studio selezioniamo File –> New –> Project… per visualizzare la finestra New Project illustrata in figura 1. Nel pannello Project type selezioniamo il tipo DataBase nel ramo Visual C#, nel pannello Templates selezioniamo invece SQL Server Project.

Figura 1- Creazione nuovo progetto

Dopo aver creato il progetto e averlo visualizzato nel Solution Explorer procediamo con un click destro del mouse sul progetto stesso, apparirà un popup menù in cui selezioneremo Add –> User-Defined Function, verrà visualizzata la finestra illustrata in figura 2.

Figura 2 – Add New Item

Premendo il tasto Add, la funzione udf_float_isnan() verrà aggiunta al progetto come illustrato in figura 3.

Figura 3 – Project SQLCLRUtils

Implementiamo la funzione udf_float_isnan() con il seguente codice C#:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class udf_math
{
    [Microsoft.SqlServer.Server.SqlFunction]

    /*
     * Descrizione: Metodi della classe udf_math
     *
    */

    // udf_float_isnan()
    public static SqlInt16 udf_float_isnan(SqlDouble Value)
    {
        /*
         * Descrizione: CLR Scalar-Valued Functions
         *              Restituisce –1 (True) se il valore passato è NaN, IsNegativeInfinity o IsPositiveInfinity.
         *              Restituisce 0 (False) negli altri casi
         * Data: 09/10/2009
        */
        try
        {
            if (Double.IsNaN((Double)Value) ||
                Double.IsNegativeInfinity((Double)Value) ||
                Double.IsPositiveInfinity((Double)Value))
            {
                return -1;
            }
            else
            {
                return 0;
            }
        }
        catch
        {
            return -1;
        }
    }
};

Per eseguire il deploy dell’assembly SQLCLRUtils e della funzione udf_float_isnan() utilizziamo i seguenti comandi T-SQL:

use [TestDB];
go

— drop dell’assembly (se esiste)
if exists (select name from sys.assemblies where name = N’SQLCLRUtils’)
begin
  if exists (select assembly_method from sys.assembly_modules WHERE assembly_method = N’udf_float_isnan’)
    drop function dbo.udf_float_isnan
   
  drop assembly SQLCLRUtils
end;
go

— creazione assembly
create assembly SQLCLRUtils
  from ‘C:\CLR\SQLCLRUtils.dll’
  with permission_set = safe — external_access;
go

— creazione funzione .NET
create function udf_float_isnan(@value float) returns smallint
as
  — external name [nome_assembly].[nome_classe].[nome_metodo]   external name SQLCLRUtils.udf_math.udf_float_isnan;
go

Il seguente comando T-SQL mostra come è possibile create un vincolo di tipo CHECK per rafforzare l’integrità di dominio della colonna <float_flied> nella tabella <table_name> utilizzando la funzione CLR udf_float_isnan() nell’espressione logica che verifica la condizione:

— creazione CHECK CONSTRAINT
alter table
  dbo.<table_name>
with nocheck add constraint
  chk_floatfield_isNaN check (dbo.udf_float_isnan(<float_field>)=0)

 

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Automazione delle attività di manutenzione in Azure SQL Database (2 Parte)

Introduzione Nell’articolo Automazione delle attività di manutenzione in Azure SQL Database abbiamo descritto le attività …