Skip to content

RE: SqlDependency changes for RTM [Sushil Chordia]

I want to play with this feature. I have implemented something with similiar functionality using UDP in an extended stored procedure, but this looks much simpler.

As mentioned in my previous blog, SqlDependency is a new feature in .Net framework 2.0, which provide a mechanism to notify an app when a cache is invalidated. We got enough feedback from customers in Beta 2 with regards ease of deployment (some issues here) and security that we decided to make some changes for the final release. These new changes are now available as part of the September CTP. Following is a quick example on how to get Notification working on the September CTP bits. (Things new in September CTP are marked in RED)

using System;
using System.Data;
using System.Data.SqlClient;
class QuikExe
{
  public static string connectionstring = “Get Connection String From The Config File”;
  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.OnChange += delegate(Object o, SqlNotificationEventArgs args)
      {
        Console.WriteLine(“Event Recd”);
        Console.WriteLine(“Info:” + args.Info);
        Console.WriteLine(“Source:” + args.Source);
        Console.WriteLine(“Type:” + args.Type);
      };

      SqlDataReader r = cmd.ExecuteReader();
      //Read the data here and close the reader
      r.Close();
      Console.WriteLine(“DataReader Read…”);
    }
  }

  public static void Main()
  {
    try
    {
      //Start the listener infrastructure on the client
      SqlDependency.Start(connectionstring);
      QuikExe q = new QuikExe();
      q.DoDependency();
      Console.WriteLine(“Wait for Notification Event…”);
      Console.Read();
    }
    finally
    {
      //Optional step to clean up dependency else it will fallback to automatic cleanup
      SqlDependency.Stop(connectionstring);
    }
  }
}

read the rest here…