how to notify my Program when database updated ?..

Question

Hi there,

I have a C# program that queries the database(SQL Server) for some value. 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 querie is only done when the database has been changed/updated.How do i notify my program when some thing updatedin database. Thanks

Answer 1

If by "updates to the database" you mean any update by any application, you're out of luck: it's not doable.

If, however, you mean changes made by your app, it's easy: every time you update the DB raise and event and have handlers respond to the event.

Answer 2

What I would like to be able to do is that the querie is only done when the database has been changed/updated.How do i notify my program when some thing updatedin database.

There isn't any means of the database pushing notifications to the application. The application needs to poll the database to check for updates, and then deal with the updates appropriately.

Answer 3

If you are on SQL Server 2005 and above, you can consider using the SqlDependency object.

It represents a query notification dependency between an application and an instance of SQL Server 2005.

An application can create a SqlDependency object and register to receive notifications via the OnChangeEventHandler event handler.

Refer this link on MSDN for more information

However, do note the caveat that MS puts against its use. It is advised to have a caching layer and then use SQLDependency in coordination with that layer .

SqlDependency was designed to be used in ASP.NET or middle-tier services where there is a relatively small number of servers having dependencies active against the database. It was not designed for use in client applications, where hundreds or thousands of client computers would have SqlDependency objects set up for a single database server.

Answer 4

Polling database is not very elegant solution.

SqlDefendency 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