Check out my online course: AngularJS Unit Testing in-depth with ngMock.

Creating a Data Repository using Dapper: 10 Years of .Net Compressed into Weeks #12

Bradley Braithwaite on December 14, 2012
on asp.net 10 years on

This post looks at writing a repository implementation using the Micro-ORM named Dapper. This post is part of a blog series ASP.Net 10 Years On. Even though this is part of a series I have tried to make each post standalone.

Update

I've improved upon this implementation. See this post for details.

Original

In the previous post we compared options for our repository implementation and we concluded that a Micro-ORM was best suited for our requirements. We decided on Dapper as our tool. To recap, we chose a Micro-ORM as we wanted to balance automation and control over SQL queries in the application. We're happy for the nontrivial stuff to be automated but we want control over any SQL that has a JOIN or more.

To kick off our repository we start with a base repository interface. All domain level repository interfaces will implement this interface as it will offer the convention we seek in making all repositories uniform:

public interface IRepository<T> where T : EntityBase, IAggregateRoot
{
    void Add(T item);
    void Remove(T item);
    void Update(T item);
    T FindByID(Guid id);
    IEnumerable<T> Find(Expression<Func<T, bool>> predicate);
    IEnumerable<T> FindAll();
}

Here is how the solution structure looks in Visual Studio. The IRepository is situated in the .Infrastructure project, since it is generic but the ICompetitionRepository can be found in the .Model project since it's domain specific:

Screen Shot 2012 12 13 at 23 25 00

Next we move to the .Dapper project and add a concrete implementation of the IRepository interface. In our requirements for the repository we desired convention. All concrete implementations of the domain specific interfaces such as ICompetitionRepository will inherit from this base repository class. This provides the convention that all domain entities that implement IAggregateRoot have CRUD operations and the ID property will always be a GUID.

public abstract class Repository<T> : IRepository<T> where T : EntityBase, IAggregateRoot
{
    private readonly string _tableName;

    internal IDbConnection Connection
    {
        get
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["SmsQuizConnection"].ConnectionString);
        }
    }

    public Repository(string tableName)
    {
        _tableName = tableName;
    }

 internal virtual dynamic Mapping(T item)
    {
        return item;
    }

    public virtual void Add(T item)
    {
        using (IDbConnection cn = Connection)
        {
            var parameters = (object)Mapping(item);
            cn.Open();
            item.ID = cn.Insert<Guid>(_tableName, parameters);
        }
    }

    public virtual void Update(T item)
    {
        using (IDbConnection cn = Connection)
        {
            var parameters = (object)Mapping(item);
            cn.Open();
            cn.Update(_tableName, parameters);
        }
    }

    public virtual void Remove(T item)
    {
        using (IDbConnection cn = Connection)
        {
            cn.Open();
            cn.Execute("DELETE FROM " + _tableName + " WHERE ID=@ID", new { ID = item.ID });
        }
    }

    public virtual T FindByID(Guid id)
    {
        T item = default(T);

        using (IDbConnection cn = Connection)
        {
            cn.Open();
            item = cn.Query<T>("SELECT * FROM " + _tableName + " WHERE ID=@ID", new { ID = id }).SingleOrDefault();
        }

        return item;
    }

    public virtual IEnumerable<T> Find(Expression<Func<T, bool>> predicate)
    {
        IEnumerable<T> items = null;

        // extract the dynamic sql query and parameters from predicate
        QueryResult result = DynamicQuery.GetDynamicQuery(_tableName, predicate);

        using (IDbConnection cn = Connection)
        {
            cn.Open();
            items = cn.Query<T>(result.Sql, (object)result.Param);
        }

        return items;
    }

    public virtual IEnumerable<T> FindAll()
    {
        IEnumerable<T> items = null;

        using (IDbConnection cn = Connection)
        {
            cn.Open();
            items = cn.Query<T>("SELECT * FROM " + _tableName);
        }

        return items;
    }
}

The CompetitionRepository implementation will look as below. Assuming that a domain entities properties are primitive types and are named the same as the corresponding database table our code will "just work" and the CRUD operations will be implemented by way of convention:

public sealed class Competitionepository : Repository<Competition>, ICompetitionRepository
{
    public CompetitionRepository() : base("Competitions") { }
}

Note how all the CRUD methods are virtual, so in the event we want to take control of a method we can simply override the method.

Some additional methods have been added to provide additional automation/convention of repetitive tasks. The additional features are:

An extension method for Dapper that dynamically generates the INSERT SQL query.
An extension method for Dapper that dynamically generates the UPDATE SQL query.
A utility method that will dynamically generate simple and ONLY simple lambda expressions into SQL.

Here is the code for the first two extension methods for Dapper:

public static class DapperExtensions
{
    public static T Insert<T>(this IDbConnection cnn, string tableName, dynamic param)
    {
        IEnumerable<T> result = SqlMapper.Query<T>(cnn, DynamicQuery.GetInsertQuery(tableName, param), param);
        return result.First();
    }

    public static void Update(this IDbConnection cnn, string tableName, dynamic param)
    {
        SqlMapper.Execute(cnn, DynamicQuery.GetUpdateQuery(tableName, param), param);
    }
}

The three add-ons we discussed all point to the static methods that begin with DynamicQuery.[SomeMethod]. The static methods available in this class are:

DynamicQuery.GetInsertQuery();
DynamicQuery.GetUpdateQuery();
DynamicQuery.GetDynamicQuery();

These methods do as their name suggests and generate a SQL query based on the property names of the dynamic 'param' variable passed by value to the method. The 3rd method GetDynamicQuery() accepts a lambda expression that will be converted into a simple SQL query. The dynamic query generator will ONLY work for basic queries with no JOINS. Any SQL query that has a JOIN or more must be hand crafted.

I wrote these utilities to accompany this blog series as a learning exercise so they are not production ready or performance tested (yet!), but with that said you can find the source code here.

Why not just use an ORM?

Most of the functionality described so far you also get when using an ORM. So why go to all this trouble to get the same thing? We really start to see the benefit of this implementation when things become a little more complex.

Mapping Properties when Saving

For example, the User domain entity looks as follows:

public sealed class User : EntityBase, IAggregateRoot
{
    public string Username { get; set; }
    public EncryptedString Password { get; set; }
}

The slight difference when inserting or updating this entity to the database is the property type named Password which has a data type of EncryptedString. This type is a custom value object in our domain model that provides string encryption. Since this is not a primitive type we need some way of mapping the correct property value to the database table column. In the database we have a Users table with three columns:

ID
Username
Password

In this instance we need to map the value of the Password property to the table column Password. Even though they are named the same the property accessor to get the encrypted password value is not the same as if it were a string or int data type. We can perform such a mapping by simply overriding the virtual Mapping method:

public sealed class UserRepository : Repository<User>, IUserRepository
{
    public UserRepository() : base("Users") { }

    internal override dynamic Mapping(User item)
    {
        return new
        {
            ID = item.ID,
            Username = item.Username,
            Password = item.Password.EncryptedValue
        };
    }
}

Mapping Properties when Retrieving Data

As we've discussed, if our entity matches the database table structure like-for-like then everything just works. But what happens when this is not the case? What if a single domain entity is made up of data across three database tables?

Following up our UserRepository example we need to set the value of the EncryptedString property type as follows:

public override User FindByID(Guid id)
{
    User item = null;

    using (IDbConnection cn = Connection)
    {
        cn.Open();
        var result = cn.Query("SELECT * FROM Users WHERE ID=@ID", new { ID = id }).SingleOrDefault();

        if (result != null)
        {
            item = new User();
            item.ID = result.ID;
            item.Username = result.Username;
            /* The custom mapping */
            item.Password = new EncryptedString(result.Password);
        }
    }

    return item;
}

Whenever we want to take control we can use the full range of methods from the Dapper documentation to meet our requirements. If we need to add any very custom code that goes beyond what Dapper offers we can just code this manually if we choose to.

The key point is that we never have to fight a framework when we need explicit control. We use convention to make the simple cases "just work" so we can focus on getting the more complex stuff right.

Isn't it bad to use Dynamic?

The use of the dynamic type in C# will often polarise developers into love or hate categories. There are arguments relating to performance and that the use of dynamic is more error prone since possible runtime errors will not be picked up by the compiler at compile time in the same way static types are.

Performance

The performance benchmarks from the Dapper documentation indicate that using dynamic in the data access code doesn't result in a performance hit.

For more detail on how dynamic works there is a great answer to a question at Stackoverflow.com.

Lack of static typing

In our examples we used dynamic to map properties such as:

return new
{
    ID = item.ID,
    Username = item.Username,
    Password = item.Password.EncryptedValue
};

I would contest that this is little different from manually mapping properties from a data reader object such as the following line of code:

item.Username = reader["Username"].ToString();

In either example if I make a spelling mistake I will get a runtime error regardless!

Future move to NoSql?

In the requirements there was a possibility moving the database to a cloud computing/NoSQL platform. In order to future proof the repository design I wrote a simple NoSql flavour version of the base repository just to double check my interface design makes sense before it becomes too late to change. Here is how the code looks:

public abstract class Repository<T> : IRepository<T> where T : EntityBase, IAggregateRoot
{
    private Dictionary<Guid, T> _db = new Dictionary<Guid,T>();

    public void Add(T item)
    {
        item.ID = Guid.NewGuid();
        _db.Add(item.ID, item);
    }

    public void Remove(T item)
    {
        _db.Remove(item.ID);
    }

    public void Update(T item)
    {
        _db[item.ID] = item;
    }

    public T FindByID(System.Guid id)
    {
        if (_db.ContainsKey(id))
            return _db[id];

        return default(T);
    }

    public IEnumerable<T> Find(Expression<Func<T, bool>> predicate)
    {
        return _db.Values.Where(predicate.Compile()).AsQueryable();
    }

    public IEnumerable<T> FindAll()
    {
        return _db.Values.AsQueryable();
    }
}

This is an oversimplification of how NoSQL instances work but it helps in sounding out how the repository interface works when dealing with a key-value pair. I run the repository unit test I wrote for the Dapper implementation of the CompetitionRepository to verify this proof of concept.