How to Monitor data change using SqlDependency in C#

How to detect data changes with SqlDependency in C#

Drag and drop the DataGridView from the Visual Studio toolbox to your winform, then design a simple UI that allows you to monitor sql database changes using SqlDependency in c# code as shown below.

We will use the Northwind database to play the demo, you should create a connection string that allows you to connect to the sql server database.

// Connection string
string connectionString = @"Data Source=.;Initial Catalog=Northwind;Integrated Security=True";

Create the LoadData method to help you connect to the sql database, then retrieve some product columns from the products table. To fill data to the DataGridView you can set datasource to the product table as the following c# code.

void LoadData()
{
    using (SqlConnection cn = new SqlConnection(connectionString))
    {
        // Establish connection
        if (cn.State == ConnectionState.Closed)
            cn.Open();
        // SQL command to monitor for changes
        SqlCommand cmd = new SqlCommand("select ProductID, ProductName, UnitPrice from Products", cn);
        cmd.Notification = null;
        // Register dependency
        SqlDependency sqlDependency = new SqlDependency(cmd);
        sqlDependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
        DataTable dt = new DataTable("Product");
        // Execute command and monitor for changes
        dt.Load(cmd.ExecuteReader());
        dataGridView1.DataSource = dt;
    }
}

Create a SQL command that selects the data you want to monitor for changes. This command will be used to create the SqlDependency object, then register the SQL command with a SqlDependency object.

Next, Handle the OnChange event of the SqlDependency object to receive notifications when the data changes.

How to monitor SQL Server table changes?

Create the UpdateData delegate to monitor sql database change, then update to the DataGridView control.

delegate void UpdateData();

public void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
    SqlDependency sqlDependency = sender as SqlDependency;
    sqlDependency.OnChange -= OnDependencyChange;
    UpdateData updateData = new UpdateData(LoadData);
    this.Invoke(updateData, null);
}

Adding a Form_Load event handler to your form that allows you start SqlDependency, then load data to the DataGridView as shown below.

private void Form1_Load(object sender, EventArgs e)
{
    SqlClientPermission sqlClientPermission = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
    sqlClientPermission.Demand();
    SqlDependency.Start(connectionString);
    LoadData();
}

Don't forget add the Stop method to the Form1_FormClosing event handler.

private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
    SqlDependency.Stop(connectionString);
}

Ensure that query notifications are enabled in your SQL Server database. This involves setting the ENABLE_BROKER and SERVICE_BROKER options to ON.

Open the Microsoft SQL Server Management Studio, then run the sql query below to enable sql service broker to the Northwind database.

ALTER DATABASE Northwind SET ENABLE_BROKER;

Press F5 to run your project, then open the products table and modify data some columns. You can see your application automatically updates data to DataGridView.