Dapper In .NET 6.0 Web API

Dapper is a simple object mapper for .NET that owns the title of King of Micro ORM in terms of speed, as it’s virtually as fast as using a raw ADO.NET data reader. It is available as a NuGet package. An ORM (object-relational mapper) is responsible for mapping databases and programming languages.

In this tutorial we will discuss how to use Dapper in Web API. To demonstrate this, we will create a school management database (School and Student tables) in MS SQL Server and perform CRUD operations in that database through Web API.

What is Dapper?

• It is a simple object mapper for .NET.
• It is available as Nuget Package.
• It performs well because it doesn’t translate queries that we write in .NET to SQL.
• It is SQL-Injection safe because we can use parameterized queries.
• It supports multiple database providers.
• It extends ADO.NET’s IDbConnection and provides useful extension methods to query our database.
• It supports both synchronous and asynchronous methods.

Let us quickly go over the extension methods.

Execute

Use this to execute a command one or numerous times and return the number of changed rows.

Query

This can execute the query and map the result.

QueryFirst

This executes the query and maps the first result.

QueryFirstOrDefault

This executes the query and maps the first result, or a default value if there is no result.

QuerySingle

This executes the query and maps the result. It throws an exception if there is no record.

QuerySingleOrDefault

This executes the query and maps the result. If there is no record, it will default a value. It will throw an exception if there is more than one element in the result.

QueryMultiple

This executes multiple queries within the same command and maps the results.

As previously mentioned, Dapper supports async methods. Those methods are given below:

• ExecuteAsync,
• QueryAsync,
• QueryFirstAsync,
• QueryFirstOrDefaultAsync,
• QuerySingleAsync,
• QuerySingleOrDefaultAsync,
• QueryMultipleAsync

In this tutorial, we will use async methods.

The tools which I have used for this tutorial are below:

1. VS 2022 Community Edition Version 17.3.0 Preview 6.0
2. Web API
3. .NET 6.0
4. MS SQL Server (Developer Edition 2019)
5. Dapper
6. Swagger/Postman

The source code can be downloaded from the GittHub.

Let's get started. To begin, we will create the database.

Database

Create a MS SQL database (“StudentManagement”) with two tables - Student and School.

The script is available in GitHub ( filename : DB_Scripts.sql).

Now create the Web API using VS 2022.

Web API

Create a WebAPI project “APIWithDapperTutorial”.

Now we need to install the below two Nuget packages.

• Dapper – PM> Install-Package Dapper
• SQL Client – PM> Install-Package Microsoft.Data.SqlClient

Let's create the necessary layers. As I want to keep it simple, I’m not creating multiple project libraries for each layer. I’m separating each layer within the same projection using different folders.

As we are focusing only on Dapper integration, I’m not going to follow a full-blown clean architecture.

Repository Pattern

Let's create a folder called “Entities” and add two entities – Student and School, respectively.

School Entity

namespace APIWithDapperTutorial.Data.Entities {
    public class School {
        public int Id {
            get;
            set;
        }
        public string Name {
            get;
            set;
        }
        public string Address {
            get;
            set;
        }
        public List < Student > Students {
            get;
            set;
        } = new List < Student > ();
    }
}

Student Entity

namespace APIWithDapperTutorial.Data.Entities {
    public class Student {
        public int Id {
            get;
            set;
        }
        public string Name {
            get;
            set;
        }
        public int SchoolId {
            get;
            set;
        }
        public string Grade {
            get;
            set;
        }
    }
}

These two entities represent the database tables School and Student, respectively.

Now, add an entry in AppSettings.json for connection string. The connection string looks like the below:

"ConnectionStrings": {
    "SqlConnection": "server=.; database=SchoolManagement; Integrated Security=true"
}

Note
This depends on your needs, so please feel free to update the connection string.

As we are leveraging the Microsoft.Data.SqlClient package, if our database is not using encryption, the db connection will fail with and show the below error message.

Microsoft.Data.SqlClient.SqlException

A connection was successfully established with the server, but then an error occurred during the login process (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.).

To resolve this issue, please update the connection string as shown below.

"ConnectionStrings": {
    "SqlConnection": "server=.; database=SchoolManagement; Integrated Security=true;encrypt=false"
}

Now, create a folder called “Context” and create a SchoolContext class.

using APIWithDapperTutorial.Data.Entities;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
using System.Data;
namespace APIWithDapperTutorial.Data.Context {
    public class SchoolContext {
        private ConnectionStringOptions connectionStringOptions;
        public SchoolContext(IOptionsMonitor < ConnectionStringOptions > optionsMonitor) {
            connectionStringOptions = optionsMonitor.CurrentValue;
        }
        public IDbConnection CreateConnection() => new SqlConnection(connectionStringOptions.SqlConnection);
    }
}

Here we injected an IOptionMonitor<ConnectionstringOpntions> interface to read the connection string from AppSettings.json file.

I would request that you to go through the Microsoft Documentation for the Options Pattern to understand the IOptionMonitor interface.

A method called “CreateConnection()” will return the SQLConnection object.

Once we complete this Context class creatin, we need to register it as singleton service in Program.cs. Since we are using .NET 6.0, we don’t have the Startup.cs file.

// Add services to the container.
builder.Services.AddSingleton<SchoolContext>();

Now, create a folder called “Interface” and add interface ISchoolRepository.cs.

using APIWithDapperTutorial.Data.Entities;
using APIWithDapperTutorial.Domain.Model;
namespace APIWithDapperTutorial.Domain.Interface {
    public interface ISchoolRepostory {
        Task < IEnumerable < School >> GetAllSchoolsAsync();
        Task < School > GetSchoolByIdAsync(int id);
        Task < School > CreateSchoolAsync(SchoolDto school);
        Task UpdateSchoolAsync(int id, SchoolDto school);
        Task DeleteSchoolByIdAsync(int id);
        Task < School > GetSchoolByStudentIdAsync(int studentId);
        Task < School > GetSchoolWithStudentsBySchoolId(int schoolId);
        Task < List < School >> GetMultipleSchoolsAndStudentsAsyn();
        Task CreateListOfSchoolsByAsync(List < SchoolDto > schoolList);
    }
}

Next, create a folder called “Repository” and add the repository implementation class and implement the intereface below.

using APIWithDapperTutorial.Data.Context;
using APIWithDapperTutorial.Data.Entities;
using APIWithDapperTutorial.Domain.Interface;
using APIWithDapperTutorial.Domain.Model;
using Dapper;
using System.Data;
namespace APIWithDapperTutorial.Data.Repository {
    public class SchoolRepository: ISchoolRepostory {
        private readonly SchoolContext _schoolContext;
        public SchoolRepository(SchoolContext schoolContext) {
            _schoolContext = schoolContext;
        }
    }
}

Let me go ahead and explain one method here and the rest all can be available in the GitHub repository - please feel free to go through it.

public async Task < IEnumerable < School >> GetAllSchoolsAsync() {
    var query = "SELECT Id, Name, Address FROM School";
    using
    var con = _schoolContext.CreateConnection();
    var school = await con.QueryAsync < School > (query);
    return school.ToList();
}

Here QueryAsync(), an extension method from Dapper, executed the query and mapped the result.

Now it is time to register the Repository interface and implementation class in Program.cs as below:

builder.Services.AddScoped<ISchoolRepostory, SchoolRepository>();

Controller

Now is the time to create the Controller class and add necessary action methods.

namespace APIWithDapperTutorial.Controllers {
    [Route("api/schools")]
    [ApiController]
    public class SchoolController: ControllerBase {
        private readonly ISchoolRepostory _schoolRepository;
        public SchoolController(ISchoolRepostory schoolRepostory) {
            _schoolRepository = schoolRepostory;
        }
    }
}

Here we have created a control called SchoolController and injected the ISchoolRepository interface.

[HttpGet]
public async Task < IActionResult > GetSchoolsAsync() {
    var schools = await this._schoolRepository.GetAllSchoolsAsync();
    return Ok(schools);
}

I’m not going to explain the Controller Class as I strongly believe that you all are familiar with the Web API controller class and its usage.

Here I have mentioned only one method. Please go through the GitHub link for the rest all methods.

Note
I know that there are many missing components such as:

Service Layer

This wraps the Repository class. Since we don’t have any business logic, I deliberately ignored the Service Layer to avoid unnecessary complexity in this tutorial. It depends on our application size and business logic, but usually we need to have a Service Layer to act as a wrapper on top of Repositories.

Exception Handling

I haven’t implemented the exception handling, as our intention is to understand the Dapper integration. There are multiple approaches used to handle exceptions. Use Try-catch block in each action methods or implement Global exception handling through custom middleware to avoid code repetition in each action method.

Now, let's test one method.

To keep it simple, I have displayed only three end points below. Please free to download the source and execute - you will be able to see all the end points.

Now I’m will execute the first end point /api/schools.

I may not have explained in detail about each Dapper extension method - if you can, check out the source code from GitHub and go through it. You will get an overview of each method, including stored procedure invocation and Transaction implementation.

Thank you for reading this article. Please leave your comments in comment box below.