Click here to Skip to main content
Click here to Skip to main content
Go to top

Advanced SQL 2005 SQLNotificationRequest Functionality with C#

, 24 Mar 2007
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

Comments and Discussions

 
QuestionMultiple Clients PinmemberBendage27-Oct-11 5:57 
AnswerRe: Multiple Clients PinmemberTodd Wilder27-Oct-11 8:41 
GeneralRe: Multiple Clients Pinmemberaliahmadi13679-Mar-13 2:35 
QuestionDeployment PinmemberHumdy11-Jul-11 0:50 
QuestionDetermine change of a special column Pinmemberbeni27-Jan-09 2:37 
AnswerRe: Determine change of a special column PinmemberTodd Wilder10-Feb-09 16:34 
GeneralMy vote of 1 Pinmemberchenghezhang6-Dec-08 22:16 
GeneralRe: My vote of 1 PinmemberTodd Wilder10-Feb-09 16:29 
QuestionReplace Trigger Pinmemberdipak_narsinghani29-Apr-08 0:17 
GeneralTimeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Pinmembervineet1378018-Apr-08 23:15 
Generala code was not understanded Pinmemberjason_mf5-Mar-08 15:33 
GeneralSQLNotificationRequest Pinmembersssergei31-May-07 2:03 
GeneralRe: SQLNotificationRequest PinmemberTodd Wilder31-May-07 20:17 
GeneralRe: SQLNotificationRequest Pinmembersssergei4-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 | Mobile
Web04 | 2.8.140921.1 | Last Updated 24 Mar 2007
Article Copyright 2007 by Todd Wilder
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid