At the heart of every business application is data access. As a result, developers and architects spend much of their time designing, evaluating, testing, and fretting over the data access techniques used in their applications. Since this area of application design has been so well trodden it is no surprise that common patterns have emerged for writing code in the data services layer of an application.

In this article I’ll discuss three patterns, referred to as “data source architectural patterns” for encapsulating data access logic in an application. Like the patterns for representing domain logic discussed in my previous article, those described here are derived from Martin Fowler’s book Patterns of Enterprise Application Architecture from Addison-Wesley, 2003.

Three patterns
The patterns discussed here go hand in hand with the domain logic patterns (Transaction Script, Table Module, and Domain Model) discussed in my previous article. For each of the data source architectural patterns, I’ll give a short definition accompanied by a code example or diagram and explain how each relates to the domain logic patterns and when each should be used. Keep in mind that each of these patterns can and should rely on data access helper classes that abstract the .NET Data Provider and database used and allow you to write less code. Common data access helpers include the Microsoft Data Access Application Block and custom code such as the ProviderFactory and DataFactory classes discussed in Teach Yourself ADO.NET in 21 Days, Sams 2002.

Table Data Gateway
The first Data Source pattern is the Table Data Gateway. This pattern is sometimes also referred to as a Data Access Object or Data Access Component (although that terminology is also used to refer to any class used in the Data Access Services layer). Basically, the idea is that the class encapsulates the SQL (statements or stored procedures) for accessing sets of related tabular data in a database. These sets can be queried from multiple tables, views, or joins and so needn’t, and typically won’t, map to single tables.

This pattern’s primary advantage is its alignment with the .NET Framework since it can rely on ADO.NET DataSet objects, thereby reducing the amount of code that must be written. The gateway is responsible for filling, updating, and deleting data from DataSet objects (either typed or untyped) by referencing a collection of data adapter objects that can be mapped to underlying tables. Alternatively, the gateway components can return data readers, object arrays, or even XML. In fact, all three ways of returning data can be encapsulated in the same Table Data Gateway so that it can be used in various situations.

As with other code in your application, the Table Data Gateway is a good place to employ the Layer Supertype pattern to encapsulate common functionality such as the Find, Insert, Update, and Delete methods in an abstract base class as shown in Figure A. This figure also shows how the base class can encapsulate the use of a data access helper class such as the ProviderFactory. Derived classes can then override or overload these methods in order to add functionality (for example to add an additional Find method that returns rows based on specific criteria as shown in the partial and simplified code for a Customers Gateway class in Listing A).

Figure A
Table Data Gateway

The gateway class in Listing A can then be used by a client to find a particular customer like so:
Dim c As New CustomersGateway(connect, ProviderType.SqlClient)
Dim v() As Object = c.FindById(“ALFKI”)

The Table Data Gateway can be used by all three domain logic patterns discussed in the previous article, although it is most often used with the Transaction Script and Table Module.

Active Record
The second Data Source pattern is the Active Record pattern. This pattern is used with the Domain Model and tightly couples the domain logic and data with the data access logic. A typical Active Record will include the code required to:

  • Insert, update, and delete a particular instance of the object
  • Find and load a particular object (usually through a static method) as well as find and load any associated objects
  • Build strongly-typed collections of objects retrieved from the database using a static method
  • Perform any domain logic

Since the data access code is tightly coupled to the domain objects the pattern looks as shown in Figure B. Note that the domain object (Order in this case) references the data access helper class directly. A partial example of the Active Record implementing a Find method used in the Order class is shown in Listing B.

Figure B
Active Record

While conceptually simple, the disadvantage of this pattern is that it tightly couples the domain objects with the data access code. In the event that the underlying storage of the data changes, the domain object must be modified and redeployed.

Data Mapper
The third and final data source architectural pattern is the Data Mapper. This pattern abstracts the data access code from the domain model object and is therefore used with the Domain Model pattern discussed in the previous article. Important points to note include:

  • The job of the data mapper object is simply to select, insert, update, and delete domain model objects in the underlying data store, whether it is a relational database or some other data store.
  • One technique for implementing a Data Mapper is to create a Layer Supertype (the MapperBase class shown in Figure C) that abstracts the Find behavior as well as the reference to the data access helper object used as shown in Listing C. The BusinessObjectBase class can then use composition to refer to the mapper object ensuring that each domain model object has an associated mapper.
  • Each concrete mapper class then inherits from MapperBase and implements the abstract Find, LoadAll, Load, Save, and Delete methods. Some of the behavior can be abstracted in the MapperBase class including executing statements against a data access helper class (the DataFactory in Figure C) and creating strongly-typed collections.
  • The domain model objects such as Order then defer to the mapper object to actually perform the Find, Save, and Delete functionality.
  • There are several options for handling find operations, one of which is to create a shared Find method on the domain model object that delegates to the mapper, the other is to allow the UI code to instantiate and call the mappers directly.

Figure C
Data Mapper

Obviously this pattern is both more flexible and more complex than Active Record since it creates another layer of objects that must be managed although it has the advantage of separating the data access code from the domain model objects so that they can vary independently. It also requires more code than the Table Data Gateway.

Additional Information

Note: Microsoft will include the ObjectSpaces framework in the next release of Visual Studio .NET (Whidbey) that will act as a data mapper to custom domain model objects.

When using Active Record or Data Mapper you’ll be using a Domain Model. Both of these are great places to insert code that caches the domain model objects in an application-wide (per process) or session specific cache such as a hash table. This allows the Data Mapper, for example, to cache previously created Business Entity objects so that they will not need to be retrieved from the database. This pattern is referred to as an Identity Map by Fowler. However, the Identity Map pattern introduces other issues such as determining if and when the object should be re-retrieved from the database.

How to choose
Which of these data source architectural patterns you choose (or variants thereof) depends primarily on the form your domain logic will take. If you’re using the Transaction Script or Table Module patterns, you’ll likely use the Table Data Gateway because of its leverage of ADO.NET. If you’re using the Domain Model, then you’ll want the Active Record in simple situations and the Data Mapper when the objects become more complex or when you foresee the need to change the data logic without touching the domain logic.

Dan Fox is a Technical Director for Quilogy ( in Overland Park, Kansas where he evangelizes technology within Quilogy and to its customers through writing and speaking at seminars and events such as Tech Ed and Developer Days (see