Is it possible to add SignalR messages directly to the SQL Backplane?

Question

I'd like to know if I can add SignalR messages directly to the SignalR SQL Backplane (from SQL) so I don't have to use a SignalR client to do so.

My situation is that I have an activated stored procedure for a SQL Service Broker queue, and when it fires, I'd like to post a message to SignalR clients. Currently, I have to receive the message from SQL Service Broker in a seperate process and then immediately re-send the message with a SignalR hub.

I'd like my activated stored procedure to basically move the message directly onto the SignalR SQL Backplane.

Answer 1

Yes and no. I set up a small experiment on my localhost to determine if possible - and it is, if formatted properly.

Now, a word on the [SignalR] schema. It generates three tables:

[SignalR].[Messages_0] 
--this holds a list of all messages with the columns of 
      --[PayloadId], [Payload], and [InsertedOn]
[SignalR].[Messages_0_Id]
--this holds one record of one field - the last Id value in the [Messages_0] table
[SignalR].[Scehma] 
--No idea what this is for; it's a 1 column (SchemaVersion) 1 record (value of 1) table

Right, so, I duplicated the last column except I incremented the PayloadId (for the new record and in [Messages_0_Id] and put in GETDATE() as the value for InsertedOn. Immediately after adding the record, a new message came into the connected client. Note that PayloadId is not an identity column, so you must manually increment it, and you must copy that incremented value into the only record in [Messages_0_Id], otherwise your signalr clients will be unable to connect due to Signalr SQL errors.

Now, the trick is populating the [Payload] column properly. A quick look at the table shows that it's probably binary serialized. I'm no expert at SQL, but I'm pretty sure doing a binary serialization is up there in difficulty. If I'm right, this is the source code for the binary serialization, located inside Microsoft.AspNet.SignalR.Messaging.ScaleoutMessage:

public byte[] ToBytes()
{
  using (MemoryStream memoryStream = new MemoryStream())
  {
    BinaryWriter binaryWriter = new BinaryWriter((Stream) memoryStream);
    binaryWriter.Write(this.Messages.Count);
    for (int index = 0; index < this.Messages.Count; ++index)
      this.Messages[index].WriteTo((Stream) memoryStream);
    binaryWriter.Write(this.ServerCreationTime.Ticks);
    return memoryStream.ToArray();
  }
}

With WriteTo:

public void WriteTo(Stream stream)
{
  BinaryWriter binaryWriter = new BinaryWriter(stream);
  string source = this.Source;
  binaryWriter.Write(source);
  string key = this.Key;
  binaryWriter.Write(key);
  int count1 = this.Value.Count;
  binaryWriter.Write(count1);
  ArraySegment<byte> arraySegment = this.Value;
  byte[] array = arraySegment.Array;
  arraySegment = this.Value;
  int offset = arraySegment.Offset;
  arraySegment = this.Value;
  int count2 = arraySegment.Count;
  binaryWriter.Write(array, offset, count2);
  string str1 = this.CommandId ?? string.Empty;
  binaryWriter.Write(str1);
  int num1 = this.WaitForAck ? 1 : 0;
  binaryWriter.Write(num1 != 0);
  int num2 = this.IsAck ? 1 : 0;
  binaryWriter.Write(num2 != 0);
  string str2 = this.Filter ?? string.Empty;
  binaryWriter.Write(str2);
}

So, re-implementing that in a stored procedure with pure SQL will be near impossible. If you need to do it on the SQL Server, I suggest using SQL CLR functions. One thing to mention, though - It's easy enough to use a class library, but if you want to reduce hassle over the long term, I'd suggest creating a SQL Server project in Visual Studio. This will allow you to automagically deploy CLR functions with much more ease than manually re-copying the latest class library to the SQL Server. This page talks more about how to do that.