Mezclando resultados entre Bases de Datos Microsoft SQL Server 2008 de forma realmente desconectada

enero 7, 2010
By

¿Alguna vez han necesitado enviar el resultado de una consulta sql por correo electrónico para que luego sea mezclado/integrado en otra base de datos?

El escenario es el siguiente:

  • Dos clientes A y B quieren intercambiar información (en una sola dirección) pero la única conexión que tienen es a través de correo electrónico. Cada uno se conecta a su propia base de datos SQL y el objetivo es enviar en una sola dirección actualizaciones periódicas de información escogida, digamos un conjunto de tuplas de una tabla que cumplen cierto criterio definido sin perder generalidad por el usuario A.

Si los servidores de bases de datos tuviesen algún tipo de conexión aunque fuese ocasional o existiese un tercer elemento con conexión a ambas bases de datos, éste no fuese un problema ya que existe una gamma de tecnologías que facilitan esta tarea bajo la condición de conexión y cito por poner un ejemplo Microsoft Sync Framework.

Luego de hacer una búsqueda extensiva pero infructífera por Internet, me decidí a realizar mi propia solución y resulta que la implementación es más fácil de lo que yo esperaba.

Los requerimientos son los siguientes:

  • Microsoft SQL Server 2008 (Cualquier edición incluyendo la Express)
  • .NET Framework 3.5

PASO 1: Definición de la Base de Datos

Vamos a utilizar como ejemplo una tabla de Productos. El escenario real podría ser una vez que finalice el día, el usuario A envía todos los cambios (Inserciones, Actualizaciones) al usuario B. Note que no hay Eliminaciones; en este ejemplo, los productos tienen una columna “Eliminado” de tipo Booleano que cumple esa función (digamos que no se borra nada para tener trazabilidad).

CREATE TABLE [dbo].[Productos](
	[ID] [uniqueidentifier] NOT NULL
		CONSTRAINT [DF_Productos_ID]  DEFAULT (newid()),
	[Nombre] [nvarchar](50) NOT NULL,
	[Precio] [decimal](18, 2) NULL,
	[Eliminado] [bit] NOT NULL
		CONSTRAINT [DF_Productos_Eliminado]  DEFAULT ((0)),
	[ModificadoEn] [datetime] NOT NULL
	    CONSTRAINT [DF_Productos_ModificadoEn]  DEFAULT (getdate()),
	 CONSTRAINT [PK_Productos] PRIMARY KEY CLUSTERED
	(
		[ID] ASC
	) ON [PRIMARY]
) ON [PRIMARY]

 

PASO 2: Creación del procedimiento almacenado que se encarga de realizar la mezcla

La idea es crear un procedimiento almacenado que reciba como parámetro el listado de elementos que se quieren mezclar y realice de forma eficiente la mezcla. Este es el paso que requiere SQL Server 2008.

El parámetro “elementos que se quieren mezclar” sería en .NET un DataTable que contendría los DataRow que se quieren mezclar. Con SQL Server 2008 podemos pasar como parámetro de un procedimiento almacenado un DataTable, pero para ello en el servidor hay que definir un tipo TABLA (Ver en los SQL Books Online, User-Defined Table Types) que exprese la forma de los elementos del DataTable:

CREATE TYPE Producto AS TABLE (
	[ID] [uniqueidentifier] NOT NULL,
	[Nombre] [nvarchar](50) NOT NULL,
	[Precio] [decimal](18, 2) NULL,
	[Eliminado] [bit] NOT NULL,
	[ModificadoEn] [datetime] NOT NULL
) 

Una vez creado el tipo, se puede definir el código del procedimiento almacenado de la siguiente forma:

CREATE PROCEDURE [dbo].[MezclaProductos]
	(@Productos AS dbo.Producto READONLY)
AS
BEGIN
	-- Código que Mezcla
END

De manera trivial y costosa podemos hacer la mezcla iterando por los elementos de la tabla que se nos pasa como parámetro y en dependencia de si el elemento existe o no (realizando un SELECT/WHERE) realizar una inserción o una actualización. PERO ESTA FORMA ES INEFICIENTE!

Por suerte para nosotros SQL Server 2008 nos entrega un nuevo comando: El MERGE (Ver en los SQL Books Online, Inserting, Updating, and Deleting Data with MERGE)

El procedimiento almacenado quedaría así:

CREATE PROCEDURE [dbo].[MezclaProductos]
	(@Productos AS dbo.Producto READONLY)
AS
BEGIN
   MERGE Productos as target
   USING @Productos AS source
   ON( target.ID = source.ID )
   WHEN MATCHED THEN
	UPDATE SET
		target.Nombre = source.Nombre,
		target.Precio = source.Precio,
		target.Eliminado = source.Eliminado,
		target.ModificadoEn = source.ModificadoEn
   WHEN NOT MATCHED THEN
	INSERT  (ID, Nombre, Precio, Eliminado, ModificadoEn )
	VALUES  ( source.ID, source.Nombre, source.Precio,
			  source.Eliminado, source.ModificadoEn );

END

Como se puede observar, el Servidor SQL se encargará de realizar la mezcla por lo que podemos esperar que el “cómo se mezcla” estará optimizado en cuanto a eficiencia; solo nos tenemos que encargar de definir el “qué hacer” en para los casos de coincidencias y no coincidencia entre las tuplas existentes y las nuevas que queremos mezclar.

IMPORTANTE: Para realizar réplicas entre una o más bases de datos es necesario que el identificador de la tabla sea único y que esté soportado en un contexto distribuido. En nuestro caso hemos utilizado un GUID (identificador único de 128 Bits) para garantizar la unicidad. Note que si utiliza un autonumérico, como cada usuario tiene su propia base de datos y trabaja en paralelo pues se repetirán necesariamente los identificadores involucrando un problema grave para la sincronización.

PASO 3: El programa en C# (LINQ to SQL)…

Antes de mostrar el código, quiero comentar que LINQ to SQL no soporta el tipo de parámetro trabla (DbType Structured) por lo que hay que crear el SqlCommand manualmente.

Primero, les presento un método extensor que ejecuta una consulta IQueryable y devuelve el resultado dentro de un DataTable:

public static DataTable FillDataTable<T>(this System.Data.Linq.DataContext ctx,                                          string TableName, IQueryable<T> query)
{
    if (query == null)
    {
        throw new ArgumentNullException("query");
    }

    IDbCommand cmd = ctx.GetCommand(query);
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = (SqlCommand)cmd;
    DataTable dt = new DataTable(TableName);

    try
    {
        cmd.Connection.Open();
        adapter.FillSchema(dt, SchemaType.Source);
        adapter.Fill(dt);
    }
    finally
    {
        cmd.Connection.Close();
    }
    return dt;
}

Necesitamos convertir el resultado a un DataTable para poder salvar el contenido a XML, compactarlo en .ZIP si se desea y enviarlo por correo. El código para realizar esta operación sería el siguiente:

// Creando Contexto de Datos (LINQ to SQL)
var context = new ProductosDataContext();

// Extrayendo los Productos que se han modificado hoy
var fechaInicio = DateTime.Today;
var fechaFin = DateTime.Today.AddDays(1).AddMilliseconds(-1);
var consulta = from producto in context.Productos
               where producto.ModificadoEn >= fechaInicio
                     && producto.ModificadoEn <= fechaFin
               select producto;

// Extrayendo DataTable
var resultado = context.FillDataTable("Productos", consulta);

// Salvando a XML
resultado.WriteXml("Productos.xml");

La segunda parte del código (la que ejecuta el cliente B) sería la siguiente:

// Cargando DataTable
var context = new ProductosDataContext();
var resultado = new DataTable("Productos");
resultado.ReadXml("Prodctos.xml");

// Ejecutando procedimiento almacenado para mezcla
var cmdMezcla = new SqlCommand("MezclaProductos",                                (SqlConnection)context.Connection);
cmdMezcla.CommandType = CommandType.StoredProcedure;

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@Productos";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = resultado;
cmdMezcla.Parameters.Add(parameter);
using (context.Connection)
{
    context.Connection.Open();
    var mergeResult = cmdMezcla.ExecuteNonQuery();
}

Y de esta forma se realiza la actualización. Simple ¿eh?

Uso con Múltiples Tablas

Para utilizar esta propuesta con varias tablas relacionadas hay que realizar un orden topológico sobre dichas relaciones y en ese orden es que tiene que realizarse la importación de los datos (un fichero XML por cada Tabla).

Por ejemplo, si tuviésemos dos tablas: Productos y Ordenes, cada elemento de la orden tendría un identificador de producto, por lo que si intentamos insertar una tupla en la tabla de Ordenes antes de que exista el Producto referenciado pues SQL lanzaría una excepción producto de que no se cumpliría la restricción de la llave foránea.

Tampoco se ha hablado de Transacciones, pero en dependencia de las necesidades prácticas de cada escenario deberían considerarse para cuidar la integridad de la base de datos (no querríamos una actualización aplicada parcialmente).

Conclusiones

Esta propuesta constituye una forma rápida y sencilla de realizar actualizaciones de datos de forma desconectada (utilizando por ejemplo correo electrónico) cuando estas actualizaciones son en una dirección.

Para orquestaciones más complejas donde hay que mezclar en ambos sentidos pues recomendaría el uso de Microsoft Sync Framework o las tantas configuraciones para Replica y Sincronización que brinda propiamente el Servidor SQL.

Tags: , ,

3 Responses to Mezclando resultados entre Bases de Datos Microsoft SQL Server 2008 de forma realmente desconectada

  1. rherrera on febrero 23, 2011 at 8:23 pm

    Interesante articulo, gracias por tu aportacion. Tienes experiencia en Microsoft Sync Framework, tendras un minitutorial como este?
    - saludos

  2. Ing. Reynier R. Marrero Brito on marzo 2, 2017 at 8:21 am

    UMMM, muy buena idea esto, creo que mezclare eso con la publicacion de los XMLs en un server web para que sean leidoslos datos desde allí, claro que con la debida seguridad.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

*

Acerca del autor...

Alejandro Tamayo

Web: http://www.linkedin.com/in/atamayocastillo
Alejandro Tamayo
Professor, Researcher, Developer, Consultant and technology enthusiast. Master of Science (MSc) in Computer Science and member of Weboo Research Group.Leer completo