We are developing a big system with SQL Server database, ASP.NET Web API 2.2 services and another external services.
We need to load more data on a table while we process the current data on it. To allow load data in background we call our ASP.NET Web API from a CLR SQL Server Stored Procedure.
Then, our ASP.NET Web API retrieve more data and itself insert that data on SQL Server.
Better explained the process is the following:
- SQL Server Stored procedure detects that we need more data, then it calls CLR SQL Server Stored Procedure.
- CLR SQL Server SP calls ASP.NET Web API.
- ASP.NET Web API retrieves more data and insert then into SQL Server.
What do you think about this 'architecture'? Do you know a better approach?
My doubt here is second step: a SQL Server database calling an ASP.NET Web API. But we need to do that work in background.
And also, the new data that we need to insert into SQL Server could be in a WCF SOAP Service.
Best Answer
I've worked in places where CLR assemblies are used in this fashion. In my opinion it is a bad idea.
Here's my reasoning.
1: Embedding logic in the database is always bad. This is a 'programming philosophy' position, but I feel that it is justified as a general rule
2: The CLR will be limited to .net 2 on mssql server
3: The CLR may timeout or error due to its dependency on the external resource. This will produce different result sets from the sproc
4: the DB selects? then calling the API which then inserts is going to give you locking and transaction problems with no easy solution
5: You already have a .Net layer in your solution (the webapi) so you have the infrastructure to support a windows service or similar application which can fulfill the business role.
I guess the thing missing from your question which would enable me to suggets a better pattern is what is calling the sproc in the first place?
Just a quick expansion on point 1 as I know it can be contentious. I've worked in a lot of companies and I tend to see two types of system.
Systems initially created by DBAs. these tend to have lots of SQL Agent Jobs, big sprocs with case statements, loops and business logic, SSIS packages etc
Systems initially created by programmers. These tend to have no indexes or keys, xml columns etc. DB just used for persistence of objects
The DBA created systems fall over because they don't scale
The programmer systems get corrupt data but struggle on. Because you can always add another web box