I have heard reasoning behind this and that i was curious if others think about this like a best practise/wise decision.
One reasoning is, that putting limitations on immediate access to database tables, and forcing applications/customers to make use of SPs (Saved Methods) to complete CRUD procedures allows DBAs
- fine grained control to approve/review SPs before moving these to production
- avoid random queries compiled by designers that might cause server load/other server issues
What this means is the developer can't write Linq queries on tables (although Linq queries including SPs are possible) This means the developer needs to ignore compile-time inspections in addition to being as a whole charge of the information, rather primarily use another language(SQL) to utilize data.
I'm not objecting for this, neither think this is advisable. I'm just curious to be aware what others think. What exactly are every other pros and cons for this method.
Update: As pointed out by Thomas in his answer, I am inclined to believe that SPs are logic and taking advantage of 'database programming in SQL' to complete logic is ok as lengthy as it is not 'Business logic'. Business logic needs separation, compile-time check and integration checkpoints and more.
Personally, I have never found procs flexible enough for that 'R' in CRUD. It's my job to use sights. But being able to access the database through sights &lifier procs also enables you to definitely abstract away the database tables giving the information model greater versatility later on ... similar to working against an interface.
Is dependent positioned on your atmosphere. The response to the question really is not a coding problem, but a company decision.
In case your database supports only one application, and it is reasonably tightly integrated by using it, then it is better, for reasons of versatility, to put your logic within your application program. Under these conditions handling the database simply like a plain data repository using common functionality looses you little and gains versatility - with suppliers, implementation, deployment and far else.
However you are getting through a corporate database then its highly better to screw lower the safety so far as you are able to. At the minimum all appropriate constraints should enabled, and when possible accessibility data ought to be through sights and methods only. Whining developers ought to be overlooked in these instances as...
- Having a corporate database the resource is valuable and invalid data or actions might have business-threatening effects. During these conditions most of your problem is the company, not how convenient access is perfect for your programmers.
- Such databases are obviously utilized by several application. You should utilize the abstraction that saved procs offer therefore the database could be transformed when application A is upgraded and you do not have the resource to upgrade application B.
- Similarly the encapsulation of economic logic in SPs instead of in application code enables changes to such logic to become implemented over the business easier and dependably than if such logic is baked into application code. For instance if your tax calculation changes it's less work, and much more robust, when the calculation needs to be transformed in a single SP than multiple programs. The general rule here would be that the business rule ought to be implemented in the nearest indicate the information where it's unique - if you possess a specialist application then your logic for your application could be implemented for the reason that application, but logic more broadly relevant towards the business ought to be implemented in SPs.