Como crear el tipo de datos Hora en SQLServer 2005

 


Creando el tipo de datos hora en SQL2005 usando CLR


 


Introducción


 


SQLServer dispone de dos tipos de datos que son útiles para el almacenamiento de fechas y horas (Datetime & SmallDatetime). En muchas ocasiones necesitamos tener un tipo de datos exclusivo para almacenar únicamente las horas como así también poder hacer su tratamiento como podrían ser sumas, restas, promedios, etc.


En este articulo veremos paso a paso como crear nuestro propio tipo de datos hora y poderlo utilizar como si fuere cualquier otro tipo de dato del motor nativo.


 


CLR en SQL2005


 


Una de las mayores incorporaciones que se le ha hecho a SQLServer 2005 es la posibilidad de crear objetos SQL (Triggers – Stores Procedures – User Function – User defined Type – Aggregate Function) utilizando .NET. La siguiente tabla muestra los tipos de objetos que se pueden crear con T-SQL y/o CLR.


 


Tipo de Objeto

TSQL

CLR

Store Procedures

X

X

Triggers

X

X

User Function

X

X

User Defined Type

 

X

Aggregate Function

 

X


 


Como se podrá observar si se desean crear “User defined Type” o bien “Aggregate Function” (como por ej. Sum, Avg, count,etc) es necesario hacerlos con CLR exclusivamente.


 


En este articulo no entraremos en detalle de cómo funciona el CLR dentro de SQL2005 sino que nos concentraremos en como crear un  “User Defined Types”  (UDT)


Debemos aclarar que no es necesario utilizar Visual Studio para poder programar en CLR dentro de SQL2005  pero si altamente recomendado por todas las facilidades que esta herramienta nos ofrece (Deploy – Debug , etc).


 


Al instalar SQL2005 por default no se activa el uso del CLR, esto es debido a que SQL2005 tiene como concepto “Seguro por defecto” con lo cual cierra todo tipo de funciones que pueden ser de alto riesgo.


Para poder habilitar el uso del CLR se puede o bien utilizar el SAC (Sourface Area Configuration) o sino la siguiente sentencia TSQL.


 


EXEC sp_configure ‘show advanced options’, 1


GO


RECONFIGURE


GO


sp_configure ‘clr enabled’, 1


GO


RECONFIGURE


GO


 



Fig 1 Sourface Area Configuration


 


Descripción de los User Defined Types


 


En versiones anteriores a SQL2005 los UDT eran solamente Alias de otro tipo de dato como se muestra en el siguiente código.


 


EXEC sp_addtype N’age’, N’tinyint’, N’not null’


GO


 


CREATE RULE age_range


AS


@age >= 0 AND @age <=140


GO


 


EXEC sp_bindrule N’age_range’, N’age’


GO


 


Ahora en SQL2005 los UDT son muchos mas ricos donde todo el comportamiento esta embebido en el código de este.


Los UDT deben respetar los siguientes requerimientos


 


  • Soportar el concepto de Null (INullable)
  • Soportar la conversión hacia y desde String
  • Soportar la serialización
  • Soportar un constructor por default
  • Respetar las reglas de nombres (128 caracteres máximo)

Creando el UDT desde Visual Studio 2005


 


Una de las maneras de poder crear este tipo de objeto es mediante Visual Studio 2005, para ello debemos generar un nuevo proyecto del tipo “Database” ya sea en C# o VB.NET.


 




Al realizar esta operación nuestro segundo paso en el asistente es indicar a que servidor de SQL2005 nos vamos a conectar


 




Si nuestro servidor no se encuentra en la lista, debemos agregarlo desde el botón “Add New Reference..”


Nuestro siguiente paso es agregar un componente a nuestra solución desde el “Solution Explorer”


 




Como podemos observar se pueden agregar Stores Procedures, User Defined Function, User Defined type, Trigger, Aggregate y Class. En nuestro caso usaremos “User Defined Type”. Cabe mencionar que dentro de una solución usted podría tener mas un ítem del tipo que fuere.


Al realizar esta operación el Visual Studio nos genera la estructura necesaria para este objeto, en la siguiente figura se muestra un fragmento del código.


 


using System;


using System.Data;


using System.Data.SqlClient;


using System.Data.SqlTypes;


using Microsoft.SqlServer.Server;


 


[Serializable]


[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]


public struct udttime : INullable


{


    public override string ToString()


    {


        // Replace the following code with your code


        return “”;


    }


…………


 


Ahora empezaremos modificando el código base para poder llegar a nuestro objetivo final.


En principio le indicaremos una nueva propiedad “IsByteOrdered” a valor True para que el servidor utilice comparaciones ordenadas por Bytes.


 


[Serializable]


[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,IsByteOrdered = true)]


 


Nuestro siguiente paso es crear el código de estructura para nuestro tipo de datos UDTTIME.


 


public struct UDTTIME : INullable


{


    private int milliseconds;


 


    private bool m_Null;


 


    public static UDTTIME Parse(SqlString s)


    {


        if (s.IsNull)


            return Null;


 


        UDTTIME  x = new  UDTTIME();


        TimeSpan ts = TimeSpan.Parse((string)s);


        x.milliseconds = (int)ts.TotalMilliseconds;


 


        return x;


    }


   


    public override string ToString()…………


 


Aquí estamos usando el tipo de datos TimeSpan y trabajando los tiempos en milisegundos


Ahora lo que haremos es implementar el método ToString con el siguiente código:


 


public override string ToString()


 {


  return 


  TimeSpan.FromMilliseconds(this.milliseconds).ToString();


 }


 


Este método es de suma importancia ya que nos convertirá el valor del tipo timeSpan a algo legible por nuestros usuarios J


 


Una de las cosas que se pueden hacer también dentro de un tipo de datos es definir funciones, imaginemos que ahora queremos tener dentro de este tipo de datos una función que nos permita a un valor Time X sumarle tantos milisegundos. Para poder realizar esto agregaremos el siguiente código


 


 


 


 


 


public UDTTIME Addtime(UDTTIME o)


    {


        if (o.IsNull)


            return Null;


 


        else


        {


            this.milliseconds += o.milliseconds;


            return this;


        }


    }


 


Ahora debemos implementar el tratado de los tipos Null para ello implementaremos lo siguiente.


 


public bool IsNull


    {


        get


        {


            // Put your code here


            return m_Null;


        }


    }


    public static UDTTIME Null


    {


        get


        {


            UDTTIME h = new UDTTIME();


            h.m_Null = true;


            return h;


       }


    }


 


Ahora ya estamos en condiciones de poder hacer el deploy de este componente. El código completo ha quedado de la siguiente manera:


 


using System.Data.SqlClient;


using System.Data.SqlTypes;


using Microsoft.SqlServer.Server;


 


[Serializable]


[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,IsByteOrdered = true)]


public struct UDTTIME : INullable


{


    //  milliseconds


    private int milliseconds;


 


    // Private member


    private bool m_Null;


 


    public static UDTTIME Parse(SqlString s)


    {


        if (s.IsNull)


            return Null;


 


        UDTTIME  x = new  UDTTIME();


        TimeSpan ts = TimeSpan.Parse((string)s);


        x.milliseconds = (int)ts.TotalMilliseconds;


 


        return x;


    }


    public override string ToString()


    {


       return TimeSpan.FromMilliseconds(this.milliseconds).ToString();


    }


    public UDTTIME Addtime(UDTTIME o)


    {


        if (o.IsNull)


            return Null;


 


        else


        {


            this.milliseconds += o.milliseconds;


            return this;


        }


    }


    public bool IsNull


    {


        get


        {


            // Put your code here


            return m_Null;


        }


    }


    public static UDTTIME Null


    {


        get


        {


            UDTTIME h = new UDTTIME();


            h.m_Null = true;


            return h;


       }


    }


}


 


Ahora desde Visual Studio haremos el deploy hacia el servidor que nos habíamos conectado, para ello debemos ir al solution Explorer y hacer el Deploy pulsando el botón alterno del Mouse sobre el nombre de la solución.


 


Luego de hacer el deploy (solo demorara unos segundos) entraremos al Management Studio en el cual realizaremos las pruebas con nuestro nuevo tipo de dato.


 


Primero crearemos una tabla con nuestro nuevo tipo de datos


 


USE ADVENTUREWORKS


GO


 


CREATE TABLE TIMES


(


  id int,


  [htime] UDTTIME NULL


)


GO


 


Luego insertaremos algunos valores


 


INSERT TIMES VALUES (1, ’00:01:20′)


INSERT TIMES VALUES (3, ’16:30′)


GO


 


Y por ultimo veremos como han quedado en nuestra tabla, aquí usaremos el ToString para que el resultado sea legible para un usuario


 


SELECT Id, HTIME.ToString() AS TIME


FROM TIMES


GO


 


Ahora en nuestra siguiente prueba trataremos de usar una suma de milisegundos a una hora determinada


 


DECLARE @Hours1 UDTTIME


SET @Hours1 = ’10:00′


DECLARE @Seconds UDTTIME


SET @Seconds = ’00:00:10′


SET @Hours1 =


   @Hours1.Addtime(@Seconds)


SELECT @Hours1.ToString()


 


Ya disponemos de nuestro tipo de datos Hora que SQLServer por default no lo trae J.


Se podrían seguir agregando métodos al mismo como por ej.: Restar horas, tener un método para indicar el total de horas que representa un time, etc.


 


Cabe mencionar que cuando hemos hecho el Deploy de nuestro objeto SQLServer lo introduce dentro de la base de datos con lo cual si se hace un Backup / Restore se mantendrán todos los Assemblies.


 


 


Hacer el deploy sin disponer de Visual Studio 2005


 


No es una condición disponer de Visual Studio para poder hacer objetos CLR dentro de SQL2005, de hecho con un editor de textos simples y desde líneas de comando se puede resolver. En esta sección veremos los pasos necesarios para hacer este deploy sin el uso de visual Studio, tomaremos el código completo que hemos generado anteriormente y lo transformaremos en una dll con el SDK de .NET, para ello necesitaremos ejecutar el siguiente comando desde la línea de comandos


 


csc /t:library udttime.cs


 


Ahora debemos acceder a nuestro Management Studio.


Para no generar confusión con lo anteriormente realizado crearemos una nueva base de datos.


 


USE MASTER


GO


 


CREATE DATABASE TIME1


GO


 


Nuestro siguiente paso será crear el Assembly en nuestra nueva base de datos, para ello usaremos nuevas sentencias de TSQL


 


USE TIME1


GO


 


CREATE ASSEMBLY UDTTIME


FROM ‘C:\UDTTIME.dll’


GO


 


Continuaremos creando el tipo de datos indicándole que el origen es un assembly


 


CREATE TYPE [dbo].[UDTTIME]


EXTERNAL NAME [UDTTIME].[UDTTIME]


GO


 


Nota: No confundir la palabra EXTERNAL con que la .dll este dentro o fuera del motor, siempre estará dentro y ese paso se realizo cuando hemos ejecutado el “CREATE ASSEMBLY”, el External en este caso (al igual que cuando creamos un Store Procedure , User Define Function, Aggregate Function o Trigger) indica que el código de ese objeto es un assembly y no código TSQL


 


Por ultimo paso nos quedaría probar el tipo de datos UDTTIME, para lo cual ejecutaremos el siguiente código:


 


DECLARE @TIME1 UDTTIME


SET @TIME1 = ’20:50:00′


SELECT @TIME1.ToString()


 


Conclusiones finales:


 


La inclusión del CLR dentro del motor de base de datos ha generado muchas discusiones entre los distintos especialistas, sin ninguna duda que es una feature muy importante pero hay que ser cuidadoso en como se la utiliza. Siempre es recomendado realizar con TSQL y solo dejar para CLR aquellos tipos de objetos que no se pueden crear con TSQL (como por ej. el que vimos en este articulo) o para aquellas operaciones donde el procesamiento de calculo pueda ser un problema resolver con este ultimo.