Step-by-step guide to get notifications in C# when a SQL database table changes

Article
2016-08-11

Question

_{Thursday, August 11, 2016 7:06 AM}

Hi everyone

I've searched the web, but didn't find a working step-by-step guide how to get notifications in C# when a SQL database table changes without 3rd party plugins/ NuGets.

Let's say we have installed a Microsoft SQL Server (default installation) and a database called "TestDB" created (with default options) with "Microsoft SQL Server Management Studio". This database has one table, called "Table1" with two rows: "ID" and "Name". "ID" is the primary key, "Name" is a string (varchar(max)).

What I need:

A step-by-step guide what to do in "Microsoft SQL Server Management Studio" (or outside "Microsoft SQL Server Management Studio") to enable notifications (and, how to disable them again, just in case...).
A short explanation for SQL beginners what these modifications of SQL Server, "TestDB", or "Table1" do.
The code of a C# Hello-World console application, that is as short as possible, uses no 3rd party plugins/ NuGets, and writes "Change detected." to the console every time a row in "Table1" is added, edited, or deleted. LINQ-to-SQL can be used.
Optionally, the Hello-World program may also output what kind of change was detected (add, edit, delete).

Thanks very much,
Thomas

All replies (11)

_{Thursday, August 11, 2016 9:40 AM ✅Answered | 1 vote}

Hello,

See Using SqlDependency for data change events.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Thursday, August 11, 2016 1:48 PM ✅Answered | 1 vote}

So, here is my solution to my question.

Well, it's not a console application, but a WinForms application, and watches only the first row of the table. But, it shows what needs to be done.

using System;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
 public partial class Form1 : Form
 {
  DataClassesDataContext dataContext;
  SqlConnection sqlConnection;
  SqlCommand sqlCommand;

  public Form1()
  {
   InitializeComponent();
  }

  private void Form1_Load(object sender, EventArgs e)
  {
   // Remark: Run "ALTER DATABASE TestDB SET ENABLE_BROKER" from "Microsoft SQL Server Management Studio" once, before executing this code for the first time.
   dataContext = new DataClassesDataContext(); // optional LINQ-to-SQL data context.
   sqlConnection = new SqlConnection(dataContext.Connection.ConnectionString);
   sqlConnection.Open();
   SqlDependency.Start(dataContext.Connection.ConnectionString);
   sqlCommand = new SqlCommand("SELECT Name FROM dbo.Table1 WHERE ID = 1", sqlConnection); // "dbo" is required for SqlDependency: http://stackoverflow.com/questions/7946885/sqldependency-notification-immediate-failure-notification-after-executing-quer
                         // The SQL command must meet special conditions: https://msdn.microsoft.com/en-us/library/aewzkxxh.aspx
   SqlDependency sqlDependency = new SqlDependency(sqlCommand); // Also sets sqlCommand.Notification.
   sqlDependency.OnChange += SqlDependecy_OnChange;
   label1.Text = sqlCommand.ExecuteScalar().ToString(); // SQL command must be executed AFTER "SqlDependency sqlDependency = new SqlDependency(sqlCommand)".
  }

  private void Form1_FormClosing(object sender, FormClosingEventArgs e)
  {
   SqlDependency.Stop(dataContext.Connection.ConnectionString);
   sqlConnection.Close();
  }
  private void SqlDependecy_OnChange(object sender, SqlNotificationEventArgs e)
  {
   if (InvokeRequired)
    Invoke(new Action(() => SqlDependecy_OnChange(sender, e)));
   else
   {
    // The OnChange event of a SqlDependency fires only once. When the result of the SQL command changes for the second time, the OnChange event does not fire again.
    // http://www.codeproject.com/Articles/12335/Using-SqlDependency-for-data-change-events
    // https://msdn.microsoft.com/en-us/library/a52dhwx7.aspx
    // So, detach this event handler and create a new SqlDependency.
    SqlDependency sqlDependency = (SqlDependency)sender;
    sqlDependency.OnChange -= SqlDependecy_OnChange;
    sqlCommand.Notification = null; // Make sqlCommand forget sqlDependency. Otherwise, the next line of code throws an exception.
    sqlDependency = new SqlDependency(sqlCommand);
    sqlDependency.OnChange += SqlDependecy_OnChange;

    // Execute the SQL command again AFTER "SqlDependency sqlDependency = new SqlDependency(sqlCommand)".
    label1.Text = sqlCommand.ExecuteScalar().ToString();
   }
  }
 }
}

_{Thursday, August 11, 2016 7:18 AM}

read about SSIS

_{Thursday, August 11, 2016 9:35 AM}

Hi everyone

I've searched the web, but didn't find a working step-by-step guide how to get notifications in C# when a SQL database table changes without 3rd party plugins/ NuGets.

Let's say we have installed a Microsoft SQL Server (default installation) and a database called "TestDB" created (with default options) with "Microsoft SQL Server Management Studio". This database has one table, called "Table1" with two rows: "ID" and "Name". "ID" is the primary key, "Name" is a string (varchar(max)).

What I need:

A step-by-step guide what to do in "Microsoft SQL Server Management Studio" (or outside "Microsoft SQL Server Management Studio") to enable notifications (and, how to disable them again, just in case...).

A short explanation for SQL beginners what these modifications of SQL Server, "TestDB", or "Table1" do.

The code of a C# Hello-World console application, that is as short as possible, uses no 3rd party plugins/ NuGets, and writes "Change detected." to the console every time a row in "Table1" is added, edited, or deleted. LINQ-to-SQL can be used.

Optionally, the Hello-World program may also output what kind of change was detected (add, edit, delete).

Thanks very much,
Thomas

That pattern was not very viable to begin with, so naturally it never got developed. Trust me, have been there too.
I considerd locking to avoid those issues, only to have to realise it is not viable.

If you want to avoid the Update race condition the approach is simple:
Either a SQL ROWVERSION column to be checked during Update.
Or a DateTime collumn updated via trigger, checked during Update.

You just put a column in that is changed on any DML statement and you can detect any Update race conditions. Personally I would prefer Rowversion, because it is tied into the DBMS itself.

_{Thursday, August 11, 2016 1:22 PM}

Thanks for your hint. It made me take another look at SqlDependency. This time I was able to find several web pages, each of which provided a part of the puzzle. I will post the solution to my question soon.

_{Thursday, August 11, 2016 4:54 PM}

I'm not certain, why you would even leave the database in order to achieve this task. With modern versions of SQL Server, there is a built in utility called Change Data Capture (CDC). This logs changes to specific tables, including the type of change, the before and after values, and the login that performed the change. You can then create a process in SQL Server, either an SP or an SSIS package, to generate a detailed email with the required data.

_{Thursday, August 11, 2016 7:31 PM}

It's needed by the inhouse software of my company. One department enters detailed product information of a product that should be bought, another department accepts the request or rejects it or sends it backs to the first department, so they can add missing data or edit wrong data.

The data is displayed in the inhouse software. So, when one department changes data, the application instance run by the other department needs to update its LINQ-to-SQL data context and display the new data in real-time, i. e. without the user clicking a "Refresh" button or so.

_{Thursday, August 11, 2016 8:47 PM}

It's needed by the inhouse software of my company. One department enters detailed product information of a product that should be bought, another department accepts the request or rejects it or sends it backs to the first department, so they can add missing data or edit wrong data.

The data is displayed in the inhouse software. So, when one department changes data, the application instance run by the other department needs to update its LINQ-to-SQL data context and display the new data in real-time, i. e. without the user clicking a "Refresh" button or so.

The simple option would be to just do the querry again, to get the updated data.

But as long as it stays this really small scale (a handful lof clients, 1 dataset each) you could use change notification as additional option. Note that you should still first provide a manual reload function.

_{Thursday, August 18, 2016 10:36 AM}

It turned out, that there is one thing more to consider: User permissions for the SQL server database.

Here is what I found: http://keithelder.net/2009/01/20/sqldependency-and-sql-service-broker-permissions/

--Database level permissions
GRANT CREATE PROCEDURE TO [UserGroup];
GRANT CREATE QUEUE TO [UserGroup];
GRANT CREATE SERVICE TO [UserGroup];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [UserGroup];
GRANT VIEW DEFINITION TO [UserGroup];

--Service Broker permissions
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [UserGroup];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [UserGroup];

You can also set these permissions via Microsoft SQL Server Management Studio:

In the Object Explorer, expand your database, then Security/Users. Select the user group that needs the permissions. Under "Select a page", select "Securables", then "Search...".

A dialog window pops up, select "Specific objects...".

Another dialog window pops up, select "Object Types...".

Select "Databases" and "Queues". Then click "Browse..." and select your database and its QueryNotificationErrorsQueue. Click OK.

Now, you can set the required permissions for the database and its QueryNotificationErrorsQueue.

BUT: I didn't find a way yet how to set the permission "GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [UserGroup]" via Microsoft SQL Server Management Studio. Does anyone know?

_{Thursday, August 18, 2016 11:18 PM}

All of the T-SQL code in that article may be executed on your database server in a query window by simply replacing the values in brackets with the ones for your environment.

_{Friday, August 19, 2016 6:16 AM}

I know, but I prefer the GUI way.

Share via

Step-by-step guide to get notifications in C# when a SQL database table changes

Question

All replies (11)

Additional resources