Broadcasting Database Changes Through SignalR
There are several posts out there on using SignalR to signal changes in a database, but because I really didn’t quite like any of them, I decided to write my own.
The possibility to detect changes in a database comes from the SqlDependency class. Other implementations exist, such as SqlCacheDependency and SqlNotificationRequest, but with these we don’t really receive a notification event. The process is a bit tricky, lots of people out there have problems. These classes only work with SQL Server non-Express, but there are equivalents for other databases, such as OracleDependency for Oracle.
Because I like to make things generic and reusable, I decided to encapsulate things in my class. Unfortunately, since SqlDependency does not have an ADO.NET base class, we have to use it directly.
So, my purpose is to:
-
Have a generic class that, given a database connection and SQL string that illustrates the changes I would like to detect, starts monitoring them, and raises an event whenever it sees changes;
-
I also want a SignalR hub that I can call from the outside – in this case, the changes event – and broadcasts a message about these changes.
First things first, here is the ChangeNotifier class:
1: public sealed class ChangeNotifier : IDisposable
2: {
3: private SqlConnection con;
4: private SqlCommand cmd;
5: private SqlDependency dependency;
6: private String connectionString;
7: private String dependencyCheckSql;
8:
9: ~ChangeNotifier()
10: {
11: this.Dispose();
12: }
13:
14: public event EventHandler<ChangeEventArgs> Change;
15:
16: public Boolean Start(String connectionStringName, String dependencyCheckSql)
17: {
18: new SqlClientPermission(PermissionState.Unrestricted).Demand();
19:
20: this.connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
21: this.dependencyCheckSql = dependencyCheckSql;
22:
23: var result = SqlDependency.Start(this.connectionString);
24:
25: this.con = new SqlConnection(this.connectionString);
26: this.con.Open();
27:
28: this.cmd = this.con.CreateCommand();
29: this.cmd.CommandText = this.dependencyCheckSql;
30:
31: this.Setup(true);
32:
33: return (result);
34: }
35:
36: public Boolean Stop()
37: {
38: var result = false;
39:
40: if (this.cmd != null)
41: {
42: this.cmd.Notification = null;
43: this.cmd.Dispose();
44: this.cmd = null;
45: }
46:
47: if (this.con != null)
48: {
49: this.con.Close();
50: this.con = null;
51: }
52:
53: if (this.dependency != null)
54: {
55: result = SqlDependency.Stop(this.connectionString);
56: this.dependency.OnChange -= this.OnChange;
57: this.dependency = null;
58: }
59:
60: this.Change = null;
61:
62: return (result);
63: }
64:
65: private void Setup(Boolean initial)
66: {
67: if (initial == false)
68: {
69: this.dependency.OnChange -= this.OnChange;
70: }
71:
72: this.cmd.Notification = null;
73: this.dependency = new SqlDependency(this.cmd);
74: this.dependency.OnChange += this.OnChange;
75:
76: this.cmd.ExecuteScalar();
77: }
78:
79: private void OnChange(Object sender, SqlNotificationEventArgs e)
80: {
81: this.Setup(false);
82:
83: var handler = this.Change;
84:
85: if (handler != null)
86: {
87: handler(sender, new ChangeEventArgs((ChangeInfo)(Int32)e.Info, (ChangeSource)(Int32)e.Source, (ChangeType)(Int32)e.Type));
88: }
89: }
90:
91: public void Dispose()
92: {
93: this.Stop();
94: }
95: }
It all starts with the Start method, which receives the name of a connection string stored in Web.config, and the SQL to use for detecting changes. When they occur, it raises the Change event. Action stops when the Stop or Dispose methods are called – do note that this class implements the Dispose Pattern, but in a particular way, because it is not meant to be subclassed. Whenever the OnChange event is raised, we need to set it up again, that’s how SqlDependency works. Because I don’t want to expose directly .NET classes, I created my own event classes and enumerations:
1: public enum ChangeInfo
2: {
3: AlreadyChanged = -2,
4: Unknown = -1,
5: Truncate = 0,
6: Insert = 1,
7: Update = 2,
8: Delete = 3,
9: Drop = 4,
10: Alter = 5,
11: Restart = 6,
12: Error = 7,
13: Query = 8,
14: Invalid = 9,
15: Options = 10,
16: Isolation = 11,
17: Expired = 12,
18: Resource = 13,
19: PreviousFire = 14,
20: TemplateLimit = 15,
21: Merge = 16,
22: }
23:
24: public enum ChangeSource
25: {
26: Client = -2,
27: Unknown = -1,
28: Data = 0,
29: Timeout = 1,
30: Object = 2,
31: Database = 3,
32: System = 4,
33: Statement = 5,
34: Environment = 6,
35: Execution = 7,
36: Owner = 8,
37: }
38:
39: public enum ChangeType
40: {
41: Unknown = -1,
42: Change = 0,
43: Subscribe = 1,
44: }
45:
46: [Serializable]
47: public sealed class ChangeEventArgs : EventArgs
48: {
49: public ChangeEventArgs(ChangeInfo info, ChangeSource source, ChangeType type)
50: {
51: this.Info = info;
52: this.Source = source;
53: this.Type = type;
54: }
55:
56: public ChangeInfo Info { get; private set; }
57: public ChangeSource Source { get; private set; }
58: public ChangeType Type { get; private set; }
59: }
We start the notifier in Global.asax.cs, maybe in the Application_Start method:
1: var notifier = new ChangeNotifier();
2: notifier.Change += this.OnChange;
3: notifier.Start("MyConnection", "SELECT SomeColumn FROM dbo.SomeTable");
A connection named MyConnection should exist in Web.config, as well as a table SomeTable with a column SomeColumn. Do change those for your own.
Next, a SignalR notification hub:
1: public class ChangeNotificationHub : Hub
2: {
3: public void OnChange(Int32 info, Int32 source, Int32 type)
4: {
5: this.Clients.All.onChange(info, source, type);
6: }
7: }
What it does is merely broadcast to connected parties an OnChange message. We can get a reference to it by calling GetHubContext:
1: private void OnChange(object sender, ChangeEventArgs e)
2: {
3: var context = GlobalHost.ConnectionManager.GetHubContext<ChangeNotificationHub>();
4: context.Clients.All.onChange(e.Info, e.Source, e.Type);
5: }
Notice that we call an onChange method (uncapitalized), not OnChange, this is so as to respect JavaScript conventions. We hook our page to SignalR using the following script:
1: <script type="text/javascript">
1:
2:
3: function onChange(info, source, type)
4: {
5: //do something
6: }
7:
8: window.addEventListener('load', function() {
9: var notifier = $.connection.changeNotificationHub;
10: notifier.client.onChange = onChange;
11: $.connection.hub.start();
12: });
</script>
The call to $.connection should match you hub class, but using camel casing. The callback function onChange is the same as you call in ChangeNotificationHub and in the OnChange event handler.
I take it for granted that you know how to setup SignalR, if not, do read the introduction in the ASP.NET site.
And that’s basically it!
Some common gotchas:
-
Does not work with SQL Server Express;
-
There’s a specific order by which the connection and SqlDependency.Start must be called, if you stick to my code, it should work;
-
Needs SQL Server Service Broker enabled for the database that we are monitoring, but does not require creating a queue or service, SqlDependency does that for us automatically;
-
Your application pool identity needs to have permissions to connect to the notification queue, see them here;
-
Won’t work with any SQL query, a number of restrictions apply; see here;
-
SqlDependency is very sensitive, and you can easily run into problems if you try different things.