Bei einem Vortrag bei der .NET Usergroup zeigte Christian Weyer ein kleines SignalR Beispiel, bei dem eine Änderung an einem Datensatz in einer Tabelle sofort beim Client sichtbar wurde. SignalR wurden dadurch nicht weniger interessant, doch wie hat er das mit der Änderung gemacht? Das Zauberwort heißt SqlDependency und ist gar nicht so neu. Wie konnte sich dieses Feature so lange vor mir verstecken?

Was braucht man für SqlDependency?

Voraussetzung ist der SQL Server ab 2005 mit aktiviertem Service Broker. Den Service Broker kann man über das Einstellungsmenü konfigurieren (was bei mir aber nicht klappte, da noch eine Verbindung zum Server offen war). Schneller geht es, wenn sich auf die Master Datenbank verbindet und folgende Query absetzt:

ALTER DATABASE datenbank SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

Wie verwendet man SqlDependency?

Die Verwendung von SqlDependency könnte einfacher nicht sein. Nehmen wir an, wir würden für eine Webanwendung beim Start die vorhandenen Produkte (ID, Name) in einen Cache laden. Eine Funktion LoadProducts könnte dann in etwa so aussehen:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

private List<Product> LoadProducts()
{
    List<Product> rValue = new List<Product>();
 
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.InitialCatalog = "BusinessSample";
    builder.IntegratedSecurity = true;
    builder.DataSource = @".\SQLExpress";
    using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
    {
        connection.Open();
 
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandText = "Select ID, Name FROM dbo.Product";
 
            SqlDependency.Start(builder.ConnectionString);
            System.Data.SqlClient.SqlDependency dependency = new System.Data.SqlClient.SqlDependency();
            dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
            dependency.AddCommandDependency(command);
 
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Product product = new Product();
                    product.ID = reader.GetInt32(0);
                    product.Name = reader.GetString(1);
                    rValue.Add(product);
                }
            }
        }
    }
    return rValue;
}

Über den größten Teil der Funktion müssen wir glaube ich nicht sprechen. SqlConnection aufbauen, SqlCommand erstellen, Daten auslesen. Neu sind die Zeilen 17 – 20.
Zeile 17: Starten des Listeners
Zeile 18: Erstellen eines SqlDependency Objektes
Zeile 19: Ein Event anhängen, dass ausgelöst wird, falls sich eine Änderung ergibt.
Zeile 20: Das Command zur Dependency hinzufügen.

Der Vollständigkeit: Hier noch die komplexe Klasse Product:

1
2
3
4
5

class Product
{
    public int ID { get; set; }
    public string Name { get; set; }
}

Wie kann man SqlDependency praktisch nutzen?

Die Antwort wäre: cache invalidation. Normalerweise liegen im Cache häufig benötigte Daten die sich nur selten ändern. Ändern sich die Daten nach dem Befüllen des Cache müsste der Client irgendwie über diese Änderung informiert werden bzw. den Cache neu laden. Das lässt sich mit SqlDependency leicht umsetzten. Zur Funktion oben packen wir einfach noch ein wenig Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

private List<Product> _cachedProducts = null;
 
private void InitApp()
{            
    LoadProductCache();
}
 
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    LoadProductCache();
}
 
private void LoadProductCache()
{
    _cachedProducts = LoadProducts();
}

‚LoadProductCache‘ ruft unsere ‚LoadProducts‘ Funktion auf und setzt die SqlDependency.
‚InitApp‘ wird bei der Initialisierung unsere fiktive Applikation aufgerufen. Damit werden die Produkte in den Cache geladen.
Und dann haben wir noch das OnChange Event, dass bei einer Änderung an den Daten ausgelöst wird. Dort laden wir die Produkte neu aus der Datenbank und setzten wieder eine SqlDependency, um über weitere Änderungen informiert zu werden.

Sobald zur Laufzeit des Anwendung ein neues Produkt in die Tabelle hinzugefügt oder ein bestehendes Produkt geändert wird, wird die OnChange Methode ausgelöst und die Produkte werden neu geladen. Damit haben wir eine (Web-)Anwendung mit immer aktuellem Cache.

Fazit

SqlDependency lässt sich wirklich einfach einbinden. Einige Punkte will ich euch aber nicht verschweigen:

• SqlDependency reagiert etwas empfindlich. Bei meinem Test schlug das ganze beim ersten Versuch fehl, weil ich beim Select Statements das ‚dbo.‘ nicht mit angegeben hatte
• Die Notifications werden nur einmal ausgeführt. Will man weiterhin benachrichtigt werden, muss man jedes Mal wieder eine neue Notification setzten (wie es beim gezeigten Beispiel der Fall ist)
• Bei der Angabe der Query gibt’s noch ein paar Einschränkungen. Dazu verweise ich auf folgenden MSDN Artikel:Creating a Query for Notification