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.