Compartilhar via

Criar tipos definidos pelo usuário com ADO.NET

Aplica-se:SQL Server

Ao codificar sua definição de UDT (tipo definido pelo usuário), você deve implementar vários recursos, dependendo se você está implementando o UDT como uma classe ou uma estrutura e nas opções de formato e serialização escolhidas.

O exemplo nesta seção ilustra a implementação de um Point UDT como um struct (ou Structure no Visual Basic). O Point UDT consiste em coordenadas X e Y implementadas como procedimentos de propriedade.

Os seguintes namespaces são obrigatórios na definição de uma UDT:

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

O namespace Microsoft.SqlServer.Server contém os objetos necessários para vários atributos do UDT e o namespace System.Data.SqlTypes contém as classes que representam os tipos de dados nativos do SQL Server disponíveis para o assembly. Pode haver outros namespaces necessários para que o assembly funcione corretamente. O Point UDT também usa o namespace System.Text para trabalhar com cadeias de caracteres.

Observação

Objetos de banco de dados do Visual C++, como UDTs, compilados com /clr:pure não têm suporte para execução.

Especificar atributos

Os atributos determinam como a serialização é usada para construir a representação de armazenamento de UDTs e transmiti-los por valor para o cliente.

O Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute é necessário. O atributo Serializable é opcional. Você também pode especificar o Microsoft.SqlServer.Server.SqlFacetAttribute para fornecer informações sobre o tipo de retorno de um UDT. Para obter mais informações, consulte integração clr: atributos personalizados para rotinas CLR.

Apontar atributos UDT

O Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute define o formato de armazenamento do Point UDT como Native. IsByteOrdered está definido como true, o que garante que os resultados das comparações sejam os mesmos no SQL Server como se a mesma comparação ocorresse no código gerenciado. O UDT implementa a interface System.Data.SqlTypes.INullable para tornar o UDT ciente nulo.

O fragmento de código a seguir mostra os atributos do UDT Point.

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

Implementar a nulidade

Além de especificar corretamente os atributos dos assemblies, a UDT também precisa oferecer suporte à nulidade. Os UDTs carregados no SQL Server têm reconhecimento nulo, mas para que o UDT reconheça um valor nulo, o UDT deve implementar a interface System.Data.SqlTypes.INullable.

Você deve criar uma propriedade chamada IsNull, que é necessária para determinar se um valor é nulo de dentro do código CLR. Quando o SQL Server encontra uma instância nula de um UDT, o UDT é mantido usando métodos normais de tratamento de nulos. O servidor não perde tempo serializando ou desserializando o UDT se não precisar e não desperdiçará espaço para armazenar um UDT nulo. Essa verificação de nulos é executada sempre que um UDT é trazido do CLR, o que significa que usar o constructo Transact-SQL IS NULL para verificar se os UDTs nulos sempre devem funcionar. A propriedade IsNull também é usada pelo servidor para testar se uma instância é nula. Quando determina que a UDT é nula, o servidor pode usar a manipulação de nulos nativa.

O método get() de IsNull não é de forma especial. Se uma variável de Point@p for Null, @p.IsNull, por padrão, será avaliada como NULL, não 1. Isso ocorre porque o atributo SqlMethod(OnNullCall) do método IsNull get() usa como padrão false. Como o objeto é Null, quando a propriedade é solicitada, o objeto não é desserializado, o método não é chamado e um valor padrão de "NULL" é retornado.

Exemplo

No seguinte exemplo, a variável is_Null é privada e mantém o estado de nulidade para a instância da UDT. O código deve manter um valor apropriado para is_Null. O UDT também deve ter uma propriedade estática chamada Null que retorna uma instância de valor nulo do UDT. Isso permite que a UDT retorne um valor nulo caso a instância seja realmente nula no banco de dados.

  • C#
  • do .NET do Visual Basic 
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 X IsNull

Considere uma tabela que contém o esquema Points(id int, location Point), em que Point é um UDT CLR e as seguintes 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 as consultas retornam as IDs de pontos com locais não nulos. Na Consulta 1, é usada a manipulação de nulos normal, não havendo nenhuma desserialização das UDTs obrigatória. A consulta 2, por outro lado, precisa desserializar cada objeto não nulo e chamar o CLR para obter o valor da propriedade IsNull. Claramente, usar IS NULL exibe um melhor desempenho e nunca deve haver um motivo para ler a propriedade IsNull de um UDT de Transact-SQL código.

Então, qual é o uso da propriedade IsNull? Primeiro, é necessário determinar se um valor é nulo de dentro do código CLR. Em segundo lugar, o servidor precisa de uma maneira de testar se uma instância é nula, portanto, essa propriedade é usada pelo servidor. Depois que for determinado como nulo, ele poderá usar seu tratamento nulo nativo para lidar com ele.

Implementar o método de análise

Os métodos Parse e ToString permitem conversões de e para representações de cadeia de caracteres do UDT. O método Parse permite que uma cadeia de caracteres seja convertida em um UDT. Ele deve ser declarado como static (ou Shared no Visual Basic) e usar um parâmetro do tipo System.Data.SqlTypes.SqlString.

O código a seguir implementa o método Parse para o Point UDT, que separa as coordenadas X e Y. O método Parse tem um único argumento do tipo System.Data.SqlTypes.SqlStringe pressupõe que os valores X e Y sejam fornecidos como uma cadeia de caracteres delimitada por vírgulas. Definir o atributo Microsoft.SqlServer.Server.SqlMethodAttribute.OnNullCall como false impede que o método Parse seja chamado de uma instância nula do Point.

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

Implementar o método ToString

O método ToString converte o Point UDT em um valor de cadeia de caracteres. Nesse caso, a cadeia de caracteres "NULL" é retornada para uma instância nula do tipo Point. O método ToString inverte o método Parse usando um System.Text.StringBuilder para retornar uma System.String delimitada por vírgula que consiste nos valores das coordenadas X e Y. Como InvokeIfReceiverIsNull o padrão é false, a verificação de uma instância nula de Point é desnecessária.

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

Expor propriedades UDT

O Point UDT expõe coordenadas X e Y que são implementadas como propriedades públicas de leitura/gravação do tipo System.Int32.

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

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

Validar valores UDT

Ao trabalhar com dados UDT, o Mecanismo de Banco de Dados do SQL Server converte automaticamente valores binários em valores UDT. Esse processo de conversão envolve verificar se os valores são apropriados ao formato de serialização do tipo e garantir que o valor possa ser desserializado corretamente. Isso garante que o valor possa ser convertido de volta para a forma binária. No caso das UDTs ordenadas por byte, isso também garante que o valor binário resultante corresponda ao valor binário original. Isso impede a manutenção de valores inválidos no banco de dados. Em alguns casos, esse nível de verificação pode ser inadequado. A validação extra pode ser necessária quando os valores UDT são necessários para estar em um domínio ou intervalo esperado. Por exemplo, uma UDT que implementa uma data pode exigir que o valor de dia seja um número positivo e que esteja em um determinado intervalo de valores válidos.

A propriedade Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute.ValidationMethodName do Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute permite fornecer o nome de um método de validação executado pelo servidor quando os dados são atribuídos a um UDT ou convertidos em um UDT. também é chamado durante a execução do utilitário bcp , , , , , consulta distribuída e operações de RPC (fluxo de dados de tabela) de procedimento remoto (RPC). O valor padrão para ValidationMethodName é nulo, indicando que não há nenhum método de validação.

Exemplo

O fragmento de código a seguir mostra a declaração da classe Point, que especifica um ValidationMethodName de ValidatePoint.

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

Caso seja especificado, um método de validação deve ter uma assinatura semelhante ao seguinte fragmento de código.

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

O método de validação pode ter qualquer escopo e deve retornar true se o valor for válido e false caso contrário. Se o método retornar false ou gerar uma exceção, o valor será tratado como não válido e um erro será gerado.

No exemplo a seguir, o código permite apenas valores de zero ou maior que as coordenadas X e Y.

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

Limitações do método de validação

O servidor chama o método de validação quando o servidor está executando conversões, não quando os dados são inseridos definindo propriedades individuais ou quando os dados são inseridos usando uma instrução Transact-SQL INSERT.

Você deve chamar explicitamente o método de validação de setters de propriedade e o método Parse se quiser que o método de validação seja executado em todas as situações. Isso não é um requisito e, em alguns casos, pode até não ser desejável.

Exemplo de validação de análise

Para garantir que o método ValidatePoint seja invocado na classe Point, você deve chamá-lo do método Parse e dos procedimentos de propriedade que definem os valores da coordenada X e Y. O fragmento de código a seguir mostra como chamar o método de validação ValidatePoint da função Parse.

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

Exemplo de validação de propriedade

O fragmento de código a seguir mostra como chamar o método de validação ValidatePoint dos procedimentos de propriedade que definem as coordenadas X e Y.

  • C#
  • do .NET do Visual Basic 
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

Durante a codificação dos métodos de UDT, considere se o algoritmo usado pode ser alterado com o passar do tempo. Nesse caso, talvez você queira considerar a criação de uma classe separada para os métodos usados pela UDT. Se o algoritmo for alterado, você poderá recompilar a classe com o novo código e carregar o assembly no SQL Server sem afetar a UDT. Em muitos casos, os UDTs podem ser recarregados usando a instrução Transact-SQL ALTER ASSEMBLY, mas isso pode causar problemas com os dados existentes. Por exemplo, o Currency UDT incluído com o banco de dados de exemplo AdventureWorks2025 usa uma função ConvertCurrency para converter valores de moeda, que é implementada em uma classe separada. É possível que algoritmos de conversão possam mudar de maneiras imprevisíveis no futuro ou que essa nova funcionalidade possa ser necessária. A separação da função ConvertCurrency da implementação de Currency UDT proporciona maior flexibilidade ao planejar alterações futuras.

Exemplo

A classe Point contém três métodos simples para calcular a distância: Distance, DistanceFrome DistanceFromXY. Cada um retorna um double calculando a distância de Point a zero, a distância de um ponto especificado para Pointe a distância das coordenadas X e Y especificadas para Point. Distance e DistanceFrom cada chamada DistanceFromXYe demonstrar como usar argumentos diferentes para cada método.

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

Usar atributos sqlMethod

A classe Microsoft.SqlServer.Server.SqlMethodAttribute fornece atributos personalizados que podem ser usados para marcar definições de método para especificar determinismo, comportamento de chamada nula e especificar se um método é um modificador. Os valores padrão dessas propriedades são pressupostos, e o atributo personalizado só é usado quando um valor não padrão é necessário.

Observação

A classe SqlMethodAttribute herda da classe SqlFunctionAttribute, portanto, SqlMethodAttribute herda os campos FillRowMethodName e TableDefinition de SqlFunctionAttribute. Isso implica que é possível escrever um método com valor de tabela, o que não é o caso. O método é compilado e o assembly é implantado, mas um erro sobre o tipo de retorno IEnumerable é gerado em runtime com a seguinte mensagem: "Método, propriedade ou campo <name> na classe <class> no assembly <assembly> tem um tipo de retorno inválido".

A tabela a seguir descreve algumas das propriedades Microsoft.SqlServer.Server.SqlMethodAttribute relevantes que podem ser usadas em métodos UDT e lista seus valores padrão.

Propriedade Descrição
DataAccess Indica se a função envolve o acesso a dados do usuário armazenados na instância local do SQL Server. O padrão é DataAccessKind.None.
IsDeterministic Indica se a função produz os mesmos valores de saída, dados os mesmos valores de entrada e o mesmo estado de banco de dados. O padrão é false.
IsMutator Indica se o método causa uma alteração de estado na instância da UDT. O padrão é false.
IsPrecise Indica se a função envolve computações imprecisas como, por exemplo, operações de ponto flutuante. O padrão é false.
OnNullCall Indica se o método é chamado quando são especificados argumentos de entrada de referência nulos. O padrão é true.

Exemplo

A propriedade Microsoft.SqlServer.Server.SqlMethodAttribute.IsMutator permite marcar um método que permite uma alteração no estado de uma instância de um UDT. Transact-SQL não permite que você defina duas propriedades UDT na cláusula SET de uma instrução UPDATE. No entanto, é possível ter um método marcado como um modificador que altera os dois membros.

Observação

Métodos mutadores não são permitidos em consultas. Eles só podem ser chamados em instruções de atribuição ou de modificação de dados. Se um método marcado como modificador não retornar void (ou não for um Sub no Visual Basic), CREATE TYPE falhará com um erro.

A instrução a seguir pressupõe a existência de um UDT Triangles que tenha um método Rotate. A seguinte instrução de atualização Transact-SQL invoca o método Rotate:

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

O método Rotate é decorado com a configuração de atributo SqlMethodIsMutator para true para que o SQL Server possa marcar o método como um método mutador. O código também define OnNullCall como false, o que indica ao servidor que o método retorna uma referência nula (Nothing no Visual Basic) se qualquer um dos parâmetros de entrada for referências nulas.

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

Implementar um UDT com um formato definido pelo usuário

Ao implementar um UDT com um formato definido pelo usuário, você deve implementar métodos Read e Write que implementam a interface Microsoft.SqlServer.Server.IBinarySerialize para lidar com a serialização e desserialização de dados UDT. Você também deve especificar a propriedade MaxByteSize do Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute.

A moeda UDT

O Currency UDT é incluído com os exemplos clr que podem ser instalados com o SQL Server.

O Currency UDT dá suporte ao tratamento de quantias de dinheiro no sistema monetário de uma cultura específica. Você deve definir dois campos: um string para CultureInfo, que especifica quem emitiu a moeda (en-us, por exemplo) e um decimal para CurrencyValue, a quantidade de dinheiro.

Embora não seja usado pelo servidor para executar comparações, o Currency UDT implementa a interface System.IComparable, que expõe um único método, System.IComparable.CompareTo. Isso é usado no lado do cliente em situações em que é desejável comparar ou ordenar com precisão valores de moeda dentro das culturas.

O código em execução no CLR compara a cultura separadamente do valor de moeda. Para o código Transact-SQL, as seguintes ações determinam a comparação:

  1. Defina o atributo IsByteOrdered como true, que informa ao SQL Server para usar a representação binária persistente no disco para comparações.

  2. Use o método Write para o Currency UDT para determinar como o UDT é persistido no disco e, portanto, como os valores UDT são comparados e ordenados para operações de Transact-SQL.

  3. Salve o Currency UDT usando o seguinte formato binário:

    1. Salve a cultura como uma cadeia de caracteres codificada UTF-16 para bytes de 0 a 19 com preenchimento à direita com caracteres nulos.

    2. Use bytes 20 e acima para conter o valor decimal da moeda.

A finalidade do preenchimento é garantir que a cultura seja completamente separada do valor da moeda, de modo que, quando um UDT for comparado com outro no código Transact-SQL, os bytes de cultura sejam comparados com os bytes da cultura e os valores de byte de moeda sejam comparados com os valores de byte de moeda.

Atributos de moeda

O Currency UDT é definido com os atributos a seguir.

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

Criar métodos de leitura e gravação com ibinaryserialize

Ao escolher UserDefined formato de serialização, você também deve implementar a interface IBinarySerialize e criar seus próprios métodos Read e Write. Os procedimentos a seguir da Currency UDT usam o System.IO.BinaryReader e System.IO.BinaryWriter para ler e gravar no UDT.

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

Conteúdo relacionado

  • Last updated on