SqlDependency / Service Broker in LocalDB, implementing Signal R

Question

I am implementing SignalR in my project and wanting a real time notification in my client when something is change in my database.

I have 3 components in my project

1. Database
2. Web API Service
3. WPF Client

I have my code written to enable my (2) Web API service and (3) WPF client communication.

My concern is the real time notification for my (1) database and (2) Web API service. After reading some tutorials on how to do such https://gkulshrestha.wordpress.com/2014/05/02/signalr-with-sql-server-query-notification/

My problem

1. I am currently developing using LocalDB and upon searching, Remote Queue is not enabled with local db (Service Broker Or SqlDependency in SqlLocalDb?). Does this mean that my (1) database and (2) web api service could not be on separate machines? I will be running my database and web api on separate machines after development.

2. How does my Web API get notified when there is change in my database? Perhaps, does service broker broadcast event to listening client (my web api service)?

Any links or up to date tutorial about SqlDependency with SignalR are welcome.

Answer 1

I will start answer with second query.First you need to enable service broker

SELECT [name], [service_broker_guid], [is_broker_enabled] FROM [master].[sys].[databases]

In the output check is_broker_enabled column is set to 1, If not run below query (change the database name)

ALTER DATABASE sampleNotifications SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;

Add the necessary tables to this database which you want to monitor. Add the connection string of this database in your webAPI's web.config

In your webAPI install signalR nuget Install-Package Microsoft.AspNet.SignalR

Create a hubs folder and add a signalR hub class and can have the basic onConnected method

public override Task OnConnected()
{
    //you can log the connection ID.
    return base.OnConnected();
}

Register signalR in global.asax.cs or startup.cs

RouteTable.Routes.MapHubs(); //for global.asax.cs and will work only for signalR 1.1. From 2.0 onwards you need to have startup class

or

app.MapSignalR(); //startup.cs

Now the most important part is to start SQLDependency class and handle on change event.The important part is you need to define what exactly you are monitoring, like below a simple method that I have added in global.asax.cs that monitors TestNotifications table of sampleNotifications DB

    private void RegisterSQLNotifications()
    {
        string connectionString = ConfigurationManager.ConnectionStrings["sampleNotifications"].ConnectionString;
        SqlDependency.Start(connectionString);
        string commandText = @"Select * from dbo.TestNotifications";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {

            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                connection.Open();
                var sqlDependency = new SqlDependency(command);


                sqlDependency.OnChange += sqlDependency_OnChange;

                // NOTE: You have to execute the command, or the notification will never fire.
                using (SqlDataReader reader = command.ExecuteReader())
                {
                }
            }
        }
    }

    void sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Info == SqlNotificationInfo.Insert)
        {
            //This is how signalrHub can be accessed outside the SignalR Hub MyHub.cs file
            // you can add your business logic here, like what exactly needs to be broadcasted
            var context = GlobalHost.ConnectionManager.GetHubContext<MyHub>();                    
            context.Clients.All.sendNotifications();

        }
        //Call the RegisterSQLNotifications method again
        RegisterSQLNotifications();   
    }   

Call the RegisterSQLNotifications in Application_Start of global.asax.cs. Now whenever there is a insert in your table sqlDependency_OnChange event will be triggered and you can broadcast to respective clients

On the client side add below on a HTML page. You can modify this accordingly for your WPF project.
var connection = $.hubConnection(); connection.url = "http://localhost:40471/signalr"; //API URL var alertsHubProxy = connection.createHubProxy('MyHub')

//broadcast alert
alertsHubProxy.on('sendNotifications', function (item) {
    //do something here
});

Regarding your first query - It should be ok to run webAPI and database in 2 different machines. See this scale-out example where multiple IIS instances are used

http://www.asp.net/signalr/overview/performance/scaleout-with-sql-server

Couple of closest links http://techbrij.com/database-change-notifications-asp-net-signalr-sqldependency

http://www.codeproject.com/Articles/883702/Real-Time-Notifications-using-SignalR-and-SQL-Depe

http://venkatbaggu.com/signalr-database-update-notifications-asp-net-mvc-usiing-sql-dependency/