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.

  • 1
    possible duplicate of I've inherited 200K lines of spaghetti code -- what now?
    – user53019
    Commented Jul 25, 2013 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
    Commented Jul 26, 2013 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.
    – user53019
    Commented Jul 26, 2013 at 17:44

1 Answer 1


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.

  • 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
    Commented Jul 24, 2013 at 15:37
  • yes, WCF which also has many ways to make itself available to non-MS apps too.
    – ozz
    Commented Jul 24, 2013 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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