Service Broker activation procedure in C# with external dependencies

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 c

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

StackExchange Database Administrators Q#152738, answer score: 4