c# - How can I notify my program when the database has been updated?

Question

I have a C# program that queries the SQL Server database for some values.

Currently the application queries the database every minutes to make sure that the table is up to date.

What I would like to be able to do is that the query is only done when the database has been changed / updated. How do I notify my program when something has been updated in the database?

Thanks

Answer 1

Polling database is not very elegant solution.

SqlDependency from ADO.NET will be useful in your case. It does not use polling but notification mechanism. The notifications are provided by Service Broker in your database, so will need to enable this service in your databse. The OnChange event will raise when specified table changes(update, delete, insert..)

Here is an example how to use SqlDependency:

void Initialization()
{
    // Create a dependency connection.
    SqlDependency.Start(connectionString, queueName);
}

void SomeMethod()
{
    // Assume connection is an open SqlConnection.

    // Create a new SqlCommand object.
    using (SqlCommand command=new SqlCommand(
        "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", 
        connection))
    {

        // Create a dependency and associate it with the SqlCommand.
        SqlDependency dependency=new SqlDependency(command);
        // Maintain the refence in a class member.

        // Subscribe to the SqlDependency event.
        dependency.OnChange+=new
           OnChangeEventHandler(OnDependencyChange);

        // Execute the command.
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Process the DataReader.
        }
    }
}

// Handler method
void OnDependencyChange(object sender, 
   SqlNotificationEventArgs e )
{
  // Handle the event (for example, invalidate this cache entry).
}

void Termination()
{
    // Release the dependency.
    SqlDependency.Stop(connectionString, queueName);
}

from http://msdn.microsoft.com/en-us/library/62xk7953.aspx

Here is how to enable Service Broker(note that you will have exclusiveness on the database to do that - best do it after restart of the sql server): http://blogs.sftsrc.com/stuart/archive/2007/06/13/42.aspx(Broken link)

Possible alternative link: http://technet.microsoft.com/en-us/library/ms166086(v=sql.105).aspx