ASP .NET Core Web API and Database First Approach Best Practices
Introduction
In this article, we will discuss Onion Architecture with ASP .NET Core Web API and MySql Entity Framework Core (Database First Approach). Basically onion architecture provides us a better way to build application. Using this architecture our application becomes better testable, Flexible and Maintainable.
![](https://vibersol.com/wp-content/uploads/2023/11/image-15.png)
- Domain Layer : Domain Layer basically a entity layer in which all database table exists.
- Repository Layer : Repository is an abstraction of the data access layer. It hides the details of how exactly the data is saved or retrieved from the underlying data source.
- Service Layer : A Service Layer is an additional layer that work as a mediator between controller and repository layer. A Service Layer contains business logic.
- Presentation Layer : The Presentation Layer is the top most layer of the N-tier architecture used to display result to the user.
Table of Contents
Pre-requisites:
- Install MySql Server 8.0
- Install dbForge Studio.
- Install Visual Studio or VS Code
- Open Visual Studio and Configure your Project. Click here to know how to configure.
Now Create Database and Table in MySql Server
- Open MySql Server.
![](https://vibersol.com/wp-content/uploads/2023/11/image-16-1024x509.png)
Step 1: Create Database => Create Table
CREATE DATABASE SMS;
CREATE TABLE tbl_StudentInfo
(
s_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
s_name varchar(100),
s_city varchar(100),
s_qualification varchar(50),
s_email varchar(20),
s_phone varchar(10)
);
![](https://vibersol.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-032311-1024x603.png)
Step 2: I assume you have created a default Project API with WeatherForecastController. If not please see me.
![](https://vibersol.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-133259.png)
Step 3: Add Core.Entity class library under the SMS Solution Explorer.
Right Click on the solution => Add =>New Project => Class Library
![](https://vibersol.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-134151-1024x724.png)
![](https://vibersol.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-134523.png)
Step 4 : Add Entity Framework Tool and MySql Provider to Core.Entity class library.
Right click on Core.Entity=>Manage NuGetPackage
![](https://vibersol.com/wp-content/uploads/2023/11/image-17.png)
Go to Browse => In Search box type “Microsoft.EntityFrameworkCore.Tools” => Select Microsoft.EntityFrameworkCore.Tools => Install => Apply
![](https://vibersol.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-140452-1024x403.png)
![](https://vibersol.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-140527-1024x484.png)
Install MySql Server Provider “MySql.EntityFrameworkCore” same as “Microsoft.EntityFrameworkCore.Tools“
![](https://vibersol.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-141203-1024x213.png)
Step 5 : Build the solution.
Step 6 : Provide project library[Core.Entity] reference to Core.SMS
Step 7: Double click on Core.Entity then csproj will be open. Comment the blow line.
![](https://vibersol.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-145459-1024x410.png)
Step 8 : Now using below Scaffold-DbContext command to create a model from existing database. Here you will notice we are creating model in separate class library.
Go to Package Manager Console:
PM> Scaffold-DbContext “Server=localhost;Database=SMS;port=3306;user=root;password=12345;”MySql.EntityFrameworkCore -OutputDir Model -Project “Core.Entity” -Tables tbl_StudentInfo -f
Here you can see SmsDbContext has been created in Model folder under the Core.Entity Project
![](https://vibersol.com/wp-content/uploads/2023/11/Screenshot-2023-11-27-145758-1024x560.png)
Now remove the Connection String from here and place it into appsetting.json in Core.Entity
appsetting.json
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"SMSConnection": "Server=localhost;Database=SMS;port=3306;user=root;password=12345;"
}
}
Step 9 : Add new project class library – Core.Service under SMS Solution Explorer.
![](https://vibersol.com/wp-content/uploads/2023/11/image-18.png)
Step 10 : Add new class IRepositoryBaseService in Core.Service project class library.
Create generic repository base service interface IRepositoryBaseService where <T> indicates class name.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Core.Service
{
public interface IRepositoryBaseService<T>
{
IQueryable GetAllRecords();
IQueryable GetFilterRecords();
void CreateRecord(T entity);
void UpdateRecord(T entity);
void DeleteRecord(T entity); } }
Step 11 : Create IStudentInfoService interface and inherit to the IRepositoryBaseService
using Core.Entity.Model; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Core.Service { public interface IStudentInfoSevice : IRepositoryBaseService<TblStudentinfo> { IEnumerable GetStudentInfos(); IEnumerable BetStudentInfoByConditoin(TblStudentinfo tblStudentinfo); void CreateStudent(TblStudentinfo entity); void UpdateStudent(TblStudentinfo entity); void DeleteStudent(TblStudentinfo entity); } }
Step 12 : Create IRepositoryWrapperService interface to store all table repository service interface.
namespace Core.Service
{
public interface IRepostoryWrapperService
{
IStudentInfoSevice studentInfoSevice { get; }
}
}
Step 13 : Next we have to implement the interface inside the Repository Class Library. Create RepositroyBaseService class inside the SMS project solution.
![](https://vibersol.com/wp-content/uploads/2023/11/image-19.png)
Add project reference Core.Service to Repository.
![](https://vibersol.com/wp-content/uploads/2023/11/image-20.png)
Implement IRepositoryBaseService interface in RepositoryBaseService class that is Abstract class.
using Core.Entity.Model;
using Core.Service;
using Microsoft.EntityFrameworkCore;
using System.Linq.Expressions;
namespace Repository
{
public abstract class RepositoryBaseService<T> : IRepositoryBaseService<T> where T : class
{
private SmsContext _context;
public RepositoryBaseService(SmsContext context)
{
_context = context;
}
public IQueryable<T> GetAllRecords() {
return _context.Set<T>().AsNoTracking();
}
public IQueryable<T> GetFilterRecords(Expression<Func<T, bool>> expression)
{
return _context.Set<T>().Where(expression).AsNoTracking();
}
public void CreateRecord(T entity)
{
_context.Set<T>().Add(entity);
}
public void UpdateRecord(T entity)
{
_context.Set<T>().Update(entity);
}
public void DeleteRecord(T entity)
{
_context.Set<T>().Remove(entity);
}
}
}
Step 14 : Add new class StudentInfoService to implement RepositoryBaseService and IStudentInfoSevice interface inside the Repository.
using Core.Entity.Model;
using Core.Service;
using Microsoft.EntityFrameworkCore;
namespace Repository
{
public class StudentInfoService : RepositoryBaseService<TblStudentinfo>,IStudentInfoSevice
{
public StudentInfoService(SmsContext smsContext):base(smsContext)
{
}
public IEnumerable<TblStudentinfo> GetStudentInfos()
{
return GetAllRecords().OrderBy(m => m.SName).ToList();
}
public IEnumerable<TblStudentinfo> GetStudentInfoByCondition(Guid SId)
{
return GetFilterRecords(m => m.SId.Equals(SId)).ToList();
}
public void CreateStudent(TblStudentinfo entity)
{
CreateRecord(entity);
}
public void UpdateStudent(TblStudentinfo entity)
{
UpdateRecord(entity);
}
public void DeleteStudent(TblStudentinfo entity)
{
DeleteRecord(entity);
}
}
}
Step 15 : Now Create RepositoryWrapperService to implement IRepostoryWrapperService.
using Core.Entity.Model;
using Core.Service;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.NetworkInformation;
using System.Text;
using System.Threading.Tasks;
namespace Repository
{
public class RepositoryWrapperService : IRepostoryWrapperService
{
private SmsContext context;
private IStudentInfoSevice studentInfo;
public RepositoryWrapperService(SmsContext smsContext)
{
context = smsContext;
}
public IStudentInfoSevice studentInfoSevice
{
get
{
if(studentInfo == null)
{
studentInfo = new StudentInfoService(context);
}
return studentInfo;
}
}
}
}
Step 16 : Create Controller under the Core.SMS Project file.
Right click on Controller folder => Add=>Controller
![](https://vibersol.com/wp-content/uploads/2023/11/image-21-1024x683.png)
Under the Common section select API => select API controller-Empty => Click Add
Rename the ValueController to StudentController.
![](https://vibersol.com/wp-content/uploads/2023/11/image-26.png)
Add Core.Service project reference to Core.SMS Project.
Now get all student records :
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Core.Service;
using Core.Entity.Model;
namespace Core.SMS.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class StudentController : ControllerBase
{
private IRepostoryWrapperService _repostoryWrapperService;
private SmsContext SmsContext;
public StudentController(IRepostoryWrapperService repostoryWrapperService, SmsContext smsContext)
{
_repostoryWrapperService = repostoryWrapperService;
SmsContext = smsContext;
}
[HttpGet]
public IActionResult GetStudentInfos()
{
var students = _repostoryWrapperService.studentInfoSevice.GetAllRecords();
return Ok(students);
}
}
}
Step 17: Go to Program.cs class to register IRepostoryWrapperService and RepositoryWrapperService. No need to register all the Services because here WrapperService work as a parent service.
Configure your connection string in Program.cs
var connectionString = builder.Configuration[“ConnectionStrings:SMSConnection”];
builder.Services.AddDbContext<SmsContext>(option => option.UseMySQL(connection));
Lifecycle
- Transient :- Transient objects are always different, a new instance is provided to every controller and every service.
- Scope :- A scope is typically associated with a single HTTP request. The same instance is shared within the same scope, but different scopes will have different instances.
- Singleton :- A Singleton objects are the same for every object and every request.
using Core.Entity.Model;
using Core.Service;
using Microsoft.EntityFrameworkCore;
using Repository;
var builder = WebApplication.CreateBuilder(args);
var connectionString = builder.Configuration["ConnectionStrings:SMSConnection"];
builder.Services.AddDbContext<SmsContext>(option => option.UseMySQL(connectionString));
// Add services to the container.
builder.Services.AddScoped<IRepostoryWrapperService, RepositoryWrapperService>();
//builder.Services.AddScoped<IStudentInfoSevice, StudentInfoService>();
//Add Controller
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
//Set Cors Policy
builder.Services.AddCors(options =>
{
options.AddPolicy("CorsPolicy",
builder => builder.AllowAnyOrigin()
.AllowAnyMethod()
.AllowAnyHeader());
});
//Add Swagger Functionality
builder.Services.AddSwaggerGen();
var app = builder.Build();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
//Allow Cors
app.UseCors("CorsPolicy");
app.Run();
Execute your API and See the Result:
![](https://vibersol.com/wp-content/uploads/2023/11/image-27-1024x438.png)
![](https://vibersol.com/wp-content/uploads/2023/11/image-29-1024x594.png)
Now Add Student information to tbl_StudentInfo table:
[HttpPost]
public IActionResult AddStudent(TblStudentinfo tblStudentinfo)
{
try
{
_repostoryWrapperService.studentInfoSevice.CreateStudent(tblStudentinfo);
SmsContext.SaveChanges();
return Ok();
}
catch (Exception ex)
{
return BadRequest(ex.Message);
}
}
![](https://vibersol.com/wp-content/uploads/2023/11/image-30-1024x437.png)
![](https://vibersol.com/wp-content/uploads/2023/11/image-31-1024x486.png)
![](https://vibersol.com/wp-content/uploads/2023/11/image-32-1024x449.png)
![](https://vibersol.com/wp-content/uploads/2023/11/image-33.png)
Now Update Student information to tbl_StudentInfo table:
[HttpPost]
public IActionResult UpdateStudent(TblStudentinfo tblStudentinfo)
{
try
{
_repostoryWrapperService.studentInfoSevice.UpdateStudent(tblStudentinfo);
SmsContext.SaveChanges();
return Ok();
}
catch (Exception ex)
{
return BadRequest(ex.Message);
}
}
![](https://vibersol.com/wp-content/uploads/2023/11/image-34-1024x650.png)
![](https://vibersol.com/wp-content/uploads/2023/11/image-35.png)
Now Delete Record from tbl_StudentInfo :
[HttpPost]
public IActionResult DeleteStudent(TblStudentinfo tblStudentinfo)
{
try
{
_repostoryWrapperService.studentInfoSevice.DeleteRecord(tblStudentinfo);
SmsContext.SaveChanges();
return Ok();
}
catch (Exception ex)
{
return BadRequest(ex.Message);
}
}
![](https://vibersol.com/wp-content/uploads/2023/11/image-36-1024x435.png)
![](https://vibersol.com/wp-content/uploads/2023/11/image-37.png)
Related Topics