Click here to Skip to main content
11,718,016 members (73,538 online)
Click here to Skip to main content

Advanced SQL 2005 SQLNotificationRequest Functionality with C#

, 24 Mar 2007 CPOL 55.2K 1.6K 40
Rate this:
Please Sign up or sign in to vote.
Using the SQLNotificationRequest object and the Service Broker to get row and column change notifications, mimicking supposed SQLDependency capabilities

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

  • 24th March, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Todd Wilder
Web Developer
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 1 Pin
Member 1079835224-Nov-14 21:05
memberMember 1079835224-Nov-14 21:05 
QuestionMultiple Clients Pin
Bendage27-Oct-11 5:57
memberBendage27-Oct-11 5:57 
AnswerRe: Multiple Clients Pin
Todd Wilder27-Oct-11 8:41
memberTodd Wilder27-Oct-11 8:41 
GeneralRe: Multiple Clients Pin
aliahmadi13679-Mar-13 2:35
memberaliahmadi13679-Mar-13 2:35 
QuestionDeployment Pin
Humdy11-Jul-11 0:50
memberHumdy11-Jul-11 0:50 
QuestionDetermine change of a special column Pin
beni27-Jan-09 2:37
memberbeni27-Jan-09 2:37 
AnswerRe: Determine change of a special column Pin
Todd Wilder10-Feb-09 16:34
memberTodd Wilder10-Feb-09 16:34 
sorry for the late reply. Because we use a trigger we can get some good information about exactly what changed. A quick google for detecting column level changes in TSQL trigger shows...

http://msdn.microsoft.com/en-us/library/ms187326.aspx

apparently the Update() function, run on each column, will tell you which ones changed
GeneralMy vote of 1 Pin
chenghezhang6-Dec-08 22:16
memberchenghezhang6-Dec-08 22:16 
GeneralRe: My vote of 1 Pin
Todd Wilder10-Feb-09 16:29
memberTodd Wilder10-Feb-09 16:29 
QuestionReplace Trigger Pin
dipak_narsinghani29-Apr-08 0:17
memberdipak_narsinghani29-Apr-08 0:17 
GeneralTimeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Pin
vineet1378018-Apr-08 23:15
membervineet1378018-Apr-08 23:15 
Generala code was not understanded Pin
jason_mf5-Mar-08 15:33
memberjason_mf5-Mar-08 15:33 
GeneralSQLNotificationRequest Pin
sssergei31-May-07 2:03
membersssergei31-May-07 2:03 
GeneralRe: SQLNotificationRequest Pin
Todd Wilder31-May-07 20:17
memberTodd Wilder31-May-07 20:17 
GeneralRe: SQLNotificationRequest Pin
sssergei4-Jun-07 5:28
membersssergei4-Jun-07 5:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150901.1 | Last Updated 24 Mar 2007
Article Copyright 2007 by Todd Wilder
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid