CRUD operation in ASP.NET CORE Using Entity Framework Core
In this part, we are going to learn basic CRUD operation using entity framework core in details and also how to configure it.
- Creating ASP.NET Core Application
- Setting up Database and Table
- Adding Entity Framework Core package from NuGet
- Configuring Connection string in appsettings.json
- Adding Product Model
- Adding Class and inheriting with DbContext class
- Registering Dependency injection AddDbContext.
- Performing CRUD operations
- Conclusion
Let’s get ready to learn how to perform CRUD operation with Entity framework in simple steps.
Creating ASP.NET Core Application
We are going to create ASP.NET Core Web Application for that we are going to choose ‘ASP.NET Core Web Application‘ template.
Next, we are going to set Project Name ‘WebApplication5‘ and location and in last part, we are going to choose .Net Core framework and ASP.NET Core Version 2.2 as the framework for application and few advance settings for such as configuring https and enabling docker we are not going to enable docker settings for this project.
Now finally click on create button to create a project.
Project structure
The project structure generated according to the configuration.
After creating a project, we are going to run this project.
Setting up Database and Table
We have created a database with Name Sample2 and then we have added product table in it.
Below is Table structure along with a script to create a table.
Script to Create Product Table
CREATE TABLE [dbo].[Product](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Quantity] [int] NULL,
[Color] [varchar](50) NULL,
[Price] [decimal](18, 2) NULL,
[ProductCode] [varchar](50) NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]After creating a database and table next we are going to Entity framework reference to project from NuGet package.
Adding Entity Framework Core package from NuGet
In this part, we are going to learn how to add Entity Framework Core package to project from NuGet package manager.
For adding a package from NuGet right-click on Main project ‘WebApplication5‘ and from the menu select Manage NuGet Packages. As you select it a New dialog of NuGet Package Manager with search box will pop up.
In choose browse tab and search ‘Microsoft.EntityFrameworkCore.SqlServer‘ and choose version 3.0.0 the latest version of ‘Microsoft.EntityFrameworkCore.SqlServer‘ is in the preview that why we have chosen them, finally click on the install button for adding the package.
After adding Microsoft.EntityFrameworkCore.SqlServer to project next we are going to Configuring Connection string in appsettings.json.
Configuring Connection string in appsettings.json
Here in ASP.NET core, we don’t have web.config file which was there in the older version of ASP.NET and ASP.NET MVC here we have appsettings.json file where we are going to keep application-level settings and this file is Json based.
appsettings.json file
In this file, we have added ConnectionStrings section inside that we have added ‘DatabaseConnection‘ key and other is value.
Notice: – here I am using SQL based authentication for database connection that why I have added User Id and password of SQL server.
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DatabaseConnection": "Data Source=SAI-PC\\SQLEXPRESS; initial catalog=Sample2; user id=sa; password=Pass$123"
}
}Next, after adding connection string we are going to add Model Product in Models folder.
Adding Product Model
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;
namespace WebApplication5.Models
{
[Table("Product")]
public class Product
{
[Key]
public int ProductId { get; set; }
public int Name { get; set; }
public int Quantity { get; set; }
public int Color { get; set; }
public int Price { get; set; }
public int ProductCode { get; set; }
}
}Next, after adding Product Model we are going to add DatabaseContext Class and inheriting with DbContext class.
Adding DatabaseContext Class and inheriting with DbContext class
We are going to add DatabaseContext class which will inherit DbContext class and DatabaseContext constructor accepts the DbContextOptions as an argument. The DbContextOptions carries the configuration information needed to configure the DbContext.
After adding DatabaseContext class we have defined DbSet entity for product Model.
using Microsoft.EntityFrameworkCore;
namespace WebApplication5.Models
{
public class DatabaseContext : DbContext
{
public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
{
}
public DbSet<Product> Product { get; set; }
}
}After Configuring DatabaseContext class next we are going Register DatabaseContext as a service.
Registering Dependency injection AddDbContext
In this part, we are going to register DatabaseContext class as a service for using it as dependency injection which will be available to the entire application.
We are going to register this service in ConfigureServices Method of startup class.
First, we need to read connection string from appsettings.json file to pass it to register service.
//Getting Connection String from appsettings.json
var connection = Configuration.GetConnectionString("DatabaseConnection");Next, we are going to use AddDbContext extension method to register the DatabaseContext in the dependency injection container.
services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(connection));Use a ROW_NUMBER() in queries instead of OFFSET/FETCH. This method is backwards-compatible to SQL Server 2005 then register below method.
services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(connection, b => b.UseRowNumberForPaging()));ConfigureServices Method in Startup Class
public void ConfigureServices(IServiceCollection services)
{
//Getting Connection String from appsettings.json
var connection = Configuration.GetConnectionString("DatabaseConnection");
services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(connection));
// OR
//services.AddDbContext<DatabaseContext>(options => options.UseSqlServer(connection, b => b.UseRowNumberForPaging()));
services.AddControllersWithViews();
}Now we can use DatabaseContext class as service for dependency injection in the entire application.
Performing CRUD operations
CREATE
In this part, we are going to add Controller with name ‘ManageController‘ Controller.
using Microsoft.AspNetCore.Mvc;
namespace WebApplication5.Controllers
{
public class ManageProductController : Controller
{
}
}Next, we are going add 2 Action method with the name ‘Create‘ one for handling GET request and another for a handling POST request. And also, for accessing DatabaseContext in ManageProductController we are going to use Constructor injection.
using Microsoft.AspNetCore.Mvc;
using WebApplication5.Models;
namespace WebApplication5.Controllers
{
public class ManageProductController : Controller
{
private readonly DatabaseContext _context;
public ManageProductController(DatabaseContext context)
{
_context = context;
}
// GET: ManageProduct/Create
public IActionResult Create()
{
return View();
}
// POST: ManageProduct/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create([Bind("Name,Quantity,Color,Price,ProductCode")] Product product)
{
if (ModelState.IsValid)
{
_context.Add(product);
_context.SaveChanges();
return RedirectToAction("Index");
}
return View(product);
}
}
}Note: – Bind attribute – To protect from overposting attacks, we have use bind attribute.
In above source code the CREATE method which handles POST request in that method we are validating Model first if ModelState is valid then we pass Product Model to Add method of DbContext which set entity set to Added When we call context.SaveChanges then an Insert statement is generated and executed by the database.
If ModelState is not valid then we return View along with model which was posted which carry Display error message to View.
ValidationSummary: – ValidationSummary reads all errors from the model state and displays them in a bulleted list.
ValidationMessageFor: – ValidationMessageFor displays only errors for to the property specified.
Referenced from:- https://exceptionnotfound.net/asp-net-mvc-demystified-modelstate/
After adding the Controller and action method next we are going to Add View for ‘Create‘ Action Method.
@model WebApplication5.Models.Product
@{
ViewData["Title"] = "Create";
}
<h4>Create</h4>
<hr />
<div class="row">
<div class="col-md-4">
<form asp-action="Create">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<div class="form-group">
<label asp-for="Name" class="control-label"></label>
<input asp-for="Name" class="form-control" />
<span asp-validation-for="Name" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Quantity" class="control-label"></label>
<input asp-for="Quantity" class="form-control" />
<span asp-validation-for="Quantity" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Color" class="control-label"></label>
<input asp-for="Color" class="form-control" />
<span asp-validation-for="Color" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Price" class="control-label"></label>
<input asp-for="Price" class="form-control" />
<span asp-validation-for="Price" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="ProductCode" class="control-label"></label>
<input asp-for="ProductCode" class="form-control" />
<span asp-validation-for="ProductCode" class="text-danger"></span>
</div>
<div class="form-group">
<input type="submit" value="Create" class="btn btn-primary" />
</div>
</form>
</div>
</div>
<div>
<a asp-action="Index">Back to List</a>
</div>
@section Scripts {
@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}
Next, after inserting data we are going to show data which we have inserted for that we are going to add the Index and Details action Method.
READ
In this part, we are going to show data which we have created for that we are going to add 2 action method one is Index which will show all rows which we have inserted and another one is Details which will show details of individual record.
In the Index action method, we are going to get a list of products and display it on view.
The Details action method takes product id as an input parameter and will show details of that product.
If you see code snippet of Details action method you will see we are getting data from product table by passing id which is product id and then we render it on Details View.
// GET: ManageProduct
public IActionResult Index()
{
return View( _context.Product.ToList());
}
// GET: ManageProduct/Details/5
public IActionResult Details(int? id)
{
if (id == null)
{
return RedirectToAction("Index");
}
var product = _context.Product
.FirstOrDefault(m => m.ProductId == id);
if (product == null)
{
return RedirectToAction("Index");
}
return View(product);
}Note: – This @model directive allows you to access the object that we pass from the controller to the view.
Index View
The Index view expects strongly type of object products list (IEnumerable<WebApplication5.Models.Product>) which we are passing from the Index Action method to view. Because we have a collection of products, we need to iterate to display each product.
@model IEnumerable<WebApplication5.Models.Product>
@{
ViewData["Title"] = "Index";
}
<h1>Index</h1>
<p>
<a asp-action="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Quantity)
</th>
<th>
@Html.DisplayNameFor(model => model.Color)
</th>
<th>
@Html.DisplayNameFor(model => model.Price)
</th>
<th>
@Html.DisplayNameFor(model => model.ProductCode)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Quantity)
</td>
<td>
@Html.DisplayFor(modelItem => item.Color)
</td>
<td>
@Html.DisplayFor(modelItem => item.Price)
</td>
<td>
@Html.DisplayFor(modelItem => item.ProductCode)
</td>
<td>
<a asp-action="Edit" asp-route-id="@item.ProductId">Edit</a> |
<a asp-action="Details" asp-route-id="@item.ProductId">Details</a> |
<a asp-action="Delete" asp-route-id="@item.ProductId">Delete</a>
</td>
</tr>
}
</tbody>
</table>
Details View
The Details view expects strongly type of object product (WebApplication5.Models.Product) which we are passing from Details Action method to view to display product details.
@model WebApplication5.Models.Product
@{
ViewData["Title"] = "Details";
}
<h1>Details</h1>
<div>
<h4>Product</h4>
<hr />
<dl class="row">
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Name)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Name)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Quantity)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Quantity)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Color)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Color)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Price)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Price)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.ProductCode)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.ProductCode)
</dd>
</dl>
</div>
<div>
<a asp-action="Edit" asp-route-id="@Model.ProductId">Edit</a> |
<a asp-action="Index">Back to List</a>
</div>
After we saw how to display data on index and details view next, we are going to learn how to perform Edit and Update.
Update
In this part, we are going to perform an update on product data which we have created. To perform edit we are first going to add 2 Action Method with Name ‘Edit‘ one for handling GET Request and Another for handling Post Request to edit individual Product details we are going to View Index View there we will find edit link in front of each product after click on link it will redirect to Edit Action method which takes id (product id) as input parameter according to that id we are going to get product detail from database and display it on edit view after editing details will submit details which will be handled by Edit Action Method with handles POST Request.
// GET: ManageProduct/Edit/5
public IActionResult Edit(int? id)
{
if (id == null)
{
return RedirectToAction("Index");
}
var product = _context.Product.Find(id);
return View(product);
}In Edit Get Action method which handles GET Request where we are getting id (product id) parameter as input if it is not null, then we are going get Product details from database using product id and send product model to view for rendering.
// POST: ManageProduct/Edit/5
[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Edit(int id, [Bind("ProductId,Name,Quantity,Color,Price,ProductCode")] Product product)
{
if (id != product.ProductId)
{
return RedirectToAction("Index");
}
if (ModelState.IsValid)
{
try
{
_context.Update(product);
_context.SaveChanges();
}
catch (Exception)
{
throw;
}
return RedirectToAction(nameof(Index));
}
return View(product);
}
private bool ProductExists(int id)
{
return _context.Product.Any(e => e.ProductId == id);
}The Edit POST Action method which handles POST request This action method takes Product Model and Id as input id (product id) according to which Product details will get updated before updating we validate Model first if ModelState is valid then we are going to Update Product details.
For that, we pass product Model it to update method of DbContext which set entity set to Modified When we call context.SaveChanges then an Update statement is generated and executed by the database.
While updating if it throws an error it is handled in the catch method.
Edit View
@model WebApplication5.Models.Product
@{
ViewData["Title"] = "Edit";
}
<h1>Edit</h1>
<h4>Product</h4>
<hr />
<div class="row">
<div class="col-md-4">
<form asp-action="Edit">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<input type="hidden" asp-for="ProductId" />
<div class="form-group">
<label asp-for="Name" class="control-label"></label>
<input asp-for="Name" class="form-control" />
<span asp-validation-for="Name" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Quantity" class="control-label"></label>
<input asp-for="Quantity" class="form-control" />
<span asp-validation-for="Quantity" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Color" class="control-label"></label>
<input asp-for="Color" class="form-control" />
<span asp-validation-for="Color" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Price" class="control-label"></label>
<input asp-for="Price" class="form-control" />
<span asp-validation-for="Price" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="ProductCode" class="control-label"></label>
<input asp-for="ProductCode" class="form-control" />
<span asp-validation-for="ProductCode" class="text-danger"></span>
</div>
<div class="form-group">
<input type="submit" value="Save" class="btn btn-primary" />
</div>
</form>
</div>
</div>
<div>
<a asp-action="Index">Back to List</a>
</div>
@section Scripts {
@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}Snapshot of Index View
Snapshot of Edit View
Index View after updating data.
Delete
In this part, we are going to Delete product which we have created for doing that we are going to add 2 Delete Action method one for handling GET request and another for a handling POST request.
In Delete Get Action method which handles GET Request which takes id as input parameter on bases of id (product id), we are going to fetch data from database and render on Delete View.
// GET: ManageProduct/Delete/5
public IActionResult Delete(int? id)
{
if (id == null)
{
return RedirectToAction("Index");
}
var product = _context.Product.
FirstOrDefault(m => m.ProductId == id);
if (product == null)
{
return RedirectToAction("Index");
}
return View(product);
}The Delete POST Action method which handles POST request This action method takes id (product id) as input according to Id it will fetch product details from the database. And then we pass product Model to Remove method of DbContext which set entity set to deleted When we call context.SaveChanges then a DELETE statement is generated and executed by the database.
// POST: ManageProduct/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public IActionResult DeleteConfirmed(int id)
{
var product = _context.Product.Find(id);
_context.Product.Remove(product);
_context.SaveChanges();
return RedirectToAction(nameof(Index));
}Delete View
@model WebApplication5.Models.Product
@{
ViewData["Title"] = "Delete";
}
<h1>Delete</h1>
<h3>Are you sure you want to delete this?</h3>
<div>
<h4>Product</h4>
<hr />
<dl class="row">
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Name)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Name)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Quantity)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Quantity)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Color)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Color)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.Price)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.Price)
</dd>
<dt class = "col-sm-2">
@Html.DisplayNameFor(model => model.ProductCode)
</dt>
<dd class = "col-sm-10">
@Html.DisplayFor(model => model.ProductCode)
</dd>
</dl>
<form asp-action="Delete">
<input type="hidden" asp-for="ProductId" />
<input type="submit" value="Delete" class="btn btn-danger" /> |
<a asp-action="Index">Back to List</a>
</form>
</div>Snapshot of Index View
Snapshot of Delete View
Conclusion
In this part we have learned how to create an application, installing entity framework, configure connection string and register DatabaseContext service in ConfigureServices method in startup class and use it as Constructor injection in the controller and perform CRUD operation.