What factors should effect the Data Access Layer I use on a new project?

Question

I will be teaching a class sson, and I need to explain what factors should affect your decision of data access technology. I am familiar with many data access methods like Typed Data Sets, Linq to SQL, Linq to Entities, .netTiers, LLBLGen, and custom calls with SQL connection objects and command objects. Some of my clients will only allow stored procedures to be used, and they will NOT discuss anything else. Some of my clients are NOT ready to install .NET 3.5 yet. Some clients require a middle web service tier in any web application. Most of the time I use Types Data Sets and Custom Web Services, or I use .netTiers with CodeSmith. What else should I be thinking about?

Answer 1

One important thing to remember is that a database is not necessarily just a backing data store for an application (in isolation). Other applications and process could eventually require access to the database, especially in large or “enterprise” databases (or applications) and especially given enough time.

It’s important to consider:

ETL/Loads/Migration

External integration/synchronization (BizTalk/SSIS)

Reuse by other applications (particularly web sites, mobile applications etc)

Security/attack surface (is one approach less secure than another?)

Maintenance tasks

Availability - will the database be in use 24/7? Is one approach going to provide better availability than another, etc.

Also, some design consideration is in order. Are you tuning for faster selects, or faster writes? One data access design might perform better than another.

I’m not stating that there is a single silver bullet, but what I do caution is that any data access design pattern requires “big picture” thought - will it address today's concerns and what you can reasonably predict might be tomorrow's needs?

Also, will you be providing an external API or some framework for consistent data access? Will it be exposed directly or indirectly?

There’s a place, I think, for both the Entity Framework/LINQ to SQL, traditional Stored Procedures and other tools like NHibernate (etc), but you should justify and rationalise the choice of technology first, and try to ensure it is appropriate for the present and future needs.

EDIT: Sorry, I forgot the big one: maintainability. Some of the template driven solutions offer you some decent wins in being able to regenerate a DAL after schema changes, over others (like hand written stored procedures). It's worth weighing up the productivity gains vs the disadvantages.