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.

var query = @"INSERT INTO Newsletter (Type, Name, Url, About) 
              VALUES(@Type, @Name, @Url, @About)";
var parameters = new DynamicParameters();
parameters.Add("Url", newsletter.Url, DbType.String);
parameters.Add("Name", newsletter.Name, DbType.String);
parameters.Add("Type", newsletter.Type, DbType.String);
parameters.Add("About", newsletter.About, DbType.String);

using var connection = context.Connect();
return await connection.ExecuteAsync(query, parameters) > 0;

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.

   var query = @"SELECT * FROM Newsletter WHERE Id = @Id";
var parameters = new DynamicParameters();
parameters.Add("Id", Id, DbType.Int32);

using var connection = _context.Connect();
await connection.QuerySingleOrDefaultAsync<Newsletter>(query, parameters);

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

var query = @"SELECT * FROM Newsletter WHERE Id = @Id";
var parameters = new DynamicParameters();
parameters.Add("Id", Id, DbType.Int32);

using var connection = _context.Connect();
await connection.QuerySingleOrDefaultAsync<Newsletter>(query, parameters);

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