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)
public static string connectionstring = “Get Connection String From The Config File”;
public void DoDependency()
using (SqlConnection conn = new SqlConnection(connectionstring))
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(“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
public static void Main()
//Start the listener infrastructure on the client
QuikExe q = new QuikExe();
Console.WriteLine(“Wait for Notification Event…”);
//Optional step to clean up dependency else it will fallback to automatic cleanup
read the rest here…
Alex Papadimoulis is pretty adamant about not using CLR UDTs in SQL Server 2005.
No one has asked me that question just yet [“When Should I Use SQL-Server CLR User Definied Types (UDT)?”], but with the release of SQL Server 2005 just around the corner, I’m sure a handful of people will. Unlike regular User Defined Types, CLR UDTs are a new feature of SQL Server 2005 that allows one to create a .NET class and use it as a column datatype. As long as a few requirements are followed, one can create any class with any number of properties and methods and use that class as a CLR UDT.
Generally, when a new feature is introduced with a product, it can be a bit of a challenge to know when and how to use that feature. Fortunately, with SQL Server’s CLR UDTs, knowing when to use them is pretty clear:
Let me repeat that. Never. You should never use SQL Server CLR User Defined Types. I’m pretty sure that this answer will just lead to more questions, so allow me to answer a few follow-up questions I’d anticipate.
The full article can be read here. Another reason to take a pass on CLR UDTs is that it ties your database to SQL Server 2005. If you are doing an app that can run on SQL Server 2000, you just shot yourself in the foot.
Another reason to avoid CLR UDTs? Eliminating error messages like “File or assembly name udtname, Version=18.104.22.168, Culture=neutral, PublicKeyToken=389619d4c1235f8a, or one of its dependencies, was not found.”
I finally got a spam comment. In fact, it was from another Blogger user. That’s in violation of the Blogger TOS, so forwarded his infomation to the Blogger people (Google). I gave Google about a week to take whatever action they wanted to take against that user and then I removed that comment. It was odd that way the spam appeared. I posted a new entry and within 2 minutes, it got a comment. This is by no means a high traffic site, it’s mainly to keep track of things.
There’s a good article on The Code Project that explains why you shouldn’t use serialization to store data. The root problem is that the information that gets serialized out is strongly typed. In other words, whatever wrote that data out, better be the same thing that read it back in again. Should your code change it’s data structures, trying to read in serialized data from a previous version will break the code. That kinda defeats the purpose of using XML to store data. And not in a good way.