Part 1:Configure the database
The database managment system we will use for this particular project is Sql Server and Micrsoft Sql Server Managment Studio for using sql server in GUI. so if you haven't installed it yet. go to the following Microsoft Documentation and install.
Step 1: Install SqlServer,
Step 2: Install Microsoft Sql Server Managment Studio
Part 2: configuring the .Net web API project
Step 1: Create a new .Net web API project
we can simply create new project by using this command.
dotnet new webapi -n <name>
then we will get the template for a web API. remove the default controller from the controller package.
Step 2: Install the necessary packages
to install package we have especial extension called NuGet Package Manager from the visual studio code go to the extension tab and search for NuGet Package Manager
then install it.
then after that hit on ctrl + shift + p and you will see a drop-down option from the above.
Click on the above choice which says that Nuget Package Manager: Add Package it will prompt you to enter the package name so enter the package name in this case the package we want to install is Microsoft.EntityFrameworkCore which used to connect to SQL server.
then choose the version number that can work fine with the .Net version of your computer. after some seconds it will automatically install it and put it on the .csproj file.
do the same for the rest of the packages too.
<PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="8.1.0"/>
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.12"/>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.12"/>
the whole code of our .csproj file will look like the following
<Project Sdk="Microsoft.NET.Sdk.Web" ToolsVersion="Current">
<PropertyGroup>
<TargetFramework>netcoreapp3.1</TargetFramework>
<OldToolsVersion>2.0</OldToolsVersion>
<GenerateAssemblyInfo>false</GenerateAssemblyInfo>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="8.1.0"/>
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.12"/>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.12"/>
</ItemGroup>
</Project>
Step 3: Add a model called Service.cs
First, create a folder called Model and in that folder create Service.cs model.
This model should be the same as the database column we have created before. so our Service.cs code will look like this.
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System;
namespace fixit.DTO
{
public class ServiceDto
{
[Required]
public int ServiceId { get; set; }
[Required]
public string ServiceName { get; set; }
[Required]
public string Description { get; set; }
[Required]
public string Category { get; set; }
[Required]
public int InitialPrice { get; set; }
[Required]
public int IntermediatePrice { get; set; }
[Required]
public int AdvancedPrice { get; set; }
[Required]
public DateTime AddedTime { get; set; }
}
}
Step 4: add a DTO called ServiceDto.cs.
First, create a folder called DTO and in this folder create a file called ServiceDto.cs and add the following code.
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System;
namespace fixit.DTO
{
public class ServiceDto
{
[Required]
public int ServiceId { get; set; }
[Required]
public string ServiceName { get; set; }
[Required]
public string Description { get; set; }
[Required]
public string Category { get; set; }
[Required]
public int InitialPrice { get; set; }
[Required]
public int IntermediatePrice { get; set; }
[Required]
public int AdvancedPrice { get; set; }
[Required]
public DateTime AddedTime { get; set; }
}
}
The purpose of adding this file to the project is in order to map the data that come from the database to some format we want. which will be stored on the Dto file called ServiceDto.cs
Step 5: Add a Profile called ServiceProfile.cs.
create a folder called Profile and in this folder create a file called ServiceProfile.cs and in this file add the following code.
using AutoMapper;
using fixit.DTO;
using fixit.Models;
namespace fixit.Profiles
{
public class ServiceProfile : Profile
{
public ServiceProfile()
{
CreateMap<fixit.Models.Service, ServiceDto>()
.ForMember(dest => dest.ServiceId, opt => opt.MapFrom(src => src.ServiceId))
.ForMember(dest => dest.ServiceName, opt => opt.MapFrom(src => src.ServiceName))
.ForMember(dest => dest.Description, opt => opt.MapFrom(src => src.Description))
.ForMember(dest => dest.Category, opt => opt.MapFrom(src => src.Category))
.ForMember(dest => dest.InitialPrice, opt => opt.MapFrom(src => src.InitialPrice))
.ForMember(dest => dest.IntermediatePrice, opt => opt.MapFrom(src => src.IntermediatePrice))
.ForMember(dest => dest.AdvancedPrice, opt => opt.MapFrom(src => src.AdvancedPrice))
.ForMember(dest => dest.AddedTime, opt => opt.MapFrom(src => src.AddedTime));
CreateMap<ServiceDto, fixit.Models.Service>();
}
}
}
What this code will do is mapping from the model to Dto using a special package called AutoMapper.
Step 6: Create an interface called IRepository.cs
create a folder called **Data and in that folder create a file called IRepository.cs and add the following code.
using System.Collections.Generic;
using System.Threading.Tasks;
using fixit.DTO;
namespace fixit.Data
{
public interface IRepository<T>
{
Task<List<T>> GetData();
Task<T> GetDataById(int id);
Task<T> InsertData(T service);
Task<T> UpdateData(T service);
Task<bool> DeleteData(T service);
}
}
This interface act as a mediator between the controller and repository.
Step 7: Add a DataContext for the Service model
from the Data folder, we have created before, create a new file called DataContext.cs and add the following code to it.
using fixit.Models;
using Microsoft.EntityFrameworkCore;
using MySql.Data.EntityFrameworkCore;
namespace fixit.Data
{
public class DataContext : DbContext
{
public DataContext() { }
public DataContext(DbContextOptions<DataContext> options) : base(options) { }
public DbSet<Service> Service { get; set; }
}
}
Step 8: Add ServiceRepsitory.cs
From the Data folder again create a file called ServiceRepositiry.cs this file will contain all the CRUD operations that will be performed on the Service object. the code of all the CRUD operations is like this just copy and paste it.
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using fixit.Models;
namespace fixit.Data
{
public class ServiceRepository: IRepository<Service>
{
private readonly DataContext _context;
public ServiceRepository(DataContext context)
{
_context = context;
}
// Delete Service objects
public async Task<bool> DeleteData(Service service)
{
Console.WriteLine("Delete method invoked");
_context.Service.Remove(service);
await _context.SaveChangesAsync();
return true;
}
// Get all service objects
public async Task<List<Service>> GetData()
{
// Getting database data here
var model = await _context.Service.ToListAsync();
return model;
}
// Get Service by id
public async Task<Service> GetDataById(int id)
{
return await _context.Service.FirstOrDefaultAsync(x => x.ServiceId == id);
}
// Update and create new service objects
public async Task<Service> InsertData(Service service)
{
Console.WriteLine("Create data method invoked");
_context.Service.Add(service);
await _context.SaveChangesAsync();
return service;
}
public async Task<Service> UpdateData(Service service)
{
Console.WriteLine("Update method invoked");
_context.Update(service).Property(x => x.ServiceId).IsModified = false;
_context.SaveChanges();
return service;
}
}
}
Step 9: Add ServiceController.cs
By default when the project created it will contain a folder called Controller. if it does not exist create it. then in that folder create a file called ServiceController.cs and add the following code to it.
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using AutoMapper;
using fixit.Data;
using fixit.DTO;
using Microsoft.AspNetCore.Mvc;
using fixit.Models;
using Microsoft.AspNetCore.Authorization;
namespace Controllers
{
[ApiController]
[Route("api/services")]
public class ServiceController : ControllerBase
{
private readonly IRepository<Service> _repo;
private readonly IMapper _mapper;
public ServiceController(IRepository<Service> repo, IMapper mapper)
{
_repo = repo;
_mapper = mapper;
}
[HttpGet]
public async Task<IActionResult> GetServices()
{
Console.WriteLine("This is the get All service method");
var model = await _repo.GetData();
return Ok(_mapper.Map<IEnumerable<ServiceDto>>(model));
}
[HttpGet("{id}")]
public async Task<IActionResult> GetServiceById(int id)
{
Console.WriteLine("This is the comming id ");
Console.WriteLine(id);
var model = await _repo.GetDataById(id);
return Ok(_mapper.Map<ServiceDto>(model));
}
// Service Post method
[HttpPost]
public async Task<IActionResult> CreateService(ServiceDto serviceDto)
{
Console.WriteLine("Crate Service Method Invoked");
var service = _mapper.Map<Service>(serviceDto);
await _repo.UpdateData(service);
return Ok(serviceDto);
}
// Service Delete method
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteServices(int id)
{
var service = await _repo.GetDataById(id);
// var service = _mapper.Map<Service>(serviceDto);
await _repo.DeleteData(service);
return Ok(_mapper.Map<ServiceDto>(service));
}
}
}
The purpose of this controller is to receive requests from the client , get information from the repository through the mediator interface,returning the data back to the client.
Part 3: Connect our code to the database
The database information will be store on the connection string, later on, to connect it to the data context on the startup method.
In this case, we need to give information about the database that is running.
Step 1: Start Sql Server and copy the connection string
Open or launch Microsoft Sql Server Managment Studio.
A dialog box will come witht the window together.
Select Database Engine on the server type.
It will give you the server name by default.
and make the Authentication to be Window Authentication like it is on the displayed image.
Then click on the connect
if the connection is successful you will get an explorer page like this.
so the connection string will be the one that we put on the server name when we connect to the database. you may find different ways to copy this server name after connecting to the database. but what i prefer is to disconnnect the database and try to connect again then I will copy the server name from the dialog box.
To disconnect click on the icon on the left top corner of the explorer.
Then clcik on the icon next to the disconnect icon in order to connect again.
The dialog box will appear again. and this time copy the server name option.
So now we have got our connection string value.
Step 2: Put the connection string on the appSettings.js
put this to the ConnectionStrings object by assigning to a variable that used to access this information from some other file. for my case it is fixItConnection
"ConnectionStrings": {
"fixItConnection": "Server=DESKTOP-BEKSKMU\\SQLEXPRESS;Database=FixItDb;Trusted_Connection=True;"
}
the whole appSettings.json code will be.
{
"AppSettings": {
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"fixItConnection": "Server=DESKTOP-BEKSKMU\\SQLEXPRESS;Database=FixItDb;Trusted_Connection=True;"
}
}
Step 3: Updating the startUp.cs
update the startUp.cs file of the project in order to connect to Sql Server.
from the ConfigureService method add the following line of code.
services.AddDbContext<DataContext>(opt => opt.UseSqlServer(Configuration.GetConnectionString("fixItConnection")));
here the UseSqlServer method will be imported from the package we have installed before.Microsoft.EntityFrameworkCore; and used to inter connect the database information stored on the fixItConnection to our DataContext.
down to this, we should insert a code for CORS this gives permission to our back-end server in order to be accessible by any front-end running on the same machine. unless otherwise, we will get a cors error whenever we try to access this server from the same machine.
services.AddCors(option =>
{
option.AddPolicy("allowedOrigin",
builder => builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader()
);
});
Then we need to register our Controller and Repository we have created before.
services.AddControllers();
services.AddScoped<IRepository<Service>, ServiceRepository>();
the whole startUp.cs file looks like the following
using System;
using AutoMapper;
using fixit.Data;
using fixit.Models;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using System.Text;
// using Pomelo.EntityFrameworkCore.MySql;
// using fixit.Service;
namespace fixit
{
public class Startup
{
readonly string AllowedOrigin = "allowedOrigin";
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<DataContext>(opt => opt.UseSqlServer(Configuration.GetConnectionString("fixItConnection")));
services.AddAutoMapper(AppDomain.CurrentDomain.GetAssemblies());
services.AddCors(option =>
{
option.AddPolicy("allowedOrigin",
builder => builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader()
);
});
services.AddControllers();
services.AddScoped<IRepository<Service>, ServiceRepository>();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseCors(AllowedOrigin);
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthentication();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
}
Part 4: Migrating the model to sql server database.
In this part of the article we will see on how can we set up our database objects from the code.
the traditionall way of doing this is to create the objects directly form the database. but that will be tiresome and not efficient.
Step 1:Create your first migration
You're now ready to add your first migration! Instruct EF Core to create a migration named InitialCreate:
excute the folllwong from command line with in the project directory.
dotnet ef migrations add InitialCreate
EF Core will create a directory called Migrations in your project, and generate some files. It's a good idea to inspect what exactly EF Core generated - and possibly amend it - but we'll skip over that for now.
Step2:Create your database and schema
At this point you can have EF create your database and create your schema from the migration. This can be done via the following:
dotnet ef database update
That's all there is to it - your application is ready to run on your new database, and you didn't need to write a single line of SQL. Note that this way of applying migrations is ideal for local development, but is less suitable for production environments.
check out this link to understand more about migration in .Net core Entity Frame work.
Finally: Running the Code and Check the result
Almost Done. now we have done with the implementation staffs.
the next thing will be running the code and check its functionality. in order to run any dotnet project what we need to do is to execute the following command from the terminal.
dotnet run
you will get a page that looks like this.
copy the IP address from the page running and test it using postman.
http://localhost:5000/api/services/ this is the URL for the controller Service let us try the Get method from the postman.
we got the data that has been stored in the Sql server.
You can get the full Github code here
This will be the end of this article.
Next we will look in to how to integrate this web api with the flutter application. check out the part2 of this article here
Thank you!
Top comments (1)
Listing of file StartUp.cs contains an error. According to documentation [0] app.UseCors(AllowedOrigin); should follow this order:
app.UseRouting();
app.UseCors(AllowedOrigin);
app.UseAuthentication();
[0]docs.microsoft.com/en-us/aspnet/co...