Dr Herbie said:Horses for courses.wisemx said:*snip*
All of our 250+ reports use stored procedures to pull the data from the database (into DataSets which are then attached to the reports). So when the customer asks for changes to calculations we can often just supply them with a script to alter the stored proc rather than an entire release of the software.
For other processes, we might use a stored procedure for bulk updates (we have some annual data updates that need to occur for every customer account in the system, for example).
For performance, we might consider converting complex data manipulations into stored procedures to reduce the 'shuffling about' of data from the DB server to our app server on a different machine and back again.
I've settled on the middle ground of late, simple selects/insert/deletes are done via dynamic sql in typed datasets, EF or other ORM. Anything more than that say something that would need multiple back and forths between the app and DB is wrapped up in a sproc and pushed up to the DB.
I like the encapsulation of the sprocs, and all our third party extension points are via views and sprocs, but Dynamic-SQL+EF+Linq is so much easier for stuff we have complete control over.