SqlDependency / Service Broker in LocalDB implementing Signal R

Question

I am implementing SignalR in my project and want to receive real-time notification from my client when something changes in my database.

I have 3 components in my project

• Database
• Web API Service
• WPF Client

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

I am concerned about the real-time notification for my (1) database and (2) web API service. After reading some guides on how to do this https://gkulshrestha.wordpress.com/2014/05/02/signalr-with-sql-server-query-notification/

My problem

• I am currently developing using LocalDB and when searching, the 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 cannot be on separate machines? After development, I will run my database and web api on separate machines.

• How does my web API get notified when my database changes? Perhaps the service broker sends events to listening (my web service api)?

Any links or updated manuals SqlDependencyusing SignalR are welcome.

Answer 1

. -

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

is_broker_enabled 1, ( )

ALTER DATABASE sampleNotifications SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;

, . webAPI web.config

webAPI R nuget   Install-Package Microsoft.AspNet.SignalR

signalR onConnected

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

R global.asax.cs 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

app.MapSignalR(); //startup.cs

- SQLDependency . , , , , , global.asax.cs, TestNotifications Notifications 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();   
    }   

RegisterSQLNotifications Application_Start global.asax.cs. , , sqlDependency_OnChange, .

HTML-. WPF.
  var connection = $.hubConnection();   connection.url = " http://localhost:40471/signalr";//URL- API   var warnHubProxy = connection.createHubProxy('MyHub')

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

- webAPI . . , IIS.

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

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/