Home » Good Practices » Dapper – micro-ORM I like

Dapper – micro-ORM I like

Every programmer when working with databases will use a framework, which facilitates and speeds up development time. It is no different with me. I never liked ADO.NET. Taking out the data and storing it in a DataSet or DataTable that were not strongly typed is not a best idea. Especially when someone else on the later stage had to work with this code and no one actually knows what the author had in the mind writing it. For many years, I enjoyed the heavier Object-Relational Mapping frameworks, i.e. Entity Framework (version 4.0), LinqToSQL (the beginnings of my work as a programmer) or NHibernate.

In most projects, however, I did not need all the features which were offered with these tools. Why should we spend a lot of time designing the database using Code First approach for Entity Framework, when the database was ready a long time ago and in addition it’s used by several other applications. In this case, setup can take too much time.

When I realized that I don’t need all of these, I started looking for other alternatives, i.e.: micro-ORMs. The first two frameworks, which seems to be quite popular are Simple.Data and Dapper.

After a few days of working with the Simple.Data I decided that the dynamic interfaces are not something with which I would like to work for extended periods of time. Not to mention that whenever I return to code written using the Simple Data I need to review the documentation, because IntelliSense does not support dynamic interfaces. For sure there will be a lot of people who do not agree with my opinion. Well, it’s only my opinion 🙂

Then I decided to go with Dapper. In this case it was lucky spot. Dapper offers extension methods to common interfaces like IDbConnection (i.e. SqlConnection implementation). Check the examples below.

Entity Relation Diagram I’m going to use is as follows:

Dapper - database schema example

First, the simple example:

[Test]
public void Given_DataInDatabase_When_FetchingUsersUsingDapper_Then_ItShouldReturnAtLeastOneUser()
{
    var connectionString = ConfigurationManager.ConnectionStrings["UserDatabase"].ConnectionString;
    using (var sqlConnection = new SqlConnection(connectionString))
    {
        var sqlScript = @"
            SELECT * 
            FROM dbo.[User] u";
        var result = sqlConnection.Query(sqlScript);

        Assert.That(result, Is.Not.Null);
        Assert.That(result.Any(), Is.True);
    }
}

In this scenario, the integration test loads users from a database. If you prefer to select only one user based on UserId, try this:

[Test]
[TestCase(1, "mpustelak")]
[TestCase(2, "testuser")]
[TestCase(3, "usertest")]
public void Given_DataInDatabase_When_FetchingUserWithUserIdUsingDapper_Then_ItShouldReturnExpectedUserName(
    int userId,
    string expectedUserName)
{
    var connectionString = ConfigurationManager.ConnectionStrings["UserDatabase"].ConnectionString;
    using (var sqlConnection = new SqlConnection(connectionString))
    {
        var sqlScript = @"
            SELECT * 
            FROM dbo.[User] u
            WHERE u.UserId = @UserId";
        var result = sqlConnection.QueryFirstOrDefault(sqlScript, new { userId });

        Assert.That(result, Is.Not.Null);
        Assert.That(result.UserName, Is.EqualTo(expectedUserName));
    }
}

You would like to get few SELECT results at once? it’s not a problem. Dapper offers that as well:

[Test]
public void Given_DataInDatabase_When_FetchingUsersAndUserDetailsUsingDapper_Then_ItShouldReturnAtLeastOneUserAndOneUserDetail()
{
    var connectionString = ConfigurationManager.ConnectionStrings["UserDatabase"].ConnectionString;
    using (var sqlConnection = new SqlConnection(connectionString))
    {
        var sqlScript = @"
            SELECT * FROM dbo.[User] u
            SELECT * FROM dbo.UserDetail ud";
        using (var multipleQuery = sqlConnection.QueryMultiple(sqlScript))
        {
            var users = multipleQuery.Read();
            var userDetails = multipleQuery.Read();

            Assert.That(users, Is.Not.Null);
            Assert.That(users.Any(), Is.True);
            Assert.That(userDetails, Is.Not.Null);
            Assert.That(userDetails.Any(), Is.True);
        }
    }
}

Another useful example presents how to get and map data based on a JOIN statement:

[Test]
public void Given_DataInDatabase_When_FetchingUsersAndUserDetailsUsingDapper_Then_ItShouldReturnAtLeastOneUserWithJoinedUserDetail()
{
    var connectionString = ConfigurationManager.ConnectionStrings["UserDatabase"].ConnectionString;
    using (var sqlConnection = new SqlConnection(connectionString))
    {
        var sqlScript = @"
            SELECT * 
            FROM dbo.[User] u
	         INNER JOIN dbo.UserDetail ud ON ud.UserId = u.UserId";

        var result = sqlConnection.Query<UserEntity, UserDetailEntity, UserEntity>(
            sqlScript,
            (u, ud) =>
            {
                u.UserDetails = ud;
                return u;
            },
            splitOn: "UserId");

        Assert.That(result, Is.Not.Null);
        Assert.That(result.All(e => e.UserDetails != null), Is.True);
    }
}

Dapper offers async requests as well. You may find Async clause at the end of method to get data asynchronously. Check example:

       
[Test]
public async Task Given_DataInDatabase_When_FetchingUsersUsingDapperAsync_Then_ItShouldReturnAtLeastOneUser()
{
    var connectionString = ConfigurationManager.ConnectionStrings["UserDatabase"].ConnectionString;
    using (var sqlConnection = new SqlConnection(connectionString))
    {
        var sqlScript = @"
            SELECT * 
            FROM 
	            dbo.[User] u";
        var result = await sqlConnection.QueryAsync(sqlScript);

        Assert.That(result, Is.Not.Null);
        Assert.That(result.Any(), Is.True);
    }
}

Summary

As you can see, usage of Dapper is very simple. The biggest pros comparing to ADO.NET is the automatic mapping of columns returned by the SQL query to instance properties. The syntax is also very easy and simple to remember. However, the biggest downside which immediately catches the eye is storing the SQL in the C# code. My examples are simple. What we should do in the case of a more complex scenario?

In my opinion to load more complex data we should be using views or stored procedures. In the case of saving or deleting complex data (which is unlikely to occur,) we should apply only stored procedures. Thanks to this, our code will look better (as far as a mix of SQL with C# could be named transparent) and split dependence between SQL and C#.

You may find all code examples and SQL script to set up database on my GitHub here.

Published by

Mateusz Pustelak

Software Developer with several years of commercial experience, TDD practitioner, DDD/CQRS fan. Currently working for Universal Music Group in London.

Leave a Reply

Your email address will not be published. Required fields are marked *

*