Wednesday, May 21, 2008

How to implement SQL notifications in .net?

How do i notify the .net client when data changes in SQL server backend?
How to implement SQL notifications in .net easy way?

Query Notification, a collaboration between Microsoft’s ADO.NET and SQL Server teams. In a nutshell, Query Notification allows you to cache data and be notified when the data has been changed in SQL Server. Upon notification, you can then refresh your cache or take whatever action you need to.

Query Notification is possible because of a new feature in SQL Server 2005 called Service Broker. Service Broker puts queuing functionality into the database with a coordination of queues that communicate with services that, in turn, know how to communicate back to the calling entity. The queues and services are first class objects just as tables, views, and stored procedures are. Although Service Broker can be leveraged completely within SQL Server, ADO.NET knows how to communicate with Service Broker to trigger this mechanism and retrieve the notifications back from the Service Broker.

On the .NET side, there are a number of ways of hooking into this functionality. ADO.NET 2.0 provides the System.Data.SqlClient.SqlDependency and System.Data.Sql.SqlNotificationRequest classes. SqlDependency is a higher-level implementation of SqlNotificationRequest and is most likely the one you will use when working with ADO.NET 2.0. ASP.NET 2.0 also communicates with Service Broker through the System.Web.Caching.SqlCache-Dependency class (that provides a wrapper around SqlDependency), as well as directly through functionality provided declaratively in an ASP.NET page using the <%OutputCache> directive. This allows ASP.NET developers to easily invalidate caches that are dependent on data from SQL Server.

Read this article for implementation details -

Credits: I got the pleasure of working with Jeff Clark for a short stint though at Avanade recently and i learnt quite a few things from him...

No comments: