Codage de types définis par l'utilisateur

L’attribut Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute définit le format de stockage pour le type défini par l'utilisateur Point à Native. IsByteOrdered a la valeur true, ce qui garantit que les résultats des comparaisons dans SQL Server sont les mêmes que si les comparaisons avaient eu lieu dans le code managé. Le type défini par l'utilisateur implémente l'interface System.Data.SqlTypes.INullable pour rendre le type défini par l'utilisateur compatible avec la valeur NULL.

Le fragment de code suivant montre les attributs pour le type défini par l'utilisateur Point.

<Serializable(), SqlUserDefinedTypeAttribute(Format.Native, _
  IsByteOrdered:=True)> _
  Public Structure Point
    Implements INullable

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

Dans l'exemple suivant, la variable is_Null est privée et contient l'état de Null pour l'instance du type défini par l'utilisateur. Votre code doit maintenir une valeur appropriée pour is_Null. Le type défini par l'utilisateur doit également avoir une propriété statique nommée Null qui retourne une instance de valeur Null du type défini par l'utilisateur. Cela permet au type défini par l'utilisateur de renvoyer une valeur Null si l'instance est en effet Null dans la base de données.

Private is_Null As Boolean

Public ReadOnly Property IsNull() As Boolean _
   Implements INullable.IsNull
    Get
        Return (is_Null)
    End Get
End Property

Public Shared ReadOnly Property Null() As Point
    Get
        Dim pt As New Point
        pt.is_Null = True
        Return (pt)
    End Get
End Property

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

Considérez une table qui contient le schéma Points(id int, location Point), où Point est un type défini par l'utilisateur CLR, et les requêtes suivantes :

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

Les deux requêtes retournent les ID de points avec des emplacements non-Null. Dans la Requête 1, la gestion de Null normale est utilisée et là aucune désérialisation du type défini par l'utilisateur n'est requise. La Requête 2, en revanche, doit désérialiser chaque objet non-Null et appeller le CLR pour obtenir la valeur de la propriété IsNull. Il est clair que l'utilisation de IS NULL donnera de meilleures performances et il ne devrait jamais y avoir de raison de lire la propriété IsNull d'un type défini par l'utilisateur à partir de code Transact-SQL.

Par conséquent, quelle est l'utilité de la propriété IsNull ? En premier lieu, elle est nécessaire pour déterminer si une valeur est Null à partir du code CLR. Ensuite, le serveur a besoin d'une méthode pour tester si une instance est Null ; il utilise par conséquent cette propriété. Après avoir déterminé que l'instance est Null, il peut utiliser sa gestion Null native pour la gérer.

Le fragment de code suivant illustre la déclaration de la classe Point, qui spécifie un ValidationMethodName de ValidatePoint.

<Serializable(), SqlUserDefinedTypeAttribute(Format.Native, _
  IsByteOrdered:=True, _
  ValidationMethodName:="ValidatePoint")> _
  Public Structure Point

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

Si une méthode de validation est spécifiée, elle doit avoir une signature qui ressemble au fragment de code suivant.

Private Function ValidationFunction() As Boolean
    If (validation logic here) Then
        Return True
    Else
        Return False
    End If
End Function
private bool ValidationFunction()
{
    if (validation logic here)
    {
        return true;
    }
    else
    {
        return false;
    }
}

La méthode de validation peut avoir une portée quelconque et doit retourner true si la valeur est valide et false dans le cas contraire. Si la méthode retourne false ou lève une exception, la valeur est traitée comme non valide et une erreur est déclenchée.

Dans l'exemple ci-dessous, le code autorise uniquement des valeurs de zéro ou plus pour les coordonnées X et Y.

Private Function ValidatePoint() As Boolean
    If (_x >= 0) And (_y >= 0) Then
        Return True
    Else
        Return False
    End If
End Function

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

Le serveur appelle la méthode de validation lorsqu'il effectue des conversions, et non lorsque des données sont insérées en définissant des propriétés individuelles ou à l'aide d'une instruction INSERT Transact-SQL.

Vous devez appeler explicitement la méthode de validation à partir d'accesseurs Set de propriété et la méthode Parse si vous souhaitez que la méthode de validation s'exécute dans toutes les situations. Cela n'est pas obligatoire, et dans certains cas peut ne pas être souhaitable.

Pour garantir que la méthode ValidatePoint est appelée dans la classe Point, vous devez l'appeler à partir de la méthode Parse et à partir des procédures de propriété qui ont défini les valeurs de coordonnées X et Y. Le fragment de code suivant montre comment appeler la méthode de validation ValidatePoint à partir de la fonction Parse.

<SqlMethod(OnNullCall:=False)> _
Public Shared Function Parse(ByVal s As SqlString) As Point
    If s.IsNull Then
        Return Null
    End If

    ' Parse input string here to separate out points.
    Dim pt As New Point()
    Dim xy() As String = 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 Not pt.ValidatePoint() Then
        Throw New ArgumentException("Invalid XY coordinate values.")
    End If
    Return pt
End Function

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

Le fragment de code suivant montre comment appeler la méthode de validation ValidatePoint à partir des procédures de propriété définissent les coordonnées X et Y.

Public Property X() As Int32
    Get
        Return (Me._x)
    End Get

    Set(ByVal Value As Int32)
        Dim temp As Int32 = _x
        _x = Value
        If Not ValidatePoint() Then
            _x = temp
            Throw New ArgumentException("Invalid X coordinate value.")
        End If
    End Set
End Property

Public Property Y() As Int32
    Get
        Return (Me._y)
    End Get

    Set(ByVal Value As Int32)
        Dim temp As Int32 = _y
        _y = Value
        If Not ValidatePoint() Then
            _y = temp
            Throw New ArgumentException("Invalid Y coordinate value.")
        End If
    End Set
End Property

    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.");
        }
    }
}

La classe Point contient trois méthodes simples pour calculer la distance : Distance, DistanceFrom et DistanceFromXY. Chacune retourne un double qui calcule la distance de Point à zéro, la distance d'un point spécifié à Point et la distance des coordonnées X et Y spécifiées à Point. Distance et DistanceFrom appellent chacune DistanceFromXY et illustrent comment utiliser différents arguments pour chaque méthode.

' Distance from 0 to Point.
<SqlMethod(OnNullCall:=False)> _
Public Function Distance() As Double
    Return DistanceFromXY(0, 0)
End Function

' Distance from Point to the specified point.
<SqlMethod(OnNullCall:=False)> _
Public Function DistanceFrom(ByVal pFrom As Point) As Double
    Return DistanceFromXY(pFrom.X, pFrom.Y)
End Function

' Distance from Point to the specified x and y values.
<SqlMethod(OnNullCall:=False)> _
Public Function DistanceFromXY(ByVal ix As Int32, ByVal iy As Int32) _
    As Double
    Return Math.Sqrt(Math.Pow(ix - _x, 2.0) + Math.Pow(iy - _y, 2.0))
End Function

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

La classe Microsoft.SqlServer.Server.SqlMethodAttribute fournit des attributs personnalisés pouvant être utilisés pour marquer des définitions de méthode afin de spécifier le déterminisme, pour le comportement d'appel Null, et de spécifier si une méthode est un mutateur. Les valeurs par défaut de ces propriétés sont assumées et l'attribut personnalisé est utilisé uniquement lorsqu'une valeur non définie par défaut est exigée.

Le tableau suivant décrit quelques-unes des propriétés Microsoft.SqlServer.Server.SqlMethodAttribute pertinentes qui peuvent être utilisées dans les méthodes UDT et répertorie leurs valeurs par défaut.

• DataAccess
  Indique si la fonction implique l'accès aux données utilisateur stockées dans l'instance locale de SQL Server. La valeur par défaut est DataAccessKind.None.

• IsDeterministic
  Indique si la fonction produit les mêmes valeurs de sortie étant donné les mêmes valeurs d'entrée et le même état de la base de données. La valeur par défaut est false.

• IsMutator
  Indique si la méthode provoque une modification d'état dans l'instance de type défini par l'utilisateur. La valeur par défaut est false.

• IsPrecise
  Indique si la fonction implique des calculs imprécis, tels que des opérations à virgule flottante. La valeur par défaut est false.

• OnNullCall
  Indique si la méthode est appelée lorsque des arguments d'entrée de référence nulle sont spécifiés. La valeur par défaut est true.

La propriété Microsoft.SqlServer.Server.SqlMethodAttribute.IsMutator vous permet de marquer une méthode qui autorise une modification de l'état d'une instance d'un type défini par l'utilisateur. Transact-SQL ne vous permet pas de définir deux propriétés de type défini par l'utilisateur dans la clause SET d'une instruction UPDATE. Toutefois, vous pouvez avoir une méthode marquée comme mutateur qui modifie les deux membres.

L'instruction suivante suppose l'existence d'un type défini par l'utilisateur Triangles qui a une méthode Rotate. L'instruction UPDATE Transact-SQL suivante appelle la méthode Rotate :

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

La méthode Rotate est décorée avec le paramètre d'attribut SqlMethodIsMutator à true afin que SQL Server puisse marquer la méthode comme méthode mutateur. Le code affecte également la valeur false à OnNullCall, ce qui indique au serveur que la méthode retourne une référence Null (Nothing dans Visual Basic) si l'un des paramètres d'entrée est une référence Null.

<SqlMethod(IsMutator:=True, OnNullCall:=False)> _
Public Sub Rotate(ByVal anglex as Double, _
  ByVal angley as Double, ByVal anglez As Double) 
   RotateX(anglex)
   RotateY(angley)
   RotateZ(anglez)
End Sub

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

Le type défini par l'utilisateur Currency est fourni avec les exemples de CLR qui peuvent être installés avec SQL Server, à compter de SQL Server 2005. Pour plus d'informations sur l'installation des exemples CLR, consultez Considérations relatives à l'installation d'exemples de bases de données et d'exemples de code SQL Server.

Le type défini par l'utilisateur Currency prend en charge la gestion des sommes d'argent dans le système monétaire d'une culture particulière. Vous devez définir deux champs : un string pour CultureInfo, qui spécifie qui a publié la monnaie (en-us, par exemple) et un decimal pour CurrencyValue, la somme d'argent.

Bien qu'il ne soit pas utilisé par le serveur pour effectuer des comparaisons, le type défini par l'utilisateur Currency implémente l'interface System.IComparable, qui expose une méthode unique, System.IComparable.CompareTo. Celle-ci est utilisée du côté client dans les situations où il est souhaitable de comparer ou d'ordonner précisément des valeurs monétaire dans des cultures.

Le code qui s'exécute dans le CLR compare la culture séparément de la valeur monétaire. Pour le code Transact-SQL, les actions suivantes déterminent la comparaison :

1. Affectez la valeur « true » à l'attribut IsByteOrdered, ce qui indique à SQL Server qu'il faut utiliser la représentation binaire persistante sur disque à des fins de comparaison.

2. Utilisez la méthode Write pour le type défini par l'utilisateur Currency afin de déterminer la façon dont il est rendu persistant sur disque et par conséquent la façon dont les valeurs UDT sont comparées et ordonnées pour les opérations Transact-SQL.

3. Enregistrez le type défini par l'utilisateur Currency à l'aide du format binaire suivant :

   1. Enregistrez la culture en tant que chaîne encodée UTF-16 pour les octets 0-19, avec un remplissage à droite avec des caractères Null.

   2. Utilisez les octets 20 et plus pour contenir la valeur décimale de la monnaie.

L'objectif du remplissage est de s'assurer que la culture est complètement séparée de la valeur monétaire, de sorte que lorsque deux types définis par l'utilisateur sontt comparés dans le code Transact-SQL, les octets de culture soient comparés à des octets de culture et les valeurs d'octets de monnaie soient comparées à des valeurs d'octets de monnaie.

Pour obtenir le code compler pour le type défini par l'utilisateur Currency, suivez les instructions d'installation des exemples CLR dans Considérations relatives à l'installation d'exemples de bases de données et d'exemples de code SQL Server.

Le type défini par l'utilisateur Currency est défini avec les attributs suivants :

<Serializable(), Microsoft.SqlServer.Server.SqlUserDefinedType( _
    Microsoft.SqlServer.Server.Format.UserDefined, _
    IsByteOrdered:=True, MaxByteSize:=32), _
    CLSCompliant(False)> _
Public Structure Currency
Implements INullable, IComparable, _
Microsoft.SqlServer.Server.IBinarySerialize

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