Using Dapper For Data Access In ASP.NET Core Applications
Geo J Thachankary
For most of the .NET developers out there the primary choice of data access will be Microsoft's object-relational mapper Entity Framework. But there are other ORM tools available for .NET applications as well. One such popular ORM tool is Dapper. Dapper is a micro ORM which is developed by the folks at StackOverflow. In this article, we will look at the features of Dapper by integrating Dapper with an ASP.NET Core application.
We shall be creating an ASP.NET Core Web API application with .NET Core 3.0. Most of the code will be compatible with earlier versions of .NET Core too. For using .NET Core 3.0 we need to install the .NET Core 3.0 SDK and require Visual Studio 2019. If Visual Studio 2019 is not available we can use Visual Studio Code and Dotnet CLI for development.
The application will be interacting with a SQL Server database with two tables - Movies and Directors. Use the following SQL script to create and seed the database.
CREATE DATABASE [movie-db];
USE [movie-db];
CREATE TABLE [dbo].[Directors](
[Id] [int] IDENTITY(1,1) PRIMARY KEY,
[Name] [nvarchar](50) NOT NULL,
);
CREATE TABLE [dbo].[Movies](
[Id] [int] IDENTITY(1,1) PRIMARY KEY,
[Name] [nvarchar](50) 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
Now create an ASP.NET Core Web API project. Let's name it MovieApp. Next, we need to install two Nuget packages in our project - Dapper and Microsoft.Data.SqlClient. Visual Studio users can make use of the Nuget package manager of Visual Studio. As an alternative, we can install these packages with DotNet CLI also.
Unlike Entity Framework, Dapper will not translate our LINQ queries to SQL. We need to write the SQL query ourselves and shall ask Dapper to execute it for us. Dapper will execute the query and will map the result to our POCO classes. This is the main difference between EF and Dapper. Dapper will only map our query results to objects. It will not create a query for us.
Now let's write our first API for getting all the movies from the database. Create a model class named MovieModel.cs. This class shall have property names matching with column names in the query result. Then only Dapper will map the result columns with the class. If the column names are different we can make use of SQL aliases to match with the property name.
public class MovieModel
{
public int Id { get; set; }
public string Name { get; set; }
public string DirectorName { get; set; }
public short ReleaseYear { get; set; }
}
For interacting with the database we need to set up our connection string in our application configuration. Add the following section in the appsettings.json file.
"ConnectionStrings": {
"DefaultConnection": "<connection string here>"
}
Add a class ConnectionString in the solution. We will use this class to hold the connection string value from the configuration file and use it in our classes via dependency injection.
public sealed class ConnectionString
{
public ConnectionString(string value) => Value = value;
public string Value { get; }
}
Now we need to register the configuration in the ASP.NET Core dependency injection container. Add the following lines in the ConfigureServices method in Startup.cs.
var connectionString = new ConnectionString(Configuration.GetConnectionString("DefaultConnection"));
services.AddSingleton(connectionString);
Now add an interface IMovieRepository.cs with the method signature for getting all movies.
public interface IMovieRepository
{
Task<IEnumerable<MovieModel>> GetAllMovies();
}
Add a class MovieRepository.cs which implements the IMovieRepository interface.
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;
}
}
}
In the method, we wrote the SQL query for fetching the movies along with their director names. Then we used the QueryAsync method of Dapper to execute the query and bind the response from the database to the MovieModel class.
We need to register the MovieRepository class in our DI container. Add the following code to the ConfigureServices method.
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 in 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. Using an anonymous object for supplying parameters to an SQL query via Dapper 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. Let's 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.cs and DirectorModel.cs. 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 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.
Till now we were 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 the 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 assure 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 adds the record 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.
Subscribe to my newsletter
Read articles from Geo J Thachankary directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by