Dapper

This is my first post in a very long time, and it was a new years' resolution to try to post at least once a week. I know it’s already February and I’m starting a little late but anyhow, I have started! This post is going to be on something that I have been using quite a lot lately, Dapper.

What is Dapper?

Dapper is an open source micro-ORM, created by some of the guys over at StackExchange. The way that Dapper works is by extending the IDbConnection interface, this means that it is compatible with any database that implements a provider for .NET.

How Do I Get Dapper?

There are two ways that you can add Dapper to your .NET project.

• Firstly, Dapper is compiled into a single binary file and can be downloaded on the Github page.
• Alternatively, you can download dapper using NuGet, the command for the Package Manager Console is:

  PM> Install-Package Dapper -Version 1.42.0

How Do I Use Dapper?

Dapper uses extension methods to function and can be called on any object that implements the IDbConnection interface. There are two main extension methods that can be used to either query the database or update/insert/delete rows in the database.

The Query<>() Method

Using the query method, you can either map the results of the query to a strongly typed list or map it to a list of dynamic objects. Below is an example of using a strongly typed object.

Firstly, you will need a POCO class to map your query results to, here is one that I have created based on an example database.

public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Town { get; set; }
    public string PostalCode { get; set; }
    public bool? IsActive { get; set; }
}

Now that I have the POCO, I can add the Data Access class where I will use the Dapper Query<>() method.

public class EmployeeDa
{
    private readonly IDbConnection _db = 
    	new SqlConnection("AddSqlConnectionStringHere");
    public List<Employee> GetAllEmployees() => _db.Query<Employee>
    ("SELECT EmployeeId, Name, Address1, Address2, Town, 
    PostalCode, IsActive FROM Employee").ToList();
}

As you can see, I have added a readonly IDbConnection variable called _db, this is required to connect to the database as Dapper will always assume that the database connection is open when calling any of its methods.

The second part of the class is a method that returns a list of all employees, this is where we actually use the Dapper query method. The next method below shows how you can pass variables into the Query method and also return a single object.

public Employee GetEmployeeById(int id)
            =>
                _db.Query<Employee>(
                    "SELECT EmployeeId, Name, Address1, Address2, Town, 
                    PostalCode, IsActive FROM Employee WHERE EmployeeId = @EmployeeId",
                    new {EmployeeId = id}).SingleOrDefault();

That’s how easy it is to implement the Query<>() method into your code and start using Dapper. Although I have passed the T-SQL into the method, this can be exchanged with the name of a stored procedure should your database use them.

As I mentioned earlier, Dapper also has the functionality to run a command that doesn’t return any results, such as an insert/delete/update query. Below, I have included an example of using the Execute() method.

public void InsertEmployee() =>
    _db.Execute(
        @"INSERT INTO Employee (Name, Address1, Address2, Town, PostalCode, IsActive) _
	VALUES (@Name, @Address1, @Address2, @Town, @PostalCode, @IsActive)",
        new
        {
            Name = "John Smith",
            Address1 = "1",
            Address2 = "Roman Way",
            Town = "Aberdeen",
            PostalCode = "AB1 1AB",
            IsActive = true
        });

As you can see from the code above, it is very simple to also use the Execute() method to insert data into the database. The same format can be used for using the updating and deleting commands in a database.

Dapper is very flexible and is always being updated, at the time of this blog post version 1.50 is currently in beta. As there is too much to cover in a post, be sure to check out the Github page for more information on how Dapper can help you.

That’s all from me on this post, which should help you get started with Dapper. Should you have any questions, drop me a comment below and I’ll be sure to reply.