.NET basado en eventos:cómo usar notificaciones de consulta en SQL Server para monitorear cambios en la base de datos

¿Cómo verifica si hay nuevos registros en una base de datos y reacciona a los nuevos datos?

Realmente solo tienes dos opciones:

1. Encuesta de cambios cada X segundos
2. Utilice notificaciones de consulta para recibir una notificación cuando se inserten nuevos datos

En este artículo, le mostraré cómo configurar las notificaciones de consulta para que su aplicación reciba notificaciones automáticas de SQL.

Hay una forma integrada de hacer esto en .NET llamada SqlDependency. Esto funciona, pero no está bien diseñado:es una API difícil de usar. Hay múltiples opciones de código abierto que son más fáciles de usar. En este artículo estoy usando la biblioteca de código abierto llamada SqlTableDependency.

1:agregar una referencia a SqlTableDependency

Para obtener más detalles sobre cómo funciona esto:https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency

2 – Crea una tabla y un modelo

Tabla de mensajes

CREATE TABLE [dbo].[Messages](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Text] [nvarchar](280) NOT NULL,
	[UserId] [int] NOT NULL,
	[Timestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_MessageQueue] PRIMARY KEY CLUSTERED 
 ( [Id] ASC )
)
Code language: SQL (Structured Query Language) (sql)

Modelo de mensaje

public class Message
{
	public int Id { get; set; }
	public string Text { get; set; }
	public string UserId { get; set; }
	public DateTime Timestamp { get; set; }
}
Code language: C# (cs)

3 – Habilitar Service Broker y configurar permisos

Service Broker es una característica de SQL Server que proporciona un sistema de cola de mensajes. Las notificaciones de consulta funcionan creando activadores en los cambios de la base de datos y escribiendo los cambios en una cola de mensajes. Su aplicación externa se suscribe a la cola de mensajes y Service Broker es responsable de enviar nuevos mensajes a su aplicación.

Habilitar agente de servicios

ALTER DATABASE [MessageDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [MessageDatabase] SET ENABLE_BROKER; 
ALTER DATABASE [MessageDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE
Code language: SQL (Structured Query Language) (sql)

Configurar permisos

Este script SQL creará un usuario para iniciar sesión en la base de datos adecuada y establecerá todos los permisos necesarios para que SqlTableDependency funcione.

USE [DATABASENAME]

CREATE USER [makolyte] FOR LOGIN [PCName\LoginName]
WITH DEFAULT_SCHEMA=[dbo]


GRANT ALTER to [makolyte]
GRANT CONNECT to [makolyte]
GRANT CONTROL to [makolyte]
GRANT CREATE CONTRACT to [makolyte]
GRANT CREATE MESSAGE TYPE to [makolyte]
GRANT CREATE PROCEDURE to [makolyte]
GRANT CREATE QUEUE to [makolyte]
GRANT CREATE SERVICE to [makolyte]
GRANT EXECUTE to [makolyte]
GRANT SELECT to [makolyte]
GRANT SUBSCRIBE QUERY NOTIFICATIONS to [makolyte]
GRANT VIEW DATABASE STATE to [makolyte]
GRANT VIEW DEFINITION to [makolyte]
Code language: SQL (Structured Query Language) (sql)

Obtener los permisos correctos es una de las partes más difíciles de hacer que esto funcione. Si no lo haces bien, te encontrarás con este error:

Descargo de responsabilidad:si va a configurar esto en un entorno de producción, debe trabajar con el administrador de la base de datos para garantizar que su seguridad y permisos estén configurados correctamente.

4 – Escuche los eventos de cambio de tabla

Cree una clase de repositorio que encapsule usando SqlTableDependency

Este repositorio escucha las notificaciones de consulta de SqlTableDependency y activa el evento OnNewMessage cuando se han insertado datos.

Esto implementa IDisposable para que pueda deshacerse de SqlTableDependency. Esto es importante porque destruye los recursos de Service Broker que generó cuando se inició.

using MessageQueueService.Model;
using System;
using TableDependency.SqlClient;
using TableDependency.SqlClient.Base.EventArgs;

namespace MessageQueueService.Data
{
    public delegate void NewMessageHandler(Message message);

    public class MessageRepository : IDisposable
    {
        private SqlTableDependency<Message> sqlTableDependency;

        public void Start(string connectionString)
        {
            sqlTableDependency = new SqlTableDependency<Message>(connectionString, "Messages");
            sqlTableDependency.OnChanged += HandleOnChanged;
            sqlTableDependency.Start();
        }
        public event NewMessageHandler OnNewMessage;
        private void HandleOnChanged(object sender, RecordChangedEventArgs<Message> e)
        {
            if(e.ChangeType == TableDependency.SqlClient.Base.Enums.ChangeType.Insert)
            {
                OnNewMessage?.Invoke(e.Entity);
            }
        }

        #region IDisposable Support
        private bool disposedValue = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue)
            {
                if (disposing && sqlTableDependency != null)
                {
                    sqlTableDependency.Stop();
                    sqlTableDependency.Dispose();
                }

                disposedValue = true;
            }
        }
        public void Dispose()
        {
            Dispose(true);
        }
        #endregion
    }
}

Code language: C# (cs)

Inicie MessageRepository en un servicio y escuche nuevos mensajes

Esto es inicializar MessageRepository y mostrar los nuevos mensajes que se están insertando.

using MessageQueueService.Data;
using MessageQueueService.Model;
using System;
using System.Configuration;
using System.ServiceProcess;
using System.Threading.Tasks;

namespace MessageQueueService
{
    public partial class Service : ServiceBase
    {
        private readonly MessageRepository messageRepository;

        public Service()
        {
            InitializeComponent();
            messageRepository = new MessageRepository();
            messageRepository.OnNewMessage += MessageRepository_OnNewMessage;
        }

        private void MessageRepository_OnNewMessage(Message message)
        {
            Console.WriteLine($"{message.Timestamp}\t{message.Text}");
        }

        protected override void OnStart(string[] args)
        {
            new System.Threading.Thread(StartService).Start();
        }

        protected override void OnStop()
        {
            messageRepository.Dispose();
        }
        internal void StartService()
        {
            Task.Run(() => messageRepository.Start(ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString));
        }
    }
}

Code language: C# (cs)

Nota:En ConnectionString, independientemente de la seguridad que utilice (integrada o especificando un usuario), asegúrese de que el inicio de sesión esté vinculado al usuario/permisos que creó en el Paso 3 anterior.

5 – Ejecute el servicio e inserte un registro de prueba