SQLdependency der Push vom SQL Server

Ich habe selten ein Code Beispiel geschrieben, das so wenig kann und bei dem so viel schief gehen kann. Ziel ist eine Push Benachrichtigung einer Änderung von Daten in einer Tabelle im SQL Server.

Eigentlich sollte das ganz einfach sein. Die Klasse SQLDepedency erlaubt das abonnieren von Änderungen an den Daten. Dazu braucht es aber eine ganze Reihe von Infrastrukturmaßnahmen. Zuerst einen Dienst namens SQL Broker. Den gibt es schon seit SQL Server 2005 und auch für SQLExpress 2012. Also sollten auch dynamisch angefügte (attachdbfilename) Datenbanken push können. Letztendlich findet man im Web sehr häufig “SQLDependency OnChangeEvent not firing”, was kein gutes Zeichen ist.

Die nächste Hürde (oder auch InvalidOperationException) weist auf die fehlende Aktivierung hin. Je nach SQL Express oder SQL Server per SSQL Kommando

   1:  ALTER DATABASE northwind SET ENABLE_BROKER 
   2:  ALTER DATABASE  CURRENT SET ENABLE_BROKER 
   3:   

Manchmal zickt dabei der SQL Server, dann einfach folgende Syntax probieren

   1:  ALTER DATABASE northwind SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

In meiner VB.NET Winforms Anwendung wird dann die Änderungsverfolgung aktiviert mit einem Connection String.

   1:    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
   2:          SqlDependency.Stop(ef.Database.Connection.ConnectionString)
   3:          SqlDependency.Start(ef.Database.Connection.ConnectionString)
   4:   
   5:          SetupDP()
   6:      End Sub

Ursprünglich hatte mein Code Beispiel Entity Framework im Einsatz. In Anbetracht der vielen Fehlermöglichkeiten kann ich nur empfehlen: Finger weg. Ganz fundamental sind die verwendeten SQL Kommandos zur Überwachung. Da gibt es eine Reihe von sehr strikten Begrenzungen, wie die zwingende Schreibweise mit dbo.

Wer sich nicht dran hält erhält gleich beim Start das erste Event und darin sqlnotificationeventargs info invalid. In einigen Blog Einträgen ( ich würde sagen in fast allen) wird das falsch dokumentiert.

Als nächstes wird das SQL Kommando definiert, das überwacht werden soll. Wie vorhin schon angeschnitte, sind viele Kommandos wie ein SUM oder COUNT nicht gültig. In diesem Fall wird die ID abgerufen und damit nur auf INSERT oder DELETE Änderungen reagiert. Nur Felder die im Select angegeben werden, können auch überwacht werden.

Dann wird die Abhängigkeit ( Zeile 2) definiert und das Event abonniert. Nur wenn dann auch das idente SQL COmmand ausgeführt wird (Zeile 5) ist der Watcher sozusagen scharf geschalten.

   1:  Dim cmd As SqlCommand = New SqlCommand("SELECT id from dbo.gaestebuch")
   2:  dp = New SqlDependency(cmd)
   3:  AddHandler dp.OnChange, AddressOf dataChanged
   4:   
   5:  cmd.ExecuteReader(CommandBehavior.CloseConnection)

Jetzt wird es nach meinen Begriffen noch schräger (Hello Murphy). Wenn das Event ausgelöst wird, erlischt die Motivation des SQL Brokers das noch einmal zu tun.

In den Tests nehme ich SQL Management Studio und füge Datensätze ein um die  Methode (gesetzter Breakpoint)  aufzurufen. Aber das Event kommt immer nur beim ersten Versuch.

   1:  Private Async Sub dataChanged(sender As Object, e As SqlNotificationEventArgs)
   2:          If e.Info = SqlNotificationInfo.Error Then
   3:          Else
   4:              
   5:              SetupDP()
   6:          End If
   7:  End Sub

Deswegen wird in Zeile 5 der gesamte Kreislauf noch einmal durchlaufen. Der komplette VB.NET Code zum Initialen und refresh der Daten Abhängigkeit.

   1:   Public Sub SetupDP()
   2:          Dim cmd As SqlCommand = New SqlCommand("SELECT id from dbo.gaestebuch")
   3:          If Not IsNothing(dp) Then
   4:              RemoveHandler dp.OnChange, AddressOf dataChanged
   5:          End If
   6:          dp = New SqlDependency(cmd)
   7:          AddHandler dp.OnChange, AddressOf dataChanged
   8:          ef.Database.Connection.Open()
   9:   
  10:          cmd.ExecuteReader(CommandBehavior.CloseConnection)
  11:          ef.Database.Connection.Close()
  12:  End Sub

Der Code ist echt zusammengewürfelt und noch ohne tiefe Gedanken über Konsequenzen und mögliche Leaks. Verschiede Blogs äußern sich zu den Performance Betrachtungen und das relativ positiv. Der Konsens ist, das die “Kosten “ relativ gering sind.

Ein weiteres Problem ist, das mir keine Möglichkeit bekannt ist, herauszufinden, was bzw. welcher Datensatz sich verändert hat.  Um den Traffic gering zu halten, könnte man eine Stored Procedure zwischenschalten, die nur Daten Deltas zurück liefert.

In einem anderen Blogposting habe ich gelesen, das where bedingungen sozusagen für das Watching ignoriert werden und dann folgendes ausprobiert.

   1:  Dim id As Integer = 0
   2:  Public Sub SetupDP()
   3:          Dim cmd As SqlCommand = New SqlCommand("SELECT id from dbo.gaestebuch where id>"
+ id.ToString)
   4:  ...
   5:          id = ef.GaesteBuch.OrderByDescending(Function(x) x.ID).FirstOrDefault().ID
   6:          updateUI()
   7:   End Sub
   8:   Public Sub updateUI()
   9:          BeginInvoke(Sub()
  10:                          Label1.Text = id
  11:                      End Sub)
  12:   End Sub

Hier wird ein Counter mitgeführt und die Datenmenge auf 0 gefahren, weil immer nur die Records geholt werden, die größer als der größte aktuelle sind.

Zeile 9 synchronisiert die Threads. Jedenfalls funktioniert das. Änderungen im SQL Manager sind sofort im Winforms Client sichtbar.

image

Der Vollständigkeit halber das TSQL Kommando um die Beispieltabelle zu erzeugen

   1:  CREATE TABLE [dbo].[GaesteBuch] (
   2:      [Id]      INT           IDENTITY (1, 1) NOT NULL,
   3:      [Betreff] VARCHAR (50)  NULL,
   4:      [Datum]   DATETIME      NULL,
   5:      [Poster]  VARCHAR (50)  NULL,
   6:      [lText]   VARCHAR (250) NULL,
   7:      PRIMARY KEY CLUSTERED ([Id] ASC)
   8:  );
Kommentare sind geschlossen