Using SignalR with SqlDependency to push database updates

Question

Is is possible to use SignalR in combination with SqlCacheDependency (or SqlDependency) to push database updates directly to the browser ? Maybe there is an other way to achieve this functionality ?

The only thing i can get working now includes having to call addMessage from the async call that does an update to the datase, but that doesn't really cover updates from different sources ( for example a background service that updates the table ).

score 5 · Answer 1 · answered Mar 27 '12 at 06:00

5

You should be able to use the OnChange event on a SQLDependency. In your event handler you can send a message over SignalR. Since you will be calling into your Hub from outside you'll need to use the technique shown at the bottom of the documentation here :

using SignalR.Infrastructure;

IConnectionManager connectionManager = AspNetHost.DependencyResolver.Resolve<IConnectionManager>();
dynamic clients = connectionManager.GetClients<MyHub>(); 
...

answered Mar 27 '12 at 06:00

Ian Mercer

38,490
8
97
133

Just FYI, we plan to implement a scaleout solution based on SQL using this mechanism. – davidfowl Mar 27 '12 at 07:34

Willem D'Haeseleer · Accepted Answer · 2012-03-29T20:30:52.213

1

Ok, i figured it out, or at least, one way to do it.

What i failed to understand initially is that you need to use that code from within an mvc controller, once you've done that, you can, obviously, call that controller from any other location or application as well, using the WebRequest class.

@Hightechrider For the sake off completeness, you need to include 2 more references to make that piece of code work. This demo code is done with a PersistentConnection, but the principle for the hub is the same off course.

EDIT: I'm now using a thread inside my asp.net mvc to manage the sqldependency, this feels like a more integrated solution. Check this post on how to implement background processing in asp.net "the right way" http://haacked.com/archive/2011/10/16/the-dangers-of-implementing-recurring-background-tasks-in-asp-net.aspx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;    

using SignalR.Infrastructure;
using SignalR.Hosting.AspNet;
using SignalR;

namespace SignalRDemo.Controllers
{
    public class DemoController : Controller
    {
        public void sendMessage( string message)
        {
            IConnectionManager connectionManager = AspNetHost.DependencyResolver.Resolve<IConnectionManager>();
            IConnection connection = connectionManager.GetConnection<MyConnection>();

            connection.Broadcast(message);
        }
    }
}

edited Mar 29 '12 at 20:30

answered Mar 27 '12 at 17:21

Willem D'Haeseleer

19,661
9
66
99

1

Why do you need to monitor the SQL Dependency in a separate process? You could just monitor it in a thread running under ASP.NET unless there's some infrastructure reason why your web site can't monitor your database directly?? In any case, if you do want to have an API like this you'd be better off using the new ASP.NET Web API, and you should probably make it an HttpPost only API to prevent accidental (or malicious) calls simply by visiting a web page. – Ian Mercer Mar 29 '12 at 18:02
@Hightechrider thx for your response, i was planning on implementing authentication to prevent malicious use, but a post would probably still be better to use to. I am not sure how to start a separate thread from inside my asp.net mvc application, at what point should i start the thread ( i'm thinking in Application_start from global asax, but i'm not sure ) – Willem D'Haeseleer Mar 29 '12 at 18:15
Yes, starting it in Global.asax is typical. Of course the AppPool could shut down when idle (which is why you normally need a service) but in your case, if nobody is using the site (through SignalR or otherwise) you don't care if the database is being updated. – Ian Mercer Mar 29 '12 at 20:00
Thx for the info Hightechrider ! I'm currently basing my implantation on this post http://haacked.com/archive/2011/10/16/the-dangers-of-implementing-recurring-background-tasks-in-asp-net.aspx , i feel like using IRegisterOject gives me much more control, i always want to know where my threads are at, if you catch my drift. – Willem D'Haeseleer Mar 29 '12 at 20:27

Using SignalR with SqlDependency to push database updates

2 Answers2