.NET basato su eventi:come utilizzare le notifiche di query in SQL Server per monitorare le modifiche al database

.NET basato su eventi:come utilizzare le notifiche di query in SQL Server per monitorare le modifiche al database

Come si verifica la presenza di nuovi record in un database e come si reagisce ai nuovi dati?

Hai davvero solo due opzioni:

  1. Sondaggio per le modifiche ogni X secondi
  2. Utilizza le notifiche di query per essere avvisato quando vengono inseriti nuovi dati

In questo articolo ti mostrerò come configurare le notifiche di query in modo che la tua applicazione riceva le notifiche push da SQL.

C'è un modo integrato per farlo in .NET chiamato SqlDependency. Funziona ma non è ben progettato:è un'API difficile da usare. Ci sono più opzioni open source che sono più facili da usare. In questo articolo sto usando la libreria open source chiamata SqlTableDependency.

1 – Aggiungi un riferimento a SqlTableDependency

Per maggiori dettagli su come funziona:https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency

2 – Crea una tabella e un modello

Tabella dei messaggi

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)

Modello di messaggio

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 – Abilita Service Broker e configura le autorizzazioni

Service Broker è una funzionalità di SQL Server che fornisce un sistema di code di messaggi. Le notifiche di query funzionano creando trigger sulle modifiche al database e scrivendo le modifiche in una coda di messaggi. La tua app esterna si iscrive alla coda dei messaggi e il Service Broker è responsabile dell'invio di nuovi messaggi alla tua app.

Abilita broker di servizi

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)

Configura autorizzazioni

Questo script SQL creerà un utente per un accesso al database corretto e imposterà tutte le autorizzazioni necessarie per il funzionamento di SqlTableDependency.

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)

Ottenere le autorizzazioni corrette è una delle parti più difficili per far funzionare tutto questo. Se non lo fai bene, ti imbatterai in questo errore:

Dichiarazione di non responsabilità:se intendi configurarlo in un ambiente di produzione, dovresti collaborare con l'amministratore del database per assicurarti che la sicurezza e le autorizzazioni siano configurate correttamente.

4 – Ascolta gli eventi di cambio tabella

Crea una classe di repository che incapsula utilizzando SqlTableDependency

Questo repository ascolta le notifiche di query da SqlTableDependency e genera l'evento OnNewMessage quando i dati sono stati inseriti.

Questo implementa IDisposable in modo che possa eliminare SqlTableDependency. Questo è importante, perché distrugge le risorse di Service Broker che ha creato quando è stato avviato.

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)

Inizia il MessageRepository in un servizio e ascolta i nuovi messaggi

Questo sta inizializzando il MessageRepository e visualizzando i nuovi messaggi che vengono inseriti.

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:in ConnectionString, qualunque sia la sicurezza che utilizzi (integrata o specificando un utente), assicurati che l'accesso sia collegato all'utente/autorizzazioni che hai creato nel passaggio 3 sopra.

5 – Esegui il servizio e inserisci un record di test