Think a database notifying you when one of the CRUD operation (CReate, Update and Delete) is made. Think an application taking advantages of it and refresh data in your application in real time. Something like a trigger, but managed from the application code. Think it.. and do it, because you can do it with SQL Server and its Query Notifications.

First of all, we have to do a note: only SQL Server and Oracle support, at the moment, the notifications service. MySQL supports replication streams, but they’re not the same as update notification: they cannot be set up and tear down dynamically for a client and they do not work well for monitoring individual rows a particular application is interested in. All the other DBMS can’t do this operation.

Since not all the SQL Server versions support this feature, we need to check and, if necessary, set the compatibility level of our database, because you can use this features only on SQL Server 2008 through current version.

USE MyDatabaseName;
GO
SELECT compatibility_level
FROM sys.databases WHERE name = 'MyDatabaseName';
GO

The compatibility level has to be at least 100. If it is lower, run this script:

ALTER DATABASE MyDatabaseName SET COMPATIBILITY_LEVEL = 100;

When we are sure that our compatibility level is right, we can run this other script to activate the query notifications:

ALTER DATABASE MyDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

The work on the database side is already done. Now we have to mange this wonderful feature from our application. I have used it in a WPF application, but, as you can image, you can use it in every database-based applications.

First of all, create a file with this class inside (it will be the engine of our notifications):


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

public enum SqlWatcherNotificationType
{
    Blocking,
    Threaded // Launch in another thread so SqlWatcher can immediately start monitoring again.
}

public class SqlWatcher : IDisposable
{
    private string ConnectionString;
    private SqlConnection Connection;
    private SqlCommand Command;
    private SqlDataAdapter Adapter;
    private DataSet Result;
    private SqlWatcherNotificationType NotificationType;

    public SqlWatcher(string ConnectionString, SqlCommand Command, SqlWatcherNotificationType NotificationType)
    {
        this.NotificationType = NotificationType;
        this.ConnectionString = ConnectionString;
        SqlDependency.Start(this.ConnectionString);
        this.Connection = new SqlConnection(this.ConnectionString);
        this.Connection.Open();
        this.Command = Command;
        this.Command.Connection = this.Connection;
        Adapter = new SqlDataAdapter(this.Command);
    }

    public void Start()
    {
        RegisterForChanges();
    }

    public void Stop()
    {
        SqlDependency.Stop(this.ConnectionString);
    }

    public delegate void SqlWatcherEventHandler(DataSet Result);

    public event SqlWatcherEventHandler OnChange;

    public DataSet DataSet
    {
        get { return Result; }
    }

    private void RegisterForChanges()
    {
        //Remove old dependency object
        this.Command.Notification = null;
        //Create new dependency object
        SqlDependency dep = new SqlDependency(this.Command);
        dep.OnChange += new OnChangeEventHandler(Handle_OnChange);
        //Save data
        Result = new DataSet();
        Adapter.Fill(Result, "foo");
        //Notify client of change to DataSet
        switch (NotificationType)
        {
            case SqlWatcherNotificationType.Blocking:
                OnChange(Result);
                break;
            case SqlWatcherNotificationType.Threaded:
                ThreadPool.QueueUserWorkItem(ChangeEventWrapper, Result);
                break;
        }
    }

    public void ChangeEventWrapper(object state)
    {
        DataSet Result = (DataSet)state;
        OnChange(Result);
    }

    private void Handle_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type != SqlNotificationType.Change)
            throw new ApplicationException("Failed to create queue notification subscription!");

        //Clean up the old notification
        SqlDependency dep = (SqlDependency)sender;
        dep.OnChange -= Handle_OnChange;

        //Register for the new notification
        RegisterForChanges();
    }

    public void Dispose()
    {
        Stop();
    }
}

Well, all is ready!! Now we have to do the last operations:

  1. choose the table to be monitored by the notifier;
  2. project the logic to be executed when a operation is notified.

In this function, there is sample for create a SQLWatcher, monitoring a table. It will run a function every time it receives a notify. In this code, I use this feature for checking if there are some new records in a table I use like error log.

        public void SQLServiceStartForLog()
        {
            foreach (var logFromDB in MyNamespace.Entities.dbContext.TabErrorLog)
            {
                if (!logFromDB.READ)
                {
                    hasErrorsToRead = true;
                }
            }
            string connS = MyNamespace.Entities.dbContext.Database.Connection.ConnectionString;
            SqlCommand cmd = new SqlCommand();
            cmd = new SqlCommand("SELECT ID FROM dbo.TabErrorLog");
            cmd.CommandType = CommandType.Text;
            SqlQueueWatcher = new SqlWatcher(connS, cmd, SqlWatcherNotificationType.Blocking);
            SqlQueueWatcher.OnChange += new SqlWatcher.SqlWatcherEventHandler(QueueSQLWatcher_OnChangeForLog);
            SqlQueueWatcher.Start();
        }

And, with these 2 functions, we can say WHAT the application has to do with the notifications. In my personal case, I make a refresh of the log interface.

        private void QueueSQLWatcher_OnChangeForLog(DataSet Result)
        {
            try
            {
                R = Result;
                Application.Current.Dispatcher.Invoke(new delegateUpd(updateLog));
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

        private void updateLog()
        {
            /* WRITE YOU CODE HERE FOR MANAGING THE NEW DATA
			  (INSERTED, UPDATED OR DELETED) FROM YOUR MONITORED TABLE*/
        }

NOTE: if you want to manage in this way a table where you can have multiple inserts or bulk inserts, you can implement a timer, with few second of timeout. The notify managagment will start only when the timer ends and the timer is reset everytime a new notify is received. So, if you insert 1.000 rows in your table, only one notify will be created.

Advertisements