Dapper in .NET 6.0 for CRUD Operations

Dapper Introduction

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

Using Dapper, you can execute raw SQL queries and stored procedures effectively.

Benefits

Dapper comes with its own set of pros and cons. Let’s look into the benefits first:

• High performance (as of July 2023)
• Supports multiple databases: SQL Server, MySQL, SQLite, SQLCE, and Firebird
• Simple to use: Just a few steps and you’re ready
• Improves your SQL skills

When to Use Dapper

Dapper is best suited when:

• Performance is crucial for your project
• Your team is skilled at writing SQL queries
• You are dealing with complex SQL queries
• The project size is small to medium

How to Use it in .NET 6.0

Here’s a quick guide to get started with Dapper in .NET 6.0:

• Install Dapper NuGet Package
• Install Microsoft.Data.SqlClient NuGet Package
• Set up your database context and start using it

Methods Available in Dapper

Some important methods in Dapper include:

• Execute: Used to execute single or multiple commands (Create, Delete, Update). The first parameter is the query, and the second is for any parameters.

  

• Query: Returns a sequence of dynamic objects that can be converted into DTOs. Useful for Get queries. Variants include:

  • QueryFirst: Retrieves the first record.
  • QueryFirstOrDefault: Retrieves the first record or returns default if none is found.
  • QuerySingle: Retrieves a single record.
  • QuerySingleOrDefault: Retrieves a single record or returns default if none is found.

Note: Both QuerySingle and QuerySingleOrDefault throw exceptions if more than one record is found. These methods are useful for cases like GetById, where the ID is a primary key and only one result should be returned.

• QueryMultiple: Used to execute multiple queries. For example, you can retrieve a student and their associated teachers (1-M relationship) in one query.

Calling Stored Procedures Using Dapper

Calling stored procedures with Dapper is similar to the methods above. You need to pass:

• Stored procedure name
• Parameters with direction as input
• CommandType: Set to StoredProcedure

If the stored procedure returns a single record, use one of the QuerySingle variants. If it returns multiple records, use the Execute variants.

Cons of Dapper

Before choosing Dapper, consider these drawbacks:

• Less abstraction compared to full-fledged ORMs
• Time-consuming as you need to write SQL queries manually
• No change tracking: Updates and deletions must be handled manually

GitHub Code

Check out the demo code for Dapper with CRUD operations at my GitHub Repo