How to use the Dapper ORM in ASP.NET Core

Dapper is an open source, lightweight “micro ORM” that supports many databases including SQL Server, MySQL, SQLite, SQL CE, and Firebird. By using Dapper in your applications you can simplify data access while ensuring high performance. In previous articles here I provided an introduction to Dapper and examined the Dapper Extensions Library. 

In this article I will show how Dapper’s support for asynchronous operations can be leveraged in ASP.NET Core.

Create a new ASP.NET Core Web API project

First off, let’s create an ASP.NET Core project and install the necessary packages. Assuming that Visual Studio 2017 is up and running in your system, follow the steps outlined below to create an ASP.NET Core Web API project.

1. Launch the Visual Studio 2017 IDE.
2. Click on File > New > Project.
3. Select "ASP.NET Core Web Application (.NET Core)" from the list of templates displayed.
4. Specify a name for the project.
5. Click OK to save the project.
6. Select “API” in the "New .NET Core Web Application…" window.
7. Select the version of ASP.NET Core you would like to use from the drop-down list at the top.
8. Uncheck the "Enable Docker Support" and select "No Authentication" as we won’t be using either of these features here.
9. Click OK.

Once your new ASP.NET Core project has been created, right-click on the project in the Solution Explorer window, create a solution folder named ”Models,” and create a new class. Name this class “Author” — this will be our model class that will hold our data. Here is what our Author class should look like.

public class Author
    {
        public int Key { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

Next, let’s build a simple repository. Here is the interface of the repository.

public interface IAuthorRepository
    {
        Task<Author> GetByKey(int key);
    }

The AuthorRepository class implements the IAuthorRepository interface. Here is how this class should look at first glance.

public class AuthorRepository : IAuthorRepository
    {
        public async Task<Author> GetByKey(int key)
        {
             //TODO
        }
    }

Create an asynchronous query using Dapper

For asynchronous operations, Dapper provides methods like ExecuteAsync, QueryAsync, QueryFirstAsync, QueryFirstOrDefaultAsync, QuerySingleAsync, QuerySingleOrDefaultAsync, and QueryMultipleAsync. We will make use of the QueryAsync method here.

The GetByKey method accepts a key (the primary key value) of the Author and returns an Author record. Note the usage of the QueryAsync method below.

public async Task<Author> GetByKey(int key)
        {
            using (IDbConnection connection = new SqlConnection (connectionString))
            {
                string query = "SELECT pKey, FirstName, LastName FROM Author WHERE pKey = @key";
                if(connection.State != ConnectionState.Open)
                    connection.Open();
                var result = await connection.QueryAsync<Author>(query, new { pKey = key });
                return result.FirstOrDefault();
            }
        }

To register this repository with the pipeline, you should take advantage of the ConfigureServices method as shown below. Note that this method will be called by the runtime automatically.

public void ConfigureServices(IServiceCollection services)
        {
            services.AddTransient<IAuthorRepository, AuthorRepository>();
            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
        }

Because the connection string is specified in the appSettings.json file in ASP.NET Core applications, we need a way to ensure that this connection string is available to the repository. To achieve this, you can leverage the IConfiguration object available as part of ASP.NET Core. The following code snippet illustrates how to do this.

public class AuthorRepository : IAuthorRepository
    {
        private readonly IConfiguration _config;
        private readonly string connectionString = null;
        public AuthorRepository(IConfiguration config)
        {
            _config = config;
            connectionString = _config.GetConnectionString ("IDGConnectionString");
        }
         //Other methods
    }

Lastly, here is the controller class for your reference. Note how the author repository has been injected.

    [Route("api/[controller]")]
    [ApiController]
    public class AuthorController : ControllerBase
    {
        private readonly IAuthorRepository _authorRepository;
        public AuthorController(IAuthorRepository authorRepository)
        {
            _authorRepository = authorRepository;
        }
        [HttpGet]
        [Route("{id}")]
        public async Task<ActionResult<Author>> GetByKey(int id)
        {
            return await _authorRepository.GetByKey(id);
        }
    }

Object-relational mappers, aka ORMs, are used to eliminate the "impedance mismatch" that exists between the object models of programming languages and the data models in relational databases. Dapper is a simple, flexible, fast, lightweight ORM built by Sam Saffron of Stack Overflow. And Dapper is free open source. You can learn more about Dapper ORM by reading my earlier two posts on Dapper here and here.