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.
Entradas
