c# - Detect SQL database changes

Question

Consider this example:

INSERT INTO [Table] (column1)
SELECT value1

If I were to execute this command in SSMS, in regards to a c# forms application, what would I need to do in order to recognize this event? Something as simple as the application displaying a MessageBox when this event occurs. I just can't seem to work this one out or find any helpful data on it. I have attempted to use SqlDependency but am not having any luck. If that is the path I need to go down, can anyone help me out with understanding the concept a little better?

Answer 1

If you want to detect changes and not just inserts you could achieve this using SQL Dependency. Have you read and tried the example in the link?

Heres a nice 'tutorial / example' that works and runs you through the basics.

Heres a nice overview of Query Notifications.

• The low-level implementation is provided by the SqlNotificationRequest class that exposes server-side functionality, enabling you to execute a command with a notification request.

• The high-level implementation is provided by the SqlDependency class, which is a class that provides a high-level abstraction of notification functionality between the source application and SQL Server, enabling you to use a dependency to detect changes in the server. In most cases, this is the simplest and most effective way to leverage SQL Server notifications capability by managed client applications using the .NET Framework Data Provider for SQL Server.

• In addition, Web applications built using ASP.NET 2.0 or later can use the SqlCacheDependency helper classes.

It is as basic as "A SqlDependency object can be associated with a SqlCommand in order to detect when query results differ from those originally retrieved."

You must first Enable Query Notifications and follow Creating a Query for Notification

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

void SomeMethod()
{
    // Assume connection is an open SqlConnection.
    // Create a new SqlCommand object which directly references (no synonyms) the data you want to check for changes.
    using (SqlCommand command=new SqlCommand("SELECT value1 FROM [Table]", 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);
}