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.
Monday, January 10, 2011
12 comments:
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.
Subscribe to:
Post Comments (Atom)
Thanks for the nice tip,Expecting more tips in .net
ReplyDelete@Singh From Dominos India
ReplyDeleteOh wow. Nice to receive your comment. I like your pizzas though...
They are hot.
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!
ReplyDeleteGreat information! I’ve been looking for something like this for a while now. Thanks!
ReplyDeleteHi Abhishek,
ReplyDeleteI 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
@Pankaj
ReplyDeleteHi 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.
Hi Abhishek,
ReplyDeleteI 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
@Pankaj
ReplyDeleteCan you follow the steps from the article
http://support.microsoft.com/kb/915852
Good article, but you might need to point out that this feature was dropped from SQL Server 2008.
ReplyDelete@peteohanlon
ReplyDeleteBut 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
:)
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.
ReplyDelete@ionutz
ReplyDeleteYou need to go to the link and download it frm there.