Data Access Strategies

 We  have seen in the post about 3-Layer architectures, that the Data Access Layer isn't the persistence repository, but a set of strategies for accessing it

The challenge you as an architect have to face when designing this layer is multiple:

  • How much possible is the RDBMS being replaced for a competitor? This possibility is usually low on traditional enterprises, but on independent software vendors (ISV) and other software companies is frequent the need for developing a solution compliant with several RDBMS -Oracle, IBM DB/2, MS SQL Server, MySql- in order to easily accomodate to what the customer already has
  • How frequent can the database schema change? As the business evolves and the application needs to consider more data, the possibility increases
  • How can we be sure that incoming requests from the business layer have the user already authenticated?
  • How can we design a layer reusable from different applications?
  • Are we accessing just one database? If not, how to guarantee integrity in multipart transactions? (NOTE: we discussed about Two Phase Commit here)

In his sixth session, Ph.D. Joe Hummel offers his guidance in order to better address these topics, with an approach based on Design Patterns like the Data Access Object (usually mentioned as DAO), an agent which hides the complexities in the conversation with the repository (connectors, connector pools, tables, commands and possibly DataSet and other mid-level components). The DAO at the Data-Access Layer, interchanges information with the Business and upper layers thru Data Transfer Objects (DTO)

Hummel, as usual, doesn't imposse his solution but just shows some possible answers, explaining design decisions to take into account in front of different scenarios. Some of them:

  • Should we design our DTOs reflecting exactly our business entities? What happens later with reusability?
  • Should our data-access layer expose use case-driven, high-level methods, or the typical per-table CRUD methods? (for Create / Retrieve / Update / Delete)
  • Can we apply Dynamic SQL directly from the application? What about security, performance in such case? Eventually can we call stored procedures? So, what if we need a data-access layer compliant with several database engines?

Together with data-access best practices, Hummel analyses what architects did in three different case studies. But he also shows examples of SQL injection attacks and how to protect from them

Before finishing, the presenter provides recommendations for those who wants to design a data-access layer easy to change as new applications appear or as database schema changes

So, don't stay there looking this, click below and watch now

MSDN Webcast: Architecting Desktop Applications with 2.0 (Part 06 of 15): Designing the Data Access Tier

After finishing, you are also invited to visit the Data Access knowledge base at the Microsoft Solution Architecture Center