Monitoring data change using SqlDependency and C#

Jaspalsinh Chauhan -

What is SqlDependency?

As the word describes itself, it is used to notify your application or user when certain criteria or condition is satisfied. This condition (query in technical terms) is provided to SQL notification service to be observed upon and ask to be notified when the condition is met or the data changes. In short the SqlDependency provides capabilities to your application to monitor your database for data change without the hassles of continuously querying the database using timers or loops.

I will walk you through the simplest way to enable SqlDependency in your application. For the purpose of this example I am using SQL Server 2012 Express, it also works for other versions of SQL database like SQL 2008 and 2005.

The part of SQL Server that make things happen: SQL Server – SQL Service Broker and Queues.

SQL Service broker (Ref: MSDN) –

It is a feature of SQL Server where external or internal processes can send and receive asynchronous messages reliably by using extensions of Transact-SQL Data Manipulation Language (DML).

SQL Server Queues (Ref: MSDN) –

Service broker uses queues to provide loose coupling between the message sender and the message receiver.

The information of Service Broker capabilities for each database is stored in table named SYS.DATABASES. To check if Service Broker is enabled for your target database you can the following query with more condition.

Query:

SELECT NAME, IS_BROKER_ENABLED FROM SYS.DATABASES

For this sample we have created a sample database named SampleDb.

To active Service broker on a database fire the following query:

Query:

ALTER DATABASE SampleDb SET ENABLE_BROKER

To deactivate Service broker on a database fire the following query:

Query:

ALTER DATABASE SampleDb SET DISABLE_BROKER

Note # 1: If Service Broker is not enabled on your database you will get an exception as below:

Now we create a sample table for which we will enable notifications:

Query:

USE [SampleDb]
CREATE TABLE [dbo].[SampleTable01](
    [SampleId] [bigint] IDENTITY(1,1) NOT NULL,
    [SampleName] [nvarchar](50) NOT NULL,
    [SampleCategory] [nvarchar](50) NOT NULL,
    [SampleDateTime] [datetime] NOT NULL,
    [IsSampleProcessed] [bit] NOT NULL)
 ON [PRIMARY];

The application needs to provide a query for which the notification service will monitor data change. This can be done either by providing a stored procedure or with a regular text based query from your code.

Stored Procedure way:

Create a store procedure of query for which you wish to receive data change events:

Query:

GO
CREATE PROCEDURE uspGetSampleInformation
AS
BEGIN
    SELECT
        [SampleId],
        [SampleName],
        [SampleCategory],
        [SampleDateTime],
        [IsSampleProcessed]
    FROM
        [dbo].[SampleTable01];
END

Text Based Query way:

SELECT
	[SampleId],
	[SampleName],
	[SampleCategory],
	[SampleDateTime],
	[IsSampleProcessed]
FROM
	[dbo].[SampleTable01];

Note # 2:

  1. Use of [dbo] – table schema name in the query. This is important to get proper notification.

  2. Query will not be valid if you are using * for your select query. You need to compulsory specify the column names in your query to get notified.

Now we are ready to crack some code!!

The notification is handled in code using SqlDependency class in System.Data.SqlClient namespace.

Step # 1: Define the connection string in configuration file of your application, App.Config (in my case).

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
    <connectionStrings>
      <add name="SampleDbConnection"
  connectionString="Server=PathToSqlServer;
  Database=SampleDb;User Id=YourSqlUserName;
  Password=YourSqlPassword;"
  providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Step # 2: You need to start the listener for receiving dependency change notification for your query from your client application.

Use static method Start () to start the listener for SqlDependency:

SqlDependency.Start(yourConnectionString, nameOfSqlServerServiceBrokerQueue);

Note: You can specify the SQL server service broker queue name like QueueSampleInformationDataChange in our case or provide only the connection string, in that case it will use the default queue for data change notification.

Use static method Stop() to stop the listener for SqlDependency:

SqlDependency.Stop(yourConnectionString);

If you the queue name specified in Start() method or the default queue is not enabled it will throw exception as below:

Also, if the Queue name you specified is not valid or not created in the SQL server it will throw exception as below:

Step # 3: Create instance of SqlConnection by fetching the connection string from configuration file.

this.sampleSqlConnection =
new SqlConnection(
ConfigurationManager.ConnectionStrings["SampleDbConnection"].ConnectionString);

Step # 4: Now we create an instance of SqlCommand using above SqlConnection.

Case#1- Using stored procedure.

this.sampleSqlCommand = new SqlCommand();
this.sampleSqlCommand.Connection = this.sampleSqlConnection;
await this.sampleSqlCommand.Connection.OpenAsync();
this.sampleSqlCommand.CommandType = CommandType.StoredProcedure;
this.sampleSqlCommand.CommandText = "uspGetSampleInformation";
this.sampleSqlCommand.Notification = null;

Case#2- Using text based query:

this.sampleSqlCommand = new SqlCommand();
this.sampleSqlCommand.Connection = this.sampleSqlConnection;
await this.sampleSqlCommand.Connection.OpenAsync();
this.sampleSqlCommand.CommandType = CommandType.Text;
this.sampleSqlCommand.CommandText = "SELECT [SampleId],[SampleName], [SampleCategory], [SampleDateTime], [IsSampleProcessed] FROM [dbo].[SampleTable01];";
this.sampleSqlCommand.Notification = null;

Step # 5: Create an instance of SqlDependency for above command and subscribe for data change notification event.

this.sampleSqlDependency = new SqlDependency(this.sampleSqlCommand);
this.sampleSqlDependency.OnChange += this.SqlDependencyOnChange;
await this.sampleSqlCommand.ExecuteReaderAsync();

When data change occurred for the above query the subscribed event will be fired providing details of data change in event argument of type SqlNotificationEventArgs.

SqlNotificationEventArgs contains three properties:

1. Info – Indicates the reason for the notification event of enum type SqlNotificationInfo

2. Source – Indicates the source that generated the notification of enum type SqlNotificationSource

**3. Type **– indicates whether this notification is generated because of an actual change, or by the subscription of enum type SqlNotificationType

private void SqlDependencyOnChange(object sender, SqlNotificationEventArgs eventArgs)
        {
            if (eventArgs.Info == SqlNotificationInfo.Invalid)
            {
                Console.WriteLine("The above notification query is not valid.");
            }
            else
            {
                Console.WriteLine("Notification Info: " + eventArgs.Info);
                Console.WriteLine("Notification source: " + eventArgs.Source);
                Console.WriteLine("Notification type: " + eventArgs.Type);
            }
        }

Notes:

  1. Remember if eventArgs.Info is equal to SqlNotificationInfo.Invalid, means the SQL query is not valid or the input command or stored procedure name is not in correct format.

  2. Consider the use of async methods that are added in .NET 4.5 and utilized it using async – await keyword.

For complete sample implementation download from GitHub. Run the sample and perform any operation on the SampleTable01 (like Insert, Update, Delete etc.) to see the event notification to work.

Final and Important Note: - SqlDependency notification is to get notified for type of data change not to get the exact detail of what is changed, for that you need to implement some technique of your own or use other options.

Credits:

1. MSDN: Query Notification in SQL Server

2. MSDN: Understanding SQL Dependencies