Dapper - A Simple ORM

Entity Framework

Entity Framework is a full featured ORM. This is nice when performance is not important but rapid development is. Coding Linq statements instead of stored procedures can speed implementation dramatically.

EF's ease of use has trade-offs. All the behind the scenes work means slower performance and frustrating gotchas. Occasionally EF's generated SQL differs from the desired result. This inevitably necessitates a greater understanding of EF's inner workings. At that point it's just easier to write your own SQL.

Dapper

Dapper is a Micro-ORM. It focuses on simplifying database interactions with extension methods for query and execute commands. It also auto-maps values to POCO objects.

That's more or less it. Because Dapper's not managing relationships and data in memory its performance is an order of magnitude better.

According to Dapper's Github Page, “Select mapping over 500 interactions and POCO serializations” section:

• Hand Coded: 47ms
• Dapper: 49ms
• Entity Framework: 631ms

There is almost no performance hit because Dapper doesn't do too much.

Stored Procedure Example

Let's write code to return a list of Diamond Club Members from a Stored Procedure.

Here's our POCO:

private class Member
{
    public string Guid {get;set;}
    public string Name {get;set;}
    public string MemberType {get;set;}
}

Let's look at an ADO.NET example:

private IEnumerable<Members> GetAllDiamondClubMembers()
{
    using (var sqlConnection = new SqlConnection(connectionString))
    using (var sqlCommand = new SqlCommand(
        "dbo.GetAllMembers", sqlConnection){
             CommandType = CommandType.StoredProcedure }) {

        sqlCommand.Parameters.Add("@MemberType", SqlDbType.VarChar)
            .Value = "DiamondClub";

        sqlConnection.Open();

        var sqlDataReader = sqlCommand.ExecuteReader();

        var members = new List<Members>();

        while(sqlDataReader.Read())
        {
            members.Add(new Member
            {
                Guid = sqlDataReader["Guid"],
                Name = sqlDataReader["Name"],
                MemberType = sqlDataReader["MemberType"]
            });
        }

        sqlConnection.Close();

        return members;
    }
}

In the above example we are:

1. Creating a SQL Connection
2. Creating a SQL Command
3. Loading the SQL Command
4. Opening the SQL Connection
5. Executing the Stored Procedure
6. Iterating through the returned values
7. Mapping each individual value
8. Closing the connection
9. Returning the results

Dapper dramatically simplifies this:

private IEnumerable<Members> GetAllDiamondClubMembers()
{
    IEnumerable<Members> results;

    using (var sqlConnection = new SqlConnection(connectionString))
    {
        sqlConnection.Open();
        results = sqlConnection.Query("dbo.GetAllMembers", 
            new { MemberType = "DiamondClub" },
            commandType: CommandType.StoredProcedure);
    }

    return results;
}

Now we:

1. Create the SQL Connection
2. Open the SQL Connection
3. Run the Query
4. Return the results

Dapper has taken care of most of the rudimentary details for us. This is a healthy compromise between Entity Framework's performance hit and ADO.NET's code requirements.