Service Broker. Qué es y utilización en un sistema de alertas.

En nuestro software de gestión se deben llevar a cabo una importante cantidad de procesos en tiempo real. Además, existen distintos usuarios trabajando sobre las mismas parrillas de datos, y cuando un usuario cambia algo, todos los demás deben verlo instantáneamente. Hasta ahora, para solucionar esto utilizábamos Socket Broadcast, de forma que cuando un registro era modificado, enviábamos un mensaje codificado por la red (utilizando un puerto), que era escuchado por los demás y hacía un refresco de su pantalla. La utilización de sockets nos ha dado algunos problemas, relativos a cortafuegos o antivirus por ejemplo, ya que detectan que se abre un puerto no esperado. Además, si por algún motivo nos salimos del programa incorrectamente, al volver a entrar nos sale un mensaje diciendo que no se puede abrir ese puerto porque ya está abierto.

ServiceBroker se introduce a partir de SQL 2005, y nos permite solucionar este tipo de problemas. No voy a entrar en todos los detalles sobre esta tecnología, lo que hace y cómo funciona, para eso puede consultarse más información en la web de Microsoft. Para nuestro caso, lo interesante es que nos permite monitorizar registros, de forma que cuando haya un cambio en cualquier campo del mismo, se nos envíe una notificación. Tiene la ventaja de notificarnos siempre que haya un cambio, aunque este no se realice desde nuestro software. Pongámonos pués manos a la obra.

Lo primero que debemos hacer es activar Service Broker en nuestra base de datos. Para ello, debemos ejecutar sobre ella el siguiente comando:

ALTER DATABASE [Database Name] SET NEW_BROKER
ALTER DATABASE [Database Name] SET ENABLE_BROKER;

Otros comandos interesantes pueden ser, desactivar ServiceBroker:

ALTER DATABASE [Database Name] SET DISABLE_BROKER;

O comprobar si está activado:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name';

Si en alguna ocasión el comando, debido a los permisos de la base de datos, no se ejecuta correctamente, deberemos precederlo de lo siguiente:

ALTER AUTHORIZATION ON DATABASE::[Database name] TO [sa];

Con esto ya tenemos la base de datos preparada para notificar estos cambios. Ahora nos vamos a nuestro software. Desde .NET, podemos detectar los cambios producidos a través del objeto SqlDependency.

Nos vamos a nuestra capa de datos (trabajamos en 3 capas), y añadimos lo siguiente (existen otras formas de hacerlo, pero esta es la que nosotros utilizamos):

Private Structure sDependency
  Dim Tabla As String
  Dim ClavePrimaria As String
  Dim Registro As String
  Dim SeparadorClavePrimaria As String
End Structure
Dim rDependency As sDependency
Public Event RegistroModificado(ByVal cTabla As String, ByVal cRegistro As String)
Public Event TablaModificada(ByVal cTabla As String)
Public Event SQLModificada(ByVal cTabla As String)

Lo que vamos a hacer aquí es crear una estructura genérica para indicar que es lo que queremos monitorizar y crearemos un evento de notificación de cambios para envíar a quien use esta capa de datos. En este caso vamos a crear 3, para poder monitorizar un registro en concreto, un registro que cumpla las condiciones de una sentencia SQL, o un cambio en cualquier lugar de una tabla. Esto nos dará mucho juego, ya que podremos monitorizar y estar informados de los cambios en los que más nos interese. Para ello, definimos a continuación las rutinas para activar la monitorización y los eventos que se dispararán.

' Para un registro

Public Sub ActivarNotificacionCambiosRegistro(ByVal cTabla As String, ByVal cClavePrimaria As String, ByVal cRegistro As String, Optional ByVal cSeparadorClavePrimaria As String = "'")
  Try
    rDependency.Tabla = cTabla
    rDependency.ClavePrimaria = cClavePrimaria
    rDependency.Registro = cRegistro
    rDependency.SeparadorClavePrimaria = cSeparadorClavePrimaria

    Dim cmd As New SqlClient.SqlCommand
    cmd.CommandText = "SELECT " + cClavePrimaria + " FROM dbo." + cTabla + " WHERE " + cClavePrimaria + "=" + cSeparadorClavePrimaria + cRegistro + cSeparadorClavePrimaria
    cmd.Connection = oCon
    Dim dependency As New SqlClient.SqlDependency(cmd)
    dependency.AddCommandDependency(cmd)
    AddHandler dependency.OnChange, AddressOf OnDependencyChangeRegistro
    cmd.ExecuteNonQuery()

  Catch ex As Exception
    Throw New Exception(ex.Message)
  End Try
 End Sub

Private Sub OnDependencyChangeRegistro(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)
  RaiseEvent RegistroModificado(rDependency.Tabla, rDependency.Registro)
End Sub

' Para cualquier registro de una tabla
Public Sub ActivarNotificacionCambiosTabla(ByVal cTabla As String, Optional ByVal cClavePrimaria As String = "")
  Try
    rDependency.Tabla = cTabla
    If cClavePrimaria = "" Then
      rDependency.ClavePrimaria = Me.ClavePrimaria(cTabla)
    Else
      rDependency.ClavePrimaria = cClavePrimaria
    End If
    rDependency.Registro = ""
    rDependency.SeparadorClavePrimaria = ""

    Dim cmd As New SqlClient.SqlCommand
    cmd.CommandText = "SELECT " + rDependency.ClavePrimaria + " FROM dbo." + cTabla
    cmd.Connection = oCon
    Dim dependency As New SqlClient.SqlDependency(cmd)
    AddHandler dependency.OnChange, AddressOf OnDependencyChangeTabla
     cmd.ExecuteNonQuery()

  Catch ex As Exception
    Throw New Exception(ex.Message)
  End Try
End Sub

Private Sub OnDependencyChangeTabla(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)
  RaiseEvent TablaModificada(rDependency.Tabla)
End Sub


' Para un registro cualquiera de los que entren en una sentencia SQL

Public Sub ActivarNotificacionCambiosSQL(ByVal cSQL As String)
  Try
    rDependency.Tabla = cSQL
    rDependency.ClavePrimaria = ""
    rDependency.Registro = ""
    rDependency.SeparadorClavePrimaria = ""

    Dim cmd As New SqlClient.SqlCommand
    cmd.CommandText = cSQL
    cmd.Connection = oCon
    Dim dependency As New SqlClient.SqlDependency(cmd)
    AddHandler dependency.OnChange, AddressOf OnDependencyChangeSQL
    cmd.ExecuteNonQuery()

  Catch ex As Exception
    Throw New Exception(ex.Message)
  End Try
End Sub

Private Sub OnDependencyChangeSQL(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)
  RaiseEvent SQLModificada(rDependency.Tabla)
  Call ActivarNotificacionCambiosSQL(rDependency.Tabla)
End Sub

Y con esto, ya tenemos lista nuestra capa de datos genérica con soporte para ServiceBroker. Leyendo un poco el código, necesita poca explicación. Para ver como se utiliza, vamos a hacerlo con un caso real que hemos implementado. En nuestro sistema, nos han pedido crear una serie de alertas que se disparan cuando suceden ciertos eventos. En esos casos, hay que notificar inmediatamente a uno o varios usuarios para que tomen ciertas decisiones. Para conseguir esto, lo primero que hacemos es definir una tabla para almacenar las alertas.

Con la estructura definida para esta tabla, podemos crear alertas relativas a ciertos registros, y con el campo acción podremos realizar diferentes acciones al pulsar sobre la alerta (esto lo hacemos en otro formulario, un visor de alertas). Desde abrir un registro para consultarlo o modificarlo, hasta realizar ciertas acciones de forma automática, pasando por tomar decisiones (alertas de decisión) que impliquen realizar ciertas modificaciones, o por la generación automática de informes programados desde un servicio.

Una vez definida, y con la utilización de ServiceBroker, sólo tendremos que, en los supuestos en los que los eventos deseados se producen, escribir en la tabla de alertas. En el software tenemos un formulario siempre abierto y visible (trabajamos con 2 pantallas) en el que se muestran las alertas pendientes que tiene el usuario logado. Utilizando ServiceBroker, ese formulario se actualizará en tiempo real, y el código es tan sencillo como el que se muestra a continuación.

  Dim WithEvents oCon As New VB3.Conexion

  Private Sub FrmAlertas_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Try
      Call CargarAlertas()

      Dim SQL_Monitorizacion As String
      SQL_Monitorizacion = "SELECT id FROM dbo.[Alertas.Alertas] WHERE cod_usuario='" + Constantes.Login.Codigo + "' AND fecha_lectura=''"
      oCon.ActivarNotificacionCambiosSQL(SQL_Monitorizacion)

    Catch ex As Exception
      oForm.ControlErrores(ex, Me.Name, "FrmAlertas_Load")
    End Try
  End Sub


  Private Sub SQLCambia(ByVal cSQL As String) Handles oCon.SQLModificada
    Dim oMetodoOriginal As MethodInvoker
    oMetodoOriginal = New MethodInvoker(AddressOf CargarAlertas)
    Me.Invoke(oMetodoOriginal)
  End Sub


  Public Sub CargarAlertas()
    Try
      Dim dt As New Data.DataTable, SQL As String

      SQL = "SELECT * FROM [Alertas.vAlertas] WHERE cod_usuario='" + Constantes.Login.Codigo + "' AND fecha_lectura=''"
      dt = oCon.GetDataTable(SQL)

      dg.AutoGenerateColumns = False
      dg.DataSource = dt

      dt.Dispose()

    Catch ex As Exception
      oForm.ControlErrores(ex, Me.Name, "CargarAlertas")
    End Try
  End Sub

Este sistema es muy versátil, muy fácil de utilizar, y nos puede servir para monitorizar agendas, parrillas de datos, para disparar ciertos servicios cuando se produzca algún cambio, ... El límite lo pone nuestra imaginación. Espero que este post os haya sido de utilidad. Hasta la próxima.