Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

Data Access Layer

This is a rather old question but I still havn't found a definite answer

when should one use Data Access Layer and when should one use Stored Procedures

Thursday, September 9, 2004

There is no definitive answer, i.e. one that applies to all projects all the time.

However, I would assert that you should use both in most projects.

Using stored procedures allows you to protect the underlying data from inappropriate access and also boosts performance in most cases.

Using a data access layer (by which I assume you mean a class or classes to wrap the database functionality) allows you to centralize all database access code, which aids development and re-use, and insulates the rest of the project from changes to the database.

Both of these are useful concepts, which are certainly not mutually exclusive, hence my earlier assertion that you should use both, most of the time.

Thursday, September 9, 2004

SP for speed, use DAL abstractions as a thin layer to abstract away the syntactical differences of different vendor SQL commands. Most writers on this topic have advocated practicality over bogus theory.

Re: DALs: they shouldn't have the resolution of commands and frameworks, but should instead be named after various business specific calls you make. GetClientList for example. Don't reinvent what Microsoft/Sun should have done right in the first place (or will some day).

Regarding SPs, don't feel bad if you must place an significant amount of business logic in them in order to defeat a serious hot spot, but avoid doing so to prevent migration problems. Don't feel bad if you must use vendor specific features. That's why you paid the premium--to solve serious problems. However, everything in moderation, your upgrade/migration cost is also at stake.

Li-fan Chen
Thursday, September 9, 2004

Check out the article linked below.  I believe this is what Li-fan was alluding to.  Microsoft calls them Data Access Logic Components.  But they definately don't preclude a base layer that they can all call from.

Friday, September 10, 2004

*  Recent Topics

*  Fog Creek Home