SQL Server CLR to embed business logic and schedule execution with SQL Server Agent

Question

Hi,

I have the business logic of an workflow-like application in a C# class library, but at end of each month certain process in the library needs to be invoked, i thought that the ideal mechanism to invoke this scheduled execution is using the Sql server agent (sql job preferably, althought i'm open to even go into SSIS hell if its absolutely required), but the question is how do i invoke the process of the application library from the sql job?

the application library is also used from asp.net pages, so it usually has a web application context (web.config) with configured input and output databases

I understand that the steps are more or less

1) embed the class library in a sql server CLR assembly, strong key sign and deploy it 2) call the library

the thing i am not clear is, when deploying such a CLR assembly, what happens with the web application context? how do i exactly go to deploy the CLR with the appropiate app.config? do i have to redeploy the CLR if i need to change the connection strings?

I am unsure also because the CLR needs to connect back to the database which is invoking it (for read and write table purposes) but i'm guessing one has to be extra cautious to avoid self-invoking and that sort of thing.

Answer 1

First, this post ends up in "use SSIS" :-P

As long as your library contains links to System.Web.dll I don't think that you will be able to register it in SQL Server as SQLCLR assembly. SQLCLR assembles are very restricted and cannot contain any reference.

You could move front-end specific parts of your business logic into a separate presentation layer library. This is generally a better solution than mixing business logic and presentation classes like a web application context. After that you can try to deploy your assembly to SQL Server.

Now you are able to create a second SQLCLR library, containing the .NET stored procedures that orchestrate your business objects.

However, I don't really see the advantage of SQLCLR, here. It would require huge changes in your source code and causes several restrictions for your whole application.

My personal preferences would be Either use a SSIS package. Here you can refer any other DLL and consume its functionality. Or write a simple .NET console application which will be started by SQL Agent. In both cases, the implementation is almost equal.

SQLCLR is especially designed to handle purely database specific tasks that hit the wall of possible (and suggestive) T-SQL features.

Greets Flo