Automatische Kommunikation zur Datenbank via Service Broker

(Automatically communication to a database via Service Broker)

Es gibt viele verschiedene Arten aus einem .Net-Programm auf eine Datenbank zuzugreifen und Daten auszulesen. Möchte man jedoch auf inhaltliche Änderungen einer Tabelle (z. B. Schnittstellentabelle) zeitnah reagieren, sollte man sich schon vorher ein paar Gedanken über die Abfragemethodik machen, um überflüssige Last zu vermeiden.
Die einfachste Möglichkeit Änderungen mitzubekommen, wäre wohl ein Timer, welcher in regelmäßigen Intervallen die Tabelle abfragt. Um hier aber zeitnah reagieren zu können, müsste der Intervall entsprechend kurz eingestellt werden. Dieses wiederum hätte wahrscheinlich viele unnötige Abfragen zur Folge, die folglich auch wieder Last auf dem SQL-Server verursachen.
Um dieses Verhalten zu umgehen, könnte man den Weg über den sogenannten Service Broker gehen, der im SQL-Server (im Datenbankmodul) integriert ist. Der Service Broker wird für komplette Datenbank aktiviert. In der Programmierung steuert man dann gezielt die Tabellen an. Der Service Broker dient zur asynchronen Kommunikation zwischen SQL-Server und externen Anwendung. Außerdem arbeitet der Service Broker mit Warteschlangen, so dass generell keine Informationen verloren gehen sollten.
Zum Aktivieren des Service Brokers (im Management Studio) in der Datenbank muss man wie folgt vorgehen:
- Öffnen der Datenbank-Eigenschaften (Rechtsklick auf Datenbank => Eigenschaften/Properties)
- Auswählen von Optionen/Options
- Die Option “Broker aktivieren” / “Broker enabled” auf True setzen



Mit der Aktivierung des Service Brokers funktioniert die Kommunikation zur Datenbank generell.
Nun kann dieser auch direkt im .Net angesprochen werden. Hierfür gibt es die Klasse SqlDependency.
Hier braucht man quasi nur die Verbindung starten und ein EventHandler hinterlegen, wo die Verarbeitung der Änderungen definiert ist.

public void StartWatcher()
{
    String connectionString = "Server=(local);Database=Playground;Trusted_Connection=True;";
    SqlDependency.Start(connectionString);
    ReadData();
}


private void ReadData()
{
    DataTable data = GetNewData();

    if (data != null && data.Rows.Count > 0)
    {
        ChangeEventArgs daten = new ChangeEventArgs(data.Rows[0]);
        ChangeEventHandler(this, daten);
    }
}


public DataTable GetNewData()
{
    DataTable dt = new DataTable();
    
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        conn.Open();

        SqlCommand cmd = conn.CreateCommand();
                
        cmd.CommandText = SELECT ID, Name, DescriptionShort FROM dbo.Product WHERE State = @State";
        cmd.Notification = null;

        cmd.Parameters.Add(new SqlParameter("@State", "new"));

        dep = new SqlDependency(cmd);
        dep.OnChange += new OnChangeEventHandler(dep_OnChange);

        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = cmd;
        adapter.Fill(dt);

        conn.Close();
    }

    return dt;
}


public class ChangeEventArgs : EventArgs
{
    public readonly DataRow data;

    public ChangeEventArgs(DataRow data)
    {
        this.data = data;
    }
}


void dep_OnChange(object sender, SqlNotificationEventArgs e)
{
    ReadData();
}

Im gezeigten Beispiel wird nun also direkt die Verarbeitung des Handlers ausgeführt, sobald ein neuer Datensatz mit dem Status “new” in die Tabelle Product eingefügt wird.

Für die Abfragen in Verbindung mit dem Service Broker gibt es ein paar kleinere Spielregeln zu beachten, welche im MSDN zu finden sind. Eine wichtige Regel, über die ich gestolpert bin ist, dass die Abfragen z.B. SELECTS nicht mit Platzhaltern wie * verwendet werden dürfen. Hier muss man gezielt Spalten angeben, die abgefragt/eingelesen werden sollen.