Very often, developers need a way to refresh the UI when something changes in the database. I can safely say that instead of having their application warned that something has changed, developers usually just re-query the database every so often (most of the time based on a timer).

Since Microsoft SQL Server 2005 has been released, a new more appropriate mechanism is available but unknown from most developers I have met. This mechanism is available straight from ADO.Net and is known as SQL Dependency. ADO.Net relies on SQL Service Broker to be warned.

The mechanism is simple. You create a subscription to receive notifications whenever the underlying data of a query changes on the server. The application receives the notification in the form of an event.

Demo application

The downloadable demo application is available this month in both VB and C#. It was created using Visual Studio 2010 (but you could recreate it using Visual Studio 2005 and higher).

You will also need Microsoft SQL Server 2005 and higher to test this mechanism. The script I am providing here has been tested on SQL Server 2008 R2.

Figure 1: The demo application in action

Database Setup

For this demo to work, I strongly suggest that you create a new database before trying to use it in your current database (the downloadable demo contains the full script to create the test database).

The first step is to create a new database for which we need to set a special option (Enable_Broker):

CREATE DATABASE [DemoNotifications2] 
GO
ALTER DATABASE [DemoNotifications2] SET  ENABLE_BROKER 
GO

Because there are permissions to give, you might want to create special login and user:

CREATE LOGIN DemoUserDep WITH PASSWORD='demo'
GO
CREATE USER [DemoUserDep] FOR LOGIN [DemoUserDep]
GO
EXEC sp_addrolemember N'db_owner', N'DemoUserDep'
GO

We are now ready to create a table. In the section speaking of the best practices, you will learn why I like this small table to trigger the events for the dependency object. The table is plain and has absolutely nothing saying it will be used to monitor dependencies:

CREATE TABLE [dbo].[NotificationTrigger](
	[Code] [varchar](50) NOT NULL PRIMARY KEY CLUSTERED,
	[LastUpdate] [datetime] NOT NULL
)

Finally, I insert some test data that goes with the demo application:

INSERT INTO dbo.NotificationTrigger( Code, LastUpdate ) 
VALUES  ( 'Employees', GETDATE() )

INSERT INTO dbo.NotificationTrigger( Code, LastUpdate ) 
VALUES  ( 'Countries', GETDATE() )

INSERT INTO dbo.NotificationTrigger( Code, LastUpdate ) 
VALUES  ( 'Other', GETDATE() )

The demo application – part 1 – updating the table

Now that we have a database, we would like to have an application that will be able to react when one of the values is modified. We won’t check the full database because of the overhead. Instead, we will limit our self to what is really required.

My test UI is very simple. It only contains 4 buttons and a data grid. The first 3 buttons each update one row of the trigger table. The last button is to start the monitoring of the table changes.

The first 3 buttons all have the very same code to the exception of a little word saying which of the row is getting updated (Employees, Countries, or Other). It is a simple update command which updates the LastUpdate column of the table.

Using conn As New SqlConnection(CS)
    conn.Open()
    Using cm As New SqlCommand(String.Format(QueryUpdate, "Employees"), conn)
        cm.ExecuteNonQuery()
    End Using
End Using

This method relies on 2 constants defined at the top of the class:

Private Const CS As String = "Data Source=moer-x220t;" +
                            "User ID=DemoUserDep;Password=demo;" +
                            "Initial Catalog=DemoNotifications2;" +
                            "Network Library=dbmssocn;" +
                            "Application Name=DemoDep;"

Private Const QueryUpdate As String = "UPDATE NotificationTrigger SET LastUpdate = GETDATE() WHERE Code = '{0}'"

Don’t forget to customize the first constant (CS) which is the connection string. You should modify the DataSource as I really doubt you have the same computer name as I do.

The second constant (QueryUpdate), is the update command that will be sent to the database. As you can see, I have a place holder for a value ({0}) which will get replaced by the String.Format method.

At this point, you should test the application to see if it is at least updating the table correctly.

The demo application – part 2 – Monitoring changes

We are now ready for the fun part. As you will soon discover, there is not a lot of code involved.

The first thing is to add code behind the last button of our screen. It contains a single line. It calls a method that we will create next:

StartChecking()

This new method creates a DataAdapter with a SELECT query. This DataAdapter will then be used by the constructor of a SqlDependency object. A handler of the OnChange event of the SqlDependency object is created. We start the SqlDependency object. And finally, we need to use the DataAdapter at least once.

It might sound complicated but it really isn’t. Here is all the code required:

Private Sub StartChecking()
    Dim cn As SqlClient.SqlConnection = Nothing
    cn = New SqlClient.SqlConnection(CS)

    Dim dad As New SqlClient.SqlDataAdapter(QueryDependency, cn)

    Dim dependency As New SqlClient.SqlDependency(dad.SelectCommand)
    AddHandler dependency.OnChange, AddressOf Me.OnNotificationChange
    SqlClient.SqlDependency.Start(CS)

    Dim ds As New DataSet
    dad.Fill(ds)
End Sub

There is another constant used in the StartChecking method. In my demo application, this constant is defined like this (I only want to be warned if one of these 2 rows are being modified:

Private Const QueryDependency As String = "SELECT LastUpdate FROM dbo.NotificationTrigger WHERE Code IN ( 'Employees', 'Countries' )"

This method is giving the address of OnNotificationChange to handle the event. We need to provide this method. Since this event will come up from a thread different than the one running the form, we need to invoke the call to fill the data grid on screen. Once the data grid is filled, we need to restart the dependency object. This is the code:

Private Sub OnNotificationChange(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)
    Me.Invoke(New UICallback(AddressOf FillGrid))
    StartChecking()
End Sub

The last missing part is the FillGrid method which simply fill the data grid with the content of the table:

Private Sub FillGrid()
    Using conn As New SqlConnection(CS)
        conn.Open()
        Using cm As New SqlCommand(QuerySelect, conn)
            Using adapter As New SqlDataAdapter(cm)
                Dim ds As New DataSet
                adapter.Fill(ds)
                grdData.DataSource = ds.Tables(0)
            End Using
        End Using
    End Using
    grpData.Text = "Data - Last Update at " + Date.Now.ToString("yyyy-MM-dd HH:mm:ss")
End Sub

Try it

You now have all the parts to run it. After you started the application, you can click the “Start Monitoring” button and nothing will happen. Only if you press the “Update Employees” or the “Update Countries” you will find the grid to be refreshed as well as the caption of the resulting group box. If you press “Update Others”, even if the table is really getting updated in the database, you won’t have the data grid refreshed because the QueryDependency is voluntarily filtering out anything but Employees and Countries.

Do the extra step of opening SSMS (SQL Server Management Studio) and update the table while the application is running. You will find your data grid to be refreshed. The update does not have to come from your own application to have it to react.

Best practices

It is a good idea to limit the number of dependencies you create because resources on the database server are created. You won’t notice it if you only have 10 users but what would happen if you have 1000 users each having 20 dependencies object active at the same time? I haven’t found any official number but apparently, 50 concurrent dependencies is the maximum you should target.

This is an important thing to keep this in mind if you don’t want to kill your server.

Using a NotificationTrigger table

To bypass many limitations and to limit the number of data monitored, I have found an interesting and very simple mechanism. I have created a small table in my database with 2 columns that you can create with this simple script:

CREATE TABLE [dbo].[NotificationTrigger](
	[Code] [varchar](50) NOT NULL PRIMARY KEY CLUSTERED,
	[LastUpdate] [datetime] NOT NULL
) 

With this table, instead of creating a dependency on a query like this one that might potentially contains one of the many limitations:

SELECT FirstName, LastName, DOB, Salary, ADDRESS, City, STATE, Zip, ... FieldX FROM MyLargeTableOfValues

You will create a dependency on very simple query like this:

SELECT LastUpdate FROM dbo.NotificationTrigger WHERE Code = 'EmployeeUpdate'

If you import a batch of data, instead of using triggers, you can simply update the NotificationTrigger table only once at the end of your batch. This allows you to restrict the number of event to a minimum.

You can create also triggers to update your NotificationTrigger table.

Many things can go wrong with this setup

I would just like to remind you that this mechanism is complex and involves many little things that need to be setup properly.

One very important thing is the setup of your database and the user that connect to it. Be sure to follow the script I have provided to set them up properly.

The other thing that might prevent you from getting your events is that your query is too complex. There is a very long list of SQL statements that will prevent you from receiving notifications. You will find the complete list of limitations by visiting the “Supported SELECT Statements” section from the “Creating a Query for Notification” page of MSDN. For example, you cannot use SELECT *, no PIVOT, no UNION, no DISTINCT, no AVG, no COUNT(*), no TOP clause, … and the list continues.

One very strange case I have personally met is that the query to create your dependency object seems to absolutely require the schema. That means that this query might not work:

SELECT LastUpdate FROM NotificationTrigger

In my case, it was really requiring:

SELECT LastUpdate FROM dbo.NotificationTrigger

Conclusion

Instead of querying your database over and over the next time you need to display up-to-date data, remember that you can use the SQL Dependency object.