Advanced SQL 2005 SQLNotificationRequest Functionality with C#

• Download script - 19.31 KB

Introduction

SQL Server 2005 and ADO.NET 2.0 promised a powerful notification of database changes through the SQLDependency object. While SQL 2000 could only inform a client app of changes at the Table level, SQL 2005 promised the ability to notify of changes at the query level. If changes to the database table affect a query given to SQL 2005, a notification is given to the client app. If data is changed that does not affect a query given to SQL 2005, then no notification takes place.

Anyone that has tried using the SQLDependency object has found that this functionality does not actually exist. Notifications are still only at the table level, no matter what query is given to SQL 2005 to examine. I present here a workaround using SQL 2005 triggers and TSQL Service Broker Conversation to send a notification to a client app.

Using the Code

Just unzip the attached code, run the SQL script, and then run the C# application. Make changes to the SQLNotificationRequestTable (update, insert, delete), and see your changes be reported on the C# form...

The C# portion of this article is taken from this MSDN page with some slight modifications and one large modification: There is a location in the code where you can handle the Notification as an SQLDataReader and examine the message. I use this functionality to get the messages and display them.

The SQL portion of this article I wrote myself once I learned how the Service Broker and TSQL Triggers worked. The Service Broker is a complex concept, and I was thrilled to actually get my messages working. One other avenue I explored was the Microsoft.SQLServer.Samples namespace, which has some C# classes for the Service Broker, but I actually hit a dead-end using these classes. There may be a better way to do this if you are super knowledgeable in the Service Broker.

The majority of the SQL is devoted to examining the inserted and deleted tables to determine what caused the trigger to fire. The contents of the inserted or deleted tables are copied to a string variable which is then inserted into an XML variable because this Service Broker Message requires well formed XML. Then, in the last part of the SQL, I send the message.

Points of Interest

I have never tried this, but according to documentation Client and Database can be separate computers, and the IP address of the client app would replace 'LocalHost' in the SQL script attached when creating a Service Broker Route. Would love to hear back if anyone tries that.

I also do not know how production quality this is, and I haven't tried this on multiple tables.

I tried to copy snippets of my code into this article, but the formatting would not behave, so refer to the download.

History

• 24^th March, 2007: Initial post