current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 0 down vote favorite

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:

  1. SQL Server Stored procedure detects that we need more data, then it calls CLR SQL Server Stored Procedure.
  2. CLR SQL Server SP calls ASP.NET Web API.
  3. 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.

share|improve this question
1  
Sounds like a horrible violation of SRP. What triggers the stored proc, and why can that not look after calling other APIs? –  Julia Hayward May 6 at 8:13
    
piggybacking SQL Server's CLR for this sort of thing is a terrible idea IMHO –  devnull May 6 at 8:34

1 Answer 1

up vote 0 down vote

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

share|improve this answer
    
Thanks for your answer but I don't understand you have the infrastructure to support a windows service or similar application which can fulfill the business role. in point 5. I have an ASP:NET Web API, I can use it to fulfill the business role. If I want to migrate all my SP to C#, do you recommend me Entity Framework Code Firtst to do it? –  VansFannel May 6 at 9:25
    
I mean if you only had a database server, then your options of where to put business logic are limited. if you have a database server AND a web server you have the option to put logic on the webserver instead –  Ewan May 6 at 9:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.