Geovanny "Gio" Hernandez

Microsoft Community Contributor Award 2012

Hoy ha sido un dia especial, me he topado con la grata sorpresa de recibir el reconocimiento Microsoft Community Contributor 2012:

Dear Geovanny, 

Congratulations! We’re pleased to inform you that your contributions to Microsoft online technical communities have been recognized with the Microsoft Community Contributor Award. 

The Microsoft Community Contributor Award is reserved for participants who have made notable contributions in Microsoft online community forums such as TechNet, MSDN and Answers. The value of these resources is greatly enhanced by participants like you, who voluntarily contribute your time and energy to improve the online community experience for others. 

Y que mejor lugar para compartir la buena nueva que con mis colegas de Geeks, quienes siempre han tenido las puertas abiertas de esta valiosa comunidad.  Gracias a todos y a Microsoft por honrarme de esta forma, lo que me compromete cada nuevo día a dar lo mejor de mi y continuar ayudando y aprendiendo de la vasta comunidad de amantes de la tecnología.

Del Cursor y otros demonios

El titulo de este post es un pequeño homenaje a una novela del Nobel Gabriel García Márquez, su nombre me vino a la mente después de asociarlo a una serie de usos y malas prácticas que por lo general los desarrolladores cometemos al iniciar con la programación  a nivel de Base de Datos; queriendo de forma inconsciente extrapolar todos nuestros conocimientos y "manías"en lenguajes procedimentales y orientados a objetos para transferirlos a nuestras soluciones en T-SQL.

Cuantas veces nos hemos topado en diferentes foros, reuniones o en la misma oficina con comentarios como: Eso es fácil, mételo en un cursor y ..... La triste historia inicia, en especial cuando meses después escuchamos: La consulta está demasiado lenta, se tarda N horas y sigue creciendo... Generalmente comienzan a llover soluciones mágicas, en las que por descabelladas que puedan parecer se han llegado a implementar, algunas simples y poco efectivas hasta algunas muy caras y poco productivas.

Es en este punto donde elementos como los Cursores transtornan la salud de nuestras aplicaciones, convirtiéndose en un lastre difícil de quitar, en especial si forma parte de la cultura del desarrollador.  Como principio fundamental que puede resultar orientador esta una afirmación que hacía en uno de sus textos Itzik Ben-Gan al mencionar que el uso de cursores representaba una opción viable si se requería manipular una fila a la vez.

A continuación tomo prestado de los foros de MSDN una consulta en la que el usuario se avocaba directamente al cursor como el método de solución elegido, decía algo así:

"Quiero preguntarles como puedo hacer un script en el que se maneje un cursor para depurar una base de datos...bueno tengo una tabla llamada auditoria_homologacion donde tengo varios registros con id autonumerico pero el resto de los campos están repetidos, entonces cuando se busca un registro por cualquier campo(menos el id_auditoria)  me aparecen varios registros iguales, entonces debo buscar los registros repetidos, luego tengo que mirar que esos registros que están repetidos no estén  amarrados a otra tabla que se llama log_error_homologacion en donde tengo el fk_id_auditoria de la tabla auditoria_homologacion, (entonces los que están en la tabla log_error_homologacion no se borran), pero hay mas registros repetidos los cuales no tienen error pero debo borrar los demás que están repetidos (por ejemplo tengo 5 registros con diferente id_auditoria pero el resto de los campos en igual..debo borrar 4 y dejar 1) y no se como hacerlo por medio de un cursor, agradezco de antemano la colaboración me es de gran ayuda gracias."

Aunque la forma de explicarlo no fue la idónea, la idea central consistía en obtener un conjunto de filas duplicadas y verificar que las mismas se encontraban en una tabla de log, caso contrario eliminarlas.   Partiendo de la afirmación de Ben-Gan en este caso no es necesario procesar una fila a la vez, por el contrario generar una solución basada en cursores sería extremadamente deficiente y muy tediosa.

Para solventar esta situación procedimos al uso de una solución basada en CTE (Common Table Expression) y la función ROW_NUMBER(), aquí presento el código:

DECLARE @AuditoriaHomologacion TABLE (IDAuditoria INT,Columna1 VARCHAR(30),Columna2 INT,Columna3 VARCHAR(30),Columna4 INT)

DECLARE @LogErrorHomologacion TABLE (IDAuditoria INT)

INSERT @AuditoriaHomologacion

VALUES  (1,'LAUREN FONSECA',1,'NICARAGUA',2),

               (2,'LAUREN FONSECA',1,'NICARAGUA',2),

               (3,'LAUREN FONSECA',1,'NICARAGUA',2),

               (4,'OCTAVIO HERNANDEZ',1,'ESPANA',2),

               (5,'OCTAVIO HERNANDEZ',1,'ESPANA',2),

               (6,'LAUREN GIOVANNA HERNANDEZ',1,'ESPANA',2),

               (7,'LAUREN GIOVANNA HERNANDEZ',1,'ESPANA',2)

INSERT @LogErrorHomologacion

VALUES (7),(5)

; WITH MiCTE AS

(

  SELECT IDAuditoria,ROW_NUMBER() OVER(PARTITION BY Columna1,Columna2,Columna3,Columna4 ORDER BY IDAuditoria)AS 'SEC'

  FROM @AuditoriaHomologacion

)

DELETE FROM MiCTE

WHERE MiCTE.SEC >1 AND NOT EXISTS(SELECT IDAuditoria FROM @LogErrorHomologacion L WHERE L.IDAuditoria=MiCTE.IDAuditoria)

--Verificando filas eliminadas

SELECT * FROM @AuditoriaHomologacion

En esta solución planteada se potenciaban aspectos que permiten contar con un código mantenible y sobre todo enfocado en aspectos orientados al manejo de <<set theory>>.

Pero como no todo es desolador y gris,  un escenario común en donde el uso de cursores es viable puede ser en tareas administrativas de nuestras base de datos,  el siguiente ejemplo permite recorrer todas las tablas de una Base de Datos y obtener información sobre las mismas (filas, tamaño,etc) a través de la ejecución del sp_spacedused.

USE AdventureWorks

GO

DECLARE  @tabla VARCHAR(128)

CREATE TABLE #rowcount (tablename varchar(128), rowcnt int)

exec sp_MSforeachtable

   'insert into #rowcount select ''?'', count(*) from ?'

DECLARE tablas CURSOR FOR

SELECT tablename FROM #rowcount

OPEN tablas

FETCH NEXT FROM tablas INTO @tabla

WHILE @@fetch_status = 0

 BEGIN

  EXEC sp_spaceused @tabla

  FETCH NEXT FROM tablas INTO @tabla

 END

CLOSE tablas

DEALLOCATE tablas

DROP TABLE #rowcount

Por lo pronto un hasta luego, seguimos escribiendo, pero sobre todo aprendiendo.   

Email: info@geohernandez.com     Website: http://geeks.ms/blogs/ghernandez

A Lauren ...

Aquellas pequeñas cosas, transformando datos XML en un rowset

El intercambio de información entre empresas y organizaciones representan una tarea cada vez más común, por esto no es de extrañar el amplio uso y aceptación del formato XML como estándar para el intercambio de dicha información.  SQL Server cuenta entre sus muchas características con una serie de funciones que nos permiten potenciar el uso de XML, pero en esta ocasión nos centraremos el proceso de transformación de datos XML en un conjunto de datos (rowset).

El procesamiento de XML se realiza en cinco pasos fundamentales como se ilustra en la siguiente imagen:

Una vez que se cuenta con el documento XML (bien formado) este se procede a procesar por medio del procedimiento almacenado del sistema sp_xml_preparedocument, el cual nos permite  validar un documento XML y transformarlo en una estructura de árbol en memoria, cuyo  contenido podemos consultar por medio de la instrucción OPENXML para obtener las columnas deseadas y así manipular los datos en la forma requerida.

Iniciemos con un ejemplo simple, suponiendo que formamos parte de la empresa dedicada a la reventa de artículos por internet y estamos recibiendo información de un proveedor externo, el documento XML es el siguiente:

DECLARE @doc xml

SET @doc = '<?xml version="1.0" ?>

            <Factura FacturaID="1000" ClienteID="123" FechaFactura="2012-01-14" FormaPago="1">

              <Lineas>

                <Linea ProductoID="12" Cantidad="2" PrecioUnitario="12.99"><Descripcion>Encendedor marca Zippo</Descripcion></Linea>

                <Linea ProductoID="41" Cantidad="1" PrecioUnitario="17.45"><Descripcion>Ron Flor de Cana</Descripcion></Linea>

                <Linea ProductoID="2" Cantidad="1" PrecioUnitario="2.99"><Descripcion>Bebida embotellada natural</Descripcion></Linea>

              </Lineas>

            </Factura>'

DECLARE @IdentificadorDoc int

EXEC sp_xml_preparedocument @IdentificadorDoc OUTPUT, @doc

De forma simple podemos decir que el sp_xml_preparedocument nos permite procesar un documento XML y obtener una representación del mismo, el resultado de este sp es un código identificador que apunta hacia la estructura de árbol del XML creado previamente.  Una descripción más amplia se encuentra en el siguiente link:

http://msdn.microsoft.com/es-es/library/ms187367.aspx

Una vez que construida la estructura o árbol en memoria debemos acceder a ella, para esto nos apoyamos en la instrucción OPENXML , este nos permite obtener un conjunto de datos a partir del árbol.  La instrucción OPENXML tiene la siguiente estructura:

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )

[ WITH ( SchemaDeclaration | TableName ) ]

El parámetro idoc representaría en nuestro caso el parámetro de salida resultante de la ejecución del sp_xml_preparedocument, en donde especifica la representación interna del documento XML, el patrón de filas (rowpattern)  es el patrón XPATH que permite  definir la ruta o nodos que deberá reconocerse en la lectura del XML, a continuación nuestro código:

SELECT * FROM

OPENXML(@IdentificadorDoc, '/Factura/Lineas/Linea')

WITH

(              FacturaTarget       int '../../@FacturaID',

                ClienteTarget       int '../../@ClienteID',

                FechaFacturaTarget            datetime '../../@FechaFactura',

                FormaPagoTarget   int '../../@FormaPago',

                DetallesTarget      varchar(30) './Descripcion',

                CantidadTarget    int '@Cantidad',

                PrecioUnitarioTarget          float '@PrecioUnitario')

En este caso especifico  utilizamos una opción conocida como patrones de columna, estos contienen un patron XPath para la recuperación de los nodos y este será relativo al patrón de fila (rowpattern) especificado.  Y esto que quiere decir? Que la recuperación de los nodos estará basada en una expresión o expresiones en donde se navegara a través de la jerarquía del documento XML para obtener los datos deseados.

Aquí caben notar dos cosas:

OPENXML(@IdentificadorDoc, '/Factura/Lineas/Linea')

Le estamos indicando a la instrucción que debe tomar la representación interna perteneciente al documento que acabamos de transformar, posteriormente le indicamos hasta que nivel deseo "navegar" en la estructura del documento XML.  Posteriormente comenzamos a construir la estructura que deseamos recuperar, especificando el nombre del campo, su tipo de datos  y el origen o expresión XPATH a partir de donde obtendrá los datos.

WITH

(              FacturaTarget       int '../../@FacturaID',

                ClienteTarget       int '../../@ClienteID',

                FechaFacturaTarget            datetime '../../@FechaFactura',

                FormaPagoTarget   int '../../@FormaPago',

                DetallesTarget      varchar(30) './Descripcion',

                CantidadTarget    int '@Cantidad',

                PrecioUnitarioTarget          float '@PrecioUnitario')

El manejo o navegación se realize desde el ultimo nodo que se especifico en el patron de fila, cabe mencionar que los atributos se representan por medio de @nombreatributo, el siguiente caso funciona así:

FacturaTarget       int '../../@FacturaID' 

FacturaTarget int: Es el campo con su respectivo tipo de datos que almacenara el resultado devuelto por la expresión.

../../@FacturaID: Cabe recordar que según el patrón de fila este tiene una estructura '/Factura/Lineas/Linea' , en este caso se quiere recuperar el atributo FacturaID, este se encuentra en el nodo correspondiente a Factura, por lo que si partimos de la ruta especificada en el patrón de filas y viendo el documento XML tenemos que subir dos niveles , para este caso se usa la expresión << ../>>.

En los casos de los atributos que se encuentran en el ultimo nivel (Linea) solo se debe de anteponer el símbolo @NombreAtributo.

Un caso especial aquí es el del ELEMENTO Descripcion, debido a su naturaleza y ubicación dentro del XML para obtener su valor se debe de especificar de la siguiente manera:

./Descripcion'

Aquí le indicamos que seleccione el nodo actual a partir del nodo raíz (nodo Linea), como punto de ayuda presento una tabla resumen de las principales expresiones de ruta usadas en XPath.

Con estos pasos descritos anteriormente ya tenemos como resultado los campos extraidos directamente de un documento XML.  Nuestra consulta completa quedaría así:

DECLARE @doc xml

SET @doc = '<?xml version="1.0" ?>

            <Factura FacturaID="1000" ClienteID="123" FechaFactura="2012-01-14" FormaPago="1">

              <Lineas>

                <Linea ProductoID="12" Cantidad="2" PrecioUnitario="12.99"><Descripcion>Encendedor marca Zippo</Descripcion></Linea>

                <Linea ProductoID="41" Cantidad="1" PrecioUnitario="17.45"><Descripcion>Ron Flor de Cana</Descripcion></Linea>

                <Linea ProductoID="2" Cantidad="1" PrecioUnitario="2.99"><Descripcion>Bebida embotellada natural</Descripcion></Linea>

              </Lineas>

            </Factura>'

DECLARE @IdentificadorDoc int

EXEC sp_xml_preparedocument @IdentificadorDoc OUTPUT, @doc

SELECT * FROM

OPENXML(@IdentificadorDoc, '/Factura/Lineas/Linea')

WITH

(      FacturaTarget int '../../@FacturaID',

       ClienteTarget int '../../@ClienteID',

       FechaFacturaTarget  datetime '../../@FechaFactura',

       FormaPagoTarget   int '../../@FormaPago',

       DetallesTarget      varchar(30) './Descripcion',

       CantidadTarget      int '/@Cantidad',

       PrecioUnitarioTarget       float '@PrecioUnitario')

EXEC sp_xml_removedocument @IdentificadorDoc

GO

Espero les haya servido de ayuda y seguimos escribiendo, pero sobre todo aprendiendo...

Gio

Email: info@geohernandez.com

Usando tablas derivadas con agrupaciones

El el foro de MSDN un colega planteaba dudas acerca del uso de la clausula GROUP BY, entre ellas hacía referencia a la restricción que existe en las columnas que pueden ser listadas en el SELECT de la consulta, ya que solo es  posible que listemos columnas que estén incluidas en el GROUP BY o que utilicemos funciones de agregación.

Es interesante notar que existen muchas situaciones en las que requerimos listar columnas que no sería lógico que se incluyeran en el GROUP BY, entonces ¿Cómo podemos sobrellevar la restricción inherente esta clausula?, es en estos casos donde nos podemos auxiliar de las tablas derivadas.

Tomemos como modelo la Base de Datos Northwinds, tenemos un requerimiento en el que se nos solicita una consulta que nos retorne la sumatoria de las  ventas por producto, debiendo mostrar el código del producto, el nombre del producto y las sumatoria de las ventas.  Si nos basamos en la restricción de la clausula GROUP BY tendríamos que incluir no solo el código del producto, sino también el nombre, y esto no sería muy lógico, aquí vienen las tablas derivadas a darnos elegancia y coherencia a nuestra consulta.

Tablas:

dbo.Products

dbo.Order Details

La consulta es la siguiente:

SELECT P.ProductID, P.ProductName ,O.QuantityTotal

FROM Products AS P

INNER JOIN (SELECT ProductID, SUM(Quantity) AS QuantityTotal

         FROM [Order Details]

         GROUP BY ProductID) AS O

ON P.ProductID = O.ProductID

ORDER BY P.ProductID

Podemos ver que en este caso la implementación de una tabla derivada nos brinda la ventaja de “separar” y categorizar las ordenes  sumarizando las ventas totales por producto, para luego simplemente utilizar el operador de tabla (JOIN) ligándolo a la tabla Productos y permitiéndonos de forma natural extraer el nombre o cualquier otra columna que nos interese, sin tener que incluirla en el GROUP BY, permitiéndonos pasar por alto la restricción antes mencionada.

Condiciones booleanas en Where?

El ejemplo basico que utilizaremos usa la Base de Datos Northwind, y consiste en obtener todos los registros de la tabla Orders que coincidan con los filtros relacionados a: Codigo de Empleado, Codigo de Cliente y Via de Compra (ShipVia). 

CREATE PROCEDURE uspGetOrders

(

 @CustomerID nchar(5)=null,

 @EmployeeID int=null,

 @ShipVia int=null

)

As

SELECT * FROM dbo.Orders

WHERE (CustomerID=COALESCE(@CustomerID,CustomerID))

AND (EmployeeID=COALESCE(@EmployeeID,EmployeeID))

AND (ShipVia=COALESCE(@ShipVia,ShipVia))

  Set @tabla='Products'

En este segmento de código podemos observar que se reciben los parametros antes mencionados, pero teniendo un default value igual a null, en caso de que no se envie un parametro este sera obviado en el procesamiento o filtro de la consulta. 

Al momento de establecer las condiciones del Where por ejemplo: CustomerID=COALESCE(@CustomerID,CustomerID))

Estamos indicando que la columna CustomerID sera igual al valor retornado por la funcion Coalesce la cual nos retorna el primer valor no nulo, por lo que en caso de que el parametro sea diferente de nulo se tendra conformada la condicion, y en caso de ser null entonces procede a establecer CustomerID=CustomerID, esto representa una condicion de igualdad, lo que no causa ningun procesamiento y sirve perfectamente a nuestro objetivo.

Cuando procedemos a ejecutar el sp pasamos los valores a determinados parametros según nuestras necesidades.   Como ejemplo:

EXEC uspGetOrders

@CustomerID='PICCO',

@EmployeeID=2

Nos devuelven 3 registros.

O

EXEC uspGetOrders

@CustomerID='PICCO'

Nos devuelve 10 registros

Esta solucion a mi parecer resulta mas simple y menos compleja que otras en las que utilizan IF o instrucciones CASE WHEN.

Precompilación de Vistas en el Entity Framework

En la búsqueda de un mejor rendimiento dentro de mis aplicaciones que utilizan Entity Framework (EF), logre encontrar información relacionada con la precompilación de vistas, el cual es un procedimiento que permite disminuir de manera significativa el costo de ejecución en consultas hechas contra el modelo.

La documentación oficial de Microsoft menciona el hecho de que antes de que el EF ejecute una consulta contra un modelo conceptual o salve cambios a la fuente de datos, este debe generar un conjunto de vistas locales que acceden a la base de datos,  este proceso resulta demasiado costoso, como referencia sobre este tema hay un blog titulado "Exploring the Performance of the ADO.NET Entity Framework - Part 1" (http://blogs.msdn.com/b/adonet/archive/2008/02/04/exploring-the-performance-of-the-ado-net-entity-framework-part-1.aspx). 

Una solución propuesta para contrarestar esta conducta del Entity es la precompilación de vistas y su inclusión en el proyecto a fin de garantizar no tener que crearlas en tiempo de ejecución. 

Para este blog usare  SQL Server 2008  y Visual Studio 2010, basando nuestro modelo en la BD Adventure Works.  Iniciamos creando una nueva solución, y especificamos un proyecto de tipo <<Class Library>>, esto nos brinda la ventaja de poder usarlo en muchos proyectos, el siguiente paso consiste en crear un archivo de tipo <<ADO .Net Entity Data Model>> y definimos el modelo con el nombre AWModel.edmx como muestra la siguiente pantalla:

Definimos que el modelo se generara desde la BD y seleccionamos algunas tablas representativas para su integración dentro del modelo de entidades. 

Una vez finalizado este procedimiento se nos presenta el modelo con las entidades seleccionadas, hasta el momento los pasos realizados son simplemente de configuración de nuestro proyecto y son la antesala al proceso especifico de creación de las vistas precompiladas.  El primer paso para este proceso consiste en cambiar la propiedad Metadata Artifact Processing de nuestro modelo, el cual debe contener la opción <<Copy to Output Directory>>

Una vez realizado este cambio, procedemos a realizar un Build de la solución y verificamos en el directorio de salida que se encuentren tres nuevos archivos que conforman los archivos de mapeo del modelo.  La siguiente imagen muestra la relación de estos archivos con el modelo:

Para mayor detalle sobre el tema sugiero la lectura del segundo capítulo (Pag.29) del libro Programming Entity Framework  Second Edition de Julie Lerman.  Los archivos generados para nuestro ejemplo son los siguientes:

Estos archivos los incluiremos dentro de nuestra solución, damos click derecho en el proyecto y escogemos la opción Add - Existing Items, y seleccionamos los tres archivos, luego seleccionamos a cada uno de estos archivos y en la ventana de propiedades establecemos Build Action al valor Embedded Resource este paso nos permite definir dichos archivos como recursos embebidos en el Assembly que contiene nuestro modelo.

Continuamos con la creación de la denominada vista de entidades, para lo cual nos vamos a la opción de propiedades del proyecto, y nos situamos en el tab Build Events y en la opción Prebuild Event Commands Line , en este casilla debemos de especificar el siguiente código:

"%windir%\Microsoft.NET\Framework\v4.0.30319\EdmGen.exe" /nologo /language:CSharp /mode:ViewGeneration "/inssdl:$(TargetDir)AWModel.ssdl" "/incsdl:$(TargetDir)AWModel.csdl" "/inmsl:$(TargetDir)AWModel.msl" "/outviews:$(ProjectDir)AWModel.Views.cs"

Cabe destacar que para el caso de utilizar Visual Basic, solo se reemplaza CSharp por VB.  El siguiente paso consiste en hacer un Rebuild de la solución.

A continuación incluimos el nuevo archivo generado AWModelView.cs, damos click derecho en el proyecto y en la opción Add - Existing Item seleccionamos el archivo de vista.

Con el apoyo de la herramienta Red Gate's .Net Reflector nos permite analizar el assembly compilado, es interesante observar como en la sección de Resources nos despliega los archivos que forman el esquema de nuestro modelo, además de incluir la vista generada.

Llegando a este punto contamos con un Assembly cuyo contenido esencialmente es nuestro modelo de entidades, así como los archivos que conforman su metadata.  Esto nos permitirá incorporar nuestro Assembly en diferentes proyectos, además de mantener una mayor eficiencia en los tiempos de ejecución de nuestra aplicación, debido a la pre compilación de las vistas. 

En posteriores blog estare abordando otros puntos y tips que pueden resultar de utilidad en el desarrollo de aplicaciones con Entity Framework.

Enlace de interés sobre LINQ

Despues de algunas lunas de ausencia, estoy de nuevo listo y con mucho material, por el momento quiero compartir un enlace que me sirvio de mucha ayuda para comprender el uso que hace LINQ de los delegados genéricos y comprender el uso de expresiones Lambda dentro de operadores como Where.  Aquí esta en enlace:

http://blog.linqexchange.com/index.php/understanding-generic-delegates-and-lambda-expressions/

En los siguientes posts estaré hablando sobre Entity Framework  y mostrare una clase modelo  para manejar un conjunto de operaciones de tipo CRUD.  

De Objetos y Clases( “Doing Objects in Visual Basic 2005”)

Normal 0 21 false false false

Existen libros fundamentales dentro de nuestro trabajo diario, y los cuales sirven como una  guía de referencia en muchos temas,  este es mi caso con el libro “Doing Objects in Visual Basic 2005” escrito por la reconocida MVP Deborah Kurata.   Mi encuentro con este valioso texto se debió a la curiosidad (casi siempre interminable) por profundizar en el tema de la POO desde una  perspectiva práctica a través del lenguaje Visual Basic 2005.

En los primeros capítulos del libro se presentan un conjunto de conceptos centrales sobre la Orientación a Objetos,  y es a través de estas definiciones precisas que comienza la exploración del proceso de desarrollo de software como un “todo”, iniciando con  el cliente y terminando siempre con él,  siendo la base fundamental de nuestro proceso la definición de requerimientos y sobre todo la división en unidades de trabajo manejables.

Entre los beneficios mencionados por la autora con el uso de una metodología orientada a objetos está la de poder  hablar el mismo lenguaje del cliente, es decir que a través de la abstracción propia de la orientación a objetos y la construcción de escenarios podremos discutir  en términos del negocio, abordando únicamente los problemas que pretendemos resolver y no la tecnología que utilizaremos.

La parte central del libro aborda el desarrollo de una aplicación para el monitoreo de compras  en la cual se va mostrando de forma detallada  el desarrollo  de las diferentes capas (User Interface Layer, Business Logic  Layer y Data Access Layer) proporcionando un base solida sobre la implementación de la orientación a objetos en Visual Basic , además podemos encontrar  a lo largo del libro  una serie de importantes y útiles consejos,  soportados por la amplia experiencia de Deborah en  el desarrollo de software. 

Un buen detalle es el resumen que se incluye al final de cada  capítulo y sobre todo la guía de lecturas adicionales, en donde se hacen referencias a libros y enlaces web sobre autores destacados en el tema, información que nos puede servir de base para profundizar en determinados tópicos.

En resumen, podría recomendar sin temor a equivocarme este libro, el cual denota un trabajo hecho con la pasión y la experiencia de una colega excepcional.

Encontrando combinaciones numéricas con SQL Server

Cuando comencé a programar mis primeras aplicaciones en SQL Server tuve que trabajar mucho en el desarrollo de la  programación en T-SQL desde una perspectiva de lógica de conjuntos; al igual que muchos principiantes caí en el error de utilizar(o abusar?) de objetos que me resultaban familiares a los utilizados en  lenguajes de programación procedimental,  como  por ejemplo los cursores.  Con el objetivo de irme adaptando a este nuevo entorno de trabajo inicie realizando ejercicios prácticos, entre los cuales destaca el que presento a continuación y que servirá de base para el presente artículo.  El enunciado del problema es el siguiente:

Como base para este articulo adjunto un archivo de Excel con una serie de combinaciones numéricas como la mostrada a continuación: 

Sobre la base del problema planteado se  requiere conocer cuáles son los pares numéricos que más se repiten en estas series, tomemos una tabla modelo con cinco series numéricas como la que se muestra a continuación:

Podemos decir que tenemos tres pares numéricos que son los que se repiten con mayor frecuencia en este grupo, estos son:

Obviamente requerimos realizar este proceso de forma rápida y con un buen rendimiento para el manejo de un alto volumen de registros, por lo que iniciaremos con la importación de nuestro archivo de trabajo a una tabla para su posterior tratamiento.  El siguiente script nos permite crear nuestra base de datos de ejemplo.

Importando de Excel a SQL Server 2005

Abrimos el SQL Server Management Studio, y nos posicionamos en nuestra BD de trabajo

Luego damos click derecho y marcamos la opción de Tarea y posteriormente  Importación de Datos .  Procedemos a seleccionar nuestro archivo de origen (marcamos la casilla que indica que nuestra primera fila tiene los nombres de las columnas).

Finalmente seleccionamos como destino nuestra base de datos.

Luego debemos seleccionar la opción que indica copiar datos de una o más tablas o vistas.

 Para finalizar la importación se nos presenta una pantalla en la que podemos dejar el nombre por defecto de la tabla (en este caso el mismo de la hoja de cálculo de donde se tomaron los datos), o escribir un nombre específico, en nuestro caso se definió el nombre Tabla_Excel.

A partir de este momento ya tenemos nuestra tabla inicial de trabajo, la cual contiene más de 4500 series numéricas.  Iniciaremos el proceso adaptando esta tabla al modelo que nosotros requerimos.

 Diseñando nuestra tabla de trabajo

El primer paso para el diseño de nuestra solución consiste en la creación de la tabla DetailNumbers  en donde se almacenaran los números que forman las series numéricas.

En la definición de las tabla se incluyeron las declaraciones de claves primarias, la invocación de las funciones getdate y suser_name como valores por defecto, así como la inclusión de la restricción referida al rango de los números para que estos se encuentren en un rango de 1 a 49.   

Para la adaptación de la tabla DetailNumbers debemos de aplicar una función que nos permita transformar las columnas (Number 1..6) en filas, agrupadas por el IdNumber que representa el código único de la serie.

Para realizar este proceso de transformación de datos nos auxiliamos de la función Unpivot incorporada en SQL Server 2005, esta nos permite rotar columnas dentro de filas.

Con esta función tomamos las columnas correspondientes a los números que forman las series y son convertidas en filas para su inserción en la tabla DetailNumbers.  Para más información sobre el uso de esta función podemos acudir a los libros en Pantalla de SQL Server.

Actualmente  los datos han sido cargados de forma tal que nos permitirá implementar la solución del problema planteado.  La solución está basada en el uso del operador Cross Join, el cual es definido como un operador que enlaza  cada fila de la primera entrada con cada fila de la segunda entrada, en nuestro caso aplicaremos este operador creando una copia virtual de nuestra tabla de trabajo.  El siguiente grafico resume el uso de este operador:

Al igual que el primer número (30) es enlazado con cada uno de los valores de la tabla T2, sucede lo mismo con los siguientes valores de la serie. Los resultados de este primer cruce seria la  combinación:  (30,30),(30,27),(30,35),(30,38),(30,11),(30,26).

En términos de T-SQL se representaría de la siguiente forma:

Hasta el momento lo que tenemos es una consulta que nos da el total de todas las combinaciones posibles, obviamente esto no es lo solicitado pero es un buen punto de partida.  El siguiente paso será ir refinando nuestra consulta, como primer paso aplicaremos un filtro dentro de una condición, y es excluir del producto cartesiano a los números que sean iguales, partiendo del hecho de que ningún numero de la serie puede repetirse.  Al aplicar el filtro el resultado reducirá el número de combinaciones (de forma poco significativa), pero cumplimos con una de las condiciones planteadas.

Es posible observar un hecho interesante y es que al hacer esta autocombinación, hay productos que se repiten, por ejemplo al combinar el primer número de la serie (30)  se genera un producto (30,27), y al proceder a realizar la combinación del siguiente número (27) este generara un producto (27,30) habiendo una duplicidad, por lo que aplicamos otro filtro en la condición que nos permita solventar este inconveniente.

Lo siguiente que debemos hacer es aplicar un filtro para que el producto cartesiano se realice únicamente entre los números que pertenecen a una misma serie.

La fase final de nuestra consulta requiere la implementación de la instrucción clave “Group By”, hasta el momento habíamos obtenido las diferentes parejas de combinaciones numéricas por serie, pero al utilizar esta función nos permitirá la agrupación de los pares numéricos encontrados en la tabla DetailNumbers, además complementamos con el uso de la función Count y Distinct .  La consulta final se muestra a continuación:

Para verificar los resultados podemos ejecutar la siguiente consulta, en donde solo debemos introducir los valores que conforman el par numérico.

Select IdSerie From DetailNumbers

Where Number In (9,38)

group by IdSerie

having  Count(IdSerie)=2

La pantalla anterior muestra una pequeña muestra de las apariciones del par numérico (9,38), además de especificar el número de la serie en donde está presente, estos registros los podemos comparar contra el archivo original de Excel y así comprobamos la fiabilidad de los resultados. 

Bueno, ha sido un placer haber compartido este pequeño articulo con ustedes, y cualquier consulta no duden en escribirme.

Filtros dinámicos en SQL Server

En este artículo mostrare el uso de filtros dinámicos SQL para su implementación en la construcción de procedimientos almacenados, que pueden ser fuentes de información para reportes generados  a partir de las entradas o condiciones establecidas por el usuario.  El potencial en el uso de filtros dinámicos es muy amplio, en este pequeño artículo introductorio intentare dar una visión general del tema, haciendo hincapié en los elementos centrales que conllevan al desarrollo ordenado y claro de aplicaciones dinámicas. 

Una introducción sobre SQL dinámico

El uso de SQL Dinámico es una técnica basada en la construcción de secciones de código a partir de información suministrada por el usuario, lo cual puede resultar muy efectivo bajo ciertos escenarios de trabajo tanto a nivel de aplicaciones de escritorio y web; pero cuyo uso debe de manejarse con responsabilidad y tomando en cuenta el nivel de riesgo que nuestras aplicaciones pueden correr al estar expuestos a un tipo de ataque conocido como Inyección SQL (basado en la <<implantación>> de código dañino).

Supongamos que desarrollamos una pequeña consulta que devolverá el conjunto total de registros de una tabla específica, siendo el nombre de la tabla un valor suministrado por el usuario, utilizaremos la aplicación de ejemplo Northwind, mas adelante en este articulo se provee la dirección web para su descarga (en caso de tener SQL Server 2005).  La aplicación será la siguiente:

En este segmento de código podemos observar que a la variable @consulta se le asigna una sentencia preconstruida con el “cuerpo” o sintaxis central, que consiste en la selección de todas las columnas de una tabla aun no especificada, es en este punto donde entra en juego la utilización  de SQL dinámico ya que la tabla sobre la que se hará la consulta es suministrada directamente por el usuario, esta se almacena en la variable @tabla y luego es concatenada para formar la consulta. 

Un vez que tenemos la consulta construida requerimos que esta pueda ser ejecutada, SQL Server nos brinda dos formas de hacerlo :

·         Exec(@variableString) o Exec(Cadena T-SQL)

·         sp_executesql [@stmt =] stmt[     {, [@params =] N'@parameter_name  data_type [,...n]' }     {, [@param1 =] 'value1' [,...n] }]

En nuestro ejemplo anterior por asuntos ilustrativos utilizamos la función Exec, pero en términos prácticos esta presenta dos inconvenientes a tener en consideración, el primero es a nivel de seguridad puesto que su estructura hace que  las entradas realizadas por el usuario sean potencialmente más riesgosas a ataques de inyección SQL, y la segunda está relacionada con el rendimiento, ya que si deseáramos incluir dentro de un procedimiento almacenado código dinámico este obligaría a que se realizara una compilación cada vez que se ejecuta, perdiendo de esta manera una de las principales ventajas de los procedimientos almacenados como es la reutilización de los planes de ejecución.

Esta fuera del alcance de este articulo abordar en detalle el tema de inyección SQL, pero es muy importante que antes de utilizar técnicas basadas en el uso dinámico de SQL se investiguen los riesgos que este implica y las medidas de precaución que podemos utilizar, al respecto existe abundante información y me permito recomendar a forma de introducción dos artículos cuyos enlaces son los siguientes:

http://msdn2.microsoft.com/en-us/library/ms161953(SQL.90).aspx.

http://www.sommarskog.se/index.html

Que es un filtro dinámico? 

Un filtro dinámico es  una variante en el uso de  SQL Dinámico que les permite a los usuarios seleccionar los filtros o condiciones que necesitan, siendo de utilidad en aplicaciones web y de escritorio. 

Los filtros dinámicos son recomendados en situaciones donde es requerido que el propio usuario ingrese las condiciones o filtros para la información que necesita obtener y el numero de estos sea muy  grande , es preciso mencionar que la forma optima de trabajar en la construcción de código dinámico  es a través del uso de procedimientos almacenados que recolectan los filtros de  los usuarios a través de parámetros de entrada, permitiendo una mejor protección y reduciendo los riesgos de seguridad.

En el desarrollo por ejemplo de reportes es posible que decidamos utilizar código estático, construyendo procedimientos almacenados para evaluar las condiciones especificadas por el usuario, pero en este caso debemos tomar en cuenta que el número de procedimientos almacenados que se necesitan crear para N parámetros es equivalente a 2 ⁿ, en casos como este  nos puede resultar viable el uso de filtros dinámicos.

Y entonces, construyamos un filtro dinámico

Y ahora que hemos explorado un poco “del que, porque y para que puede” ser útil la implementación de esta técnica, podemos entrar en materia de programación.  La Base de Datos que utilizaremos en el ejemplo es Northwind, en caso de que tenga SQL Server 2005 puede descargarla de la siguiente dirección:

http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Supongamos que estamos bajo un escenario de trabajo en el que se nos asigno la tarea de desarrollar  un procedimiento almacenado que nos devuelva información relacionada con los clientes de la compañía, permitiendo que desde el sistema aplicativo, el cliente pueda seleccionar diferentes parámetros de filtro o simplemente no seleccionar ninguna condición.

Iniciamos con la construcción del procedimiento almacenado cuya información proviene de la tabla Customers (Clientes), al procedimiento lo llamaremos Pa_CustomerReport y los parámetros estarán constituidos por las columnas CustomerID,CompanyName,ContactName y City , a continuación presentamos el código:

Análisis del código :

Esta instrucción nos permite conocer si el Procedimiento Pa_CustomerReport existe y en caso de que la condición sea Verdadero lo elimina.  Este método es el más sencillo y lo aprendí del autor Itzik Ben-Gan, tiene como ventajas a favor su sencillez, exactitud y el hecho de no representar carga alguna  para el plan de ejecución.

Utilizamos las variables anteriores para almacenar la información a partir de la cual se construirá la consulta, facilitándonos el mantenimiento futuro al tener un código limpio y fácil de leer al desglosar de manera ordenada los elementos de la consulta como son:

@Selección –  Almacena la instrucción Select

@Origen-  Almacena la instrucción From

 @Condiciones-  Almacena la instrucción Where

La condición Where 1=1 se establece con el propósito de prevenir la necesidad  de tener que determinar dinámicamente si especificar una clausula Where  , en especial para cuando no hayan entradas o condiciones por parte del usuario.   Esta expresión siempre será Verdadera,  por lo que no genera efecto alguno en el rendimiento.

Se inicia un proceso de evaluación de cada uno de los parámetros de entrada del procedimiento, si el parámetro tiene un valor Not Null este pasara a formar parte de las condiciones.  Utilizamos la instrucción Quotename con el propósito de mejorar la lectura y facilidad en la escritura del código, además de permitir que en los parámetros del procedimiento almacenado se pueda incluir el uso de comillas (') sin que se genere error; por ejemplo si buscáramos a un cliente de nombre Let's Stop N Shop . Otra ventaja es que representa una mejora sustancial en la seguridad contra posibles ataques  por inyección SQL, esta protección .  La limitación que presenta el uso de Quotename en los parámetros es que  soporta un valor máximo de 128 caracteres.

Una vez que evaluamos todos los parámetros de entrada procedemos a concatenar las variables que conforman la estructura de la consulta y procedemos a ejecutarla.  En caso de que no se haya suministrado parámetro alguno el resultado corresponderá con todos los registros de la tabla Customers (clientes).

Conclusiones

Con este artículo he intentado dar una perspectiva inicial al tema de la construcción de código dinámico SQL, en específico al uso de filtros dinámicos.  El desarrollo de aplicaciones que permitan el ingreso de criterios o filtros por parte del usuario son comunes, pero su implementación debe ser manejada de forma cuidadosa y responsable, sabiendo distinguir los escenarios en donde es optimo su uso.  Como recomendaciones generales podemos mencionar:

·         Evaluar la conveniencia del uso de SQL Dinámico, y compararlo con la programación SQL estática a fin de establecer una decisión basada criterios técnicos, y no simplemente en un capricho o una mala práctica de programación.

·         La construcción de filtros dinámicos (y SQL Dinámico en general) se haga a través del uso de procedimientos almacenados y parámetros de entrada dada sus ventajas en aspectos de seguridad, rendimiento, y facilidad de mantenimiento.

·         Mantener cierta disciplina en la escritura de código, teniendo especial cuidado con el manejo de los espacios, ya que es probable que el procedimiento compile bien ; pero que genere error durante su ejecución. 

·         Nunca otorgar más privilegios de los necesarios, asignando convenientemente permisos de ejecución sobre los procedimientos almacenados (objetos sugeridos para el desarrollo de SQL Dinámico) y en algunos casos permisos Select .

·         En el caso de que desarrollemos Filtros dinámicos sobre aplicaciones web evitemos que al usuario se le presenten mensajes de error.

Mi presentación en Geeks

Entre los temas que mas escribiré esta sobre todo SQL Server 2005, y en especial T-SQL. Procurare una alta participación, no solo escribiendo, sino también haciendo comentarios sobre los artículos de mis colegas, y apoyando la comunidad.

Un Saludo a todos y hasta pronto.

email: geohernandez@gmail.com