Using Dapper For Data Access In ASP.NET Core Applications

CREATE DATABASE [movie-db];
USE [movie-db];

CREATE TABLE [dbo].[Directors](
    [Id] [int] IDENTITY(1,1) PRIMARY KEY,
      NOT NULL,
);

CREATE TABLE [dbo].[Movies](
    [Id] [int] IDENTITY(1,1) PRIMARY KEY,
      NOT NULL,
    [DirectorId] [int] NOT NULL,
    [ReleaseYear] [smallint] NOT NULL,
    FOREIGN KEY([DirectorId]) REFERENCES [dbo].[Directors]([Id])
);

SET IDENTITY_INSERT [dbo].[Directors] ON
GO
INSERT [dbo].[Directors] ([Id], [Name]) VALUES (1, N'Chistopher Nolan')
GO
INSERT [dbo].[Directors] ([Id], [Name]) VALUES (2, N'James Camron')
GO
SET IDENTITY_INSERT [dbo].[Directors] OFF
GO
SET IDENTITY_INSERT [dbo].[Movies] ON
GO
INSERT [dbo].[Movies] ([Id], [Name], [DirectorId], [ReleaseYear]) VALUES (1, N'Dunkirk', 1, 2017)
GO
INSERT [dbo].[Movies] ([Id], [Name], [DirectorId], [ReleaseYear]) VALUES (2, N'Interstellar', 1, 2014)
GO
INSERT [dbo].[Movies] ([Id], [Name], [DirectorId], [ReleaseYear]) VALUES (3, N'Avatar', 2, 2009)
GO
INSERT [dbo].[Movies] ([Id], [Name], [DirectorId], [ReleaseYear]) VALUES (4, N'Titanic', 2, 1997)
GO
INSERT [dbo].[Movies] ([Id], [Name], [DirectorId], [ReleaseYear]) VALUES (5, N'The Terminator', 2, 1984)
GO
SET IDENTITY_INSERT [dbo].[Movies] OFF

public class MovieModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string DirectorName { get; set; }
    public short ReleaseYear { get; set; }
}

"ConnectionStrings": {
    "DefaultConnection": "<connection string here>"
}

public sealed class ConnectionString
{
    public ConnectionString(string value) => Value = value;
    public string Value { get; }
}

var connectionString = new ConnectionString(Configuration.GetConnectionString("DefaultConnection"));
services.AddSingleton(connectionString);

public interface IMovieRepository
{
    Task<IEnumerable<MovieModel>> GetAllMovies();
}

using Dapper;
using Microsoft.Data.SqlClient;
using MovieApp.Models;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

public class MovieRepository : IMovieRepository
{
    private readonly ConnectionString _connectionString;

    public MovieRepository(ConnectionString connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<IEnumerable<MovieModel>> GetAllMovies()
    {
        const string query = @"SELECT m.Id, m.Name, d.Name AS DirectorName, m.ReleaseYear
                                FROM Movies m
                                INNER JOIN Directors d
                                ON m.DirectorId = d.Id";

        using (var conn = new SqlConnection(_connectionString.Value))
        {
            var result = await conn.QueryAsync<MovieModel>(query);
            return result;
        }
    }
}

services.AddScoped<IMovieRepository, MovieRepository>();

Add the controller class MoviesController.cs and write an action method to fetch all movies using this MovieRepository.

[ApiController]
public class MoviesController : ControllerBase
{
    private readonly IMovieRepository _movieRepository;

    public MoviesController(IMovieRepository movieRepository)
    {
        _movieRepository = movieRepository;
    }

    [HttpGet("api/movies")]
    public async Task<IActionResult> GetMovies()
    {
        return Ok(await _movieRepository.GetAllMovies());
    }
}

Now using any tool like Postman we can test this API. Run the application and call the API http://localhost:<port-number>/API/movies using Postman. We shall get a JSON response containing all movies with their director names.

Now, let's add an API for getting a movie detail by supplying its ID. In this case, we shall use a SELECT query with a WHERE clause. Add the following code to the IMovieRepository interface.

Task<MovieModel> GetMovieById(int id);

Add the method implementation in the MovieRepository class.

public async Task<MovieModel> GetMovieById(int id)
{
    const string query = @"SELECT m.Id
                                , m.Name
                                , d.Name AS DirectorName
                                , m.ReleaseYear
                            FROM Movies m
                            INNER JOIN Directors d
                            ON m.DirectorId = d.Id
                            WHERE m.Id = @Id";

    using (var conn = new SqlConnection(_connectionString.Value))
    {
        var result = await conn.QueryFirstOrDefaultAsync<MovieModel>(query, new { Id = id });
        return result;
    }
}

In this method, we use Dapper's QueryFirstOrDefaultAsync method to fetch one record. We provide the parameter value in the SQL query as an anonymous object. This is the recommended method of supplying parameters to an SQL query via Dapper as this protects against an attack like SQL Injection.

Now let's add the controller action.

[HttpGet("api/movies/{id}")]
public async Task<IActionResult> GetMovies(int id)
{
    var movie = await _movieRepository.GetMovieById(id);

    if (movie != null)
    {
        return Ok(movie);
    }
    return NotFound(new { Message = $"Movie with id {id} is not available." });
}

When we test this API at the route http://localhost:<port-number>/api/movies/<id> we will get the details of the movie with the supplied ID from the database.

One of the powerful features of Entity Framework is the ability to fetch related data from the database. We make use of navigation properties to achieve this. But in Dapper, there is no straightforward way to achieve this. Next, we can look at how we can achieve this. We shall fetch all the directors along with their list of movies from the database.

Create two classes DirectorMovie and DirectorModel. These classes will be used to bind the SQL query response from the database.

public class DirectorMovie
{
    public int Id { get; set; }
    public string MovieName { get; set; }
    public short ReleaseYear { get; set; }
}

public class DirectorModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<DirectorMovie> Movies { get; set; }
}

Add the following method definition in IMovieRepository.

Task<IEnumerable<DirectorModel>> GetAllDirectors();

Add the implementation in MovieRepository.

public async Task<IEnumerable<DirectorModel>> GetAllDirectors()
{
    const string query = @"SELECT d.Id  
                                    , d.Name  
                                    , m.DirectorId  
                                    , m.Id  
                                    , m.Name MovieName  
                                    , m.ReleaseYear  
                                FROM dbo.Directors d  
                                INNER JOIN dbo.Movies m  
                                ON d.Id = m.DirectorId";  

    using (var conn = new SqlConnection(_connectionString.Value))
    {
        var directorDictionary = new Dictionary<int, DirectorModel>();

        var result = await conn.QueryAsync<DirectorModel, DirectorMovie, DirectorModel>(
            query,
            (dir, mov) =>
            {
                if (!directorDictionary.TryGetValue(dir.Id, out DirectorModel director))
                {
                    director = dir;
                    director.Movies = new List<DirectorMovie>();
                    directorDictionary.Add(director.Id, director);
                }
                director.Movies.Add(mov);
                return director;
            },
            splitOn: "DirectorId");

        return result.Distinct();
    }
}

In this method, we are making use of the Multi Mapping feature of Dapper. We will supply a mapping function as the parameter of the QueryAsync method of Dapper specifying how we need to populate the navigation property. We shall specify the field name of the query result set on which Dapper needs to split the result set. We can make use of the split data and pass that to our mapping function to populate the navigation properties.

Now, let's add the controller action.

[HttpGet("api/directors")]
public async Task<IActionResult> GetDirectors()
{
    return Ok(await _movieRepository.GetAllDirectors());
}

When we test this API at the route http://localhost:<port-number>/API/directors we will get the details of all directors along with their list of movies from the database.

So far we have been looking at SELECT statement execution using Dapper. Now we can look at how Dapper can be used to modify data. We shall now add a movie to the database.

Create a new model class CreateMovieModel.cs.

public class CreateMovieModel
{
    public string Name { get; set; }
    public int DirectorId { get; set; }
    public short ReleaseYear { get; set; }
}

Add the following method definition in IMovieRepository.

Task<int> AddMovie(CreateMovieModel movie);

Add the implementation in MovieRepository.

public async Task<int> AddMovie(CreateMovieModel movie)
{
    const string query = @"INSERT INTO dbo.Movies ([Name], [DirectorId], [ReleaseYear]) VALUES(@Name, @DirectorId, @ReleaseYear)";

    using (var conn = new SqlConnection(_connectionString.Value))
    {
        var result = await conn.ExecuteAsync(
            query,
            new { Name = movie.Name, DirectorId = movie.DirectorId, ReleaseYear = movie.ReleaseYear });
        return result;
    }
}

We are using Dapper's ExecuteAsync method for executing the insert query. This method returns an integer value representing the number of rows modified in that SQL query. If the number is greater than zero we can be assured that our query has been executed successfully.

Add the controller action.

[HttpPost("api/movies")]
public async Task<IActionResult> AddMovie(CreateMovieModel model)
{
    var result = await _movieRepository.AddMovie(model);
    if (result > 0)
    {
        return Ok(new { Message = "Movie added successfully." });
    }
  
    return StatusCode(500, new { Message = "Some error happened." });
}

Test the API by providing a JSON object representing the CreateMovieModel class. Dapper will execute the query and add the records to the database.

Summary

In this article, we looked at how to use Dapper as an ORM in .NET Core applications. For detailed documentation on Dapper refer to this link.