Compartir a través de

Creación de tipos definidos por el usuario con ADO.NET

Se aplica a:SQL Server

Al codificar la definición de tipo definido por el usuario (UDT), debe implementar varias características, en función de si va a implementar el UDT como una clase o una estructura, y en las opciones de formato y serialización que elija.

En el ejemplo de esta sección se muestra cómo implementar un UDT de Point como struct (o Structure en Visual Basic). El Point UDT consta de coordenadas X e Y implementadas como procedimientos de propiedad.

Para definir un UDT, se requieren los espacios de nombres siguientes:

  • de C#
  • de Visual Basic para .NET 
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

El espacio de nombres Microsoft.SqlServer.Server contiene los objetos necesarios para varios atributos del UDT y el espacio de nombres System.Data.SqlTypes contiene las clases que representan los tipos de datos nativos de SQL Server disponibles para el ensamblado. Es posible que haya otros espacios de nombres que el ensamblado requiera para funcionar correctamente. El Point UDT también usa el espacio de nombres System.Text para trabajar con cadenas.

Nota:

Los objetos de base de datos de Visual C++, como udT, compilados con /clr:pure no se admiten para su ejecución.

Especificar atributos

Los atributos determinan el modo de usar la serialización para construir la representación de almacenamiento de los UDT y para transmitirlos por valor al cliente.

Se requiere el Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute. El atributo Serializable es opcional. También puede especificar el Microsoft.SqlServer.Server.SqlFacetAttribute para proporcionar información sobre el tipo de valor devuelto de un UDT. Para obtener más información, consulte integración de CLR: atributos personalizados para rutinas de CLR.

Atributos UDT de punto

El Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute establece el formato de almacenamiento del UDT de Point en Native. IsByteOrdered se establece en true, lo que garantiza que los resultados de las comparaciones sean los mismos en SQL Server que si la misma comparación tuvo lugar en código administrado. El UDT implementa la interfaz System.Data.SqlTypes.INullable para que el UDT sea compatible con valores NULL.

En el fragmento de código siguiente se muestran los atributos de la Point UDT.

  • de C#
  • de Visual Basic para .NET 
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
  IsByteOrdered=true)]
public struct Point : INullable { ... }

Implementación de la nulabilidad

Además de especificar correctamente los atributos de los ensamblados, el UDT también debe admitir la nulabilidad. Los UDT cargados en SQL Server son compatibles con valores NULL, pero para que el UDT reconozca un valor NULL, el UDT debe implementar la interfaz System.Data.SqlTypes.INullable.

Debe crear una propiedad denominada IsNull, que es necesaria para determinar si un valor es NULL desde el código CLR. Cuando SQL Server encuentra una instancia nula de un UDT, el UDT se conserva mediante métodos normales de control de valores NULL. El servidor no desperdicia tiempo en serializar ni deserializar el UDT si no tiene que hacerlo y no desperdicia espacio para almacenar un UDT nulo. Esta comprobación de valores NULL se realiza cada vez que se lleva un UDT desde CLR, lo que significa que el uso de la construcción Transact-SQL IS NULL para comprobar si hay UDT null siempre debe funcionar. El servidor también usa la propiedad IsNull para comprobar si una instancia es null. Una vez que el servidor determina que el UDT es NULL, puede usar su propio control de valores NULL nativos.

El método get() de IsNull no está en mayúsculas y minúsculas especiales. Si una variable de Point@p es Null, @p.IsNull se evaluará de forma predeterminada como NULL, no 1. Esto se debe a que el atributo SqlMethod(OnNullCall) del método IsNull get() tiene como valor predeterminado false. Dado que el objeto es Null, cuando se solicita la propiedad , no se deserializa el objeto, no se llama al método y se devuelve un valor predeterminado de "NULL".

Ejemplo

En el ejemplo siguiente, la variable is_Null es privada y contiene el estado NULL para la instancia del UDT. El código debe mantener un valor adecuado para is_Null. El UDT también debe tener una propiedad estática denominada Null que devuelva una instancia de valor null del UDT. Esto permite al UDT devolver un valor NULL si la instancia también es NULL en la base de datos.

  • de C#
  • de Visual Basic para .NET 
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;
    }
}

IS NULL frente a IsNull

Considere una tabla que contiene el esquema Points(id int, location Point), donde Point es un UDT clR y las siguientes consultas:

  • Consulta 1:

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

  • Consulta 2:

    SELECT ID FROM Points
WHERE location.IsNull = 0;

Ambas consultas devuelven los identificadores de puntos con ubicaciones que no son NULL. En la consulta 1 (Query 1), se usa el control habitual de valores NULL y no se requiere ninguna deserialización de UDT. La consulta 2, por otro lado, tiene que deserializar cada objeto no NULL y llamar a clR para obtener el valor de la propiedad IsNull. Claramente, el uso de IS NULL muestra un mejor rendimiento y nunca debe haber una razón para leer la propiedad IsNull de un UDT desde Transact-SQL código.

Entonces, ¿cuál es el uso de la propiedad IsNull? En primer lugar, es necesario determinar si un valor es NULL desde el código CLR. En segundo lugar, el servidor necesita una manera de probar si una instancia es null, por lo que el servidor usa esta propiedad. Después de determinar que es null, puede usar su control nativo null para controlarlo.

Implementación del método de análisis

Los métodos Parse y ToString permiten conversiones hacia y desde representaciones de cadena del UDT. El método Parse permite convertir una cadena en un UDT. Debe declararse como static (o Shared en Visual Basic) y tomar un parámetro de tipo System.Data.SqlTypes.SqlString.

El código siguiente implementa el método Parse para el UDT de Point, que separa las coordenadas X e Y. El método Parse tiene un único argumento de tipo System.Data.SqlTypes.SqlStringy supone que los valores X e Y se proporcionan como una cadena delimitada por comas. Establecer el atributo Microsoft.SqlServer.Server.SqlMethodAttribute.OnNullCall en false impide que se llame al método Parse desde una instancia nula de Point.

  • de C#
  • de Visual Basic para .NET 
[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;
}

Implementación del método ToString

El método ToString convierte el Point UDT en un valor de cadena. En este caso, se devuelve la cadena "NULL" para una instancia Null del tipo Point. El método ToString invierte el método Parse mediante un System.Text.StringBuilder para devolver un System.String delimitado por comas que consta de los valores de coordenadaS X e Y. Dado que InvokeIfReceiverIsNull el valor predeterminado es false, la comprobación de una instancia nula de Point no es necesaria.

  • de C#
  • de Visual Basic para .NET 
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();
    }
}

Exponer propiedades UDT

El Point UDT expone las coordenadas X e Y que se implementan como propiedades públicas de lectura y escritura de tipo System.Int32.

  • de C#
  • de Visual Basic para .NET 
public Int32 X
{
    get
    {
        return this._x;
    }
    set
    {
        _x = value;
    }
}

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

Validación de valores UDT

Al trabajar con datos UDT, SQL Server Motor de base de datos convierte automáticamente los valores binarios en valores UDT. Este proceso de conversión implica la comprobación de que los valores son adecuados para el formato de serialización del tipo, así como asegurarse de que el valor puede deserializarse correctamente. Esto garantiza que el valor se pueda convertir de nuevo en formato binario. En el caso de los UDT ordenados por bytes, esto también garantiza que el valor binario resultante coincida con el valor binario original. De esta forma, se evita que los valores que no son válidos se conserven en la base de datos. En algunos casos, este nivel de comprobación podría ser inadecuado. Es posible que se requiera validación adicional cuando se necesiten valores UDT para estar en un dominio o intervalo esperados. Por ejemplo, un UDT que implementa una fecha podría exigir que el valor de día sea un número positivo que pertenezca a un intervalo determinado de valores válidos.

La propiedad Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute.ValidationMethodName del Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute permite proporcionar el nombre de un método de validación que el servidor ejecuta cuando los datos se asignan a un UDT o se convierten en udT. ValidationMethodName también se llama durante la ejecución de las operaciones de llamada a procedimiento remoto (RPC) de bcp, BULK INSERT, DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, consulta distribuida y flujo de datos tabulares (TDS). El valor predeterminado de ValidationMethodName es NULL, lo que indica que no hay ningún método de validación.

Ejemplo

El fragmento de código siguiente muestra la declaración de la clase Point, que especifica un ValidationMethodName de ValidatePoint.

  • de C#
  • de Visual Basic para .NET 
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
  IsByteOrdered=true,
  ValidationMethodName = "ValidatePoint")]
public struct Point : INullable { ... }

Si se especifica un método de validación, debe tener una firma de aspecto similar al fragmento de código siguiente.

  • de C#
  • de Visual Basic para .NET 
private bool ValidationFunction()
{
    if (validation logic here)
    {
        return true;
    }
    else
    {
        return false;
    }
}

El método de validación puede tener cualquier ámbito y debe devolver true si el valor es válido y false de lo contrario. Si el método devuelve false o produce una excepción, el valor se trata como no válido y se genera un error.

En el ejemplo siguiente, el código solo permite valores de cero o superior a las coordenadas X e Y.

  • de C#
  • de Visual Basic para .NET 
private bool ValidatePoint()
{
    if ((_x >= 0) && (_y >= 0))
    {
        return true;
    }
    else
    {
        return false;
    }
}

Limitaciones del método de validación

El servidor llama al método de validación cuando el servidor realiza conversiones, no cuando se insertan datos estableciendo propiedades individuales o cuando se insertan datos mediante una instrucción Transact-SQL INSERT.

Debe llamar explícitamente al método de validación desde establecedores de propiedades y el método Parse si desea que el método de validación se ejecute en todas las situaciones. Esto no es un requisito y, en algunos casos, puede que ni siquiera sea deseable.

Ejemplo de validación de análisis

Para asegurarse de que el método ValidatePoint se invoca en la clase Point, debe llamarlo desde el método Parse y desde los procedimientos de propiedad que establecen los valores de coordenadaS X e Y. En el fragmento de código siguiente se muestra cómo llamar al método de validación ValidatePoint desde la función Parse.

  • de C#
  • de Visual Basic para .NET 
[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;
}

Ejemplo de validación de propiedades

El fragmento de código siguiente muestra cómo llamar al método de validación ValidatePoint desde los procedimientos de propiedad que establecen las coordenadas X e Y.

  • de C#
  • de Visual Basic para .NET 
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.");
        }
    }
}

Métodos UDT de código

Cuando codifique los métodos UDT, tenga en cuenta si el algoritmo usado podría cambiar con el tiempo. Si es así, es posible que quiera considerar la posibilidad de crear una clase independiente para los métodos que usa el UDT. Si cambia el algoritmo, puede volver a compilar la clase con el nuevo código y cargar el ensamblado en SQL Server sin afectar al UDT. En muchos casos, los UDT se pueden volver a cargar mediante la instrucción Transact-SQL ALTER ASSEMBLY, pero esto podría causar problemas con los datos existentes. Por ejemplo, el Currency UDT incluido con la base de datos de ejemplo AdventureWorks2022 usa una función ConvertCurrency para convertir valores de moneda, que se implementa en una clase independiente. Es posible que los algoritmos de conversión cambien de maneras impredecibles en el futuro o que se requiera una nueva funcionalidad. Separar la función ConvertCurrency de la implementación de Currency UDT proporciona una mayor flexibilidad al planear cambios futuros.

Ejemplo

La clase Point contiene tres métodos sencillos para calcular la distancia: Distance, DistanceFromy DistanceFromXY. Cada devuelve un double calculando la distancia entre Point y cero, la distancia desde un punto especificado hasta Pointy la distancia de las coordenadas X e Y especificadas a Point. Distance y DistanceFrom cada llamada DistanceFromXY, y muestran cómo usar argumentos diferentes para cada método.

  • de C#
  • de Visual Basic para .NET 
// 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));
}

Uso de atributos SqlMethod

La clase Microsoft.SqlServer.Server.SqlMethodAttribute proporciona atributos personalizados que se pueden usar para marcar definiciones de método con el fin de especificar determinismo, comportamiento de llamada null y especificar si un método es un mutador. Se asume el uso de valores predeterminados para estas propiedades y solamente se usa el atributo personalizado cuando se necesita un valor no predeterminado.

Nota:

La clase SqlMethodAttribute hereda de la clase SqlFunctionAttribute, por lo que SqlMethodAttribute hereda los campos FillRowMethodName y TableDefinition de SqlFunctionAttribute. Esto implica que es posible escribir un método con valores de tabla, que no es el caso. El método compila y el ensamblado se implementa, pero se genera un error sobre el tipo de valor devuelto IEnumerable en tiempo de ejecución con el siguiente mensaje: "Method, property o field <name> in class <class> in assembly <assembly> has invalid return type".

En la tabla siguiente se describen algunas de las propiedades Microsoft.SqlServer.Server.SqlMethodAttribute pertinentes que se pueden usar en los métodos UDT y se enumeran sus valores predeterminados.

Propiedad Descripción
DataAccess Indica si la función implica el acceso a los datos de usuario almacenados en la instancia local de SQL Server. El valor predeterminado es DataAccessKind.None.
IsDeterministic Indica si la función genera los mismos valores de salida si se especifican los mismos valores de entrada y el mismo estado de la base de datos. El valor predeterminado es false.
IsMutator Indica si el método produce un cambio de estado en la instancia del UDT. El valor predeterminado es false.
IsPrecise Indica si la función implica cálculos imprecisos, como operaciones de punto flotante. El valor predeterminado es false.
OnNullCall Indica si se llama al método cuando se especifican argumentos de entrada de referencia NULL. El valor predeterminado es true.

Ejemplo

La propiedad Microsoft.SqlServer.Server.SqlMethodAttribute.IsMutator permite marcar un método que permite un cambio en el estado de una instancia de un UDT. Transact-SQL no permite establecer dos propiedades UDT en la cláusula SET de una instrucción UPDATE. Sin embargo, puede tener un método marcado como mutador que cambie los dos miembros.

Nota:

Los métodos mutadores no se permiten en las consultas. Solo puede llamarse a estos métodos en instrucciones de asignación o en instrucciones de modificación de datos. Si un método marcado como mutador no devuelve void (o no es un Sub en Visual Basic), CREATE TYPE produce un error.

En la siguiente instrucción se supone la existencia de un UDT de Triangles que tiene un método Rotate. La siguiente instrucción update Transact-SQL invoca el método Rotate:

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

El método Rotate está decorado con la configuración del atributo SqlMethodIsMutator en true para que SQL Server pueda marcar el método como un método mutador. El código también establece OnNullCall en false, que indica al servidor que el método devuelve una referencia nula (Nothing en Visual Basic) si alguno de los parámetros de entrada son referencias nulas.

  • de C#
  • de Visual Basic para .NET 
[SqlMethod(IsMutator = true, OnNullCall = false)]
public void Rotate(double anglex, double angley, double anglez)
{
   RotateX(anglex);
   RotateY(angley);
   RotateZ(anglez);
}

Implementación de un UDT con un formato definido por el usuario

Al implementar un UDT con un formato definido por el usuario, debe implementar métodos Read y Write que implementen la interfaz de Microsoft.SqlServer.Server.IBinarySerialize para controlar la serialización y deserialización de los datos UDT. También debe especificar la propiedad MaxByteSize del Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute.

El UDT de moneda

El Currency UDT se incluye con los ejemplos clR que se pueden instalar con SQL Server.

El Currency UDT admite el manejo de cantidades de dinero en el sistema monetario de una referencia cultural determinada. Debe definir dos campos: un string para CultureInfo, que especifica quién emitió la moneda (en-us, por ejemplo) y un decimal para CurrencyValue, la cantidad de dinero.

Aunque el servidor no lo usa para realizar comparaciones, el Currency UDT implementa la interfaz System.IComparable, que expone un único método, System.IComparable.CompareTo. Esto se usa en el lado cliente en situaciones en las que es conveniente comparar o ordenar con precisión los valores de moneda dentro de las referencias culturales.

El código que se ejecuta en CLR compara por separado la referencia cultural y el valor de moneda. En el caso del código transact-SQL, las siguientes acciones determinan la comparación:

  1. Establezca el atributo IsByteOrdered en true, lo que indica a SQL Server que use la representación binaria persistente en el disco para realizar comparaciones.

  2. Use el método Write para la Currency UDT para determinar cómo se conserva el UDT en el disco y, por tanto, cómo se comparan y ordenan los valores udT para las operaciones de Transact-SQL.

  3. Guarde el Currency UDT con el siguiente formato binario:

    1. Guarde la referencia cultural como una cadena codificada mediante UTF-16, con bytes del 0 al 19, que se rellena a la derecha con caracteres NULL.

    2. Use el byte 20 y los bytes siguientes para almacenar el valor decimal de la moneda.

El propósito del relleno es asegurarse de que la referencia cultural está completamente separada del valor de moneda, de modo que, cuando un UDT se compara con otro en código Transact-SQL, los bytes de referencia cultural se comparan con los bytes de referencia cultural y los valores de bytes de moneda se comparan con los valores de bytes de moneda.

Atributos de moneda

El Currency UDT se define con los atributos siguientes.

  • de C#
  • de Visual Basic para .NET 
[Serializable]
[SqlUserDefinedType(Format.UserDefined,
    IsByteOrdered = true, MaxByteSize = 32)]
    [CLSCompliant(false)]
public struct Currency : INullable, IComparable, IBinarySerialize
{ ... }

Creación de métodos de lectura y escritura con ibinaryserialize

Al elegir UserDefined formato de serialización, también debe implementar la interfaz IBinarySerialize y crear sus propios métodos de Read y Write. Los procedimientos siguientes de Currency UDT usan el System.IO.BinaryReader y System.IO.BinaryWriter para leer y escribir en el UDT.

  • de C#
  • de Visual Basic para .NET 
// 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();
}

Contenido relacionado