Choosing a Data Access Layer Technique or Approach


Following up from a series of previous posts, here is  the granddaddy summary of all time.

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.

    Lastly, 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.


  • About Rob Sanders

    IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

    Leave a comment

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>