.NET Core API With Dapper, Repository And UnitOfWork

Introduction

In this tutorial, you will use ASP.NET Core Web API to create a web API that returns a list of brands. This article focuses on creating web API using dapper, repository pattern, and UnitOfWork pattern in .NET 8. I will guide you through the steps by step to create web API.

HTTP is not just for serving up web pages. HTTP is also a powerful platform for building APIs that expose services and data. HTTP is simple, flexible, and ubiquitous. Almost any platform that you can think of has an HTTP library, so HTTP services can reach a broad range of clients, including browsers, mobile devices, and traditional desktop applications.

ASP.NET Core Web API is a framework for building web APIs on top of the .NET Framework.

Creating a New Project in Visual Studio

Launch Visual Studio and select Create a New Project.

Creating a Core Web API

Start Visual Studio and choose Create a new project.
In the Create a new project dialog, select ASP.NET Core Web API
Click Next.

Configuring Your New Project

Enter the project name as BikeStore.
Choose a location for your project.
Specify the solution name.
Click on Next.
Select Framework 8.0.
Click Create.

Option 1. Install the Dapper, Microsoft.Data.SqlClient Library through NuGet Package Manager

Go to Project > Manage NuGet Packages.
On the NuGet Package Manager page, select nuget.org as the Package source.
Under the Browse tab, search for Dapper, then choose Dapper from the list and select Install.
If prompted to verify the installation, click OK.

Option 2. Installing the Dapper Library through the Visual Studio Command Line

In Visual Studio, navigate to Tools > NuGet Package Manager > Package Manager Console.
Enter the following command in the Package Manager Console tab:

Install-Package Dapper

Now, the package will download and install, making it ready for use in your current Core Web API project.

Adding a Data Model Class

A model is an object that represents the data in your application. ASP.NET Web API can automatically serialize your model to JSON, XML, or some other format and then write the serialized data into the body of the HTTP response message. As long as a client can read the serialization format, it can deserialize the object. Most clients can parse either XML or JSON.

In Solution Explorer, right-click the Models folder. From the context menu, select Add, then select Class

Name the class "BaseModel". Add the following properties to the BaseModel class.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.Models
{
    public class BaseModel
    {
        public Guid f_uid { get; set; }
        [Display(Name = "ID")]
        public int f_iid { get; set; }
        [Display(Name = "Create Date")]
        public DateTime f_create_date { get; set; }
        [Display(Name = "Update Date")]
        public DateTime f_update_date { get; set; }
        [Display(Name = "Delete Date")]
        public DateTime f_delete_date { get; set; }
    }
}

In Solution Explorer, right-click the Models folder. From the context menu, select Add, then select Class

Name the class "BrandModel". Add the following properties to the BrandModel class.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.Models
{
    public class BrandModel:BaseModel
    {
        [Display(Name ="Brand Name")]
        public string f_brand_name { get; set; }
    }
}

Adding interfaces

In Solution Explorer, right-click Add New Folder.
Rename the folder to Interfaces.
Right-click on the renamed folder and Add the interface name IGenericRepository.cs

IGenericRepository.cs

using BikeStores.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.BusinessLogic.Interfaces
{
    public interface IGenericRepository<T> where T : class
    {
        Task<IEnumerable<T>> Get();
        Task<T> Find(Guid uid);
        Task<T> Add(T model);
        Task<T> Update(T model);
        Task<int> Remove(T model);
    }
}

IUnitOfWork.cs

using BikeStores.BusinessLogic.Repositories;
using BikeStores.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.BusinessLogic.Interfaces
{
    public interface IUnitOfWork 
    {
        IBrand Brands { get; }
    }
}

UnitOfWork.cs

using BikeStores.BusinessLogic.Interfaces;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.BusinessLogic.Repositories
{
    public class UnitOfWork : IUnitOfWork
    {
        public IBrand Brands { get; set; }

        public UnitOfWork(IBrand Brands)
        {
            this.Brands = Brands;
        }
    }
}

IBrand.cs

using BikeStores.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BikeStores.BusinessLogic.Interfaces
{
    public interface IBrand :IGenericRepository<BrandModel>
    {
    }
}

BrandsRepository.cs

using BikeStores.BusinessLogic.Interfaces;
using BikeStores.Models;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;

namespace BikeStores.BusinessLogic.Repositories
{
    public class BrandsRepository : IBrand
    {
        private readonly IConfiguration _configuration;
        private readonly SqlConnection _connection;
        public BrandsRepository(IConfiguration configuration)
        {
            _configuration = configuration;
            _connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection"));
        }
        public async Task<IEnumerable<BrandModel>> Get()
        {
            var sql = $@"SELECT [f_uid]
                               ,[f_iid]
                               ,[f_brand_name]
                               ,[f_create_date]
                               ,[f_update_date]
                               ,[f_delete_date]
                              FROM 
                                [t_brands]";

            return await _connection.QueryAsync<BrandModel>(sql);
        }
        public async Task<BrandModel> Find(Guid uid)
        {
            var sql = $@"SELECT [f_uid]
                               ,[f_iid]
                               ,[f_brand_name]
                               ,[f_create_date]
                               ,[f_update_date]
                               ,[f_delete_date]
                          FROM 
                                [t_brands]
                          WHERE
                                [f_uid]=@uid";

            return await _connection.QueryFirstOrDefaultAsync<BrandModel>(sql, new { uid });
        }
        public async Task<BrandModel> Add(BrandModel model)
        {
            model.f_uid = Guid.NewGuid();
            model.f_create_date = DateTime.Now;

            var sql = $@"INSERT INTO [t_brands]
                               ([f_uid],
                                [f_brand_name],
                                [f_create_date])
                         VALUES
                               (@f_uid,
                                @f_brand_name,
                                @f_create_date)";

            await _connection.ExecuteAsync(sql, model);
            return model;
        }
        public async Task<BrandModel> Update(BrandModel model)
        {
            model.f_update_date = DateTime.Now;
            var sql = $@"UPDATE [t_brands] 
                        SET
                            [f_brand_name] = @f_brand_name,
                            [f_update_date] =@f_update_date
                        WHERE 
                            [f_uid]=@f_uid"; ;

            await _connection.ExecuteAsync(sql, model);
            return model;
        }
        public async Task<int> Remove(BrandModel model)
        {
            model.f_delete_date = DateTime.Now;
            var sql = $@"
                        UPDATE [t_brands] 
                        SET
                            [f_delete_date] =@f_delete_date
                        WHERE 
                            [f_uid]=@f_uid";

            return await _connection.ExecuteAsync(sql, model);
        }
    }
}

Adding a Controller

In Web API, a controller is an object that handles HTTP requests. We'll add a controller that can return either a list of brands or a single brand specified by ID.

In Solution Explorer, right-click the Controllers folder. Select Add and then select Controller.
In the Add Scaffold dialog, select Web API Controller - Empty. Click Add.
In the Add Controller dialog, name the controller "BrandsController". Click Add.
The scaffolding creates a file named BrandsController.cs in the Controllers folder.

If this file is not open already, double-click the file to open it. Replace the code in this file with the following.

using BikeStores.BusinessLogic.Interfaces;
using BikeStores.Models;
using Microsoft.AspNetCore.Mvc;

namespace BikeStore.Api.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class BrandsController : ControllerBase
    {
        private readonly IUnitOfWork unitOfWork;
        public BrandsController(IUnitOfWork unitOfWork)
        {
            this.unitOfWork = unitOfWork;
        }

        [HttpGet]
        public async Task<IEnumerable<BrandModel>> GetBrands()
        {
            return await unitOfWork.Brands.Get();
        }

        [HttpGet("{id}")]
        public async Task<IActionResult> GetBrand(Guid id)
        {
            if (id == Guid.Empty)
            {
                return BadRequest();
            }
            var brand = await unitOfWork.Brands.Find(id);
            if (brand == null)
            {
                return NotFound();
            }

            return Ok(brand);
        }

        [HttpPost]
        public async Task<IActionResult> PostBrand(BrandModel model)
        {
            await unitOfWork.Brands.Add(model);
            return Ok();
        }

        [HttpPut]
        public async Task<IActionResult> PutBrand(BrandModel model)
        {
            if (model == null || model.f_uid == Guid.Empty)
            {
                return BadRequest();
            }

            var brand = await unitOfWork.Brands.Find(model.f_uid);

            if (brand == null)
            {
                return NotFound();
            }

            await unitOfWork.Brands.Update(brand);
            return Ok();
        }

        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteBrand(Guid id)
        {
            if (id == Guid.Empty)
            {
                return BadRequest();
            }

            var brand = await unitOfWork.Brands.Find(id);

            if (brand == null)
            {
                return NotFound();
            }

            await unitOfWork.Brands.Remove(brand);
            return Ok();
        }
    }
}

Add Connection String

In Solution Explorer, select a file appsettings.json. Double-click on this file to open the "Add" connection string, as shown below.

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=FARHAN1086\\SQLEXPRESS;Initial Catalog=BikeStore-Dev-DB;Integrated Security=True;MultipleActiveResultSets=True;Encrypt=False;Trust Server Certificate=False;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Add repository services in the Program.cs

builder.Services.AddTransient<IBrand, BrandsRepository>();
builder.Services.AddTransient<IUnitOfWork, UnitOfWork>();

Run the Application

Select Ctrl+F5 to run the app without the debugger. Visual Studio runs the ASP.NET app and opens the default browser

Run application

Conclusion

The above article has taught us. How can we use Dapper with repository and UnitOfWork pattern? Dapper is Micro ORM, whereas Entity Framework is ORM. Dapper is faster than entity framework. I hope you enjoyed the article. Happy Coding.