What is the best way to trigger events based on changes made to database tables?

Question

Is there a better way than the below to detect if the value retrieved from a database is different to the last retrieved value?

I have a feeling that something better than in infinite poll is available out there?

public void CheckForNewMofificationDate(string username)
{
    while(true)
    {
        OdbcConnection sql = null;
        if (!DBClass.Instance.OpenConn(ref sql))
            throw new DatabaseConnectionException();
        try
        {
            string query = "SELECT MODIFIED_ON FROM USER_DTLS WHERE USERNAME=?";
            using (var cmd = new OdbcCommand(query, sql))
            {
                cmd.Parameters.Add("USERNAME", OdbcType.VarChar, 50).Value = username;
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {

                        if( OldValue != reader.GetString(0))
                        {
                            //use INotifyPropertyChange
                        }
                    }
                }
            }
        }
        finally
        {
            DBClass.Instance.CloseConn(ref sql);
        }
    }
}

How is this value updated? If you don't have code yet and your question is how to get an event if a value in the database has changed, you can use [`SqlDependency`](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency) (presuming sql-server). — Tim Schmelter, Mar 29 '18 at 13:25
@TimSchmelter The value will have to be retrieved from a database. — Abs, Mar 29 '18 at 13:26
Is this "variable" a class property? Take a look https://stackoverflow.com/questions/2246777/raise-an-event-whenever-a-propertys-value-changed — Marcus Vinicius, Mar 29 '18 at 13:27
@CamiloTerevinto: the property doesn't change but a value in the database. I guess he wants to update this property then, but the question is how to know when to update without constantly requesting the database. Is that correct @Abs? — Tim Schmelter, Mar 29 '18 at 13:27
@TimSchmelter Something has to update the variable in the code, it won't automagically get the value from the database, will it? — Camilo Terevinto, Mar 29 '18 at 13:28
@Abs: then look at my first comment and try `SqlDependency`. It might be what you are looking for. — Tim Schmelter, Mar 29 '18 at 13:31
@TimSchmelter I think this is the best solution to employ, please add as an answer for me to accept. — Abs, Mar 29 '18 at 14:00

score 0 · Answer 1 · answered Mar 29 '18 at 13:28

Short answer: you would have to employ a polling (looping) mechanism like you suggested.

Or, you could do something crazy with triggers on the database and have the trigger execute a custom function or web service that uses an event bus or WCF to notify your application of a change in data, but I would highly recommend not pursuing this approach.

score 0 · Accepted Answer · answered Apr 03 '18 at 10:30

As recommended by @TimSchmelter, A SqlDependancy is the best approach I found so far, it causes Sql Server to detect changes made to tables assoiciated with a query and fire events based on that:

A SqlDependency object can be associated with a SqlCommand in order to detect when query results differ from those originally retrieved. You can also assign a delegate to the OnChange event, which will fire when the results change for an associated command. You must associate the SqlDependency with the command before you execute the command. The HasChanges property of the SqlDependency can also be used to determine if the query results have changed since the data was first retrieved.

This eliminates the need to have a serprate thread with an infinite loop continuasslt polling to detect changes.

What is the best way to trigger events based on changes made to database tables?

2 Answers2