How to Create CRUD Web API in Asp.Net Core With Dapper ORM and Sqlite

- by Ahmed

In this Article we will learn How to Create Asp.Net Core CRUD Web API Using Dapper ORM and Sqlite.

Prerequisites

  • Download and install .Net Core 6.0 SDK from here
  • Download and Install Microsoft Visual Studio 2022 from here

Previous Article Also check Create .Net Core Web API – CRUD Operation

Create ASP.Net Core Web API

  • Open visual studio and click the Create New Project Option
  • Select the Template
Asp.Net Web API
  • Enter the name of the Project

Select Frame Work Click on Create

Install NuGet Packages

  • Dapper
  • Microsoft.Data.Sqlite
  • AutoMapper

Configure appsettings.json

Now Add the Database connection in the appsettigns.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
   "ConnectionStrings": {
        "connectionstr": "Data Source=CustomerSqlite.db"
  },
  "AllowedHosts": "*"
}

ASP.Net Core Web API Project Structure

  • Controller – It is a class that handles requests to specific endpoints and returns HTTP response in the form of JSON or XML.  Each action method in a controller class should handle a specific HTTP request verb (GET, POST, PUT, DELETE) and a specific route.
  • Entities – It represent the application data the stored in the database. Dapper ORM maps relational data from the database to instances of entity objects to be used within the application for data management and CRUD operations.
  • Models –  it represent request and response models for controller method , request model define parameters for incoming requests and response model define custom data returned in responses when required
  • Services – it contain validation , Business logic and database code
  • Helper – It contains DbContext class , AppException custom Exception and Middleware Error Handler

Create Entities

Customer.cs

using System.ComponentModel.DataAnnotations;

namespace WebApi_Dapper.Entities
{
    public class Customer
    {
        [Key]

        public int Id { get; set; }
        
        public string? FirstName { get; set; }
        
        public string? LastName { get; set; }
        public string? Address { get; set; }
        
        public string? City { get; set; }
       
        public string? PhoneNumber { get; set; }     
 
    }
}

Helpers

Helper Folder Contains DbContext , AppException, Middleware Error Handler

DbContext Class

DbContext Class. The DbContext is used for accessing application data through Dapper and is configured to connect to a SQLite database. 

using System.Data; 
using Dapper;
using Microsoft.Data.Sqlite;

namespace WebApi_Dapper.Helpers
{
    public class DbContextData  
    {
        private readonly IConfiguration _configuration;
        public DbContextData(IConfiguration configuration)
        {
            _configuration= configuration;
        }

        public IDbConnection Createconnection()
        {

             return new SqliteConnection(_configuration.GetConnectionString("connectionstr"));

        }

        public async Task Init()
        {
            var conn = Createconnection();

            await _initcustomer();
        
          async Task _initcustomer()
          { 
              var sqlstr = "CREATE TABLE IF NOT EXISTS Customer(Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,FirstName TEXT,LastName TEXT,Address TEXT,City TEXT,PhoneNumber TEXT);";

            await conn.ExecuteAsync(sqlstr);
         
          }
        }
    }
}

AppException

App Exceptions – The app exception is a custom exception class used in the .NET API to differentiate between handled and unhandled exception. Unhandled exceptions are generated by the .NET framework or caused by bugs in application code , where handled exception are generated by application code and used to return friendly error message or user define error, such as business logic or validation exception caused by invalid request parameters

using System.Globalization;

namespace WebApi_Dapper.Helpers
{
    public class AppException :Exception
    {
        public AppException() { }
        public AppException(string message):base(message) {  }

        public AppException(string message,params object[] args) 
        : base(String.Format(CultureInfo.CurrentCulture,message ,args)) 
        {
        }
    }
}

Middleware Error Handler

Error Handler Is used to catch all error and remove the need for duplicated error handling code throughout the .Net 6.0 API. It’s configured as middleware in the program.cs file.

using System.Net;
using System.Text.Json;

namespace WebApi_Dapper.Helpers
{
    public class MiddlewareErrorHandler
    {

        private readonly ILogger _logger;
        private readonly RequestDelegate _errorHandler;

        public MiddlewareErrorHandler(ILogger<MiddlewareErrorHandler> logger, RequestDelegate errorHandler)
        {
            _logger = logger;
            _errorHandler = errorHandler;
        }

        public async Task Invoke(HttpContext context)
        {
            try
            {
                await _errorHandler(context);
            }
            catch (Exception Err)
            {
                var resp = context.Response;
                resp.ContentType= "application/json";

                switch (Err)
                {

                    case AppException e:
                        resp.StatusCode = (int)HttpStatusCode.BadRequest; 
                        break;
                    case KeyNotFoundException e:
                        resp.StatusCode = (int)HttpStatusCode.NotFound;
                        break;
                    default:
                        _logger.LogError(Err, Err.Message);
                        resp.StatusCode = (int)(HttpStatusCode.InternalServerError);
                        break;
                }

                var outresult = JsonSerializer.Serialize(new { Message = Err.Message });
                await resp.WriteAsync(outresult);
            }

        }
    }
}

Models

AutoMapper

using System.Runtime;
using AutoMapper;
using WebApi_Dapper.Entities;

namespace WebApi_Dapper.Models
{
    public class AutoMapper :Profile 
    {
        public AutoMapper()
        {
            CreateMap<CreateCustRequest, Customer>();
            CreateMap<UpdateCustRequest, Customer>().ForAllMembers(m=>m.Condition(

                (source,destination,prop)=>
                {
                    if (prop == null) return false;

                     if (prop.GetType()== typeof(string) && string.IsNullOrEmpty((string)prop)) return false;   
                    return true;

                }
                ));          
        }
    }
}

Create Request

The Create Customer Request Model define the parameters for incoming POST requests to the route.

using System.ComponentModel.DataAnnotations;
 
namespace WebApi_Dapper.Models
{
    public class CreateCustRequest
    {
        public int Id { get; set; }
        [Required]
        public string FirstName { get; set; }
        [Required]
        public string LastName { get; set; }
        public string Address { get; set; }
        [Required]
        public string City { get; set; }
        [Required]
        public string PhoneNumber { get; set; }
    }
}

Update Request

The Update CustomerRequest Model define the parameters for incoming PUT requests to the route.

using System.ComponentModel.DataAnnotations;
 

namespace WebApi_Dapper.Models
{
    public class UpdateCustRequest
    {

        public int Id { get; set; }
       
        public string? FirstName { get; set; }
        
        public string? LastName { get; set; }
        public string? Address { get; set; }
        
        public string? City { get; set; }
       
        public string? PhoneNumber { get; set; }

    }
}

Repositories

Create Interface Repository

using WebApi_Dapper.Entities;

namespace WebApi_Dapper.Repositories
{
    public interface ICustomerRepository
    {
        Task<IEnumerable<Customer>> GetAll();     
        Task<Customer> FindByName(string firstname);
        Task<Customer> FindById(int id);
        Task CustCreate(Customer customer);
        Task CustUpdate(int id,Customer customer);
        Task CustDelete(int id);     
    }
}

Create Concrete Class 

using Dapper;
using WebApi_Dapper.Entities;
using WebApi_Dapper.Helpers;
 

namespace WebApi_Dapper.Repositories
{
    public class CustomerRepository : ICustomerRepository
    {
        private readonly DbContextData _context;
        public CustomerRepository(DbContextData context)
        {
            _context = context;
        }
        public async Task CustCreate(Customer customer)
        {
            using var connection = _context.Createconnection();
            var sqlstr = "INSERT INTO CUSTOMER(FirstName,LastName,Address,City,PhoneNumber)" +
                         "VALUES(@FirstName,@LastName,@Address,@City,@PhoneNumber);";
            await connection.ExecuteAsync(sqlstr,customer);
        
        }

        public async Task CustDelete(int id)
        {
            using var connection = _context.Createconnection();
            var sqlstr = "DELETE FROM CUSTOMER WHERE Id=@id";

            await connection.ExecuteAsync(sqlstr,new {id});
        }

        public async Task CustUpdate(int id,Customer customer)
        {
            using var connection = _context.Createconnection();
            var sqlstr = "UPDATE CUSTOMER SET FirstName=@FirstName,LastName=@LastName,Address=@Address," +
                         "City=@City,PhoneNumber=@PhoneNumber WHERE Id=" + id;
                         
            await connection.ExecuteAsync(sqlstr,customer);
        }

        public async Task<Customer> FindByName(string firstname)
        {
            using var connection = _context.Createconnection();
            var sqlstr = "SELECT * FROM CUSTOMER WHERE FirstName=@Id";

            return await connection.QuerySingleOrDefaultAsync<Customer>(sqlstr,new { firstname } );
        }

        public async Task<Customer> FindById(int id)
        {
            using var connection = _context.Createconnection();
            var sqlstr = "SELECT * FROM CUSTOMER WHERE Id=@Id";

            return await connection.QuerySingleOrDefaultAsync<Customer>(sqlstr, new { id });
        }

        public async Task<IEnumerable<Customer>> GetAll()
        {
            using var connection = _context.Createconnection();
            var sqlstr = "SELECT * FROM CUSTOMER";

            return await connection.QueryAsync<Customer>(sqlstr);

        } 
    }
}

Services

It contain validation , Business logic and database code

Create Interface

using WebApi_Dapper.Entities;
using WebApi_Dapper.Models;

namespace WebApi_Dapper.Services
{
    public interface Icustomer
    {
        void Customercreate(CreateCustRequest CustRequest);
        Task Customerupdate(int id, UpdateCustRequest CustRequest);
        Task Customerdelete(int id);
        Task<IEnumerable<Customer>> GetAll();
        Task<Customer> CustomerGetById(int id);

    }
}

Create Concrete Class

using AutoMapper; 
using WebApi_Dapper.Entities;
using WebApi_Dapper.Helpers;
using WebApi_Dapper.Models;
using WebApi_Dapper.Repositories;

namespace WebApi_Dapper.Services
{
    public class customerService : Icustomer
    {
        private  ICustomerRepository  _customerRepository;
        private readonly IMapper _mapper;

        public customerService(ICustomerRepository customerRepository, IMapper mapper)
        {
            _customerRepository = customerRepository; 
            _mapper = mapper;
        }

        public void Customercreate(CreateCustRequest CustRequest)
        {

            var CustExist = _customerRepository.FindByName(CustRequest.FirstName); 
            if (CustExist != null)
            {
                throw new  AppException("Customer Name : '" + CustRequest.FirstName + "' Already Exist");
            }

            var customer = _mapper.Map<Customer>(CustRequest);
            _customerRepository.CustCreate(customer); 
        }

        public async Task Customerdelete(int id)
        {
            await _customerRepository.CustDelete(id);             
        }

        public async Task<Customer> CustomerGetById(int id)
        {
            var customer =  _customerRepository.FindById(id);
            if (customer == null) 
            {
                throw new AppException("Customer Not Found"); 
            }
            return await customer;
        }

      
        public async Task Customerupdate(int id,UpdateCustRequest CustRequest)
        {
            var customer =await  _customerRepository.FindById(id); 

            if (customer == null)
            {
                throw new KeyNotFoundException("Customer Not Found");
                
            }
             _mapper.Map(CustRequest,customer);

            await _customerRepository.CustUpdate(id,customer);

        }

        public async Task<IEnumerable<Customer>> GetAll()
        {
            return await _customerRepository.GetAll();
        }        
 
    }
}

Program.cs

using WebApi_Dapper.Helpers;
using WebApi_Dapper.Services;
using WebApi_Dapper.Repositories;
using System.Text.Json.Serialization;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers().AddJsonOptions(j=>{

    j.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());
    j.JsonSerializerOptions.DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull;

});

builder.Services.AddSingleton<DbContextData>();
builder.Services.AddScoped<Icustomer, customerService>();
builder.Services.AddScoped<ICustomerRepository, CustomerRepository>();


builder.Services.AddAutoMapper(AppDomain.CurrentDomain.GetAssemblies());


var app = builder.Build();

{
    using var appscope= app.Services.CreateScope();
    var context = appscope.ServiceProvider.GetRequiredService<DbContextData>();
    await context.Init();

}

// Configure the HTTP request pipeline.

app.UseHttpsRedirection();

app.UseAuthorization();

app.UseMiddleware<MiddlewareErrorHandler>();
app.MapControllers();

app.Run();

Controller

Controller is a class that handles requests to specific endpoints and returns HTTP response in the form of JSON or XML.  

using AutoMapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using WebApi_Dapper.Models;
using WebApi_Dapper.Services;

// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace WebApi_Dapper.Controllers
{
    
    [Route("api/[controller]")]
    [ApiController]
    public class CustomerController : ControllerBase
    {
        private readonly Icustomer _customer;
        private readonly IMapper _mapper;

        public CustomerController(Icustomer customer, IMapper mapper)
        {
            _customer= customer;
            _mapper = mapper;
        }
        // GET: api/<CustomerController>
        [HttpGet]
        public async Task<IActionResult> Get()
        {
            var customer = await _customer.GetAll();
            return Ok(customer);
        }

        // GET api/<CustomerController>/5
        [AllowAnonymous]
        [HttpGet("{id}")]
        public async Task<IActionResult> Get(int id)
        {
            var customer =await  _customer.CustomerGetById(id);
            return Ok(customer);
        }

        // POST api/<CustomerController>
        [HttpPost]
        public IActionResult Post(CreateCustRequest createcustmodel)
        {
            _customer.Customercreate(createcustmodel);

            return Ok(new { Message = "Customer Created" });
        }

        // PUT api/<CustomerController>/5
        [HttpPut("{id}")]
        public async Task<IActionResult> Put(int id, UpdateCustRequest updatecustomer)
        {

            await _customer.Customerupdate(id, updatecustomer);
            return Ok(new { Message = "Customer Updated" });
        }

        // DELETE api/<CustomerController>/5
        [HttpDelete("{id}")]
        public IActionResult Delete(int id)
        {
            _customer.Customerdelete(id);
            return Ok(new { Message = "Customer Deleted" });
        }
    }
}

Test the ASP.Net Core Web API Using Postman

  • Create a New Customer
  • Retrieve a list of all Customer
  • Update the Customer
  • Delete the Customer
  • Retrieve a Customer by id

Create a New Customer

Retrieve a list of all Customer

Update the Customer

Delete the Customer

Retrieve a Customer by id

Related Posts

.NET 8 Release New Data Annotation in ASP.NET Core C#

LINQ: Sort Multiple Columns using LINQ in C#

Group by Multiple Columns in LINQ C#

About Ahmed

View all posts by Ahmed →

One Comment on “How to Create CRUD Web API in Asp.Net Core With Dapper ORM and Sqlite”

  1. Nice example, but there is a bug in Create method need change if (CustExist != null) for if (CustExist.Result != null)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *