Overview

Asynchronous programming is a programming paradigm that allows operations to run independently of the main application thread, enabling more efficient use of resources and improved application performance. In the context of web applications, especially those built using ASP.NET Core, asynchronous programming is essential when performing I/O-bound operations such as database access. Traditional synchronous database calls can lead to thread blocking, which degrades the performance of applications, particularly under heavy load.

Dapper is a lightweight, open-source ORM (Object-Relational Mapping) tool for .NET that simplifies data access by mapping database results to C# objects. By leveraging Dapper's capabilities in conjunction with asynchronous programming, developers can create highly responsive applications that manage database interactions efficiently. This approach is particularly beneficial in scenarios where applications require high concurrency, such as web APIs, e-commerce platforms, and real-time data-driven applications.

Prerequisites

• C# Basics: Understanding of C# syntax and concepts.
• ASP.NET Core: Familiarity with building web applications using ASP.NET Core.
• SQL Knowledge: Basic understanding of SQL and relational databases.
• Dapper Library: Awareness of how to install and use Dapper in .NET applications.
• NuGet Package Manager: Knowledge of using NuGet to manage dependencies in ASP.NET Core.

Setting Up a New ASP.NET Core Project

To start implementing asynchronous data access with Dapper, we first need to set up a new ASP.NET Core project. This includes creating a web application and adding the necessary dependencies for Dapper.

dotnet new webapi -n AsyncDapperExample

This command creates a new ASP.NET Core Web API project named AsyncDapperExample. Next, navigate into the project directory:

cd AsyncDapperExample

Now, we will add the Dapper NuGet package to our project. Dapper can be installed using the following command:

dotnet add package Dapper

After installing Dapper, we need to ensure that our project can connect to a database. For this tutorial, we'll use SQLite as it is lightweight and easy to set up. Install the SQLite NuGet package:

dotnet add package Microsoft.Data.Sqlite

With our project set up, we are ready to implement asynchronous data access using Dapper.

Creating a Database and Data Model

Before we can access data asynchronously, we need to create a database and define a data model. For this example, we'll create a simple Product model representing products in an inventory system.

public class Product { public int Id { get; set; } public string Name { get; set; } public decimal Price { get; set; } }

This Product class contains three properties: Id, Name, and Price. Next, we need to create a SQLite database and a Products table to store product data. In the Startup.cs file, we can add a method to initialize our database:

public void InitializeDatabase() { using (var connection = new SqliteConnection("Data Source=products.db")) { connection.Open(); var cmd = connection.CreateCommand(); cmd.CommandText = "CREATE TABLE IF NOT EXISTS Products (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Price REAL);"; cmd.ExecuteNonQuery(); } }

This method opens a connection to a SQLite database file named products.db and creates a Products table if it does not already exist. To call this method during application startup, add the following line in the Configure method:

InitializeDatabase();

Now that our database and data model are set up, we can move on to implementing asynchronous data access with Dapper.

Implementing Asynchronous Data Access with Dapper

To demonstrate asynchronous data access, we will create a repository class that performs CRUD operations on the Products table using Dapper. We will focus on the GetAllProductsAsync method, which retrieves all products from the database asynchronously.

public class ProductRepository { private readonly string _connectionString; public ProductRepository(string connectionString) { _connectionString = connectionString; } public async Task> GetAllProductsAsync() { using (var connection = new SqliteConnection(_connectionString)) { await connection.OpenAsync(); var products = await connection.QueryAsync("SELECT * FROM Products"); return products; } } }

In this ProductRepository class, the constructor accepts a connection string. The GetAllProductsAsync method opens a connection to the database asynchronously using OpenAsync and retrieves all products using Dapper's QueryAsync method. This method returns an IEnumerable of Product objects.

Calling the Asynchronous Method from a Controller

Next, we will create a controller that uses the ProductRepository to fetch products asynchronously. In the Controllers folder, create a new controller named ProductsController:

[ApiController] [Route("api/[controller]")] public class ProductsController : ControllerBase { private readonly ProductRepository _repository; public ProductsController(ProductRepository repository) { _repository = repository; } [HttpGet] public async Task Get() { var products = await _repository.GetAllProductsAsync(); return Ok(products); } }

This controller defines a single Get action that calls GetAllProductsAsync to retrieve the products and returns them as a JSON response. The use of async/await ensures that the method is non-blocking, allowing for better scalability.

Edge Cases & Gotchas

While implementing asynchronous data access with Dapper, developers should be aware of common pitfalls that can lead to issues.

Connection Management

One common mistake is not properly managing database connections. Always ensure that connections are opened and closed correctly, especially in asynchronous methods.

// Incorrect approach - connection not disposed properly public async Task> GetProductsAsync() { var connection = new SqliteConnection(_connectionString); await connection.OpenAsync(); var products = await connection.QueryAsync("SELECT * FROM Products"); return products; } // Correct approach using (var connection = new SqliteConnection(_connectionString)) { await connection.OpenAsync(); var products = await connection.QueryAsync("SELECT * FROM Products"); return products; }

Handling Exceptions

Another important aspect is exception handling. Always wrap asynchronous calls in try-catch blocks to handle potential database exceptions gracefully.

try { var products = await _repository.GetAllProductsAsync(); return Ok(products); } catch (Exception ex) { return StatusCode(500, ex.Message); }

Performance & Best Practices

When working with asynchronous data access, following best practices can significantly enhance application performance.

Use Connection Pooling

Utilizing connection pooling can reduce the overhead of establishing connections by reusing existing ones. Ensure that your data source supports connection pooling and configure it appropriately in your connection string.

Minimize Database Calls

Reduce the number of database calls by fetching only the necessary data. Use pagination for large datasets to optimize performance.

Real-World Scenario: Implementing a Product API

Let’s tie everything together by implementing a simple product API that allows users to add, retrieve, and delete products.

[HttpPost] public async Task Create(Product product) { using (var connection = new SqliteConnection(_connectionString)) { await connection.OpenAsync(); var id = await connection.ExecuteAsync("INSERT INTO Products (Name, Price) VALUES (@Name, @Price)", product); return CreatedAtAction(nameof(Get), new { id }, product); } }

This Create action allows users to add new products to the database. The ExecuteAsync method is used for executing commands that do not return results.

[HttpDelete("{id}")] public async Task Delete(int id) { using (var connection = new SqliteConnection(_connectionString)) { await connection.OpenAsync(); var rowsAffected = await connection.ExecuteAsync("DELETE FROM Products WHERE Id = @Id", new { Id = id }); return rowsAffected > 0 ? NoContent() : NotFound(); } }

The Delete action removes a product based on its ID. It uses the same ExecuteAsync method to perform the delete operation.

Conclusion

• Asynchronous data access with Dapper improves application responsiveness.
• Proper connection management is crucial to avoid resource leaks.
• Exception handling is essential for robust applications.
• Follow best practices for optimal performance and scalability.
• Real-world scenarios demonstrate practical usage of concepts learned.