Building a simple data mapper

Have you ever wondered how a data mapper works? In this post, I'll build a simple mapper to illustrate how it works internally.

Fetching rows

To start with, if you look at the IDataReader interface, you'll see that it implements an interface called IDataRecord. That interface gives you access to the record without a lot of fluff. That knowledge can be used to create a very simple mapping method.

public static void Map(IDataRecord record, User user)
{
    user.Id = (int)record["Id"];
    user.FirstName = (string)record["FirstName"];
}

That allows us to create a repository method like this:

public async Task<List<User>> List()
{
    await using var cmd = _transaction.CreateCommand();
    cmd.CommandText = "SELECT * FROM Users";
    await using var reader = await cmd.ExecuteReaderAsync();
    
    List<User> users = new List<User>();
    while (await reader.ReadAsync())
    {
        var user = new User();
        Map(reader, user);
        users.Add(user);
    }
    
    return users;
}

That method can be simplified more by creating a mapping structure. Let's start with a base interface so that we can store all mappings in a dictionary:

public interface IMapper<TEntity>
{
    void Map(IDataRecord record, TEntity entity);
}

To map, we need to know the columns and the properties to match them. That's a very basic approach, but it works for the purpose of this article. In a more complete mapper, you would typically also match the data types (for instance, cast int to enums and vice versa).

private void CreateMapping(IDataRecord record)
{
    var props = typeof(TEntity).GetProperties();
    for (int i = 0; i < record.FieldCount; i++)
    {
        var name = record.GetName(i);
        var prop = props.FirstOrDefault(x => x.Name.Equals(name, StringComparison.OrdinalIgnoreCase));
        if (prop == null)
        {
            continue;
        }

        var setMethod = prop.GetSetMethod() ?? throw new InvalidOperationException(
            $"All properties must have a public getter. Fix {typeof(T).Name}.{name}.");

        _propertySetMethods.Add(setMethod);
    }
}

See? The column name is fetched using record.GetName(i) , and we match the property name against that. In a real solution, you might support different casing styles in the column names and allow manual mapping. The property name is stored in an internal list if a match is made.

The actual mapping is simple, thanks to the work done above:

public void Map(IDataRecord record, TEntity entity)
{
    if (_firstPass)
    {
        _firstPass = false;
        CreateMapping(record);
    }

    for (int i = 0; i < _propertySetMethods.Count; i++)
    {
        var value = record[i];
        var setter = _propertySetMethods[i];
        setter.Invoke(entity, new[] { value });
    }
}

To use the code above, let's introduce a new extension method that takes care of invoking ExecuteReaderAsync() and creating the entity list.

public static async Task<List<TEntity>> ToList<TEntity>(this DbCommand command) where TEntity : new()
{
    var mapper = GetMapper<TEntity>();

    await using var reader = await command.ExecuteReaderAsync();
    var items = new List<TEntity>();

    while (await reader.ReadAsync())
    {
        var entity = new TEntity();
        mapper.Map(reader, entity);
        items.Add(entity);
    }

    return items;
}

That's it. Here is a complete usage:

public class UserRepository
{
    private readonly IDbTransaction _transaction;

    public UserRepository(IDbTransaction transaction)
    {
        _transaction = transaction;
    }

    public async Task<List<User>> List()
    {
        await using var cmd = _transaction.CreateCommand();

        cmd.CommandText = "SELECT * FROM Users";
        cmd.AddParameter("userId", 10);
        return await cmd.ToList<User>();
    }
}

CRUD (Create, Update, Delete)

Creating extension methods for create, update, and delete is a bit harder since we need to keep track of the primary key and if it's autoincremented (which means that it should not be included as a column in the insert statement).

We'll assume that there is a single primary key; its name is always Id and it's an auto-incremented column in SQL Server :)

The method below is executed once in the mapping class to build the strings for insert and update.

private void CreateCrudMapping()
{
    // These two are for INSERT statements. Columns and values
    var cols = "";
    var values = "";

    // Column assignment for UPDATE statements.
    var updateLine = "";

    foreach (var property in typeof(TEntity).GetProperties())
    {
        if (property.Name == "Id")
        {
            // We need the Id setter for the auto-incremented key
            _idSetter = property.GetSetMethod()!;

            // And the getter for the UPDATE/DELETE WHERE clause
            _idGetter = property.GetGetMethod()!;
            continue;
        }

        cols += $"{property.Name}, ";
        values += $"@{property.Name}, ";
        updateLine += $"{property.Name} = @{property.Name}, ";

        // Need all properties for 
        _propertyGetMethods.Add(property.Name, property.GetGetMethod()!);
    }

    _updateStatement = $"UPDATE {typeof(TEntity).Name} SET {updateLine} WHERE Id = @Id";
    _insertStatement = $"INSERT INTO {typeof(TEntity).Name} ({cols[..^2]}) VALUES({values[..^2]})";
}

Let's introduce the INSERT, UPDATE, and DELETE methods into the mapping class.

public async Task Insert(DbCommand command, object entity)
{
    if (_insertStatement == "")
    {
        CreateCrudMapping();
    }

    // SCOPE_IDENTITY is to get the generated primary key
    command.CommandText = _insertStatement + ";SELECT (cast SCOPE_IDENTITY() as int)";
    foreach (var kvp in _propertyGetMethods)
    {
        if (kvp.Key == "Id")
        {
            continue;
        }

        command.AddParameter(kvp.Key, kvp.Value.Invoke(entity, null) ?? DBNull.Value);
    }

    // INSERT, get the PK and assign it to the entity
    var id = await command.ExecuteScalarAsync();
    _idSetter.Invoke(entity, new[] { id });
}

public async Task Update(DbCommand command, object entity)
{
    if (_updateStatement == "")
    {
        CreateCrudMapping();
    }

    command.CommandText = _updateStatement;
    foreach (var kvp in _propertyGetMethods)
    {
        command.AddParameter(kvp.Key, kvp.Value.Invoke(entity, null) ?? DBNull.Value);
    }

    command.AddParameter("Id", _idGetter.Invoke(entity, null) ?? DBNull.Value);
    await command.ExecuteNonQueryAsync();
}

public async Task Delete(DbCommand command, object entity)
{
    if (_updateStatement == "")
    {
        CreateCrudMapping();
    }

    command.CommandText = $"DELETE FROM {typeof(TEntity).Name} WHERE Id = @Id";
    command.AddParameter("Id", _idGetter.Invoke(entity, null) ?? DBNull.Value);
    await command.ExecuteNonQueryAsync();
}

Using them directly is a bit cumbersome. We'll add extension methods to make it easier.

public static async Task Insert<TEntity>(this IDbTransaction transaction, [DisallowNull] TEntity entity)
{
    if (entity == null) throw new ArgumentNullException(nameof(entity));
    var mapper = MappingRegistry.Instance.GetMapper<TEntity>();
    await using var cmd = transaction.CreateCommand();
    await mapper.Insert(cmd, entity);
}

public static async Task Update<TEntity>(this IDbTransaction transaction, [DisallowNull] TEntity entity)
{
    if (entity == null) throw new ArgumentNullException(nameof(entity));
    var mapper = MappingRegistry.Instance.GetMapper<TEntity>();
    await using var cmd = transaction.CreateCommand();
    await mapper.Update(cmd, entity);
}

public static async Task Delete<TEntity>(this IDbTransaction transaction, [DisallowNull] TEntity entity)
{
    if (entity == null) throw new ArgumentNullException(nameof(entity));
    var mapper = MappingRegistry.Instance.GetMapper<TEntity>();
    await using var cmd = transaction.CreateCommand();
    await mapper.Delete(cmd, entity);
}

Usage example

Ok. Now, we are ready to rock! Let's see what this minimal mapper can do for us:

// Setup
await using var connection = new SqlConnection("Server=;Database=AdoNetDemo;Trusted_Connection=True;");
await connection.OpenAsync();
await using var transaction = connection.BeginTransaction();


// INSERT
var user = new User
{
    FirstName = "Adam",
    LastName = "Petter",
    UserName = "nicklas"
};
await transaction.Insert(user);
Console.WriteLine($"Got id {user.Id} after insert :)");

// A query:
await using var command = transaction.CreateCommand();
command.CommandText = "SELECT * FROM Users";
var users = await command.ToList<User>();
foreach (var entity in users)
{
    Console.WriteLine($"Id: {entity.Id}");
}

Final words

I hope you have learned more about ADO.NET and what your favorite ORM does internally when you fetch information. I would love to hear if some parts are unclear or if I can improve the post.

I've also built a complete ORM with everything from scaffolding to migrations. The source code is available on GitHub. Or as a nuget package: Griffin.Data.

The source code from this post is available on GitHub.