How to perform asynchronous methods using Dapper?

In this article, we'll explore how to use Dapper in an asynchronous manner to perform database operations using async/await.

Dapper is a popular micro-ORM for .NET that provides a simple and fast way to query databases using SQL. While Dapper does not directly support asynchronous programming, it is still possible to use it in an asynchronous manner by using async/await and the Task Parallel Library (TPL).

Installing dapper

You can install through the nuget package manager visual interface in the visual studio 2019 IDE, or enter the following command on the. Net cli command line.

dotnet add package Install-Package Dapper

Once the package is successfully installed into your project, it can be officially used.

Steps to perform Asynchronous method using Dapper

STEP 1: Configure the Connection String

The first step is to configure the connection string for your database. This can be done in the app.config or web.config file:

<connectionStrings>
<add name="DefaultConnection" 
     connectionString="Data Source=myserver;
                       Initial Catalog=mydatabase;
                       Integrated Security=True" 
     providerName="System.Data.SqlClient" />
</connectionStrings>

STEP 2: Define a Model

Next, define a model that represents the data you want to retrieve from the database. For example:

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

STEP 3: Define the Database Operations

Now that the model is defined, create a class that will perform the database operations. This class should have a constructor that takes an IDbConnection as a parameter, which can be passed in when creating the instance of the class. For example:

public class CustomerRepository
{
    private readonly IDbConnection _db;

    public CustomerRepository(IDbConnection db)
    {
        _db = db;
    }

    public async Task<Customer> GetAsync(int id)
    {
        const string sql = "SELECT * FROM Customers WHERE Id = @id";
        return await _db.QuerySingleOrDefaultAsync<Customer>(sql, new { id });
    }

    public async Task<IEnumerable<Customer>> GetAllAsync()
    {
        const string sql = "SELECT * FROM Customers";
        return await _db.QueryAsync<Customer>(sql);
    }

    public async Task<int> CreateAsync(Customer customer)
    {
        const string sql = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email); SELECT CAST(SCOPE_IDENTITY() AS INT)";
        return await _db.ExecuteScalarAsync<int>(sql, customer);
    }

    public async Task<bool> UpdateAsync(Customer customer)
    {
        const string sql = "UPDATE Customers SET Name = @Name, Email = @Email WHERE Id = @Id";
        return await _db.ExecuteAsync(sql, customer) > 0;
    }

    public async Task<bool> DeleteAsync(int id)
    {
        const string sql = "DELETE FROM Customers WHERE Id = @id";
        return await _db.ExecuteAsync(sql, new { id }) > 0;
    }
}

In this example, we have defined a CustomerRepository class that performs basic CRUD operations on a Customers table. Each method is marked as async and returns a Task that can be awaited.

STEP 4: Create a Database Connection

Before we can use the CustomerRepository class, we need to create a database connection. This can be done using the SqlConnection class provided by the ADO.NET framework:

var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using var connection = new SqlConnection(connectionString);

Note that we are using the using keyword to ensure that the connection is properly disposed of when it is no longer needed.

STEP 5: Perform Database Operations

Now that we have a database connection, we can use the CustomerRepository class to perform database operations:

var repository = new CustomerRepository(connection);  

// Get a single customer by ID
var customer = await repository.GetAsync(1); 

// Get all customers
var customers = await repository.GetAllAsync();  

// Create a new customer
var newCustomer = new Customer { Name = "John Doe", Email = "johndoe@example.com" }; 
var newCustomerId = await repository.CreateAsync(newCustomer);  

// Update a customer
var existingCustomer = await repository.GetAsync(1); 
existingCustomer.Email = "newemail@example.com"; 
var updated = await repository.UpdateAsync(existingCustomer);  

// Delete a customer
var deleted = await repository.DeleteAsync(newCustomerId);

In this example, we have used the CustomerRepository class to perform database operations in an asynchronous manner using async/await. The GetAsync method retrieves a single customer by ID, the GetAllAsync method retrieves all customers, the CreateAsync method creates a new customer and returns the new customer ID, the UpdateAsync method updates an existing customer, and the DeleteAsync method deletes a customer by ID.

Note that each method is marked as async and returns a Task that can be awaited. This allows the database operations to be performed in a non-blocking manner, which can improve performance and scalability.

In addition to the QueryAsync and ExecuteAsync methods used in this example, Dapper also provides a number of other asynchronous methods for performing database operations, such as QueryFirstOrDefaultAsync, QueryMultipleAsync, and ExecuteScalarAsync.

Conclusion:

In conclusion, while Dapper does not directly support asynchronous programming, it can still be used in an asynchronous manner using async/await and the Task Parallel Library (TPL). By performing database operations asynchronously, we can improve the performance and scalability of our applications, especially when working with large datasets or performing complex queries.