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 👇

​

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. 💌