Dapper In .NET 6.0 Web API

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