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.

I work in a fairly large and old solution that has many entry points for different kinds of clients, with web sites for public access, web sites for internal access, some web sites and web services for partner companies access, etc.

All those applications use different (Microsoft-based) technologies, such as Classic ASP, ASP.NET WebForms, MVC 3, ASMX, DTSX, etc. The code has no standard, and many programmers with no experience on the codebase and in the business have coded their own way.

The central point of all the applications is a SQL Server database with tons of stored procedures that implement all the business rules. The applications are usually just a shell for the database. The data access is done with no framework or ORM (using pure ADO.NET, usualy rewriting everything from creating a connection to iterating through a data reader on each method, function or whatever).

What are the most modern best practices for creating an productive data access layer based on stored procedures?

For business reasons, we cannot rewrite the older applications (the customer won't pay for that, and the volume of daily work is too large for doing that internally). We also cannot use any third-party ORM (the architects are against it for "security reasons"). So, the improvements must be more like refactorings.

share|improve this question
1  
possible duplicate of I've inherited 200K lines of spaghetti code -- what now? –  GlenH7 Jul 25 '13 at 13:05
    
@GlenH7, maybe I've put too much justification for my main question ("What are the most modern best practices for creating an productive data access layer based on stored procedures?"). I think the duplicate sugestion may be too broad for the answer I need. –  Raphael Jul 26 '13 at 17:28
1  
Raphael, feel free to edit your question and narrow the scope then based upon the answers within the suggested duplicate. A duplicate just means "look here first for answers; we've already covered this." If the answers don't address your concerns then edit to clarify why you still have a question. –  GlenH7 Jul 26 '13 at 17:44

1 Answer 1

up vote 6 down vote accepted

Ah, the classic "1000 line business rule stored procedure, no unit tests, ball of mud application".

It allows production tweaks because you can just login to SQL server and update a little bit of SQL code and you don't need to redeploy your app.

Well, basically my advice is to stop putting new features business rules in the stored procedure layer for a start.

Anything that you are writing from scratch that accesses the database, and assuming you can't use ORM tools, only put your CRUD SQL in stored procedures. Keep your business rules in your code, and make sure it is unit tested.

You don't need to use ORM tools to achieve this, (although the "security reasons" excuse sounds like BS to me)

If you have multiple applications accessing a common database, you might want to look at introducing a common Service layer, to provide common functionality to multiple applications in a consistent manner.

share|improve this answer
    
The Service Layer is a very interesting idea. What would be the ideal way to create a service-oriented architecture for internal use by all the applications in a Microsoft or Micorsoft-compatible way? WCF? –  Raphael Jul 24 '13 at 15:37
    
yes, WCF which also has many ways to make itself available to non-MS apps too. –  jmo21 Jul 24 '13 at 15:40

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.