.NET APIs Part 3 – Dapper

Posted on August 27, 2023 by Jesse Liberty

In part 2 of this series we created a simple database. In this part we’ll look at how to perform CRUD operations against that DataBase in anticipation of creating APIs for these operations.

Dapper is a micro-ORM (Object Relational Mapper) for .NET that simplifies data access against a database. In this blog post, we’ll focus on a code example that employs Dapper to manage a Car entity. This example uses a custom DatabaseConnectionFactory to handle database connections. Let’s dive right in.

Database configuration

You will need a working MS Sql database running to test the code. In the sample code, you can set your connections details in the appsettings.json file.

Create the database and car table

Create your database and then you can use the following script to initialize the database

USE [Cars]
GO
/****** Object:  Table [dbo].[car] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[car](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[make] [varchar](50) NULL,
	[model] [varchar](50) NULL,
	[model_year] [int] NULL,
	[price] [decimal](18, 0) NULL,
	[deleted] [int] NULL,
 CONSTRAINT [add_identity] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Required NuGet Packages

We are using the following NuGet packages:

- Dapper
- Dapper.SqlBuilder

You can install these packages using the NuGet Package Manager Console:

Install-Package Dapper
Install-Package Dapper.SqlBuilder

Setting Up the Database Connection Factory

Before we get into the CarRepository class, let’s discuss the DatabaseConnectionFactory class. This class is responsible for creating and opening database connections. It leverages DbSettings to get the connection string:

DatabaseConnectionFactory takes an IOptions<DbSettings> dependency, which allows it to read the database settings from a configuration source (like appsettings.json). The GetConnection method uses this information to create and open a SQL Server connection, which it then returns.

The Car Repository

Initialization and Dependency Injection

The CarRepository class takes a DatabaseConnectionFactory as a constructor argument, indicating dependency injection:

Methods in Car Repository

GetAll Method

This method returns all the car records. Optionally, it can also return records marked as deleted.

The GetAll method uses Dapper.SqlBuilder to construct the SQL query dynamically. This allows for more flexibility, as you can conditionally add WHERE clauses, joins, or other SQL constructs.

Get Method

This method retrieves a single car record based on its ID:

UpsertAsync Method

The UpsertAsync method handles both insert and update operations:

DeleteAsync Method

The DeleteAsync method marks the record as deleted:

Resources

Dapper GitHub Repository 
Dapper SqlBuilder

Conclusion

Dapper offers a powerful, yet simple way to perform CRUD operations in .NET applications. The code example in this post demonstrates how to make the most of Dapper in a practical scenario involving a Car entity. The DatabaseConnectionFactory class helps to manage database connections efficiently, ensuring that the rest of the code can focus on business logic rather than connection management. Whether you need to build SQL queries dynamically or perform simple operations, Dapper has tools to help you get the job done efficiently.

Rodrigo Juarez is a full-stack developer who has specialized in Xamarin in recent years and is now focusing on MAUI.
He is also a book author. With over 25 years of experience, Rodrigo has contributed to a diverse array of projects, developing applications for web, desktop, and mobile platforms. Specialized in Microsoft technologies, he has expertise across various sectors, including management, services, insurance, pharmacy, and banking. Rodrigo Juarez can be reached at info@rodrigojuarez.com

About Jesse Liberty

** Note ** Jesse is currently looking for a new position. You can learn more about him at https://jesseliberty.bio Thank you. Jesse Liberty has three decades of experience writing and delivering software projects and is the author of 2 dozen books and a couple dozen online courses. His latest book, Building APIs with .NET, is now available wherever you buy your books. Liberty was a Team Lead and Senior Software Engineer for various corporations, a Senior Technical Evangelist for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS. He is a 13 year Microsoft MVP.

View all posts by Jesse Liberty →