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.

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 setupsALTER DATABASE TEST SET ENABLE_BROKERGRANT SUBSCRIBE QUERY NOTIFICATIONS TO user1GRANT SELECT ON Message TO user1

but running the query GRANT RECEIVE ON Message TO user1is generating the exceptiopn "Msg 4606, Level 16, State 1, Line 2Granted 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

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 dbI 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.

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.

Blog Subscription

Learn MVC 5 step by step

My friend Shivprasad Koirala who is also a Microsoft ASP.NET MVP has released Learn MVC 5 step by step video series. It starts right from basics of MVC and goes to a level until you become a professional.
You can start taking the course for free using the below youtube video.
Please try it, you will find it awesome.

My Awards

Hit Counter

Twitter

Best .NET 4.5 Expert CookBook

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 !!!
Another book on .NET 4.5 has been released very recently. It covers Debugging, Testing, Extensibility, WCF, Windows Phone, Windows Azure and many more...
Grab it now !!!GET ANY BOOK AT $5 from PacktPub. Offer is limited