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