đź’» A Beginner's Guide To Dapper with a .NET Web API


Hey Reader,

In today’s tutorial, I’ll walk you through how to use Dapper, a high-performance micro ORM (object-relational mapper) with a .NET 8 Web API and SQL Server. We’ll cover everything you need to know to build a simple CRUD (Create, Read, Update, Delete) application.

Watch on YouTube đź“ş

Check out the full tutorial on YouTube to see everything in action 👇

video preview​

What We’ll Be Doing

We’re going to work with a simple video game database to start. You’ll learn how to:

  • Read all games or a specific game from the database,
  • Add a new game,
  • Update an existing game,
  • Delete a game, and
  • Understand how Dapper handles all that

First, we’ll set up our Web API using Visual Studio, configure the project, and add Dapper as a NuGet package. Once everything's set, we’ll create a model for our video games that mirrors the database structure: ID, title, publisher, developer, and release date.

Next up, you’ll see how to establish a connection to the SQL Server using a connection string in the appsettings.json. From there, we’ll write the code that interacts with our database, sending queries through Dapper to perform the CRUD operations.

Why Dapper?

Dapper gives you full control over your SQL queries. While it doesn’t auto-generate queries like Entity Framework, Dapper ensures everything stays efficient and lightweight, making it ideal for high-performance apps.

Plus, if you're working in an environment where you're dealing with an existing database or a team that handles the database design, Dapper might be perfect for you!

Step-by-Step Guide

1. Set up the Project:

First, let's create a new ASP.NET Core Web API project in Visual Studio. Make sure to select .NET 8.

2. Install Dapper:

In the NuGet Package Manager, search for and install Dapper. This package will allow us to easily map our SQL queries to C# objects.

3. Create a Video Game Model:

Add a new folder called Models and then create the following class:

4. Add a Connection String:

Next, open the appsettings.json file and add a connection string for your SQL Server Express database.

  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost\SQLEXPRESS;Database=VideoGameDbSimple;Trusted_Connection=true;TrustServerCertificate=true;"
  },

5. Create a Repository Interface:

Add a new folder called Repositories and create an interface for our repository.

6. Create the Repository Implementation:

Now, create a class that implements the IVideoGameRepository interface. This is where Dapper comes into play.

First, we inject IConfiguration, so that we get access to the connection string from the appsettings.json file. It is necessary to create a connection to the database each time we want to run a query.

We also add a little function to create the connection whenever we need it.

Next, we implement all the CRUD operations. Let's start with the easy one, receiving all video games.

Let's also create the VideoGamesController and add the corresponding GET method there.

Last step is registering the repository in the Program.cs.

Now we can run the application and test the first call with Swagger.

Awesome! Now, let's continue with the other CRUD operations - receiving a single video game, adding a video game, updating and deleting one.

Please note, that we're using parameters here. Otherwise, we would risk SQL injection attacks.

Don't forget the corresponding controller methods.

Wrapping Up

Now you have a fully functional API that can interact with a SQL Server database using Dapper. You understand how to fetch data from the database, add new records, update existing ones, and delete records you no longer need.

What's your experience with Dapper? Is it your goto tool in your projects or would you rather use Entity Framework or something else?

Happy coding and have an awesome weekend!

Take care,

Patrick

PS: Need help understanding .NET & Blazor? There are two ways I can help you with:

  1. Check out the .NET Web Academy, which provides masterclasses and a supportive community of like-minded developers.
  2. I'm open to coaching. If you need specific help, reply to this email and we'll figure something out.

PPS: Would you like to sponsor this newsletter? Click here. đź’Ś

Patrick God

Become a .NET & Blazor expert with weekly tutorials featuring best practices and the latest improvements, right in your inbox.

Read more from Patrick God
video preview

Hey Reader, Today, I show you how combining Blazor and JavaScript can give you the best of both worlds. Let’s dive into how you can easily mix the power of these two technologies in your projects! Watch on YouTube 📺 Check out the full tutorial on YouTube to see everything in action 👇 A Simple Example: Console Logging in Blazor Imagine you’re working on a Blazor Server application, and you want to log the current count (of the Counter page) to the console. Your first thought might be to use...

video preview

Today's Sponsor: HackerPulse — The Ultimate Dev Profile Don’t send 69 links to share your work - just share one! Bring together your GitHub, Stack Overflow, and LinkedIn profiles into a single HackerPulse dev profile. Perfect for job applications and networking, HackerPulse gives a complete picture of your skills and projects, all in one place. Create Your Profile Hey Reader, Today, I'm going to walk you through setting up a Blazor project using Clean Architecture. If you've ever wondered how...

video preview

Hey Reader, Today, I'd like to show you two powerful tools for managing data in .NET projects: Entity Framework and Dapper. I'm sure you already heard about them. First, let me explain what an ORM is and then walk you through examples of CRUD (Create, Read, Update, Delete) operations using both tools. Watch on YouTube 📺 Check out the full tutorial on YouTube to see everything in action 👇 What is an ORM? An ORM (Object-Relational Mapper) is a tool that helps your application communicate with a...