API Development Using Dapper and Microsoft Asp.NET Core Web API

Introduction

ASP.NET Core is a powerful framework for building web applications, and "Dapper" is a popular micro ORM (Object-Relational Mapping) that works well with ASP.NET Core for database operations. Dapper provides a simple way to interact with databases using raw SQL queries while mapping the results to objects.

Setting Up Dapper in ASP.NET Core

1. Create an ASP.NET Core Project: Use Visual Studio or the .NET CLI to create a new ASP.NET Core project.
2. Install Dapper: You can add Dapper to your project using NuGet Package Manager.

   dotnet add package Dapper
   

3. Set Up Database Connection: Configure a database connection in your appsettings.json file.

   "ConnectionStrings": {
     "DefaultConnection": "YourDatabaseConnectionString"
   }
   

Create Model

namespace APIDevelopmentUsingDapper.Model
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string Category { get; set; }
        public string CategoryName { get; set; }
        public string CategoryDescription { get; set; }
        public string CategoryCategory { get; set; }
        public string CategoryCategoryName { get; set;}
    }
}

Create IDapperDbConnection

using System.Data;

namespace APIDevelopmentUsingDapper.Interfaces
{
    public interface IDapperDbConnection
    {
        public IDbConnection CreateConnection();
    }
}

Create Dapper Service Connection

using APIDevelopmentUsingDapper.Interfaces;
using Microsoft.Data.SqlClient;
using System.Data;

namespace APIDevelopmentUsingDapper.DapperDbConnections
{
    public class DapperDbConnection: IDapperDbConnection
    {
        public read-only string _connectionString;

        public DapperDbConnection(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DefaultConnection");
        }

        public IDbConnection CreateConnection()
        {
            return new SqlConnection(_connectionString);
        }

    }
}

Create IProductRepository

using APIDevelopmentUsingDapper.Model;

namespace APIDevelopmentUsingDapper.Interfaces
{
    public interface IProductRepository
    {
        Task<IEnumerable<Product>> GetAllProductsAsync();
        Task<Product> GetProductByIdAsync(int id);
        Task<int> CreateProductAsync(Product Product);
        Task<bool> UpdateProductAsync(Product Product);
        Task<bool> DeleteProductAsync(int id);
    }
}

Product Repository Implementation

using APIDevelopmentUsingDapper.Interfaces;
using APIDevelopmentUsingDapper.Model;
using Dapper;
using System.Data;

namespace APIDevelopmentUsingDapper.Repositories
{
    public class ProductRepository: IProductRepository
    {
        public IDapperDbConnection _dapperDbConnection;
        public ProductRepository(IDapperDbConnection dapperDbConnection)
        {
            _dapperDbConnection = dapperDbConnection;
        }
        public async Task<IEnumerable<Product>> GetAllProductsAsync()
        {
            using(IDbConnection db = _dapperDbConnection.CreateConnection())
            {
                return await db.QueryAsync<Product>("SELECT * FROM Products");
            }
        }

        public async Task<Product> GetProductByIdAsync(int id)
        {
            using(IDbConnection db = _dapperDbConnection.CreateConnection())
            {
                return await db.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Products WHERE Id = @Id", new { Id = id });
            }
        }

        public async Task<int> CreateProductAsync(Product Product)
        {
            using(IDbConnection db = _dapperDbConnection.CreateConnection())
            {
                const string query = "INSERT INTO Products (Name) VALUES (@Name); SELECT SCOPE_IDENTITY();";
                return await db.ExecuteScalarAsync<int>(query, Product);
            }
        }

        public async Task<bool> UpdateProductAsync(Product Product)
        {
            using(IDbConnection db = _dapperDbConnection.CreateConnection())
            {
                const string query = "UPDATE Products SET Name = @Name WHERE Id = @Id";
                int rowsAffected = await db.ExecuteAsync(query, Product);
                return rowsAffected > 0;
            }
        }

        public async Task<bool> DeleteProductAsync(int id)
        {
            using(IDbConnection db = _dapperDbConnection.CreateConnection())
            {
                const string query = "DELETE FROM Products WHERE Id = @Id";
                int rowsAffected = await db.ExecuteAsync(query, new { Id = id });
                return rowsAffected > 0;
            }
        }
    }
}

using APIDevelopmentUsingDapper.Interfaces;
using APIDevelopmentUsingDapper.Model;
using APIDevelopmentUsingDapper.Repositories;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace APIDevelopmentUsingDapper.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductController : ControllerBase
    {
        private readonly IProductRepository _repository;

        public ProductController(IProductRepository repository)
        {
            _repository = repository;
        }

        [HttpGet]
        public async Task<ActionResult<IEnumerable<Product>>> GetProducts()
        {
            var Products = await _repository.GetAllProductsAsync();
            return Ok(Products);
        }

        [HttpGet("{id}")]
        public async Task<ActionResult<Product>> GetProduct(int id)
        {
            var Product = await _repository.GetProductByIdAsync(id);
            if(Product == null)
            {
                return NotFound();
            }
            return Ok(Product);
        }

        [HttpPost]
        public async Task<ActionResult<Product>> CreateProduct(Product Product)
        {
            int newProductId = await _repository.CreateProductAsync(Product);
            Product.Id = newProductId;
            return CreatedAtAction(nameof(GetProduct), new { id = newProductId }, Product);
        }

        [HttpPut("{id}")]
        public async Task<IActionResult> UpdateProduct(int id, Product Product)
        {
            if(id != Product.Id)
            {
                return BadRequest();
            }

            bool updated = await _repository.UpdateProductAsync(Product);
            if(!updated)
            {
                return NotFound();
            }

            return NoContent();
        }

        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteProduct(int id)
        {
            bool deleted = await _repository.DeleteProductAsync(id);
            if(!deleted)
            {
                return NotFound();
            }
            return NoContent();
        }
    }
}

using APIDevelopmentUsingDapper.App;
using APIDevelopmentUsingDapper.DapperDbConnections;
using APIDevelopmentUsingDapper.Interfaces;
using APIDevelopmentUsingDapper.Repositories;
using Microsoft.EntityFrameworkCore;
using Microsoft.OpenApi.Models;

var builder = WebApplication.CreateBuilder(args);

// Access configuration from the builder
var configuration = builder.Configuration;

// Configure database connection using Entity Framework Core
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddScoped<IProductRepository,ProductRepository>();
builder.Services.AddScoped<IDapperDbConnection, DapperDbConnection>();

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen(c =>
{
    c.SwaggerDoc("v1", new OpenApiInfo { Title = "Your API", Version = "v1" });
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if(app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI(c =>
    {
        c.SwaggerEndpoint("/swagger/v1/swagger.json", "Your API V1");
        // Configure additional settings for SwaggerUI if needed
    });
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();

app.Run();