How to use the Dapper ORM in ASP.NET Core

Create a new ASP.NET Core Web API project

Creating an ASP.NET Core Web API project and utilizing Dapper to simplify data access.

• Launch Visual Studio 2019.
• Click on File > New > Project.
• From the project templates, select ASP.NET Core Web Application (.NET Core).
• Name the project and choose a location, then click OK.
• In the next window, select the API template.
• Choose the ASP.NET Core version you’d like to work with from the drop-down.
• Uncheck Enable Docker Support and choose No Authentication, as these options are unnecessary for this example.
• Click OK to create the project.

Set Up the Model Class

Once the project is created, we’ll start by adding a model class for our application. Create a new folder called Models, then add a class named Book to represent the data we’ll be working with.

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
}

Define the Repository Interface

Next, define the interface for accessing the data. This interface will contain a method to retrieve a Book by its Id.

public interface IBookRepository
{
    Task<Book> GetById(int id);
}

Implement the Repository Using Dapper

Now, we’ll implement the repository that uses Dapper for database queries. First, install the Dapper package via NuGet:

Install-Package Dapper

Here’s the implementation of BookRepository:

public class BookRepository : IBookRepository
{
    private readonly IConfiguration _config;
    private readonly string _connectionString;

    public BookRepository(IConfiguration config)
    {
        _config = config;
        _connectionString = _config.GetConnectionString("DefaultConnection");
    }

    public async Task<Book> GetById(int id)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            string query = "SELECT Id, Title, Author FROM Books WHERE Id = @id";
            if (connection.State != ConnectionState.Open)
                connection.Open();
            
            var result = await connection.QueryAsync<Book>(query, new { id });
            return result.FirstOrDefault();
        }
    }
}

Register the Repository with Dependency Injection

In Startup.cs, register the repository with the dependency injection container to make it available throughout the application:

public void ConfigureServices(IServiceCollection services)
{
    services.AddTransient<IBookRepository, BookRepository>();
    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
}

Inject the Repository into the Controller

Now, let’s create the BooksController to handle the API endpoints. We’ll inject the IBookRepository and use it in our controller methods.

[Route("api/[controller]")]
[ApiController]
public class BooksController : ControllerBase
{
    private readonly IBookRepository _bookRepository;

    public BooksController(IBookRepository bookRepository)
    {
        _bookRepository = bookRepository;
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<Book>> GetById(int id)
    {
        var book = await _bookRepository.GetById(id);
        if (book == null)
        {
            return NotFound();
        }
        return Ok(book);
    }
}

Configuration for the Connection String

Ensure that the connection string is available to the repository by placing it in appsettings.json and retrieving it via the IConfiguration object.

Here’s an example of the appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;"
  },
  ...
}

Now, in the BookRepository constructor, we can inject the IConfiguration to retrieve the connection string.

public BookRepository(IConfiguration config)
{
    _config = config;
    _connectionString = _config.GetConnectionString("DefaultConnection");
}

At this point, everything is set up. You can run the application and use the GET endpoint to retrieve book data by Id.

Dapper is a micro ORM that makes it easy to interact with relational databases in a simple and efficient manner. By using Dapper, we are able to simplify data access in an ASP.NET Core application while still maintaining high performance.