How to implement the Data Mapper design pattern in C#

What is the Data Mapper Design Pattern?

The Data Mapper design pattern is a technique to save data from in-memory objects to the database, and load data from the database into in-memory objects. This pattern has the code for saving and loading inside a separate class from the model class.

The Active Record Design Pattern is a similar pattern but moves the code for database saving and loading inside the Model class.

Example C# source code for the Data Mapper design pattern

For this example, we’ll use this simple Customer class as the Model. It only has properties. It could have functions that do not handle database loading and saving, and still be following the data mapper pattern.

namespace DesignPatternsCSharpNet6.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;
    }
}

This DataMapper class handles all connections to the database.

In this example, the class and its functions are static. This is not a requirement of the Data Mapper design pattern. Also, a real program would likely get the connection string from a configuration file, and may be passed into the Data Mapper class during instantiation. The hard-coded connection string was just used to keep this example code small.

The code for the data mapper class has functions that accept the Id value (GetByIdAsync, on lines 11-36), or a Customer object (SaveAsync, on lines 39-74 and DeleteAsync, on lines 78-90). The Auto Mapper design pattern puts these functions inside the model class, so you do not need to pass in the Model object.

using System.Data;
using System.Data.SqlClient;
namespace DesignPatternsCSharpNet6.DataMapper;
public static class CustomerDataMapper
{
    private static string s_connectionString =
        "Data Source=(local);Initial Catalog=DesignPatterns;Integrated Security=True";
    public static async Task<Customer> GetByIdAsync(int id)
    {
        await using SqlConnection connection = new SqlConnection(s_connectionString);
        connection.Open();
        await 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 = await command.ExecuteReaderAsync();
        // 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;
    }
    // INSERT if new, otherwise UPDATE in the database
    public static async void SaveAsync(Customer customer)
    {
        if (customer.Id == 0)
        {
            await using SqlConnection connection = new SqlConnection(s_connectionString);
            connection.Open();
            await using SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            // SELECT SCOPE_IDENTITY() returns the Id value the database created
            command.CommandText =
                "INSERT INTO [Customer] ([Name], [IsPremiumMember]) VALUES (@Name, @IsPremiumMember); SELECT SCOPE_IDENTITY();";
            command.Parameters.AddWithValue("@Name", customer.Name);
            command.Parameters.AddWithValue("@IsPremiumMember", customer.IsPremiumMember);
            // Get the Id generated by the database and save it to the object's property
            customer.Id = (int)await command.ExecuteScalarAsync();
        }
        else
        {
            await using SqlConnection connection = new SqlConnection(s_connectionString);
            connection.Open();
            await using SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText =
                "UPDATE [Customer] SET [Name] = @Name, [IsPremiumMember] = @IsPremiumMember WHERE [Id] = @Id";
            command.Parameters.AddWithValue("@Name", customer.Name);
            command.Parameters.AddWithValue("@IsPremiumMember", customer.IsPremiumMember);
            command.Parameters.AddWithValue("@Id", customer.Id);
            await command.ExecuteNonQueryAsync();
        }
    }
    // 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 static async void Delete(Customer customer)
    {
        await using SqlConnection connection = new SqlConnection(s_connectionString);
        connection.Open();
        await using SqlCommand command = connection.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = "DELETE FROM [Customer] WHERE [Id] = @Id";
        command.Parameters.AddWithValue("@Id", customer.Id);
        await command.ExecuteNonQueryAsync();
    }
}

Should you use the Active Record or Data Mapper design pattern?

My personal preference is to use the Data Mapper pattern.

Putting the database saving and loading logic into the Model feels like a violation of the Single Responsibility Principle. A Model class is likely to contain business rules – either as functions or expression-bodied properties. Adding in the code to connect to the database, run insert/update/delete queries, etc. seems like a completely different set of functions than functions that handle business logic. Whichever pattern you choose, use that pattern for every Model in your project. Being consistent makes it easier to understand, modify, and maintain code.