Create Simple CRUD with .NET Core and SQL Server
Hello I'm Feri ??
on this article I want to share my experience creating simple CRUD with .NET Core and SQL Server.
here the Tools that I use:
First open Visual Studio Code to make new project
Choose the project template, here I choose ASP .NET Core Web API
then we can rename our project and set the directory of the project
after several minutes project will be generated and we can see the project structure
we can run the project via terminal or just press the Run
it will run the project and show the default documentations
Okay now we can make our database to store our data
here is the Diagram for our database
Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance. You can create a new database by running SQL commands or by using SSMS's GUI.
For example, you can create a database named "RestaurantDemo" on the querry:
CREATE DATABASE RestaurantDemo;
Open VS again and install dependencies
search the
if your project already install the dependencies, it will become like this
make new "Models" Folder, then add Food, Customer, and Transaction Model Class
领英推荐
Transaction.cs
namespace APICrudRestaurant.Models
{
public class Transaction
{
public int TransactionId { get; set; }
public int CustomerId { get; set; }
public int FoodId { get; set; }
public DateTime TransactionDate { get; set; }
public int Quantity { get; set; }
public Customer Customer { get; set; }
public Food Food { get; set; }
}
}
Customer.cs
namespace APICrudRestaurant.Models
{
public class Customer
{
public int CustomerId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Phone { get; set; }
public ICollection<Transaction> Transactions { get; set; }
}
}
Food.cs
namespace APICrudRestaurant.Models
{
public class Food
{
public int FoodId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public decimal Price { get; set; }
public ICollection<Transaction> Transactions { get; set; }
}
}
then create new file DbContext on root folder
DbContext .cs
using Microsoft.EntityFrameworkCore;
using APICrudRestaurant.Models;
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }
public DbSet<Food> Foods { get; set; }
public DbSet<Customer> Customers { get; set; }
public DbSet<Transaction> Transactions { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Food>()
.Property(f => f.Price)
.HasColumnType("decimal(18, 2)");
modelBuilder.Entity<Transaction>()
.HasOne(t => t.Customer)
.WithMany(c => c.Transactions)
.HasForeignKey(t => t.CustomerId);
modelBuilder.Entity<Transaction>()
.HasOne(t => t.Food)
.WithMany(f => f.Transactions)
.HasForeignKey(t => t.FoodId);
base.OnModelCreating(modelBuilder);
}
}
dont forget to add this script to appsettings.json to connect to database
"ConnectionStrings": {
"DefaultConnection": "Server=[Server Name];Database=[Database Name];User Id=[User Id];Password=[password];"
},
change [Server Name], [Database Name], [User Id] and [password] with your own
then on Startup.cs
add
using Microsoft.EntityFrameworkCore;
and configuration
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers();
services.AddDbContext<ApplicationDbContext>(options =>
{
options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"));
});
...
}
then after that open terminal to migrate our database
dotnet ef migrations add InitialCreate
dotnet ef database update
you can check if it succes on your SMSS
create new folder DTO for response data then make new class
FoodDTO.cs
public class FoodDTO
{
public int FoodId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public decimal Price { get; set; }
}
then make the food controller class on Controller Folder
FoodControllers.cs
[Route("api/food")]
[ApiController]
public class FoodController : ControllerBase
{
private readonly ApplicationDbContext _context;
public FoodController(ApplicationDbContext context)
{
_context = context;
}
// GET: api/food
[HttpGet]
public IActionResult GetFoods()
{
var foods = _context.Foods
.Select(f => new FoodDTO
{
FoodId = f.FoodId,
Name = f.Name,
Description = f.Description,
Price = f.Price
})
.ToList();
return Ok(foods);
}
// GET: api/food/{id}
[HttpGet("{id}")]
public IActionResult GetFood(int id)
{
var food = _context.Foods.Find(id);
if (food == null)
{
return NotFound();
}
var foodDTO = new FoodDTO
{
FoodId = food.FoodId,
Name = food.Name,
Description = food.Description,
Price = food.Price
};
return Ok(foodDTO);
}
// POST: api/food
[HttpPost]
public IActionResult CreateFood([FromBody] Food food)
{
if (food == null)
{
return BadRequest();
}
_context.Foods.Add(food);
_context.SaveChanges();
return CreatedAtAction("GetFood", new { id = food.FoodId }, food);
}
// PUT: api/food/{id}
[HttpPut("{id}")]
public IActionResult UpdateFood(int id, [FromBody] Food food)
{
var existingFood = _context.Foods.FirstOrDefault(f => f.FoodId == id);
if (existingFood == null)
{
return NotFound(); // Return a 404 Not Found response if the resource doesn't exist
}
try
{
// Perform the update on 'existingFood' properties
existingFood.Name = food.Name;
existingFood.Description = food.Description;
existingFood.Price = food.Price;
_context.SaveChanges();
return Ok($"Edit food with ID: {id} successfully.");
}
catch (Exception ex)
{
// Log the exception
return StatusCode(500, "Internal server error: " + ex.Message);
}
}
// DELETE: api/food/{id}
[HttpDelete("{id}")]
public IActionResult DeleteFood(int id)
{
var food = _context.Foods.Find(id);
if (food == null)
{
return NotFound();
}
_context.Foods.Remove(food);
_context.SaveChanges();
return Ok($"Deleted food with ID: {id} successfully.");
}
try run the program and test in on postman
after successful we can do the rest like Transaction and Customer,
the complete code can you found on my Github page ????
Thanks for your attention. I hope this toturial is useful for you ??