Monitoreo de transacciones en tablas MS Sql Server con C#

SqlTableDependency es una libreria creada por Cristian del bianco, que nos permite monitorear las operaciones DML (insert, delete, update) efectuadas en una tabla de las bases de datos MS SQL SERVER (desde 2008 r2 en adelante), utilizando el service broker de la base de datos.

para el siguiente ejemplo utilizare

  • Base de datos sql server 2008 R2
  • Visual studio community 2015 para programar una app de consola con C#
  • SqlTableDependency. lo descargas con nugget o en codeplex

Ejercicio

Imaginémonos como supervisores de una empresa que se dedica a arrendar autos y que necesitamos recibir una notificación al instante cuando un auto ingresa o sale de la empresa, sabemos que cada vez que un auto ingresa a la automotora se agrega un registro a la tabla “autosDisponibles” gracias a que tenemos un pórtico lector de tags en nuestro portón que identifica cada vez que sale o ingresa un auto y no tenemos que ingresarlos a mano como en el siglo pasado………….

Base de datos

Para el ejemplo tenemos una base de datos de nombre “automotora” en ms sql server 2008 con la siguiente estructura:

Tabla autos: contiene los autos de la automotora.

Tabla AutosDisponibles: nos informa que autos se encuentran disponibles para arriendo en la columna “Disponible” con un “si o no” y tiene en la columna AutoId el id del automovil que se encuentra en la tabla autos.

el esquema es el siguiente:

Programando

  1. Creamos un proyecto de tipo “Aplicación de consola” en c# de nombre “automotora”.
  2. En nuestra aplicación vamos a proyectos e instalamos vía NuGet el proyecto SqlTableDependency de Christian del bianco, que nos permitirá monitorear las operaciones DML sobre la tabla autosDisponibles de nuestro ejemplo. toda la info y magia de este package en: https://tabledependency.codeplex.com
  3. En nuestra aplicación agregamos un nuevo elemento: Linq to classes, que nos creara el archivo DataClasses1.dbml. Una vez en el dbml agregamos una nueva conexión a nuestra base de datos automotora y arrastramos las dos tablas al modelo para mapearlas, este punto también puede ser con un simple conecctionString en el app.config a mano, acá depende del estilo de cada uno, a mi me gusta más esta forma.
  4. Habilitamos el service bróker en sql server

Si no hacemos esto no parte el monitoreo y obtenemos una excepción no controlada del tipo:

TableDependency.SqlClient.Exceptions.ServiceBrokerNotEnabledException en TableDependency.SqlClient.dll

El código

Correr el programa

si hacemos correr nuestro proyecto, lo que se ve en la consola es lo siguiente:

Si modificamos la columna disponible de nuestra tabla “autosDisponibles” de si a no,  una vez ejecutada la acción la podremos ver reflejada en pantalla.

modificamos de si a no el registro Id:2, en nuestro consola se puede visualizar que el automóvil salio de la empresa.

Volvamos a dejar disponible el registro…

y vemos como el auto volvió a la empresa.

Así que si quiere enviar un sms, email, un mensaje de chat, entre otros,  cada vez que salga o entre un vehículo a la automotora puedes programar esta acción en vez del simple Console.WriteLine que utilize en este ejemplo.

Saludos.