Agrupando sumas mediante particiones en SQL

Cuando el cliente te pide una funcionalidad relacionada con la generación de informes, sabes que vas a tener que enfrentarte a un concepto que, a menudo, provoca verdaderos dolores de cabeza cuando se trata de realizar consultas SQL: los valores agrupados.

No entraremos en detalle en el concepto de agrupación, ya que cualquier manual de SQL le dedica secciones enteras a este concepto. Trataremos un caso particular de agrupación: la agrupación de sumas.

Read the rest of this post »

Crear un campo autoincrementable en ORACLE

Para los que nos iniciamos en los Gestores de Bases de Datos con SQL Server, la transición a ORACLE resulta siempre más dura que cuando se realiza el camino inverso. Realizando una analogía simple, en mi caso podría decir que T-SQL es a PL/SQL lo que C# es a C++. Más complicación, más potencia… y un sistema más “tiquismiquis” que los desarrollados por Microsoft.
SQL Server proporciona una forma muy sencilla de crear campos autonuméricos, generalmente utilizados en los IDs de las claves primarias de las tablas. Para crear un campo autoincrementable, lo único que teníamos que hacer era editar las propiedades de la columna que queríamos convertir en autoincrementable, acceder a sus propiedades y seleccionar la opción [Especificación de Identidad] marcando la casilla (Identidad) e informando del número de inicio del índice (Inicialización de identidad) y el número de unidades que se incrementará cada vez que se intente insertar un registro (Incremento de identidad).

Oracle nos pone las cosas un poco más difíciles, obligándonos a realizar dos pasos para realizar el mismo procedimiento: crear una secuencia y crear un trigger.

Read the rest of this post »

Utilizar la Base de Datos como repositorio de imágenes (II)

En un artículo anterior aprendimos a almacenar una imagen en nuestra base de datos SQL Server. Ahora vamos a obtener la imagen que almacenamos previamente utilizando como dirección de la imagen un formulario aspx al que le pasaremos el identificador de la imagen a través de la QueryString.

Comenzaremos creando un nuevo formulario, al que llamaremos showImage.aspx. En el formulario, crearemos un nuevo método al que llamaremos ObtenerImagen, que recibirá como parámetro un entero (el ID de la imagen) y devolverá una secuencia de bytes (un byte[]).

byte[] ObtenerImagen(int idImagen)
{
}

Lo primero que hará este método será declarar una cadena de conexión y una sentencia SELECT que devuelva la imagen cuyo ID le pasamos. Para ello, declararemos ambas cadenas de texto.


string selectCommandText = "SELECT Fichero FROM Imagen WHERE IdImagen = " + Convert.ToString(idImagen);
string ConnectionString = @"Data Source=DANIGARCIA\SQLSERVER2005;Initial Catalog=TestDB;Persist Security Info=True;User ID=dani;Password=c0ntr4s3n14";

A continuación podemos hacer dos cosas: crear un SqlCommand y ejecutar la sentencia SELECT, o bien declarar un DataSet y un SqlDataAdapter y dejar que sea éste último objeto el que nos proporcione los datos. Optaremos por la segunda opción, que pese a ser menos óptima, resulta mucho más cómoda :) 


// Declaramos un DataSet y un SqlDataAdapter
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(selectCommandText, selectConnectionString);

// Rellenamos el DataSet con la consulta y la cadena de conexión anteriores
adapter.Fill(ds);

Llegados a este punto, ya deberíamos tener almacenada en el DataSet la imagen que estábamos buscando. Por lo tanto, lo único que deberemos hacer será serializarla y devolverla, realizando las comprobaciones pertinentes:


if ((ds != null) && (ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
{
if (!ds.Tables[0].Rows[0].IsNull("Fichero"))
{
return ((byte[])(ds.Tables[0].Rows[0]["Fichero"]));
}
}

return null;

Llegados a este punto, codificaremos el contenido del Page_Load de nuestro aspx, comprobando la QueryString y pasándole el parámetro a nuestra función ObtenerImagen, declarando, en primer lugar, un array de Bytes para almacenar el resultado:


protected void Page_Load(object sender, EventArgs e)
{
byte[] bImagen = null;

if (!string.IsNullOrEmpty(Convert.ToString(Request.QueryString["id"])))
{
bImagen = ObtenerImagen(Convert.ToInt32((Convert.ToString(Request.QueryString["id"]))));
}

Si el resultado obtenido no es nulo, modificamos la respuesta para que sea de tipo imagen, haciendo lo siguiente:


if(bImagen != null)
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "image/jpeg";
Response.BinaryWrite(bImagen);
Response.End();
}
}

Con lo cual, si invocamos la ruta a través del ID, obtendremos directamente una imagen:


Esto es una prueba <br />
<img src="../aspx/showImage.aspx?id=1" />

Utilizar la Base de Datos como repositorio de imágenes (I)

En cierto proyecto necesitaba mostrar una serie de imágenes que el usuario debía insertar desde su equipo local. En un principio pensé enviar al servidor las imágenes, guardar la ruta relativa en Base de Datos y, cuando fuese necesario acceder a las imágenes, utilizar dicha ruta para acceder a la imagen. Pero no era tan sencillo. Por desgracia, no teníamos permiso de escritura en el disco duro, por lo que tenía que juguetear con las imágenes sin que éstas existieran físicamente. ¿Cómo? Almacenando y recuperando las imágenes de base de datos.

Guardando una imagen en SQL Server

Para empezar, veremos cómo almacenar en base de datos una imagen. Crearemos, desde el SQL Server Management Studio, una nueva tabla que tendrá tres campos:

09111201 Creada la tabla, crearemos una página que, a partir de la ruta de la imagen, la inserte en base de datos. Podemos utilizar un Input File o un control similar para indicarle la ruta, pero aquí indicaremos únicamente el código necesario para subir la imagen dada su ruta física. Lo que haremos a continuación será lo siguiente:

string ConnectionString = @"Data Source=DANIGARCIA\SQLSERVER2005;Initial Catalog=TestDB;Persist Security Info=True;User ID=dani;Password=c0ntr4s3n14";
string CommandString = "INSERT INTO Imagen(NombreImagen, Fichero) VALUES (@NombreImagen, @Fichero)";
  byte[] bImagen = null; System.IO.MemoryStream ms = new System.IO.MemoryStream();  System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(@"C:\TEMP\inicio.jpg"); if (bmp != null) { bmp.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); bImagen = ms.ToArray(); ms.Close(); }  
  SqlConnection conexion = new SqlConnection(ConnectionString);  SqlCommand command = new SqlCommand(CommandString, conexion);  
  SqlParameter nombre = new SqlParameter("@NombreImagen", "inicio.jpg"); SqlParameter imagen = new SqlParameter("@Fichero", bImagen);  
  conexion.Open();  command.Parameters.Add(nombre); command.Parameters.Add(imagen); command.ExecuteNonQuery();  conexion.Close();

Hecho esto insertaremos una imagen en una tabla de la base de datos. 09111202 Más adelante aprenderemos cómo recuperarla y referenciarla directamente a través de una URL con extensión aspx, es decir, invocar una ruta de tipo getImage.aspx?Id=xxx.

Transacciones en ADO.NET (II): Activando las Transacciones Distribuidas mediante MSDTC

Anteriormente hemos aprendido a utilizar entornos transaccionales o Transaction Scopes. El ejemplo anterior mostraba lo que se conoce como una ‘transacción ligera’ que afecta a varias tablas de una misma fuente de datos. Sin embargo, en numerosas ocasiones se nos presentará la dificultad añadida de trabajar con múltiples fuentes de datos localizadas incluso en puntos geográficos diversos.

Para solventar esta dificultad, el propio Windows nos ofrece la posibilidad de utilizar las llamadas Transacciones distribuidas. Por defecto aparecen deshabilitadas, por lo que a continuación, mostraremos cómo activarlas. Una vez activadas, utilizaremos la clase TransactionScope como si se tratara de una transacción ligera, dejando en manos del sistema operativoel control de la propia transacción.

Read the rest of this post »

Abstracción de Datos (V): Aplicando el patrón Abstract Factory con C# y ADO.NET

Por norma general, un proyecto estará “atado” a una fuente de datos en particular (SQL Server, MySQL, Oracle…). Sin embargo, hay veces que esto no será así: la fuente de datos podría cambiar en cualquier momento, por lo que deberíamos ser capaces de, en la medida de lo posible, abstraer nuestra aplicación de nuestra base de datos.

Previamente comentábamos la flexibilidad otorgada por el patrón de diseño Abstract Factory. Hoy veremos una aplicación práctica, orientada precisamente a obtener práctica independencia de la fuente de datos mediante la utilización de clases comunes a todas las fuentes de datos.

Por norma general utilizamos clases específicas para lograr interactuar con una fuente de datos en concreto (por ejemplo, usaremos un objeto de la clase SqlConnection cuando trabajemos con SQL Server). Éstas clases específicas están optimizadas para trabajar con unos esquemas determinados, aportando un importante incremento del rendimiento de la aplicación. Pero como todo en esta vida, no es gratis. ¿Cuál es el precio? La portabilidad.

Read the rest of this post »

Comprobar la existencia de una tabla en SQL Server

Cuando tratamos con un procedimiento almacenado en el que utilizamos tablas temporales, es posible que un fallo en la consulta arruine el flujo normal del programa, haciendo que la eliminación de la propia tabla temporal no se realice.

Este caso en concreto puede provocar un error en la próxima ejecución del procedimiento, ya que la creación de la tabla temporal provocará un fallo si esta ya existe.
Para intentar subsanar esta posibilidad, podemos comprobar, antes de crear la tabla temporal, si esta ya existe. Para ello haremos lo siguiente:

IF OBJECT_ID('tempdb..#UsuarioTemp') IS NOT NULL
BEGIN
DROP TABLE #UsuarioTemp;
END

Esto comprobará la existencia de la tabla temporal, y si existe, la eliminará. Posteriormente podremos incluir el código de creación de la tabla.

El el caso de que queramos comprobar la existencia de una tabla real (no temporal) de la base de datos, utilizaremos el siguiente código T-SQL:

IF OBJECT_ID('USUARIO') IS NOT NULL
BEGIN
SELECT 'SELECT * FROM USUARIO';
END
ELSE
BEGIN
CREATE TABLE USUARIO(
[IdUsuario] [int],
[Nombre] [varchar](100),
[Apellido1][varchar](100),
[Apellido2][varchar](100),
[Login][varchar](100),
[Password][varchar](100),
)
END

Con lo cual se realizará una consulta sobre la tabla si ésta existe, o la creará en caso contrario. Queda a voluntad del programador las posibilidades de la existencia o no de las tablas.

Tratamiento de cadenas en SQL Server

En cualquier lenguaje de programación es tarea obligada poseer una manipulación de cadenas fluida.

Transact SQL proporciona una variedad bastante amplia de funciones para llevar a cabo tal tarea. Algunas de las operaciones que podemos realizar son concatenación, obtención de subcadenas, pasar a mayúsculas o minúsculas, eliminar espacios en blanco… incluso detectar la similitud entre expresiones.

A continuación veremos algunas de ellos y aplicaremos algunos ejemplos:

Read the rest of this post »

Eliminar la hora a una fecha en SQL Server 2005

Hay veces que pese a recibir un parámetro en formato fecha que almacene dia y hora, nos interese únicamente almacenar (o recuperar) la parte correspondiente a la fecha.
Aquellos que hayan realizado alguna vez comparaciones de fechas situadas en un intervalo probablemente sepan de qué hablo: ¿Comparar entre un día a las 00:00:00 y el siguiente a las 00:00:00? ¿Entre el mismo día entre las 00:00:00 y las 23:59:59?

Una forma simple de ahorrarnos dolores de cabeza sería almacenar la fecha sin hora. Para ello haremos uso de los distintos formatos de fecha que nos permite promocionar SQL Server y escribiríamos algo así:

 UPDATE NombreTabla SET CampoFecha = convert(datetime, convert(varchar(10), CampoFecha, 103))  

Con lo cual, almacenaremos nuestra fecha en formato DD:MM:AAAA 00:00:00.

Realizando una transacción en SQL Server

Por norma general, me gusta codificar la lógica de acceso a datos en programa, en lugar de en procedimientos almacenados en base de datos. Esta aproximación no es muy óptima (como se suele decir, al César lo que es del César y a Dios lo que es de Dios), ya que el entorno transaccional estará mejor gestionado por el SGBD que por el código .NET.
Si queremos trasladar la responsabilidad de gestionar la transacción al SGBD, deberemos saber en primer lugar que se declara del siguiente modo:

BEGIN TRAN
 -- Ejecución de sentencias
COMMIT TRAN

Como imaginaremos, la primera sentencia “arranca” la transacción, mientras que la última la compromete. Pero ¿y si ocurre un error en alguna de las sentencias? ¿Cómo lo detectamos?
En el siguiente ejemplo, utilizaremos variables enteras para trazar los fallos y realizar un ROLLBACK en el caso de que la transacción falle.

Se declarará una variable de tipo INT por cada sentencia a ejecutar, y se inician a 0. Se comprueba, después de cada sentencia ejecutada, la variable @@Error, y se almacena en las variables anteriormente creadas. Si es distinta de 0, hay un error, por lo que se añade información al mensaje de error.
Al final de la transacción, si todas las variables son 0, se hace el COMMIT. Si una de ellas es distinta de cero, significará que existe un error y se hace el Rollback, mostrando el mensaje de error.


DECLARE @CadenaError varchar(1000);
 DECLARE @CodigosError varchar(1000);
 DECLARE @ErrorSentencia1 int;
 DECLARE @ErrorSentencia2 int;
 DECLARE @ErrorSentencia3 int;

 SET @ErrorSentencia1 = 0;
 SET @ErrorSentencia2 = 0;
 SET @ErrorSentencia3 = 0;

 SET @CadenaError = 'Error al realizar actualización: ';
 SET @CodigosError = 'Códigos de error: ';

 -- Inicio de la transacción
 BEGIN TRAN

 -- Primera actualización
 UPDATE Usuario SET Nombre = 'Elena' WHERE Nombre = "Helena";

 -- Almacenamos el posible código de error
 SET @ErrorSentencia1 = @@Error;
 IF (@ErrorSentencia1 <> 0)
 BEGIN
     SET @CadenaError = @CadenaError + 'Actualización 1; '
     SET @CodigosError = @CodigosError + CAST(@ErrorSentencia1 AS VARCHAR(10)) + '; ';
 END

 -- Segunda actualización
 UPDATE Perfil SET Nombre = 'Administrador' WHERE Nombre = "Admin";

 -- Almacenamos el posible código de error
 SET @ErrorSentencia2 = @@Error;
 IF (@ErrorSentencia2 <> 0)
 BEGIN
     SET @CadenaError = @CadenaError + 'Actualización 2; '
     SET @CodigosError = @CodigosError + CAST(@ErrorSentencia2 AS VARCHAR(10)) + '; ';
 END

 -- Tercera actualización
 UPDATE UsuarioPerfil SET CodigoUsuario = 1 WHERE CodigoPerfil < 3;

 -- Almacenamos el posible código de error
 SET @ErrorSentencia3 = @@Error;
 IF (@ErrorSentencia3 <> 0)
 BEGIN
     SET @CadenaError = @CadenaError + 'Actualización 3; '
     SET @CodigosError = @CodigosError + CAST(@ErrorSentencia3 AS VARCHAR(10)) + '; ';
 END

 -- En ausencia de errores, comprometemos la transacción
 IF (@ErrorSentencia1 = 0) AND (@ErrorSentencia2 = 0) AND (@ErrorSentencia3 = 0)

BEGIN
     COMMIT TRAN;
     PRINT('Transacción realizada con éxito');
 END
 ELSE
 BEGIN
     ROLLBACK TRAN;
     PRINT('Transacción abortada');
     PRINT(@CadenaError);
     PRINT(@CodigosError);
 END

Con todo esto, obtendremos suficiente información (esperemos) como para detectar donde está el fallo.