Trabalhando com SQL CLR: User Defined Type

Já imaginaram criar seus próprios tipos para colunas, variáveis e parâmetros ou manipular objetos dentro do SQL Server? Com o suporte CLR do SQL Server isso é possível! Nativamente no SQL Server 2008 já encontramos muitos tipos desenvolvidos em CLR, como os spatial types (geometry, geography) e os tipos hierárquicos (hierarchyid), mas por onde começar?

O primeiro passo antes de desenvolver um tipo em CLR é justificar a utilização do mesmo, pois se não há interação com objetos COM, necessidade de comunicação com a rede ou realização de cálculos complexos, a necessidade de desenvolver seus próprios tipos CLR pode ser descartada em virtude dos tipos binários, que podem prover a necessidade de armazenar objetos serializados da aplicação, ou dos tipos XML, que podem prover necessidades de utilizar objetos serializados em XML e que permitem consultas com xquery e indexação.

Os tipos CLR são estruturas (ou struct), e tem as mesmas limitações das funções de agregação desenvolvidas em CLR para trabalhar com tipos por referência (exemplo: string e vetores), mas isso não limita sua utilização, somente pode deixar um pouco mais complexo o desenvolvimento.

Uma das vantágens destes tipos CLR é possibilidade de criar e acessar pelo SQL Server, atributos, propriedades, métodos e métodos estáticos customizados, estas estruturas podem ser desconhecidas para DBAs, mas são muito importantes para os desenvolvedores.

Abaixo um exemplo bem simples de um tipo CLR:

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

[Serializable]
[SqlUserDefinedType(Format.Native)]
public struct UDT_Point3D : INullable
{
    // Atributo
    private bool isNull;

    // Propriedade
    public double X { get; set; }

    public double Y { get; set; }

    public double Z { get; set; }

    public bool IsNull { get { return isNull; } }

    // Métodos
    public static UDT_Point3D Null
    {
        get { return new UDT_Point3D() { isNull = true }; }
    }

    public override string ToString()
    {
        return string.Format("{0};{1};{2}", X, Y, Z);
    }

    public static UDT_Point3D Parse(SqlString str)
    {
        var quarto = new UDT_Point3D();

        if (str.IsNull) return Null;

        var spt = str.ToString().Split(';');

        double aux;
        quarto.X = double.TryParse(spt[0], out aux) ? aux : 0;
        if (spt.Length > 1)
            quarto.Y = double.TryParse(spt[1], out aux) ? aux : 0;
        if (spt.Length > 2)
            quarto.Z = double.TryParse(spt[2], out aux) ? aux : 0;

        return quarto;
    }

    public static UDT_Point3D New() { return new UDT_Point3D(); }
}

Não se assustem com o método Parse ou com o método ToString, pois estes métodos são basicamente responsáveis para transformar um texto em no seu tipo CLR e o tipo CLR em um texto, sendo muito útil quando sua aplicação não é uma solução desenvolvida em .NET.

Existem duas formas comuns de utilizar estes tipos CLR no SQL Server, primeiramente a forma mais simples, trabalhando com texto (utilizando de forma transparente o método Parse):

DECLARE @Pnt UDT_Point3D
SET @Pnt = '1.01;21.2;21.7'

SELECT @Pnt.ToString() AS String
, @Pnt.X AS X
, @Pnt.Y AS Y
, @Pnt.Z AS Z
, @Pnt AS Bin
, CAST(@Pnt AS VARCHAR) AS String2

Ou criando seus próprios métodos estáticos para gerar uma instância de um objeto, como no caso do método “New”:

DECLARE @Pnt UDT_Point3D
SET @Pnt = UDT_Point3D::New()
SET @Pnt.X = 1.01
SET @Pnt.Y = 21.2
SET @Pnt.Z = 21.7

SELECT @Pnt.ToString() AS String
, @Pnt.X AS X
, @Pnt.Y AS Y
, @Pnt.Z AS Z
, @Pnt AS Bin
, CAST(@Pnt AS VARCHAR) AS String2

E quando houver necessidade de recuperar este tipo CLR em uma aplicação, basta adicionar a referência do projeto SQL CLR a esta aplicação, e da mesma forma que se trabalha com os outros tipos, podemos recuperar os tipos CLR com uma simples conversão, exemplo:

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT CAST('1.01;21.2;21.7' AS UDT_Point3D)";
    var point = (UDT_Point3D)cmd.ExecuteScalar();
}

A partir de agora é questão de utilizar a criatividade ao identificar situações onde a utilização de tipos CLR é justificável no SQL Server.

Anúncios

2 pensamentos sobre “Trabalhando com SQL CLR: User Defined Type

  1. Pingback: Trabalhando com SQL CLR – Resumo « SQL From Hell.com

  2. Pingback: Trabalhando com SQL CLR – Resumo « SQL From Hell.com

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s