How to perform async operations using Dapper

Dapper is an easy to use, lightweight, flexible, fast, and open source “micro ORM” (object-relational mapper) that was developed by Sam Saffron of Stack Overflow. You can take advantage of Dapper to simplify data access and ensure high performance in your applications. Dapper supports asynchronous operations as well.

Dapper was built with performance and ease of use in mind. You can learn more about Dapper from my previous article here. In this article, we’ll examine how we can perform asynchronous operations using Dapper in ASP.NET Core.

To work with the code examples illustrated in this article, you should have Visual Studio 2019 installed in your system. If you don’t already have a copy, you can download Visual Studio 2019 here. 

Create an ASP.NET Core API project in Visual Studio

First off, let’s create an ASP.NET Core project in Visual Studio. Assuming Visual Studio 2019 is installed in your system, follow the steps outlined below to create a new ASP.NET Core project in Visual Studio.

1. Launch the Visual Studio IDE.
2. Click on “Create new project.”
3. In the “Create new project” window, select “ASP.NET Core Web Application” from the list of templates displayed.
4. Click Next.
5. In the “Configure your new project” window, specify the name and location for the new project.
6. Click Create.
7. In the “Create New ASP.NET Core Web Application” window shown next, select .NET Core as the runtime and ASP.NET Core 2.2 (or later) from the drop-down list at the top. I’ll be using ASP.NET Core 3.0 here.
8. Select “API” as the project template to create a new ASP.NET Core API application.
9. Ensure that the check boxes “Enable Docker Support” and “Configure for HTTPS” are unchecked as we won’t be using those features here.
10. Ensure that Authentication is set as “No Authentication” as we won’t be using authentication either.
11. Click Create.

You should now have a new ASP.NET Core API project in Visual Studio. Select the Controllers solution folder in the Solution Explorer window and click “Add -> Controller…” to create a new controller named DefaultController. We’ll use this project in the subsequent sections of this article.

Install the Dapper NuGet package

Now that you have created an ASP.NET Core application in Visual Studio, the next thing you should do is install the necessary NuGet package, named Dapper. You can install this package from the NuGet package manager inside the Visual Studio 2019 IDE. Alternatively, you can write the following command to install this package via the .NET CLI.

dotnet add package Install-Package Dapper

Once the Dapper package has been installed successfully into your project, you can start using Dapper.

Asynchronous methods in Dapper

Dapper contains several asynchronous methods that you can use to perform asynchronous CRUD operations. Here is the list of asynchronous methods in Dapper:

• ExecuteAsync
• QueryAsync
• QueryFirstAsync
• QueryFirstOrDefaultAsync
• QuerySingleAsync
• QuerySingleOrDefaultAsync
• QueryMultipleAsync

We’ll use the first two methods from the above list in the example below.

Dapper QueryAsync and ExecuteAsync examples

Create a new class named Author. This is our entity class.

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

Next, create an interface called IAuthorRepository and write the following code there.

 public interface IAuthorRepository
    {
        Task<Author> GetByID(int id);
        public Task<bool> Insert(Author model);
    }

The AuthorRepository class extends the IAuthorRepository interface.

 public class AuthorRepository
    {
        private readonly IConfiguration _config;
        public AuthorRepository(IConfiguration config)
        {
            _config = config;
        }
  public async Task<Author> GetByID(int id)
        {
            using (IDbConnection conn = new     
            SqlConnection(_config.GetConnectionString
            ("IDGDbConnectionString")))
            {
                string query = "SELECT Id, FirstName, LastName,
                Address FROM Author WHERE Id = @id";
                conn.Open();
                var result = await conn.QueryAsync<Author>(
                query, new { Id = id });
                return result.FirstOrDefault();
            }
        }
public async Task<bool> Insert(Author model)
      {
        return false;
      }
    }

Note how the QueryAsync method has been used here to query an Author record asynchronously.

Now replace the Insert method with the following code.

public async Task<bool> Insert(Author model)
 {
      int x = -1;
      using (var connection = new SqlConnection
       (_config.GetConnectionString
       ("IDGDbConnectionString")))
      {
          await connection.OpenAsync();
          var sqlStatement = @"
          INSERT INTO Author (FirstName, LastName, Address)";
          x = await connection.ExecuteAsync(sqlStatement, model);
      }
   return x > 0;
 }

Note how the ExecuteAsync method has been used here. The ExecuteAsync method accepts two parameters. These include the SQL statement and an object containing the data to be inserted or updated.

Use dependency injection for database connectivity in ASP.NET Core

You can specify the connection string to be used to connect to the database in the appSettings.json file. Then you can use the GetConnectionString() method of the IConfiguration instance to retrieve the connection string.

Next, you should take advantage of dependency injection to inject the IConfiguration instance to the AuthorRepository class as shown in the code snippet given below.

public class AuthorRepository : IAuthorRepository
{
    private readonly IConfiguration _config;
    public EmployeeRepository(IConfiguration config)
    {
        _config = config;
    }
    //Other methods
}

The following code snippet shows how you can create an instance of the SqlConnection class.

public IDbConnection DbConnection
    {
        get
        {
            return new SqlConnection(_config.GetConnectionString
            ("IDGDbConnectionString"));
        }
    }

The DbConnection property can now be used in lieu of creating an instance of the SqlConnection class as shown earlier. You can modify the Insert method of the AuthorRepository as shown below.

public async Task<bool> Insert(Author model)
 {
      int x = -1;
      using (var connection = DbConnection)
      {
          await connection.OpenAsync();
          var sqlStatement = @"
          INSERT INTO Author (FirstName, LastName, Address)";
          x = await connection.ExecuteAsync(sqlStatement, model);
      }
   return x > 0;
 }

Add the repository to the services layer in ASP.NET Core

You can write the following code to add the repository to the services layer using dependency injection so that you can access it from the controllers.

public void ConfigureServices(IServiceCollection services)
    {
        services.AddTransient<IAuthorRepository , AuthorRepository>();
        //Other code
    }

Use the repository instance in the controller in ASP.NET Core

We will now take advantage of dependency injection to use the AuthorRepository instance in the controller. The following code snippet illustrates how this can be done.

[Route("api/[controller]")]
[ApiController]
public class AuthorController : ControllerBase
{
    private readonly IAuthorRepository _authorRepository;
    public AuthorController(IAuthorRepository authorRepository)
    {
        _authorRepository = authorRepository;
    }
  //Action methods
}

Finally, the following code snippet illustrates how you can retrieve an Author instance using an instance of AuthorRepository.

[HttpGet]
[Route("{id}")]
public async Task<ActionResult<Author>> GetByID(int id)
 {
    return await _authorRepository.GetByID(id);
 }

Dapper is a lightweight ORM built by Sam Saffron of Stack Overflow. Object-relational mappers are used to eliminate the “impedance mismatch” that exists between the object models of programming languages and the data models of databases. If you would like to execute advanced queries using Dapper, you should look at the DapperExtensions library. You can read my article on advanced operations in Dapper here.