Notification Support in SqlClient Managed Provider

After a brief hiatus, let me start with blogging the new Notification support in SqlClient MP that is introduced in Whidbey. There are scenarios in which an application would store a cache of data obtain from a DB Server and then re-query from the same cache to save round-trips to the server (for better performance). Typically, the app would want some mechanism to be notified when this very cache was changed by some one. In short, this feature allows you to monitor a specified result-set (collection on table rows, as defined by your select statement.) and then notify you when something is changed in the monitored result-set.

In this blog, I will just post a sample illustrating on how easy it is to set a result-set for Notification. In the upcoming blogs, I will get into more details with some gotchas, tips and tricks with Notifications. Lets see how simple its to get this working. Lets say way, create a table test with following TSQL

create table test (i int)
go
insert into test values (1)
insert into test values (2)
insert into test values (3)
insert into test values (4)
go

The following sample shows how a console application that reads data from the 'test' table into its cache and will get notified if some one has made changes to this cache on the server.

using System;

using System.Data;

using System.Data.SqlClient;

class QuikExe

{

      string connectionstring = "Put you connection string here";

      public void DoDependency()

      {

            using (SqlConnection conn = new SqlConnection (connectionstring))

            {

                  conn.Open();

                  Console.WriteLine("Connection Opened...");

                  SqlCommand cmd = conn.CreateCommand();

                  cmd.CommandText = "Select i from dbo.test";

                  //Notification specific code

                  SqlDependency dep = new SqlDependency (cmd);

                  dep.OnChanged += new OnChangedEventHandler(TestEvent);

                  SqlDataReader r = cmd.ExecuteReader();

                  //Read the data here and close the reader

                  r.Close();

                  Console.WriteLine("DataReader Read...");

            }

      }

      void TestEvent(Object o,SqlNotificationEventArgs args)

      {

            Console.WriteLine("Event Recd");

      Console.WriteLine("Info:" + args.Info );

  Console.WriteLine("Source:"+args.Source );

      Console.WriteLine("Type:"+args.Type );

  }

      public static void Main()

      {

            QuikExe q = new QuikExe();

      q.DoDependency();

            Console.WriteLine("Wait for Notification Event...");

            Console.Read();

      }

}

Run the above application and you will see the following output
Connection Opened...
DataReader Read...
Wait for Notification Event...

To verify that we indeed get a notification when the test table is change. Now invalidate the cache that we read using the following T-SQL statement (using any client tools-Workbench or another console Application :))
insert into test values (5)

Now, you will see the following Notification message from the above application
Event Recd
Info:Insert
Source:Data
Type:Change

Great! All we did here was to create a SqlDependency object and associate the Command to it before executing.
SqlDependency dep = new SqlDependency (cmd);

Also, to get notified we added a call-back event.
dep.OnChange += new OnChangeEventHandler(TestEvent);

The SqlNotificationEventArgs in turn has three properties which are enums:
1. Info - Gives information on what caused the notification.
2. Source - Gives the source of this notification.
3. Type - Type of notification.
Since in our case an Insert statement caused a Change in Data. The args properties are set as Info=Insert;Source=Data;Type=Change;

Note: Since the supporting functionality that makes this happen (SSB) is only available in SQL Server 2005 (Yukon), the above code will only work with SQL Server 2005.

So how did all this happen? In SQL Server 2005, we have a SSB service running in the MSDB database (called the SqlQueryNotificationService). When a command associated with a SqlDependency executes; it makes a request to this service for being notified if there occurred any change in the obtained result set. When a change occurs, the SSB service then puts a notification message in a specified Queue. The server then send the message from the queue to the client. The client then calls the call-back Event appropriately.

Tips and tricks to be aware while using SqlDependency is coming soon,
Stay posted.

Comments

• Anonymous
  December 07, 2004
  Sushil,
  
  Good to see you blogging again :).
  
  In this webcast here - http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032263413&Culture=en-US
  
  Balaji RathaKrishnan mentions that SqlCacheDependency object should be used for ASP.NET and SqlDependency object should be used for non-web situations.
  
  I was curious why that would be so? My insinuation is around scalability and better architecture, but I could be wrong.
  
  .. also .. what does it take to get this notification working on the express editions of Sql2k5?
  
  Thanks,
  
  - Sahil
• Anonymous
  December 08, 2004
  Sahil, System.Web.SqlCacheDependency is inherited from System.Web.CacheDependency. This allows ASP.Net to be aware of the cache and can be defined in the <%@ outputcache duration="130" varybyparam="test" %> directive, with the sqldependency attribute.
  
  When you use SqlDependency there is no way for ASP.Net to know that this is a outputcache.In fact SqlCacheDependency, under the covers uses SqlDependency. More details can be found at Rob Howards blog at http://weblogs.asp.net/rhoward/archive/2003/10/27/33865.aspx
  
• Anonymous
  December 08, 2004
  Sahil, I am able to get notification on SQLEXPRESS with no problems. Let me know if you see any issues.
• Anonymous
  December 08, 2004
  Thanks Sushil - and I'll try express once again.