Windows Forms: Monitoring data change using SqlDependency in C#

Drag 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.

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))
    {
        if (cn.State == ConnectionState.Closed)
            cn.Open();
        SqlCommand cmd = new SqlCommand("select ProductID, ProductName, UnitPrice from Products", cn);
        cmd.Notification = null;
        SqlDependency sqlDependency = new SqlDependency(cmd);
        sqlDependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
        DataTable dt = new DataTable("Product");
        dt.Load(cmd.ExecuteReader());
        dataGridView1.DataSource = dt;
    }
}

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);
}

Add code to handle Form_Load event 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 Form22_FormClosing(object sender, FormClosingEventArgs e)
{
    SqlDependency.Stop(connectionString);
}

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.