Service Broker activation procedure in C# with external dependencies

Question

I am not entirely sure if I have come to the right place, because this question involves both SQL Server and .Net. Apologies if this is not an appropriate place for my question!

I am currently working on an in-house tool to transfer data from our ERP system to our SharePoint. Specifically, my boss wants the closing of a project in the ERP system to trigger a workflow in SharePoint.

My current line of thinking is to use the SQL Server Service Broker - create a T-SQL trigger on the projects table in the ERP database to send a message whenever a project is closed. The message would like carry an XML payload with some information on the project. I have done something very similar before, so that should not be too much of a problem.

In the next step I would have to receive these messages, extract the information from the XML payload and create entries in a list in our SharePoint server. Adding items to a SharePoint list from a C# program is also something I have done before, so that should not be a problem either.

My question is how I should approach the transfer from the Service Broker Message Queue to the SharePoint list. My first idea was to write a stored procedure in C# to receive messages from the queue, extract the relevant information from the XML payload, and then create a list item SharePoint. (Stored procedures in C# is not something I have done before!)

As far as I could find out, this should not be a problem per se. However, it would require access to the SharePoint client assemblies from the stored procedure. Also, I would like to do some logging, and being a lazy slob, I would like to use Log4Net for that. So I would need access to two assemblies. (More actually, I think the SharePoint client library is split up into several assemblies.)

Can I just load these assemblies into the SQL Server along with the one for my stored procedure, and then reference them from within the SQL Server? If so, are there any pitfalls, things I need to keep in mind? Or can I just install the required assemblies on the database server and reference them from within my stored procedure? Another option just comes to my mind - I could use Fody Costura, I think, to massage the additional assemblies into the one carrying my stored procedure... Are there any significant downsides to this?

The alternative, of course, would be a program running externally from the SQL Server. This seems in some ways to be a better alternative - the problem with the SharePoint and Log4Net assemblies disappears. Also, I could deploy the resulting program on an arbitrary machine.

In that case, though, I am not sure how to interact with the Service Broker and the Message queue. Can I just issue a RECEIVE statement via an SqlCommand instance and extract the fields like from a regular SELECT statement? Would I have to finish the conversation? I have found some hints to a Service Broker API, but where do I get the assemblies?

So, my questions, in short, are: - Can I reference other assemblies from a CLR stored procedure? - I so, how? What are the pros and cons? - If now, how do I speak to the Service Broker from an external program? Do I need other assemblies besides the SQL Server Client?

Thank you very much for any insight you might be able to share with me,
Benjamin

Answer 1

I suggest external activation rather than SQLCLR. That will provide capabilities like log4net that aren't easily doable in SQLCLR.

The trigger would begin a conversation and sends the project closure message. The C# app would RECEIVE the closure message from the queue, do the SharePoint stuff, and end the conversation. You'll also need to close the initiator side of the conversation to avoid endpoint leakage. Below is a single-queue example with the same SB service acting in both initiator and target roles. Note that your C# will receive EndDialog messages for both sides of the conversation with this method.

CREATE QUEUE dbo.ProjectClosureQueue;
CREATE MESSAGE TYPE ProjectClosureRequest
    VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT ProjectClosureContract (
      ProjectClosureRequest SENT BY INITIATOR
    );
CREATE SERVICE ProjectClosureService
    ON QUEUE dbo.ProjectClosureQueue ([ProjectClosureContract]);
GO

Trigger code snippet:

DECLARE @ProjectClosureConversationHandle  uniqueidentifier;
BEGIN DIALOG CONVERSATION @ProjectClosureConversationHandle
    FROM SERVICE ProjectClosureService
    TO SERVICE 'ProjectClosureService', 'CURRENT DATABASE'
    ON CONTRACT ProjectClosureContract
    WITH ENCRYPTION = OFF;
SELECT @ProjectClosureConversationHandle;

SEND ON CONVERSATION @ProjectClosureConversationHandle
    MESSAGE TYPE ProjectClosureRequest (@ProjectClosureXmlMessage);

Get next message in C# app:

WAITFOR (
    RECEIVE TOP (1)
           conversation_handle
           CAST(message_body AS xml)
        , message_type_name
    FROM dbo.ProjectClosureQueue
    ), TIMEOUT 10000;

If no message received, repeat receive.

If message_type is ProjectCloseRequest, do SharePoint stuff

If message_type is not ProjectCloseRequest or '"http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog', log error

Finally, END CONVERSATION