For one of my clients, I created a dashboard to get a centralized view where the status of various processes, devices, data quality, … is presented. It is very convenient to check at a single place instead of looking at over 50 emails in the morning (and continuously during the day). We can now look just at a single screen and anything colored other than green must be verified. As they say, a picture is worth a thousand words! After the screen has been set up, I wanted to update it in a timely manner.

A bit of background

In the first iteration of the dashboard, I was polling various databases/tables/devices on a schedule (different processes have different schedule). But it wasn’t very accurate. Some statuses were updated only twice a day (because the process to get the status is a heavy one). So after a few hours, the dashboard was still showing a green status for something that wasn’t ok anymore. I have then modified my processes to update a table in a database (called DatabaseEvent) to warn that it just ended. This table can now become a trigger to update parts of my dashboard as the processes are finishing without a negative impact on everything else.

Back in March 2012, I wrote an article on SQLDependency. I was thinking about using that mechanism against my DatabaseEvent table to update my dashboard. I proved it to be working. But… there is always something falling short, I had no way of finding out which one of the process just ended (which precise row of the table was updated). The SQLDependency triggers an event (OnChange) when one of the rows of the SELECT statement is affected without saying which one it was. It is an all or nothing mechanism. Since I didn’t want to update the full dashboard when only one of the processes has something to report, it wasn’t very efficient (because not all the processes are receiving a status from this DatabaseEnvent table).

Creating a SQLDependency object for each of my processes is not optimal neither as I have too many and more are added on a monthly basis. The dashboard automatically adjusts based on a configuration table. Also, each dependency object creates a bit of burden on the database.

I needed to find a better way.

The downloadable code

This month’s solution contains both VB and C# projects. The solution was created using Visual Studio 2017, but the code should work as well in older (or newer) versions of the .Net Framework because there isn’t really nothing very fancy.

A nice free library to the rescue

I found a nice free library on GitHub published by Christian Del Bianco called SQL Table Dependency. This library is easy to implement and works as expected even providing some bonus features.

The very first thing to do is to add the library to your project by using the “Manage NuGet Package”, searching for the latest version of SQLTableDependency and click the Install button.

Figure 1: Adding the required NuGet package

Setting up the database

To be able to use this library, you will need to use Microsoft SQL Server 2008 R2 or better. The database that will contain your monitored table, needs to have the Enable_Broker option set.

This script takes care of creating a database, a login, a table, and insert some initial data as required by the demo application:

USE master
GO

/* Drop the login if it is already existing */
IF EXISTS(SELECT name FROM master.sys.server_principals WHERE name = 'DemoUserDep')
	DROP LOGIN DemoUserDep
GO 

/* Drop the database if it is already existing */
DROP DATABASE IF EXISTS [DemoNotifications2]
GO 

/* Create a new database and enable the broker service */
CREATE DATABASE [DemoNotifications2] 
GO
ALTER DATABASE [DemoNotifications2] SET  ENABLE_BROKER 
GO

/* Create a new test login and add it has a DbOwner */
USE DemoNotifications2
GO 
CREATE LOGIN DemoUserDep WITH PASSWORD='demo'
GO
CREATE USER [DemoUserDep] FOR LOGIN [DemoUserDep]
GO
EXEC sp_addrolemember N'db_owner', N'DemoUserDep'
GO

/* Create a new table */
CREATE TABLE [dbo].[NotificationTrigger](
	[Code] [varchar](50) NOT NULL PRIMARY KEY CLUSTERED,
	[LastUpdate] [datetime] NOT NULL
)

/* Inserts data in the table */
INSERT INTO dbo.NotificationTrigger( Code, LastUpdate ) 
VALUES  
( 'Employees', GETDATE() )
, ( 'Countries', GETDATE() )
, ( 'Other', GETDATE() )

SELECT * FROM dbo.NotificationTrigger

Building the UI

The UI of the demo application is simple. A few buttons to update existing rows or insert new ones, a grid showing the current content of the table, and a listbox showing the various events happening as the application is running.

Figure 2: The demo application in action

The code

I will not show the code to insert or update the data. There is no interest. if you want to see it, it is available in the downloadable code.

The FillGrid method is called every time an event is triggered by the database. My demo method cuts the corners by always refreshing the full grid. Depending on your UI, you can decide to only refresh part of it because you know exactly what happened (provided by the ChangeType) and which data was changed.

Private Sub FillGrid(ByVal pChangeType As ChangeType, ByVal pCode As String, ByVal pLastUpdate As DateTime)
    Using conn As SqlConnection = New SqlConnection(ConnectionString)
        conn.Open()
        Dim strQuerySelect As String = "SELECT Code, LastUpdate FROM NotificationTrigger ORDER BY Code"

        Using cm As SqlCommand = New SqlCommand(strQuerySelect, conn)

            Using adapter As SqlDataAdapter = New SqlDataAdapter(cm)
                Dim ds As DataSet = New DataSet()
                adapter.Fill(ds)
                grdData.DataSource = ds.Tables(0)
            End Using
        End Using
    End Using

    listBox1.Items.Add($"OnDatabaseEventChanged - {pChangeType} - {pCode} - {pLastUpdate}")
    grpData.Text = "Data - Last Update at " & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
End Sub

private void FillGrid(ChangeType pChangeType, string pCode, DateTime pLastUpdate)
{
	using (SqlConnection conn = new SqlConnection(ConnectionString))
	{
		conn.Open();

		string strQuerySelect = "SELECT Code, LastUpdate FROM NotificationTrigger ORDER BY Code";
		using (SqlCommand cm = new SqlCommand(strQuerySelect, conn))
		{
			using (SqlDataAdapter adapter = new SqlDataAdapter(cm))
			{
				DataSet ds = new DataSet();
				adapter.Fill(ds);
				grdData.DataSource = ds.Tables[0];
			}
		}
	}

	listBox1.Items.Add($"OnDatabaseEventChanged - {pChangeType} - {pCode} - {pLastUpdate}");
	grpData.Text = "Data - Last Update at " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}

Now we want to setup code to handle database events triggered by this table. And because that library was done in a very great way, it is using a generic type so we can get Intellisense. Therefore, we need a small class that will look like the fields of the table that we want to monitor. I have created properties in that class with the same names as the table fields. If you cannot create this class to match exactly the columns’ names and types, you will need to use mapping to point from one structure to another.

The following code declare a method named RegisterForDatabaseEvents. This method instantiates the SqlTableDependency object, register for the OnChanged event and starts the monitoring. Because the event will not happen on the UI thread, we need to use Invoke to return to the correct thread to assign controls.

Private _sqlTableDependency As SqlTableDependency(Of NotificationTrigger)
Private Delegate Sub UiCallback(ByVal pChangeType As ChangeType, ByVal pCode As String, ByVal pLastUpdate As DateTime)

Private Sub RegisterForDatabaseEvents()
	Try
		_sqlTableDependency = New SqlTableDependency(Of NotificationTrigger)(ConnectionString, "NotificationTrigger")
		AddHandler _sqlTableDependency.OnChanged, AddressOf OnDatabaseEventChanged
		_sqlTableDependency.Start()
	Catch ex As Exception
		MessageBox.Show(ex.Message)
	End Try
End Sub

Private Sub OnDatabaseEventChanged(ByVal sender As Object, ByVal e As RecordChangedEventArgs(Of NotificationTrigger))
	Invoke(New UiCallback(AddressOf FillGrid), e.ChangeType, e.Entity.Code, e.Entity.LastUpdate)
End Sub

Public Class NotificationTrigger
    Public Property Code As String
    Public Property LastUpdate As DateTime
End Class

private SqlTableDependency<NotificationTrigger> _sqlTableDependency;
private delegate void UiCallback(ChangeType pChangeType, string pCode, DateTime pLastUpdate);

private void RegisterForDatabaseEvents()
{
	try
	{
		_sqlTableDependency = new SqlTableDependency<NotificationTrigger>(ConnectionString, "NotificationTrigger");
		_sqlTableDependency.OnChanged += OnDatabaseEventChanged;
		_sqlTableDependency.Start();
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
	}
}

private void OnDatabaseEventChanged(object sender, RecordChangedEventArgs<NotificationTrigger> e)
{
	Invoke(new UiCallback(FillGrid), e.ChangeType, e.Entity.Code, e.Entity.LastUpdate);
}

public class NotificationTrigger
{
    public string Code { get; set; }
    public DateTime LastUpdate { get; set; }
}

Now when you run the code, and click the various Update/Insert buttons, you should see your DataGrid and ListBox being refreshed with the data you played with. But as one might expect, the modifications do not have to come only from the same application. Open SSMS and have fun with the data while leaving the application running. You should see again the DataGrid and the ListBox being refreshed.

Notice that like the SqlDependency object, the SqlTableDependency has some limitations (like the supported datatypes). Be sure to check this section on the GitHub project page.

Conclusion

Sometimes we are expecting more than what is provided in the box. But most of the time we are not the first one having reached the limitations of the built-in .Net components. Luckily, great people like Christian Del Bianco are putting code together and makes it available to all of us!