Ereignisgesteuertes .NET:Verwenden von Abfragebenachrichtigungen in SQL Server zum Überwachen von Datenbankänderungen

Wie suchen Sie nach neuen Datensätzen in einer Datenbank und reagieren auf die neuen Daten?

Sie haben eigentlich nur zwei Möglichkeiten:

1. Änderungen alle x Sekunden abfragen
2. Verwenden Sie Abfragebenachrichtigungen, um benachrichtigt zu werden, wenn neue Daten eingefügt werden

In diesem Artikel zeige ich Ihnen, wie Sie Abfragebenachrichtigungen konfigurieren, damit Ihre Anwendung Push-Benachrichtigungen von SQL erhält.

Dafür gibt es in .NET eine integrierte Methode namens SqlDependency. Das funktioniert, ist aber nicht gut gestaltet – es ist eine schwierig zu verwendende API. Es gibt mehrere Open-Source-Optionen, die einfacher zu verwenden sind. In diesem Artikel verwende ich die Open-Source-Bibliothek namens SqlTableDependency.

1 – Fügen Sie einen Verweis auf SqlTableDependency hinzu

Weitere Einzelheiten dazu, wie dies funktioniert:https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency

2 – Erstellen Sie eine Tabelle und ein Modell

Nachrichtentabelle

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)

Nachrichtenmodell

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 – Service Broker aktivieren und Berechtigungen konfigurieren

Der Service Broker ist eine Funktion in SQL Server, die ein Nachrichtenwarteschlangensystem bereitstellt. Abfragebenachrichtigungen funktionieren, indem sie Auslöser für Datenbankänderungen erstellen und die Änderungen in eine Nachrichtenwarteschlange schreiben. Ihre externe App abonniert die Nachrichtenwarteschlange und Service Broker ist dafür verantwortlich, neue Nachrichten an Ihre App zu senden.

Service Broker aktivieren

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)

Berechtigungen konfigurieren

Dieses SQL-Skript erstellt einen Benutzer für eine Anmeldung in der richtigen Datenbank und legt alle erforderlichen Berechtigungen fest, damit SqlTableDependency funktioniert.

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)

Die richtigen Berechtigungen zu erhalten, ist einer der schwierigsten Teile, um dies zum Laufen zu bringen. Wenn Sie es nicht richtig machen, werden Sie auf diesen Fehler stoßen:

Haftungsausschluss:Wenn Sie dies in einer Produktionsumgebung einrichten, sollten Sie mit dem Datenbankadministrator zusammenarbeiten, um sicherzustellen, dass Ihre Sicherheit und Berechtigungen ordnungsgemäß konfiguriert sind.

4 – Auf Tabellenänderungsereignisse lauschen

Erstellen Sie eine Repository-Klasse, die mithilfe von SqlTableDependency kapselt

Dieses Repository wartet auf Abfragebenachrichtigungen von SqlTableDependency und löst das OnNewMessage-Ereignis aus, wenn Daten eingefügt wurden.

Dadurch wird IDisposable implementiert, sodass SqlTableDependency verworfen werden kann. Dies ist wichtig, da es die beim Start hochgefahrenen Service Broker-Ressourcen zerstört.

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)

Initiieren Sie das MessageRepository in einem Dienst und warten Sie auf neue Nachrichten

Dies initialisiert das MessageRepository und zeigt die neuen Nachrichten an, die eingefügt werden.

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)

Hinweis:Stellen Sie im ConnectionString sicher, dass die Anmeldung unabhängig von der von Ihnen verwendeten Sicherheit (integriert oder mit Angabe eines Benutzers) mit dem Benutzer/den Berechtigungen verknüpft ist, die Sie in Schritt 3 oben erstellt haben.

5 – Führen Sie den Dienst aus und fügen Sie einen Testdatensatz ein