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.

Answer 2

I'm really only thinking of two things. The first is whether I going to have so much data that anything else matters. If you're not putting millions of rows into the tables, it probably doesn't matter which technology you're going to use because they'll all work fast enough.

The second thing is whether I can use LINQ, because I find that using LINQ (to SQL, to Entities, to LLBLGen, it doesn't matter) to query the database gives you two important things. The first is that it's very easy to write queries and second, it's moderately easy to switch between two frameworks that need LINQ, in case requirements change.

Answer 3

Like with all choice's in a software project : It depends... But in my opinion the most important factor is the environment of the project.

This consists of (I don't claim that this list is complete in anyway):

• Available skills within development team AND maintenance team (if different)
• Required features
• Constraints set by clients (not all clients support all technologies available. This certainly something you should take into account when incrementally replacing a legacy system or introducing a new system to a environment)
• Constraints set by legislation

hope this helps you.

Answer 4

I think that between your original post and norbertB's additions you've covered just about everything. Start with the absolute constraints (and remember, that just because a client says no to something once - even if they say it's absolute - that doesn't mean you can't help change their mind...). Once you've narrowed down the field with the absolute constraints look at the other things.

One thing that did seem to be left out was flexibility. For example, if I were trying to choose between two similar technologies and I knew that one could support updateable views and the other couldn't, even if I had absolutely no need for updateable views at the time I would still lean towards that one "just in case".