It’s been a while since I used the SqlDependency class and the features it provides, and was then quite disappointed that I hadn’t a copy of the plumbing code anywhere on my web clipboard (a.k.a darkside.co.za). I’ve put together a static helper class that you can attach an event to, and then call the Start(…) method.
Here is the code for the class (UPDATE: or you can download it here):
Expand Code 1 using System.Data;
2 using System.Data.SqlClient;
3
4 namespace Darkside
5 {
6 public class SqlNotificationHelper
7 {
8 private static SqlConnection m_SqlConnection;
9 private static SqlCommand m_SqlCommand;
10 private static DataSet m_DataSet;
11 private static string m_ConnectionString;
12
13 public delegate void DataChanged(DataSet dataSet);
14 public static event DataChanged DataChangedEvent;
15
16 public static void Start(string connectionString, string sql, CommandType commandType)
17 {
18 m_ConnectionString = connectionString;
19 SqlDependency.Stop(m_ConnectionString);
20 SqlDependency.Start(m_ConnectionString);
21 if (m_SqlConnection == null)
22 m_SqlConnection = new SqlConnection(m_ConnectionString);
23 if (m_SqlCommand == null)
24 m_SqlCommand = new SqlCommand(sql, m_SqlConnection) { CommandType = commandType };
25 if (m_DataSet == null)
26 m_DataSet = new DataSet();
27
28 SetupNotification();
29 }
30
31 public static void Stop()
32 {
33 SqlDependency.Stop(m_ConnectionString);
34 if (m_SqlConnection != null)
35 m_SqlConnection.Close();
36 }
37
38 private static void SqlDependencyOnChange(object sender, SqlNotificationEventArgs e)
39 {
40 var sqlDependency = sender as SqlDependency;
41 if (sqlDependency != null)
42 sqlDependency.OnChange -= SqlDependencyOnChange;
43 SetupNotification();
44 if (DataChangedEvent != null)
45 DataChangedEvent(m_DataSet);
46 }
47
48 private static void SetupNotification()
49 {
50 m_DataSet.Clear();
51
52 m_SqlCommand.Notification = null;
53 var sqlDependency = new SqlDependency(m_SqlCommand);
54 sqlDependency.OnChange += SqlDependencyOnChange;
55 using (var adapter = new SqlDataAdapter(m_SqlCommand))
56 {
57 adapter.Fill(m_DataSet, "DataSet");
58 }
59 }
60 }
61 }
62
And you can use it as follows:
protected static void Main()
{
SqlNotificationHelper.DataChangedEvent += new SqlNotificationHelper.DataChanged(SqlNotificationHelper_DataChangedEvent);
SqlNotificationHelper.Start("Server=(local); Database=Test; Integrated Security=SSPI", "SELECT Id, FirstName, Surname FROM dbo.People", CommandType.Text);
Console.ReadLine();
}
private static void SqlNotificationHelper_DataChangedEvent(System.Data.DataSet dataSet)
{
Console.WriteLine("Event – Data Updated." + dataSet.Tables[0].Rows.Count);
}
I should point out that you need to be quite specific in the type of query that you use. The list contained here on MSDN is quite comprehensive. The most common pitfalls I can imagine will befall people is using a * in the SELECT statement, and not specifying the table name in two-part format. The change event still fires even if the query isn’t correct, however, it fires repeatedly and can be quite a pain to debug.