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/
