Programming is not plumbing

27. February 2013 12:26 by martijn in
As a C# developer I have a natural preference of C# over plSQL. Nevertheless i think there are many reasons why not to put business logic into the DB.
  1. plSQL does not contain the same high level building blocks (interfaces,classes,assemblies) as 3G languages. There might be equavalents sometimes for example packages but overall plSQL is a lot less expressive.
  2. Stored procedures quickly turn into large functions which are not nicely divided into small blocks. It might be possible with temp_tables etc but in practice these constructs are not used very often
  3. Introducing a SP layer usually introduces more people into the development process. Apart from changing the schema you also need people with plSQL knowledge.
  4. Adding a SP layer makes your code less DRY. Adding an extra field requires updating of all create and update SPs. This is boring, simple, repetetive and therefore very error prone. Moreover the repetitive nature of plSQL prevents changing your model and leads to suboptimal code.
I am not saying you should use EF directly in your business or view code but I do think that a DAL layer using EF instead of stored procedures strongly reduces the development time,maintenance and errors. Putting you data access code in a separate layer does allows you to switch to other techniques (for example from EF to SP) if needed.