Query Notification to .NET Application (SQLDependency & SQLCacheDependency)

SQL Server 2005 comes with a new concept which lets you to get automatic notifications to your client application whenever some data is changed in your database. The Query Notification and SQL Server Service Broker helps you in doing so. In this post I am going to show you how you could utilize this concept to build your own query notifiers for your own client application.

Introduction

It is somewhat a common practice for an application working with real-time day is to poll the database server every now and then to get the updates. We generally poll the database using a background Timer and update the data whenever new data comes in. Hence we loosen up a lots of resources while calling the database so much and even ruin our application overall performance.

The idea of this post is to let you understand the basics of Query Notification and let you through by building an application that gets automatic notification from the SQL Server itself and update the UI. SQLDependency is a class provided with .NET Base class library which enables you to get notification when SQLCommand changes its output.

I have posted an article demonstrating the concept clearly using some sample application one with a WPF windows client that gets notification alerts instantly and another an ASP.NET application that invalidates cache based on the Dependency.


http://cid-bafa39a62a57009c.office.live.com/self.aspx/.Public/NotificationMessages.zip
http://cid-bafa39a62a57009c.office.live.com/self.aspx/.Public/CacheDependencyNotifier.zip





Read the Entire Article.
Shout it Submit this story to DotNetKicks Bookmark and Share
Read Disclaimer Notice

12 comments:

Singh From Dominos India said...

Thanks for the nice tip,Expecting more tips in .net

Abhishek Sur said...

@Singh From Dominos India

Oh wow. Nice to receive your comment. I like your pizzas though...

They are hot.

Anonymous said...

Beneficial info and excellent design you got here! I want to thank you for sharing your ideas and putting the time into the stuff you publish! Great work!

Anonymous said...

Great information! I’ve been looking for something like this for a while now. Thanks!

Pankaj said...

Hi Abhishek,

I am trying to implement this in .Net Framework 3.5. I have some questions regarding while i am inserting the data into table Message the cache is not getting expired. The data is still fetched from the cache. I have kept application running for long period but it is still in cache.

I have successfuly run these setups
ALTER DATABASE TEST SET ENABLE_BROKER
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO user1
GRANT SELECT ON Message TO user1

but running the query
GRANT RECEIVE ON Message TO user1
is generating the exceptiopn
"Msg 4606, Level 16, State 1, Line 2
Granted or revoked privilege RECEIVE is not compatible with object."

I have searched it on msdn but that was also not working.

I noticed in TEST Database that there are few new stored procedures are created with names 'SqlQueryNotificationStoredProcedure-6e6a84a6-eaa6-430c-96e1-25aaf88486bf'.

In profiler i have seen that "exec sp_executesql N'BEGIN CONVERSATION TIMER (''c81e996c-0d1f-e011-816c-001ec95fd07c'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-6e6a84a6-eaa6-430c-96e1-25aaf88486bf]), TIMEOUT @p2;',N'@p2 int',@p2=60000" query is runnig

Any idea why it is not working.

Thanks
Pankaj

Abhishek Sur said...

@Pankaj

Hi Pankaj,

Did you try these in master database? Enable_Broker, Grants are to be made in master database. Make the user available to login as well as the attached database you are working on.

Pankaj said...

Hi Abhishek,

I am able to run few queries only to master db, and few to my TEST db. I am not able to run the queries
GRANT RECEIVE ON Table TO login
GRANT SEND ON SERVICE:://theservice to login

on my any db

Pleasse see the code below




ALTER DATABASE TEST SET ENABLE_BROKER -- Executed on master DB
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO user1 -- Executed on master DB

GRANT SELECT ON Message TO user1 -- Executed in TEST DB -- Not able to execute on master DB error Cannot find the object 'Message', because it does not exist or you do not have permission.
GRANT RECEIVE ON Message TO user1-- error on executing in TEST DB: Granted or revoked privilege RECEIVE is not compatible with object.-- Not able to execute on master DB error :Cannot find the object 'Message', because it does not exist or you do not have permission.
GRANT SEND ON SERVICE-- Not able to execute on any db
I am able to connect and get the grid data from message. But only concern is irrespective of change in message table or new insertion asp.net cache is not getting invalidated. i.e no fresh data is updated to page.

Thanks
Pankaj

Abhishek Sur said...

@Pankaj
Can you follow the steps from the article

http://support.microsoft.com/kb/915852

peteohanlon said...

Good article, but you might need to point out that this feature was dropped from SQL Server 2008.

Abhishek Sur said...

@peteohanlon

But MSDN has stated it is available even SQL server 2008 R2. I didnt tried it yet in 2008, if this is so, its nice to know this.

Check this :
http://msdn.microsoft.com/en-us/library/ms175110.aspx

:)

ionutz said...

Second link seams to be broken (http://cid-bafa39a62a57009c.office.live.com/self.aspx/.Public/CacheDependencyNotifier.zip). If you may please fix it. Many thanks, I really appreciate your effort. Thanks.

Abhishek Sur said...

@ionutz

You need to go to the link and download it frm there.

Post a Comment

Please make sure that the question you ask is somehow related to the post you choose. Otherwise you post your general question in Forum section.

Author's new book

Abhishek authored one of the best selling book of .NET. It covers ASP.NET, WPF, Windows 8, Threading, Memory Management, Internals, Visual Studio, HTML5, JQuery and many more...
Grab it now !!!