EP 23 : How to use Dapper in .NET 6.0 for CRUD Operations

Read Time : 4 Mins

In today’s newsletter we are going to discuss

• What is Dapper and why do we need it

• Pros and cons

• When to use dapper

• How to call stored procedure

• Cons of Dapper

• GitHub code of Demo

---

Dapper Introduction

Dapper is an open source high performance ORM (Object Relational Mapping) library with 246M+ downloads on Nuget Package Store for .NET and .NET Core.

Using it we can execute raw SQL queries and execute the stored procedures as well.

Benefits

Like every library it also has some pros and cons , let’s look into benefits first:

• It is high performant until now (July 2023)

• Supports SQL Server , MySQL , Sqlite,SqlCE and firebird

• Really simple to use , just few steps and BINGO

• It helps you in improving your SQL skills

When to use Dapper

Use dapper when:

• Performance is the key

• Your team is good at writing SQL queries

• You have to deal with a lot of complex SQL queries

• Project size is small to medium

How to use it in .NET 6.0

It is quite simple , I am using SQL so let’s see the simple steps:

• Install Dapper Nuget package

• Install Microsoft.Data.SqlClient Nuget package

• Set your database context use it

Methods available in Dapper

This is the list of few important Dapper methods:

Execute: It is used to execute single or multiple commands e.g. Create, Delete and Update. In first parameter we pass query and in second parameters

Query: It returns sequence of dynamic objects with properties, then we convert it into our desired DTOs useful in Get based queries. This method has few other variants

QueryFirst: To retrieve first record

QueryFirstOrDefault: To retrieve first record , if no record found then default

QuerySingle: To retrieve single record

QuerySingleOrDefault: To retrieve single record , if no record then default

Above both methods throws exception if more than one record found , its use case could be GetById when we know that Id is Primary Key and no other record would be found

QueryMultiple: To execute multiple queries , its use case could be when a entity has 1-M relationship like a Student with multiple Teachers so for bringing one student we can bring his teachers list as well.

Calling Stored Procedure using Dapper

Calling stored procedure is exactly same like previous methods , before calling it you have to decide about your methods that suits your need.

If SP returns single record then you can go for QuerySingle variants and if you want multiple records then you can go for Execute variants.

Only difference is of those parameters now you will pass these param

• Stored procedure name

• Parameters with direction as input

• Command Type : Which would be Stored Procedure

Cons of Dapper

You should keep these things in mind before selecting dapper

• Less abstraction

• Time-consuming because you have to write SQL queries

• No change tracking you'll manually handle updates and deletions

---

GitHub Code

Checkout code for Dapper Demo with CRUD operations at GitHub Repo

---

Whenever you’re ready , there are 4 ways I can help you

1. Promote yourself to 5800+ subscribers by sponsoring my Newsletter

2. Become a Patron and get access to 140+ .NET Questions and Answers

3. Get FREE eBook from Gumroad that contains 30 .NET Tips (2500+ downloads)

4. Book a one hour 1:1 Session and learn how I went from 0-29K on LinkedIn in 8 months , Book here

---

Special Offers 📢

Ultimate ASP.NET Core Web API Second Edition - Premium Package
10% off with discount code: 9s6nuez

---

You have to keep breaking your heart , until it opens - Rumi