UGISS User Group Italiano SQL Server
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:
alter table
dbo.<table_name>
with nocheck add constraint
chk_floatfield_isNaN check (dbo.udf_float_isnan(<float_field>)=0)
