Last updated on July 21, 2022
There are new .NET 6 versions of this at:
This article will cover two design patterns – the Data Mapper pattern and the Active Record pattern. These are two slightly different techniques you can use to let your programs interact with databases.
Video version here
What do these patterns do?
Both of these patterns are ways to convert between business objects (classes) and a database records.
Examples
For this example, we’ll use a Customer class as the business object. This is a very simple class, with only three properties, to keep the demo code small.
namespace DesignPatternDemos.DataMapper
{
public class Customer
{
public int ID { get; set; }
public string Name { get; set; }
public bool IsPremiumMember { get; set; }
public Customer(int id, string name, bool isPremiumMember)
{
ID = id;
Name = name;
IsPremiumMember = isPremiumMember;
}
}
}
With the Data Mapper design pattern, your business object classes do not have any way to directly communicate with the database. If you want to create a Customer object from a database row, or save a Customer object to the database, you need to call a separate class. That other class is the data mapper.
How to implement the Data Mapper design pattern
Here is how you might build a data mapper for this Customer object.
using System.Data;
using System.Data.SqlClient;
namespace DesignPatternDemos.DataMapper
{
public class CustomerDataMapper
{
private const string CONNECTION_STRING =
"Data Source=(local);Initial Catalog=DesignPatterns;Integrated Security=True";
public static Customer GetByID(int id)
{
using(SqlConnection connection = new SqlConnection(CONNECTION_STRING))
{
connection.Open();
using(SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT TOP 1 * FROM [Customer] WHERE [ID] = @ID";
command.Parameters.AddWithValue("@ID", id);
SqlDataReader reader = command.ExecuteReader();
// If the query returned a row, create the Customer object and return it.
if(reader.HasRows)
{
reader.Read();
string name = (string)reader["Name"];
bool isPremiumMember = (bool)reader["IsPremiumMember"];
return new Customer(id, name, isPremiumMember);
}
}
}
return null;
}
// Notice that we need to pass in the Customer object (or some information from it)
// to use some of the methods in the DataMapper class.
public void Save(Customer customer)
{
// This method needs to handle INSERT (new Customer) and UPDATE (existing Customer).
// Or, you would need to create two separate functions, and call them when appropriate.
// Pretend there is code here to do the insert and/or update to the database.
}
// We also could have only passed in the ID for this method,
// because that is the only value from the Customer object that this method needs.
public void Delete(Customer customer)
{
using(SqlConnection connection = new SqlConnection(CONNECTION_STRING))
{
connection.Open();
using(SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "DELETE FROM [Customer] WHERE [ID] = @ID";
command.Parameters.AddWithValue("@ID", customer.ID);
command.ExecuteNonQuery();
}
}
}
}
}
The Save and Delete methods need to receive the Customer object (or, at least the ID from it) in order to work. The Save method needs the Customer object to insert, or update, its property values into the database. The Delete method needs the ID, to know which record to delete in the database.
How to implement the Active Record design pattern
The code below is a Customer class that uses the Active Record pattern.
using System.Data;
using System.Data.SqlClient;
namespace DesignPatternDemos.ActiveRecord
{
public class Customer
{
private const string CONNECTION_STRING =
"Data Source=(local);Initial Catalog=DesignPatterns;Integrated Security=True";
public int ID { get; set; }
public string Name { get; set; }
public bool IsPremiumMember { get; set; }
public Customer(int id, string name, bool isPremiumMember)
{
ID = id;
Name = name;
IsPremiumMember = isPremiumMember;
}
// This static method acts like an object factory for Customer objects,
// reading the values from the database and creating the object.
//
// So, the code to get a customer from the database might be:
//
// Customer.GetByID(123);
//
public static Customer GetByID(int id)
{
using(SqlConnection connection = new SqlConnection(CONNECTION_STRING))
{
connection.Open();
using(SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT TOP 1 * FROM [Customer] WHERE [ID] = @ID";
command.Parameters.AddWithValue("@ID", id);
SqlDataReader reader = command.ExecuteReader();
// If the query returned a row, create the Customer object and return it.
if(reader.HasRows)
{
reader.Read();
string name = (string) reader["Name"];
bool isPremiumMember = (bool) reader["IsPremiumMember"];
return new Customer(id, name, isPremiumMember);
}
}
}
return null;
}
public void Save()
{
// This method needs to handle INSERT (new Customer) and UPDATE (existing Customer).
// Or, you would need to create two separate functions, and call them when appropriate.
// This function would not need to receive a parameter, with the Customer object.
// It's inside the Customer object, so all the property values are already available to it.
// Pretend there is code here to do the insert and/or update to the database.
}
public void Delete()
{
using(SqlConnection connection = new SqlConnection(CONNECTION_STRING))
{
connection.Open();
using(SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "DELETE FROM [Customer] WHERE [ID] = @ID";
// This method uses the ID value from this object's property.
// This function didn't need to receive that value from a parameter.
command.Parameters.AddWithValue("@ID", ID);
command.ExecuteNonQuery();
}
}
}
}
}
The code is almost the same as what was in the Data Mapper class. The only significant difference is that you don’t need to pass the Customer object to the database methods – it’s already available, since those methods are inside the Customer object.
When to use the Data Mapper or Active Record design pattern
This is mostly a decision based on your preference.
The Data Mapper is nice because it separates the mapping from the business object class – which follows the Single Responsibility Principle. However, if you ever modify your business class, you would need to remember to also modify the data wrapper class.
When you use the Active Record pattern, it’s slightly quicker to update the mapping, if you change the business class – because the mapping code is in the same class. However, it makes your business classes larger.
My personal preference is for the Data Mapper pattern. I try to put a lot of “business logic” in my business classes. I don’t want to make them even longer, with the extra database methods.
Whichever pattern you choose, use that pattern for every object in your project. Being consistent will make it easier for you to modify your program, and for other people to use your code.