SQL Dependency with C#.NET and SQL Server 2012

Introduction

In this tip, we will implement a solution in C#.NET that receives notifications from a SQL server database table as soon as changes occur on that specific table.

In this tip, I will present the different configuration steps that need to be done in SQL server 2012 and the code associated in C#.NET.

Background

The purpose of using SQLDependency is to avoid polling data from a database to check if any changes have occurred. Each time a table changes (insert, delete, etc.), a notification will be sent to the application.

Create Service Broker in SQL Server

The first step to use SQL dependency is to create a Queue and to enable service broker for a specific table. I have already created a table called user with the columns ID and name. This SQL script must be executed to enable service broker for the table User.

CREATE QUEUE SQLDependencyQueue;
CREATE SERVICE SQLDependencyService ON QUEUE SQLDependencyQueue; 
ALTER DATABASE TestBase SET ENABLE_BROKER with immediate rollback;

We may have to grant query notifications permissions to a user by executing the following:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUserName;

Now the configuration is completed and we move to the fun part with the code in C#.

I have created a generic class to have more flexibility. Each time a changes occurs, the generic intercepts the event which will be forwarded to the appropriate listeners.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Security.Permissions;
using System.Data;
using System.Collections;

namespace ConsoleApplication3
{
    //type T must have default constructor
    class Notify<T> where T : new()
    {
        //assign connection string and sql command for listening 
        public Notify(string ConnectionString, string Command)
        {
            this.ConnectionString = ConnectionString;
            CollectionReturn = new List<T>();
            this.Command = Command;
            this.NotifyNewItem();
        }
        //event handler to notify the calling class
        public event EventHandler ItemReceived;
        private bool isFirst = true;
        public string ConnectionString { get; set; }
        public string Command { get; set; }
        //rows to return as a collection 
        public List<T> CollectionReturn { get; set; }
        //check if user has permission 
        private bool DoesUserHavePermission()
        {
            try
            {
                SqlClientPermission clientPermission = 
                       new SqlClientPermission(PermissionState.Unrestricted);
                clientPermission.Demand();
                return true;
            }
            catch
            {
                return false;
            }
        }
        //initiate notification 
private void NotifyNewItem()
        {
            if (DoesUserHavePermission())
            {
                if (isFirst)
                {
                    SqlDependency.Stop(ConnectionString);
                    SqlDependency.Start(ConnectionString);
                }
                try
                {
                    using (SqlConnection conn = new SqlConnection(ConnectionString))
                    {
                        using (SqlCommand com = new SqlCommand(Command, conn))
                        {
                            com.Notification = null;
                            SqlDependency dep = new SqlDependency(com);
                            //subscribe to sql dependency event handler
                            dep.OnChange += new OnChangeEventHandler(dep_OnChange);
                            conn.Open();
                            using (var reader = com.ExecuteReader())
                            {
                                //convert reader to list<T> using reflection 
                                while (reader.Read())
                                {
                                    var obj = Activator.CreateInstance<T>();
                                    var properties = obj.GetType().GetProperties();
                                    foreach (var property in properties)
                                    {
                                        if (reader[property.Name] != DBNull.Value)
                                        {
                                            property.SetValue(obj, reader[property.Name], null);
                                        }
                                    }
                                    CollectionReturn.Add(obj);
                                }
                            }      }
                    }
                }
                catch (Exception ex)
                {
                    //Console.WriteLine(ex.Message);
                } 
           }
        }
        //event handler
        private void dep_OnChange(object sender, SqlNotificationEventArgs e)
        {
            isFirst = false;
            var sometype = e.Info;
            //call notify item again 
            NotifyNewItem();
            //if it s an insert notify the calling class 
            if (sometype == SqlNotificationInfo.Insert)
                onItemReceived(e);
            SqlDependency dep = sender as SqlDependency;
            //unsubscribe 
            dep.OnChange -= new OnChangeEventHandler(dep_OnChange);
        }
        private void onItemReceived(SqlNotificationEventArgs eventArgs)
        {
            EventHandler handler = ItemReceived;
            if (handler != null)
                handler(this, eventArgs);
        }
    }
}

To use the class, we just need to create an instance, specify the connection string and the command to query and subscribe to the event.

There are nevertheless some rules about using SQLDependency (from here for full list):

• The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
• The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
• The statement may not use unnamed columns or duplicate column names.
• The statement must reference a base table.
• The statement must not reference tables with computed columns.
• The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.

I hope you will appreciate my first tip and I wish to contribute more to this community.