Minimalbeispiel SqlDependency

verwendetes Datenbanksystem: Microsoft SQL Server 2008

Hallo zusammen,
ich weiß, dass das Thema schon ausführlich behandelt wurde. Das hier im Forum angeführte Beispiel ist aber viel umfangreicher als benötigt.

Ich habe versucht ein Minimalbeispiel zu realisieren. In diesem verbinde ich mich mit dem Server und richte eine SqlDependency ein. Danach füge ich der Tabelle einen Datensatz hinzu, worauf eigentlich die Funktion OnChange() ausgeführt werden sollte, dies geschieht leider nicht. Es erscheint keine Fehlermeldung und der Datensatz wird auch wirklich hinzugefügt.

Den Service Broker habe ich vorher gestartet. Ich hoffe, ich habe das alles richtig verstanden, hier mein Code:

private void button2_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConn = new SqlConnection(GetConnectionString());
            sqlConn.Open();

            SqlClientPermission perm =
                    new SqlClientPermission(
                    PermissionState.Unrestricted);

            perm.Demand();

            SqlDependency.Start(GetConnectionString());
            SqlCommand cmd = new SqlCommand("SELECT ContactID FROM dbo.Contact");
            SqlDependency dependency = new SqlDependency(cmd);
            dependency.OnChange += new OnChangeEventHandler(OnChange);
            
            SqlCommand cmd2 = new SqlCommand("INSERT INTO dbo.Contact VALUES (15, 'test', 'person')", sqlConn);
            cmd2.ExecuteNonQuery(); 
        }

        private string GetConnectionString()
        {
            return "user id=####;password=#######;server=###.###.#.###;Trusted_Connection=no;database=Person; connection timeout=10";
        }        

        void OnChange(object sender, SqlNotificationEventArgs e)
        {
            SqlDependency dependency = sender as SqlDependency;
          
            dependency.OnChange -= OnChange;

            // Fire the event
            MessageBox.Show("Database changed");
        }

Wäre super, wenn mir jemand weiterhelfen könnte. Und bitte keine Nachrichten wie "steht alles im Forum" ^^ Habe schon gesucht und wie oben beschrieben nur das sehr ausführliche Beispiel gefunden.

Vielen Dank im Voraus,
Phil

Hallo,

bei deinem Beispiel ist doch 'cmd2' gar nicht an die SqlDependency gebunden, sondern nur 'cmd'.

Moin,
habe ich da evtl. etwas Grundlegendes falsch verstanden? Ich dachte, wenn sich das Ergebnis der in "cmd" gegebenen SqlQuery ändert, wird ein Event generiert. Dies wollte ich dadurch erreichen, dass ich mittels "cmd2" ein Dataset hinzufüge.

VG

So auf dem ersten Blick sollte es funktionieren.
Wird OnChange nie ausgeführt? Kann sein dass die QueryNotification fehl schlägt, d.h. bei SqlNotificationEventArgs.Info wird Invalid zurückgegeben?

Gruß
Michael

Hier vll. mal als Vergleich eine Version, die mit LINQ2SQL funktioniert: Datenbestand aus Datenbank automatisiert aktualisieren?.

Hi,

wenn ich das mit Using SqlDependency to Detect Changes in the Server vergleiche fehlt cmd.ExecuteReader() ?

Execute the command using any of the Execute methods of the SqlCommand object. Because the command is bound to the notification object, the server recognizes that it must generate a notification, and the queue information will point to the dependencies queue.

Gruss,
Tom

Hallo zusammen,
wie so oft hat nur eine Kleinigkeit gefehlt. Ihr habt mich mit euren Hinweisen auf die richtige Fährte gebracht. Das SqlCommand war nicht mit der Connection verknüpft. Somit konnte ich z.B. kein executeReader() ausführen.
Nun sind command und connection verknüpft und ich führe auch ein executeReader() aus. Wenn ich nun der Tabelle einen Datensatz zufüge, wird die Funktion "OnChange()" getriggert. Zum Ausführen des "insert" musste ich noch eine weitere Datenbankverbindung erstellen.

Wie kann ich SqlNotificationEventArgs.Info einsehen? Eigentlich setze ich damit die Argumente und lese sie nicht aus oder?

Hier nochmal mein funktionstüchtiges Beispiel mit zusätzlichen Kommentaren. Vielleicht hilft es ja dem einen oder anderen:

private void button2_Click(object sender, EventArgs e)
        {
            // Open connection for Dependency
            SqlConnection sqlConn = new SqlConnection(GetConnectionString());
            sqlConn.Open();
            // Granting permission
            SqlClientPermission perm = new SqlClientPermission(PermissionState.Unrestricted);
            perm.Demand();

            // Start dependency
            SqlDependency.Start(GetConnectionString());
            // Bind dependency command do connection
            SqlCommand cmd = new SqlCommand("SELECT ContactID FROM dbo.Contact", sqlConn);
            // Bind command to dependency
            SqlDependency dependency = new SqlDependency(cmd);
            // Add function to be executed on change
            dependency.OnChange += new OnChangeEventHandler(OnChange);            
            cmd.ExecuteReader();

            // Open second connection to insert a dataset for test purposes
            SqlConnection sqlConn2 = new SqlConnection(GetConnectionString());
            sqlConn2.Open();
            SqlCommand cmd2 = new SqlCommand("INSERT INTO dbo.Contact VALUES (45, 'test', 'person')", sqlConn2);
            cmd2.ExecuteNonQuery();

        }

        private string GetConnectionString()
        {
            return "user id=xxxx;password=xxxxx;server=xxx.xxx.xxx.xxx;Trusted_Connection=no;database=Person; connection timeout=10";
        }

        void OnChange(object sender, SqlNotificationEventArgs e)
        {
            SqlDependency dependency = sender as SqlDependency;
          
            dependency.OnChange -= OnChange;

            // Fire the event
            MessageBox.Show("Database changed");
        }

Vielen Dank für die Hilfe!
Phil

Wobei gesagt werden sollte dass dies nur einmal funktioniert. SqlDependency muss nach jedem Auslösen neu abonniert werden.

Hm, jetzt weiß ich warum das nicht geht ^^

Wie kann ich SqlDependency neu abonnieren? Finde da gerade nichts.

Den Inhalt des Klick Events in eine Methode auslagern und diese ausgelagerte Methode im OnClick am Ende aufrufen.

Hallo!

Den Inhalt des Klick Events in eine Methode auslagern und diese ausgelagerte Methode im OnClick am Ende aufrufen.

Natürlich ohne das INSERT 😉

Da hab' ich aber mal eine andere Frage: Muss man ein solches SELECT machen?
Bei einer sehr großen Tabelle würde dies ja entsprechend lange dauern, oder?
Oder ist der Befehl egal?

Natürlich ohne Insert. Ich denke dass dies anderweitig auch nur zum Testen drinnen ist.

Bei einer sehr großen Tabelle würde dies ja entsprechend lange dauern, oder?

Hmm, interessante Frage, diesbezüglich habe ich mir auch noch keine Gedanken gemacht. Werde es sofort testen und das Ergebnis dazu posten.

EDIT: Also, hab mal schnell probiert. Habe nichts aufälliges an der Performance bemerken können, dies liegt auch daran dass ich nicht so große Tabellen überwache. Vielleicht ist es bei großen Tabellen mit millionen Sätzen anders. Vielleicht gibt es den ein oder anderen der dies bestätigen kann.

Grundsätzlich ist es so dass alle Tuples die im Result-Set zurückkommen überwacht werden. Ich steuer das meist so dass ich eine LastChange Spalte habe nach der ich auch filter
so z.b: ...WHERE LastChange > DateTime.Now. Jedoch muss man dann aufpassen dass man immer das LastChange Clientseitig ändert, sobald eine Änderung geschieht, denn sonst bekommt die SqlDependency nichts mit.

Komme nicht so recht weiter. Wenn ich den Inhalt 1 zu 1 kopiere, kriegt er ein Problem mit dem Command. Ich kann es nicht ausführen, da es noch ausgeführt wird. Command.cancel() hilft auch nicht.
Die einzige Möglickeit ist die Connection zu schließen und wieder zu öffnen. Dann funktioniert es wunderbar. Ich sucher aber gerade noch nach einem anderen Weg.

Es gibt keinen anderen weg.

Die SqlDependency braucht eine offene Connection für den Betrieb.
Das Cmd.Cancel brauchst du um den Server von der Arbeit zu befreien.

Okay, mein Code sieht demnach wie folgt aus. Es gibt eine Funktion zum starten und eine Funktion zum Restart. Sie unterscheiden sich hauptsächlich in dem Neustarten der Connection.
Ich finde es aber etwas unglücklich. Was passiert, wenn sich in der Datenbank etwas ändert, während die SqlDependency neu gestartet wird?

        public void StartDependency(string ConnString, string TriggerString)
        {
            // Granting permission
            SqlClientPermission perm = new SqlClientPermission(PermissionState.Unrestricted);
            perm.Demand();

            // Start dependency
            SqlDependency.Start(ConnString);
            // Bind dependency command do connection
            SqlCommand cmd = new SqlCommand(TriggerString, _sqlConn);
            // Bind command to dependency
            SqlDependency dependency = new SqlDependency(cmd);

            // Add function to be executed on change
            dependency.OnChange += new OnChangeEventHandler(OnChange);

            cmd.ExecuteReader();
        }

        public void ReStartDependency(string ConnString, string TriggerString)
        {
            _sqlConn.Close();
            _sqlConn.Open();

            // Start dependency
            SqlDependency.Start(ConnString);
            // Bind dependency command do connection
            SqlCommand cmd = new SqlCommand(TriggerString, _sqlConn);
            // Bind command to dependency
            SqlDependency dependency = new SqlDependency(cmd);

            // Add function to be executed on change
            dependency.OnChange += new OnChangeEventHandler(OnChange);

            cmd.ExecuteReader();
        }

Hallo, ich habe mir eine Helper Klasse geschrieben und bin auch bereit diese zu teilen. Werde sie im Laufe des Abends fallsch gewünscht hier posten.

Ich habe auf jeden Fall Interesse! Speziell der "Restart" ist für mich interessant. Ich habe nochmal nachgedacht. Der bisherige Weg ist eigentlich nicht akzeptabel, da Änderungen in der Datenbank die Zwischen dem Beenden und dem Restart der Dependency auftreten, nicht erfasst werden.

public class SqlDependencyWatcher : IDisposable
{
    #region Readonly Fields

    private readonly SqlCommand _command;
    private readonly string _connectionString;

    #endregion

    #region Fields

    private SqlDataAdapter _adapter;
    private SqlConnection _connection;
    private EventHandler<SqlNotificationEventArgs> _onChange;
    private DataSet _result;
    private bool _isStarted;

    #endregion

    #region Constructors

    public SqlDependencyWatcher(string command)
    {
        _connectionString = SchwerSqlConnection.SqlDependencyString;
        _command = new SqlCommand(command);
    }

    #endregion

    #region Instance Methods
    
    public void Start(EventHandler<SqlNotificationEventArgs> onDataChange)
    {
        if (_isStarted)
            throw new InvalidOperationException("Cannot start the same watcher twice");
        if (_isDependencyReadyToUse)
        {
            _connection = new SqlConnection(_connectionString);
            _connection.Open();
            _command.Connection = _connection;
            _adapter = new SqlDataAdapter(_command);
            _onChange = onDataChange;
            RegisterForChanges();
        }
        _isStarted = true;
    }

    private void Stop()
    {
        if (_isDependencyReadyToUse)
            SqlDependency.Stop(_connectionString);
        _command.Dispose();
        _connection.Dispose();
    }

    private void RegisterForChanges()
    {
        _command.Notification = null;
        var dep = new SqlDependency(_command);
        dep.OnChange += Handle_OnChange;
        _result = new DataSet();
        _adapter.Fill(_result);
    }

    #endregion

    #region Event Handling
  
    private void Handle_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type != SqlNotificationType.Change)
            throw new InvalidOperationException(string.Format("Failed to create queue notification subscription!\n{0}", e.Info));
        _onChange(sender, e);
        SqlDependency dep = (SqlDependency) sender;
        dep.OnChange -= Handle_OnChange;
        RegisterForChanges();
    }

    #endregion

    #region IDisposable Members

    public void Dispose()
    {
        Stop();
    }

    #endregion
}

Die Frage die sich mir jetzt stellt ist ob folgendes Konstruct performanter bzw mehr zu emfehlen sei:

 
public void StartNew(EventHandler<SqlNotificationEventArgs> onDataChange)
{
    _onChange = onDataChange;
    using (SqlConnection conn = new SqlConnection(_connectionString))
    {
        using (SqlCommand comm = new SqlCommand(_command.CommandText, conn))
        {
            conn.Open();
            using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
            {
                var dep = new SqlDependency(comm);
                dep.OnChange += Handle_OnChange2;
                using (DataSet result = new DataSet())
                {
                    adapter.Fill(result);
                }
            }
        }
    }
}

private void Handle_OnChange2(object sender, SqlNotificationEventArgs e)
{
    if (e.Type != SqlNotificationType.Change)
        throw new InvalidOperationException(string.Format("Failed to create queue notification subscription!\n{0}", e.Info));
    _onChange(sender, e);
    SqlDependency dep = (SqlDependency) sender;
    dep.OnChange -= Handle_OnChange;
    StartNew(_onChange);
}

Danke für eure Rückmeldungen, und eventuelle Verbesserungsvorschläge

@xxMUROxx:
Was haben die DataAdapter da zu suchen?
Wenn die Class als DependencyWatcher gedacht ist, sollte sie nicht auch noch die Daten liefern.

@PhilINS:
Deine Routinen unterscheiden sich nur in den ersten beiden Zeilen, und diese kann man leicht durch das vorhanden sein der Connection erkennen, warum also doppelten Code erzeugen?

@FZelle,
hm, da hast du auch wieder Recht. Das Problem ist nur, dass ich die Query ausführen muss damit die Dependency funktioniert. Zusätzlich würde sich dann ExecuteReader() bzw ExecuteScalar() anbieten. Ohne Ausführen der Query an der Server denke ich mir wird die Dependency wohl nicht funktionieren. Dann wär die Frage was die günstigste möglichkeit wäre die Abfrage auszuführen.

Nachträglich sei noch zu sagen dass ich geposteten Code früher in plain ADO.NET verwendete, d.h. der DataAdapter noch benutzt wurde. Jetzt benutze ich ihn nur noch in Verbindung mit dem EF, wobei ich wir du sagtest die Daten auch nicht benötige, noch dazu dass mir auch das Dependency nicht direkt sagt was sich geändert hat (Lässt sich aber damit lösen dass man die Query anpasst)

Weder ExecuteNonQuery noch ExecuteScalar würden sich hier anbieten, sondern eigentlich nur ExecuteReader mit anschliessendem Cancel des Cmd.

Danke FZelle,
habe es bei meinem Code umgestellt.

Beachte auch noch SQL Dependency - Notifications

Moin zusammen.

@FZelle: Ja sicher, hast recht. Ein "if" ersetzt dort die gesamt Methode.

@MURO: Bin gerade noch dabei deine zweite Variante zu verstehen. Was passiert denn mit "using (SqlConnection conn = new SqlConnection(_connectionString))" außerhalb des entsprechenden Blocks? Bleibt die Verbindung weiter bestehen? Sorry, kenne mich mit using leider nicht so gut aus.

Nein, am Ende des Usings Blocks wird die Verbindung geschlossen. Aber für SQL-Dependency brauchst du bezüglich SqlConnection keine offene Verbindung, dies macht die SqlDependency Klasse. Du muss nur mindestens 1x die Query auf den Server abfragen, dann funktionierts auch schon.
Jedoch denke ich mir wär abzuwägen wie oft sich die Daten in der zu überwachenden Tabelle ändern. Denn wenn sie sich oft ändern ist sicherlich das 2.Beispiel nicht so zu empfehlen.

Achso, ich hatte nochwas vergessen. In deinem ersten Beispiel implementierst du die Methode dispose() die eigentlich nichts anderes macht als ein close(), richtig? Wieso nimmst du nicht direkt das close()?

Edit: Sorry, habe mich vertan. Es sind dispose() und stop(). Trotzdem leuchtet mir der Zusammenhang nicht ein ^^

Beim 2. Beispiel ist dies nicht mehr nötig, beim ersten würde noch das Disposen der Connection und des Commands reinkommen, welches durch den Umbau der Klasse auf das 2. Beispiel verloren gegangen ist.

Okay, also bleibt das Problem des Reconnects bestehen. Dies wurde ja auch von FZelle angedeutet. 😦

Dein zweites Beispiel ist schön schlank. Wieso meinst du, dass es für eine große Anzahl an Änderungen nicht geeignet ist?

Das erste erscheint mir unnötig kompliziert. Aber kann auch sein, dass ich die notwendigen Schritte nicht sehe. Bin noch nicht so lange mit C# Dabei 😉

Das Problem liegt am 2. Beispiel daran dass die Connection immer geschlossen wird, im 1. aber offen bleibt bis Stop aufgerufen wird. Jedoch liegt wiederrum am ersten Beispiel das Problem daran dass die Connection normalerweise nur dann offen sein sollte sobald sie auch benötigt wird, und dies ich ja nicht der Fall fall die Tabelle alle 10 min geupdated wird.

Edit: Falls es dich interessiert, ich verwende z.Z. in meinem Projekt zweiteres Beispiel, mit der Änderung die FZelle vorgeschlagen hat.

Für mich ist die Priorität, dass keine Änderungen "übersehen" werden. Deswegen bin ich gerade am Überprüfen welche Variante die kürzeste "Downtime" hat.

Kannst du das letzte Beispiel auch noch posten?

Versuche mir gerade etwas aus deinem ersten Code zusammen zu basteln, aber mit jeder Änderung in der DB verdoppelt sich die Anzahl der Meldungen. Als würde das dependency.OnChange -= OnChange nicht funktionieren.

Ach Mensch, jetzt löcher ich dich aber mit fragen. Tut mir Leid.

Was übergibst du als Parameter für "EventHandler<SqlNotificationEventArgs> onDataChange"?

Hier mein z.Z. benutzer Code:

public class SqlDependencyWatcher
{
    #region Readonly Fields

    private readonly string _connectionString;
    private readonly string _commandString;
    #endregion

    #region Fields

    private EventHandler<SqlNotificationEventArgs> _onChange;

    #endregion

    #region Constructors

    public SqlDependencyWatcher(string command)
    {
        _connectionString = SqlConnectionHelper.SqlDependencyString; 
        _commandString = command;
    }


    #endregion

    #region Instance Properties

    /// <summary>
    /// Gets whether the <see cref="SqlDependencyWatcher"/> is successfully started or not
    /// </summary>
    public bool IsStartet { get; private set; }

    #endregion

    #region Instance Methods

    /// <summary>
    /// Starts the <see cref="SqlDependencyWatcher"/>
    /// </summary>
    /// <param name="onDataChange">Event that occurs when the <see cref="SqlDependencyWatcher"/> receives a data change from the SQL-Server</param>
    public void Start(EventHandler<SqlNotificationEventArgs> onDataChange)
    {
        _onChange = onDataChange;
        RegisterForChanges();
    }

    private void RegisterForChanges()
    {
        if (SqlDependencyHelper.IsSqlDependencyUsable) //wird bei Programmstart gecheckt ob das Starten der Dependency überhaupt möglich ist
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                using (SqlCommand comm = new SqlCommand(_commandString, conn))
                {
                    SqlDependencyHelper.DependencyStart(_connectionString);
                    conn.Open();
                    var dep = new SqlDependency(comm);
                    dep.OnChange += OnDataChange;
                    comm.ExecuteReader();
                    comm.Cancel();
                }
            }
            IsStartet = true;
        }
    }

    public void Stop()
    {
        if (SqlDependencyHelper.IsSqlDependencyUsable)
            SqlDependency.Stop(_connectionString);
    }

    #endregion

    #region Event Handling

    private void OnDataChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type != SqlNotificationType.Change)
            throw new InvalidOperationException(string.Format("Failed to create queue notification subscription!\n{0}", e.Info));
        _onChange(sender, e);
        SqlDependency dep = (SqlDependency)sender;
        dep.OnChange -= OnDataChange;
        RegisterForChanges();
    }

    #endregion
}

Benutzt wird es dann wie folgt:

var sqlDependencyWatcher = new SqlDependencyWatcher(DependencySqlCommands.Firmen);
sqlDependencyWatcher.Start(OnFirmainDatabaseChanged);

private void OnFirmainDatabaseChanged(object sender, SqlNotificationEventArgs e)
{
    //Mach was
}

Bitte beachte in Zunft immer [Hinweis] Wie poste ich richtig? 4a/b. Da ich selbst die erste Lösung angeboten habe sehen wird darüber nochmals weg

Gruß
Michael

Hast Du auch ein Beispiel für IsSqlDependencyUsable?

Danke