Condividi tramite

Creare tipi definiti dall'utente con ADO.NET

Si applica a:SQL Server

Quando si codifica la definizione del tipo definito dall'utente (UDT), è necessario implementare varie funzionalità, a seconda che si stia implementando il tipo definito dall'utente come classe o struttura e sulle opzioni di formattazione e serializzazione scelte.

Nell'esempio riportato in questa sezione viene illustrata l'implementazione di un tipo definito dall'utente Point come struct (o Structure in Visual Basic). Il Point tipo definito dall'utente è costituito da coordinate X e Y implementate come routine delle proprietà.

Quando si definisce un tipo definito dall'utente sono richiesti gli spazi dei nomi seguenti:

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

Lo spazio dei nomi Microsoft.SqlServer.Server contiene gli oggetti necessari per vari attributi del tipo definito dall'utente e lo spazio dei nomi System.Data.SqlTypes contiene le classi che rappresentano i tipi di dati nativi di SQL Server disponibili per l'assembly. Potrebbero essere presenti altri spazi dei nomi necessari per il corretto funzionamento dell'assembly. Il tipo definito dall'utente di Point usa anche lo spazio dei nomi System.Text per l'uso delle stringhe.

Nota

Gli oggetti di database di Visual C++, ad esempio i tipi definiti dall'utente, compilati con /clr:pure non sono supportati per l'esecuzione.

Specificare gli attributi

Gli attributi consentono di determinare la modalità di utilizzo della serializzazione per costruire la rappresentazione di archiviazione dei tipi definiti dall'utente e per trasmettere tali tipi al client in base al valore.

Il Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute è obbligatorio. L'attributo Serializable è facoltativo. È anche possibile specificare il Microsoft.SqlServer.Server.SqlFacetAttribute per fornire informazioni sul tipo restituito di un tipo definito dall'utente. Per altre informazioni, vedere integrazione di CLR : attributi personalizzati per routine CLR.

Attributi del tipo definito dall'utente del punto

Il Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute imposta il formato di archiviazione per il tipo definito dall'utente di Point su Native. IsByteOrdered è impostato su true, che garantisce che i risultati dei confronti siano gli stessi in SQL Server come se lo stesso confronto fosse stato eseguito nel codice gestito. Il tipo definito dall'utente implementa l'interfaccia System.Data.SqlTypes.INullable per rendere il tipo definito dall'utente null compatibile.

Il frammento di codice seguente mostra gli attributi per il tipo definito dall'utente Point.

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
  IsByteOrdered=true)]
public struct Point : INullable { ... }

Implementare il supporto dei valori Null

Oltre a specificare gli attributi per gli assembly in modo corretto, il tipo definito dall'utente deve supportare anche i valori Null. I tipi definiti dall'utente caricati in SQL Server sono in grado di riconoscere i valori Null, ma affinché il tipo definito dall'utente riconosca un valore Null, il tipo definito dall'utente deve implementare l'interfaccia System.Data.SqlTypes.INullable.

È necessario creare una proprietà denominata IsNull, necessaria per determinare se un valore è Null dall'interno del codice CLR. Quando SQL Server trova un'istanza Null di un tipo definito dall'utente, il tipo definito dall'utente viene salvato in modo permanente usando i normali metodi di gestione dei valori Null. Il server non perde tempo durante la serializzazione o la deserializzazione del tipo definito dall'utente se non è necessario e non sprecare spazio per archiviare un tipo definito dall'utente null. Questo controllo dei valori Null viene eseguito ogni volta che un tipo definito dall'utente viene spostato da CLR, il che significa che l'uso del costrutto di Transact-SQL IS NULL per verificare la presenza di tipi definiti dall'utente Null deve sempre funzionare. La proprietà IsNull viene utilizzata anche dal server per verificare se un'istanza è Null. Una volta stabilito che il tipo definito dall'utente è Null, il server è in grado di utilizzare la relativa funzionalità di gestione nativa dei valori Null.

Il metodo get() di IsNull non viene fatto in alcun modo con maiuscole e minuscole. Se una variabile Point@p è Null, @p.IsNull restituirà, per impostazione predefinita, NULL, non 1. Questo perché l'attributo SqlMethod(OnNullCall) del metodo IsNull get() viene impostato su false. Poiché l'oggetto è Null, quando viene richiesta la proprietà l'oggetto non viene deserializzato, il metodo non viene chiamato e viene restituito un valore predefinito "NULL".

Esempio

Nell'esempio seguente la variabile is_Null è privata e mantiene lo stato Null per l'istanza del tipo definito dall'utente. Il codice deve gestire un valore appropriato per is_Null. Il tipo definito dall'utente deve inoltre avere una proprietà statica denominata Null che restituisce un'istanza di valore Null del tipo definito dall'utente. In questo modo il tipo definito dall'utente può restituire un valore Null se l'istanza è Null nel database.

private bool is_Null;

public bool IsNull
{
    get
    {
        return (is_Null);
    }
}

public static Point Null
{
    get
    {
        Point pt = new Point();
        pt.is_Null = true;
        return pt;
    }
}

Confronto tra IS NULL e IsNull

Si consideri una tabella contenente lo schema Points(id int, location Point), dove Point è un tipo definito dall'utente CLR e le query seguenti:

  • Query 1:

    SELECT ID FROM Points
WHERE NOT (location IS NULL); -- Or, WHERE location IS NOT NULL;

  • Query 2:

    SELECT ID FROM Points
WHERE location.IsNull = 0;

Entrambe le query restituiscono gli ID di punti con posizioni non Null. Nella Query 1 viene utilizzata la normale gestione dei valori Null e non è necessaria la deserializzazione dei tipi definiti dall'utente. La query 2, invece, deve deserializzare ogni oggetto non Null e chiamare in CLR per ottenere il valore della proprietà IsNull. Chiaramente, l'uso di IS NULL presenta prestazioni migliori e non dovrebbe mai esserci un motivo per leggere la proprietà IsNull di un tipo definito dall'utente dal codice Transact-SQL.

Quindi, qual è l'uso della proprietà IsNull? In primo luogo, è necessario determinare se un valore è Null dall'interno del codice CLR. In secondo luogo, il server deve verificare se un'istanza di è Null, quindi questa proprietà viene utilizzata dal server. Dopo che è stato determinato come Null, può usare la gestione dei valori Null nativa per gestirla.

Implementare il metodo parse

I metodi Parse e ToString consentono le conversioni da e verso le rappresentazioni di stringa del tipo definito dall'utente. Il metodo Parse consente la conversione di una stringa in un tipo definito dall'utente. Deve essere dichiarato come static (o Shared in Visual Basic) e accettare un parametro di tipo System.Data.SqlTypes.SqlString.

Il codice seguente implementa il metodo Parse per il tipo definito dall'utente Point, che separa le coordinate X e Y. Il metodo Parse ha un singolo argomento di tipo System.Data.SqlTypes.SqlStringe presuppone che i valori X e Y vengano forniti come stringa delimitata da virgole. L'impostazione dell'attributo Microsoft.SqlServer.Server.SqlMethodAttribute.OnNullCall su false impedisce che il metodo Parse venga chiamato da un'istanza Null di Point.

[SqlMethod(OnNullCall = false)]
public static Point Parse(SqlString s)
{
    if (s.IsNull)
        return Null;

    // Parse input string to separate out points.
    Point pt = new Point();
    string[] xy = s.Value.Split(",".ToCharArray());
    pt.X = Int32.Parse(xy[0]);
    pt.Y = Int32.Parse(xy[1]);
    return pt;
}

Implementare il metodo ToString

Il metodo ToString converte il tipo definito dall'utente Point in un valore stringa. In questo caso, la stringa "NULL" viene restituita per un'istanza Null del tipo di Point. Il metodo ToString inverte il metodo Parse utilizzando un System.Text.StringBuilder per restituire un System.String delimitato da virgole costituito dai valori delle coordinate X e Y. Poiché per impostazione predefinita InvokeIfReceiverIsNull false, il controllo di un'istanza Null di Point non è necessario.

private Int32 _x;
private Int32 _y;

public override string ToString()
{
    if (this.IsNull)
        return "NULL";
    else
    {
        StringBuilder builder = new StringBuilder();
        builder.Append(_x);
        builder.Append(",");
        builder.Append(_y);
        return builder.ToString();
    }
}

Esporre le proprietà del tipo definito dall'utente

Il tipo definito dall'utente Point espone le coordinate X e Y implementate come proprietà pubbliche di lettura/scrittura di tipo System.Int32.

public Int32 X
{
    get
    {
        return this._x;
    }
    set
    {
        _x = value;
    }
}

public Int32 Y
{
    get
    {
        return this._y;
    }
    set
    {
        _y = value;
    }
}

Convalidare i valori definiti dall'utente

Quando si utilizzano dati definiti dall'utente, SQL Server motore di database converte automaticamente i valori binari in valori definiti dall'utente. Ai fini di tale processo di conversione, viene verificato che i valori siano appropriati al formato di serializzazione del tipo e che il valore possa essere deserializzato correttamente. In questo modo, il valore può essere convertito nuovamente in formato binario. Nel caso dei tipi definiti dall'utente ordinati per byte, questo processo assicura anche che il valore binario risultante corrisponda al valore binario originale. In questo modo si impedisce che valori non validi vengano resi persistenti nel database. In alcuni casi, questo livello di controllo potrebbe risultare inadeguato. È possibile che sia necessaria una convalida aggiuntiva quando i valori definiti dall'utente devono trovarsi in un dominio o un intervallo previsto. Un tipo definito dall'utente che implementa, ad esempio, una data potrebbe richiedere che il valore del giorno sia un numero positivo compreso in un determinato intervallo di valori validi.

La proprietà Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute.ValidationMethodName del Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute consente di specificare il nome di un metodo di convalida eseguito dal server quando i dati vengono assegnati a un tipo definito dall'utente o convertiti in un tipo definito dall'utente. viene chiamato anche durante l'esecuzione dell'utilità bcp , , , , , query distribuita e chiamate di routine remote TDS (Tabular Data Stream). Il valore predefinito per ValidationMethodName è Null, a indicare che non esiste alcun metodo di convalida.

Esempio

Il frammento di codice seguente mostra la dichiarazione per la classe Point, che specifica un ValidationMethodName di ValidatePoint.

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
  IsByteOrdered=true,
  ValidationMethodName = "ValidatePoint")]
public struct Point : INullable { ... }

Se si specifica un metodo di convalida, è necessario che includa una firma simile al frammento di codice seguente:

private bool ValidationFunction()
{
    if (validation logic here)
    {
        return true;
    }
    else
    {
        return false;
    }
}

Il metodo di convalida può avere qualsiasi ambito e deve restituire true se il valore è valido e false in caso contrario. Se il metodo restituisce false o genera un'eccezione, il valore viene considerato non valido e viene generato un errore.

Nell'esempio seguente il codice consente solo valori pari a zero o superiori alle coordinate X e Y.

private bool ValidatePoint()
{
    if ((_x >= 0) && (_y >= 0))
    {
        return true;
    }
    else
    {
        return false;
    }
}

Limitazioni del metodo di convalida

Il server chiama il metodo di convalida quando il server esegue conversioni, non quando i dati vengono inseriti impostando singole proprietà o quando i dati vengono inseriti utilizzando un'istruzione Transact-SQL INSERT.

È necessario chiamare in modo esplicito il metodo di convalida dai setter di proprietà e dal metodo Parse se si desidera che il metodo di convalida venga eseguito in tutte le situazioni. Questo non è un requisito e in alcuni casi potrebbe non essere nemmeno auspicabile.

Esempio di convalida dell'analisi

Per assicurarsi che il metodo ValidatePoint venga richiamato nella classe Point, è necessario chiamarlo dal metodo Parse e dalle routine delle proprietà che impostano i valori delle coordinate X e Y. Il frammento di codice seguente illustra come chiamare il metodo di convalida ValidatePoint dalla funzione Parse.

[SqlMethod(OnNullCall = false)]
public static Point Parse(SqlString s)
{
    if (s.IsNull)
        return Null;

    // Parse input string to separate out points.
    Point pt = new Point();
    string[] xy = s.Value.Split(",".ToCharArray());
    pt.X = Int32.Parse(xy[0]);
    pt.Y = Int32.Parse(xy[1]);

    // Call ValidatePoint to enforce validation
    // for string conversions.
    if (!pt.ValidatePoint())
        throw new ArgumentException("Invalid XY coordinate values.");
    return pt;
}

Esempio di convalida delle proprietà

Il frammento di codice seguente illustra come chiamare il metodo di convalida ValidatePoint dalle routine di proprietà che impostano le coordinate X e Y.

public Int32 X
{
    get
    {
        return this._x;
    }
    // Call ValidatePoint to ensure valid range of Point values.
    set
    {
        Int32 temp = _x;
        _x = value;
        if (!ValidatePoint())
        {
            _x = temp;
            throw new ArgumentException("Invalid X coordinate value.");
        }
    }
}

public Int32 Y
{
    get
    {
        return this._y;
    }
    set
    {
        Int32 temp = _y;
        _y = value;
        if (!ValidatePoint())
        {
            _y = temp;
            throw new ArgumentException("Invalid Y coordinate value.");
        }
    }
}

Metodi definiti dall'utente del codice

Quando si codificano i metodi UDT, è consigliabile valutare la possibilità che l'algoritmo utilizzato possa cambiare nel tempo. In tal caso, è possibile prendere in considerazione la creazione di una classe separata per i metodi usati dal tipo definito dall'utente. Se l'algoritmo viene modificato, è possibile ricompilare la classe con il nuovo codice e caricare l'assembly in SQL Server senza influire sul tipo definito dall'utente. In molti casi è possibile ricaricare i tipi definiti dall'utente usando l'istruzione Transact-SQL ALTER ASSEMBLY, ma ciò potrebbe causare problemi con i dati esistenti. Ad esempio, il tipo definito dall'utente Currency incluso nel database di esempio AdventureWorks2022 usa una funzione ConvertCurrency per convertire i valori di valuta, implementato in una classe separata. È possibile che gli algoritmi di conversione cambino in modi imprevedibili in futuro o che sia necessaria una nuova funzionalità. La separazione della funzione ConvertCurrency dall'implementazione del tipo definito dall'utente di Currency offre maggiore flessibilità durante la pianificazione delle modifiche future.

Esempio

La classe Point contiene tre semplici metodi per calcolare la distanza: Distance, DistanceFrome DistanceFromXY. Ogni restituisce un double calcolando la distanza tra Point e zero, la distanza da un punto specificato a Pointe la distanza dalle coordinate X e Y specificate a Point. Distance e DistanceFrom ogni chiamata DistanceFromXYe illustrano come usare argomenti diversi per ogni metodo.

// Distance from 0 to Point.
[SqlMethod(OnNullCall = false)]
public Double Distance()
{
    return DistanceFromXY(0, 0);
}

// Distance from Point to the specified point.
[SqlMethod(OnNullCall = false)]
public Double DistanceFrom(Point pFrom)
{
    return DistanceFromXY(pFrom.X, pFrom.Y);
}

// Distance from Point to the specified x and y values.
[SqlMethod(OnNullCall = false)]
public Double DistanceFromXY(Int32 iX, Int32 iY)
{
    return Math.Sqrt(Math.Pow(iX - _x, 2.0) + Math.Pow(iY - _y, 2.0));
}

Usare gli attributi SqlMethod

La classe Microsoft.SqlServer.Server.SqlMethodAttribute fornisce attributi personalizzati che possono essere usati per contrassegnare le definizioni dei metodi per specificare determinismo, sul comportamento delle chiamate Null e per specificare se un metodo è un mutatore. Per queste proprietà si presuppone l'uso dei valori predefiniti e l'attributo personalizzato viene utilizzato solo quando è necessario un valore non predefinito.

Nota

La classe SqlMethodAttribute eredita dalla classe SqlFunctionAttribute, pertanto SqlMethodAttribute eredita i campi FillRowMethodName e TableDefinition da SqlFunctionAttribute. Ciò implica che è possibile scrivere un metodo con valori di tabella, che non è il caso. Il metodo viene compilato e l'assembly viene distribuito, ma viene generato un errore relativo al tipo restituito IEnumerable in fase di esecuzione con il messaggio seguente: "Metodo, proprietà o campo <name> nella classe <class> nell'assembly <assembly> ha un tipo restituito non valido".

Nella tabella seguente vengono descritte alcune delle proprietà Microsoft.SqlServer.Server.SqlMethodAttribute rilevanti che possono essere usate nei metodi definiti dall'utente e vengono elencati i valori predefiniti.

Proprietà Descrizione
DataAccess Indica se la funzione implica l'accesso ai dati utente archiviati nell'istanza locale di SQL Server. Il valore predefinito è DataAccessKind.None.
IsDeterministic Indica se la funzione produce gli stessi valori di output quando vengono specificati gli stessi valori di input e lo stesso stato del database. Il valore predefinito è false.
IsMutator Indica se il metodo causa una modifica dello stato dell'istanza UDT. Il valore predefinito è false.
IsPrecise Indica se la funzione comporta calcoli imprecisi, quali operazioni a virgola mobile. Il valore predefinito è false.
OnNullCall Indica se il metodo viene chiamato quando vengono specificati argomenti di input con riferimento Null. Il valore predefinito è true.

Esempio

La proprietà Microsoft.SqlServer.Server.SqlMethodAttribute.IsMutator consente di contrassegnare un metodo che consente una modifica dello stato di un'istanza di un tipo definito dall'utente. Transact-SQL non consente di impostare due proprietà definite dall'utente nella clausola SET di un'istruzione UPDATE. È tuttavia possibile contrassegnare un metodo come mutatore che modifica i due membri.

Nota

I metodi mutator non sono consentiti nelle query. Tali metodi possono essere chiamati solo nelle istruzioni di assegnazione o nelle istruzioni di modifica dei dati. Se un metodo contrassegnato come mutatore non restituisce void (o non è un Sub in Visual Basic), CREATE TYPE ha esito negativo con un errore.

L'istruzione seguente presuppone l'esistenza di un Triangles tipo definito dall'utente con un metodo Rotate. L'istruzione update seguente Transact-SQL richiama il metodo Rotate:

UPDATE Triangles
SET t.RotateY(0.6)
WHERE id = 5;

Il metodo Rotate viene decorato con l'impostazione dell'attributo SqlMethodIsMutator su true in modo che SQL Server possa contrassegnare il metodo come metodo mutatore. Il codice imposta anche OnNullCall su false, che indica al server che il metodo restituisce un riferimento Null (Nothing in Visual Basic) se uno dei parametri di input è un riferimento Null.

[SqlMethod(IsMutator = true, OnNullCall = false)]
public void Rotate(double anglex, double angley, double anglez)
{
   RotateX(anglex);
   RotateY(angley);
   RotateZ(anglez);
}

Implementare un tipo definito dall'utente con un formato definito dall'utente

Quando si implementa un tipo definito dall'utente con un formato definito dall'utente, è necessario implementare Read e Write metodi che implementano l'interfaccia Microsoft.SqlServer.Server.IBinarySerialize per gestire la serializzazione e la deserializzazione dei dati definiti dall'utente. È inoltre necessario specificare la proprietà MaxByteSize del Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute.

Il tipo definito dall'utente di valuta

Il Currency tipo definito dall'utente è incluso negli esempi CLR che possono essere installati con SQL Server.

Il Currency tipo definito dall'utente supporta la gestione di quantità di denaro nel sistema monetario di una determinata cultura. È necessario definire due campi: un string per CultureInfo, che specifica chi ha emesso la valuta (en-us, ad esempio) e un decimal per CurrencyValue, l'importo di denaro.

Anche se non viene usato dal server per eseguire confronti, il tipo definito dall'utente di Currency implementa l'interfaccia System.IComparable, che espone un singolo metodo, System.IComparable.CompareTo. Viene usato sul lato client in situazioni in cui è consigliabile confrontare o ordinare con precisione i valori di valuta all'interno delle impostazioni cultura.

Il codice eseguito in CLR confronta le impostazioni cultura separatamente dal valore della valuta. Per il codice Transact-SQL, le azioni seguenti determinano il confronto:

  1. Impostare l'attributo IsByteOrdered su true, che indica a SQL Server di usare la rappresentazione binaria persistente su disco per i confronti.

  2. Usare il metodo Write per il tipo definito dall'utente di Currency per determinare in che modo il tipo definito dall'utente viene salvato in modo permanente su disco e quindi il modo in cui i valori definiti dall'utente vengono confrontati e ordinati per le operazioni di Transact-SQL.

  3. Salvare il Currency tipo definito dall'utente usando il formato binario seguente:

    1. Salvare le impostazioni cultura come stringa codificata UTF-16 per i byte 0-19 con riempimento a destra con caratteri Null.

    2. Utilizzare i byte 20 e successivi per contenere il valore decimale della valuta.

Lo scopo della spaziatura interna è garantire che le impostazioni cultura siano completamente separate dal valore di valuta, in modo che quando un tipo definito dall'utente viene confrontato con un altro nel codice Transact-SQL, i byte delle impostazioni cultura vengono confrontati con i byte delle impostazioni cultura e i valori dei byte di valuta vengono confrontati con i valori di byte di valuta.

Attributi di valuta

Il Currency tipo definito dall'utente viene definito con gli attributi seguenti.

[Serializable]
[SqlUserDefinedType(Format.UserDefined,
    IsByteOrdered = true, MaxByteSize = 32)]
    [CLSCompliant(false)]
public struct Currency : INullable, IComparable, IBinarySerialize
{ ... }

Creare metodi di lettura e scrittura con ibinaryserialize

Quando si sceglie UserDefined formato di serializzazione, è necessario implementare anche l'interfaccia IBinarySerialize e creare metodi Read e Write personalizzati. Le procedure seguenti del tipo definito dall'utente di Currency usano il System.IO.BinaryReader e System.IO.BinaryWriter per leggere e scrivere nel tipo definito dall'utente.

// IBinarySerialize methods
// The binary layout is as follow:
//    Bytes 0 - 19:Culture name, padded to the right
//    with null characters, UTF-16 encoded
//    Bytes 20+:Decimal value of money
// If the culture name is empty, the currency is null.
public void Write(System.IO.BinaryWriter w)
{
    if (this.IsNull)
    {
        w.Write(nullMarker);
        w.Write((decimal)0);
        return;
    }

    if (cultureName.Length > cultureNameMaxSize)
    {
        throw new ApplicationException(string.Format(
            CultureInfo.InvariantCulture,
            "{0} is an invalid culture name for currency as it is too long.",
            cultureNameMaxSize));
    }

    String paddedName = cultureName.PadRight(cultureNameMaxSize, '\0');
    for (int i = 0; i < cultureNameMaxSize; i++)
    {
        w.Write(paddedName[i]);
    }

    // Normalize decimal value to two places
    currencyValue = Decimal.Floor(currencyValue * 100) / 100;
    w.Write(currencyValue);
}
public void Read(System.IO.BinaryReader r)
{
    char[] name = r.ReadChars(cultureNameMaxSize);
    int stringEnd = Array.IndexOf(name, '\0');

    if (stringEnd == 0)
    {
        cultureName = null;
        return;
    }

    cultureName = new String(name, 0, stringEnd);
    currencyValue = r.ReadDecimal();
}

Contenuto correlato