I would like to mention and outline brief definitions of mainstream design patterns for the data layer, to give some examples of them and see some of their strengths and weakness.

The Data Access Object -DAO-: this pattern was published by Sun Microsystems in 2007 (now Oracle) and although is framed into the J2EE patterns, it’s widely used in .Net/C# and other platforms. This is the Oracle’s definition: “The business component that relies on the DAO uses the simpler interface exposed by the DAO for its clients. The DAO completely hides the data source implementation details from its clients”.

The DAO biggest strength is considering multiple underlying persistence repositories, but there are two weaknesses in the DAO pattern: the first one is assuming than “each BusinessObject corresponds to a specific DAO” and the second one is implementing it as an interface to one table. Another implementation issue that it may appear it’s the unnecessary complexity of mixing it with the Abstract Factory or Factory Method pattern (because nowadays enterprise libraries handle the initialization). An implementation of this pattern can be found in the ObjectContext class.

The TableDataGateway -TDG-: this pattern became popular by the PoEEA in 2002 and it’s defined as: “An object that acts as a Gateway (466) to a database table. One instance handles all the rows in the table. Mixing SQL in application logic can cause several problems. Many developers aren’t comfortable with SQL, and many who are comfortable may not write it well”. You will find methods like usersTable.Find()

The TDG biggest strength is separating the data and the operations on the data with the Gateway/man in the middle concept. The biggest weaknesses are assuming that developers are not comfortable writing SQL (in fact SQL days came in and out) and again assuming that one object can handle all rows in one table.  An example of this pattern is the DataTable class.

The RowDataGateway -RDG-: another pattern made popular by the PoEEA which stand for: “An object that acts as a Gateway (466) to a single record in a data source. There is one instance per row” and “A Row Data Gateway gives you objects that look exactly like the record in your record structure”. This pattern is in realty a specialization of the TDG, so for me makes no sense from an architectural (high level) point of view, it just tries to make emphasis on the obvious difference between one row and a collection of rows.

The Active Record: the PoEEA’s definition stands for: “An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data. An object carries both data and behaviour. Much of this data is persistent and needs to be stored in a database”. This pattern looks similar to the RDG but it adds something interesting: the object’s ability to know about its own persistence, so you can expect methods like: myUser.SaveInDb().

The Active Record biggest strength is keeping simple objects that are self-contained, but that’s also its biggest weakness because there is no separation of concerns so it can work for software with a relative small problem domain. A good example of this implementation is the SqlDataReader.


Javier Andrés Cáceres Alvis

Microsoft Most Valuable Professional – MVP
Intel Black Belt Software Developer