Pushing data from SQL Server to Web Application wi

Question

My ASP.NET MVC 4 Web application is displaying frequently updated data to the client. Data originate from an external source (an application installed on the server) and processed by SQL Server 2008 R2.

Currently the data flow is quite traditional: clients polls from ASP.NET, ASP.NET polls in its turn from SQL Server.

To avoid polling (and now that I require a real-time interaction between the users of the Web Application), I am changing the approach to push, using signalR to broadcast data to clients. This increases the smoothness of the user experience and also reduces the overhead of polling between clients and ASP.NET server.

The problem now is inverting the flow between SSRV and ASP.NET: I'd like to push the data from SSRV to ASP.NET in the most efficient way.

SSRV is running expensive queries to import some external data - and once the data are processed they are also ready for sharing over the Internet via broadcast.

My current poor-man's approach: issuing a POST Http request to the Web Application (on localhost) to send the data (I am using a CLR function for that).

Once the data are processed, I pack them ready for the HttpWebRequest, queue them in a Service Broker to avoid affecting the other activities and I'm done.

I have written off SqlDependency as would force me to query for the data - not a big advantage (I would trade off a localhost HTTP request for a query run on SQL Server)

At the same time I feel there should be a neater way to do this.

Any suggestions?

Answer 1

Well, I realized a bit late about the SignalR.Client.NET.35 library.

At the time of writing, it is not packaged in NuGet, so the code must be downloaded from the GitHub SignalR Project Site and added as a project to the solution (both SignalR.Client.NET and SignalR.Client.NET35 required).

Here is the final solution, in case it can help someone in the future:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Xml;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Net;
using System.IO;
using System.Xml.XPath;
using SignalR.Client.Hubs;

    internal static HubConnection connectionT = null;
    internal static IHubProxy msgHubT = null;

    /// <summary>
    /// allows SSRV to send a message to the Web Socket hub
    /// </summary>
    /// <param name="URL">URL of the Hub</param>
    /// <param name="hubName">Name of the message Hub to be used for broadcasting.</param>
    /// <param name="hubMethod">Hub method to be used for broadcasting.</param>
    /// <param name="message">Message to be broadcasted.</param>
    [SqlFunction()]
    public static void ut_sendMsgToHub(string URL, string hubName, string hubMethod, string message)
    { 
      try
        {
        if (connectionT == null)
        {
            connectionT = new HubConnection(URL.Trim()); // "http://localhost:56844/M2Hub"
        }
        if (msgHubT == null)
        {
            msgHubT = connectionT.CreateProxy(hubName.Trim());//"M2data"
        }

            if (!(connectionT.State == SignalR.Client.ConnectionState.Connected 
                || connectionT.State == SignalR.Client.ConnectionState.Reconnecting
                 || connectionT.State == SignalR.Client.ConnectionState.Connecting))
                connectionT.Start().Wait();
            msgHubT.Invoke(hubMethod.Trim(), message.Trim()).Wait();//"Send"
        }
        catch (Exception exc)
        {
            SqlContext.Pipe.Send("ut_sendMsgToHub error: " + exc.Message + Environment.NewLine);
        }
    }

Important to notice: together with the compiled SQL SERVER 2008R2 CLR library you'll have to place the following dlls in the same folder:

• Newtonsoft.Json
• SignalR.Client.Net35 obviously
• SMdiagnostics
• System.Runtime.Serialization
• System.ServiceModel in the right version (refer to version as stated in the GAC in C:\Windows\assembly in case of incompatibilities).
• System.Threading

finally in SQL SERVER:

CREATE ASSEMBLY CLR_Bridge from 'C:\PathToLibraries\Library_CLR.dll' 
WITH PERMISSION_SET = UNSAFE --UNSAFE required
CREATE PROCEDURE ut_sendMsgToHub 
@url nchar(125) ,
@hubName nchar(75),
@hubMethod NCHAR(75),
@message NVARCHAR(MAX)
AS
EXTERNAL NAME CLR_Bridge.[LibraryNamespace.CLR_Bridge].ut_sendMsgToHub 

To call ut_sendMsgToHub I use a service broker so that I am sure that any problem with the execution of the function is decoupled from the stored procedures processing the data

Answer 2

Have you looked at SignalR? You can use it to asynchronously push data to your UI (although it isn't a true push). This may not be acceptable for your specific situation, but I would suggest taking a look. Might be what you need. Hope that helps.