Crud operation in Asp.Net Core Mvc using jquery datatable with entity framework core database first

Recently, I've been working on a project where my project manager asked me to implement DataTables with insert, update, and delete operations using jQuery.

I also needed to reload the DataTable without refreshing the entire page. I successfully completed this task in our project, so I decided to share my experience with all developers so they can benefit from my approach.

If you are wokring on Razor pages based application then you can read this post

CRUD OPERATION IN ASP .NET CORE USING RAZOR PAGES STEP BY STEP

In our example, we are going to perform CRUD operations in ASP.NET Core using Entity Framework Core database-first approach. So, let's first set up Entity Framework in our project.

Step1:Create a new .NET Core Web Application project in Visual Studio.

Open Visual Studio and Mvc Web Application Project

Here's a step-by-step guide along with code to create a DbContext using Entity Framework Code First.:

Step 2: Install Entity Framework Core and the necessary database provider package. For example, if you're using SQL Server, you can install the packages using NuGet Package Manager Console:

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

Make sure to use correct version based on your .Net core project.

Step 3: Create model classes

using System;

namespace DemoWebApp.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public DateTime CreatedAt { get; set; }
    }
}

Step 4: Create a DbContext class that inherits from DbContext and defines a DbSet for each of your model classes. let's create a folder in the projetc named "DataBaseEntity".

using DemoWebApp.Models;
using Microsoft.EntityFrameworkCore;

namespace DemoWebApp.DataBaseEntity
{
    public class ProductDbContext : DbContext
    {
        public DbSet<Product> Products { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Data Source=DESKTOP-MFLCOI2;Initial Catalog=DemoDb;User ID=sa;Password=adk@1234");
        }
    }
}

Replace connection string with the actual connection string to your SQL Server database.

Step 6: Register DbContext in program.cs, In .NET 7, the setup for registering a DbContext remains very similar to previous versions of ASP.NET Core. Here's code how you can do it in an ASP.NET Core web application targeting .NET 7 or 8:

using DemoWebApp.DataBaseEntity;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllersWithViews();
builder.Services.AddDbContext<ProductDbContext>();

var app = builder.Build();
// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

Step 6: Create a migration to generate the initial database schema,Go to Tools > NuGet Package Manager > Package Manager Console. It will open the NuGet Console window. Copy the command below and paste it there.

Add-Migration InitialDb

After successfully running the command, it will create a folder in our project named 'Migrations'. You will be able to see it in the root directory.

Step 7: Apply the migration to create the database,After running 'Update-Database', the migration will be applied to our database, creating the tables for us.

Update-Database

Step 8: Add jQuery DataTables CDN in the layout view.

<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css" rel="stylesheet" />

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>@ViewData["Title"] - DemoWebApp</title>
    <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" />
    <link rel="stylesheet" href="~/css/site.css" asp-append-version="true" />
    <link rel="stylesheet" href="~/DemoWebApp.styles.css" asp-append-version="true" />
</head>
<body>
    <header>
        <nav class="navbar navbar-expand-sm navbar-toggleable-sm navbar-light bg-white border-bottom box-shadow mb-3">
            <div class="container-fluid">
                <a class="navbar-brand" asp-area="" asp-controller="Home" asp-action="Index">DemoWebApp</a>
                <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target=".navbar-collapse" aria-controls="navbarSupportedContent"
                        aria-expanded="false" aria-label="Toggle navigation">
                    <span class="navbar-toggler-icon"></span>
                </button>
                <div class="navbar-collapse collapse d-sm-inline-flex justify-content-between">
                    <ul class="navbar-nav flex-grow-1">
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Index">Home</a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Privacy">Privacy</a>
                        </li>
                    </ul>
                </div>
            </div>
        </nav>
    </header>
    <div class="container">
        <main role="main" class="pb-3">
            @RenderBody()
        </main>
    </div>

    <footer class="border-top footer text-muted">
        <div class="container">
            © 2024 - DemoWebApp - <a asp-area="" asp-controller="Home" asp-action="Privacy">Privacy</a>
        </div>
    </footer>
    <script src="~/lib/jquery/dist/jquery.min.js"></script>
    <script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
    <link href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css" rel="stylesheet" />
    <script src="~/js/site.js" asp-append-version="true"></script>
    @await RenderSectionAsync("Scripts", required: false)
</body>
</html>

Step 9: Create a ProductController

using DemoWebApp.DataBaseEntity;
using DemoWebApp.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Composition.Convention;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace DemoWebApp.Controllers
{

    public class ProductDTO
    {
        public List<Product> Data { get; set; }
    }
    public class ProductController : Controller
    {
        private readonly ProductDbContext _context;

        public ProductController(ProductDbContext context)
        {
            _context = context;
        }

        public IActionResult Index()
        {
            return View();
        }

        // Action method to get all products
        [HttpGet]
        public IActionResult GetAllProducts()
        {
            var products = _context.Products.ToList();
            return Json(new ProductDTO { Data=products });
        }

        // Action method to display the create form
        public IActionResult Create()
        {
            return View();
        }

        // Action method to handle the form submission and create a new product
        [HttpPost]
        [ValidateAntiForgeryToken]
        public IActionResult Create(Product product)
        {
            if (ModelState.IsValid)
            {
                product.CreatedAt = DateTime.Now;
                _context.Products.Add(product);
                _context.SaveChanges();
                return RedirectToAction(nameof(Index));
            }
            return View(product);
        }

        // Action method to display the edit form for a specific product
        public IActionResult Edit(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var product = _context.Products.Find(id);
            if (product == null)
            {
                return NotFound();
            }

            return View(product);
        }

        // Action method to handle the form submission and update an existing product
        [HttpPost]
        [ValidateAntiForgeryToken]
        public IActionResult Edit(Product product)
        {
            if (ModelState.IsValid)
            {
                try
                {
                    var productObj = _context.Products.Find(product.Id);
                    productObj.Name = product.Name;
                    productObj.Price = product.Price;
                    _context.Update(productObj);
                    _context.SaveChanges();
                }
                catch (DbUpdateConcurrencyException)
                {
                    if (!ProductExists(product.Id))
                    {
                        return NotFound();
                    }
                    else
                    {
                        throw;
                    }
                }
                return RedirectToAction(nameof(Index));
            }
            return View(product);
        }

        private bool ProductExists(int id)
        {
            return _context.Products.Any(e => e.Id == id);
        }


        // Action method to delete a product
        [HttpPost]
        public IActionResult DeleteProduct(int id)
        {
            var product = _context.Products.Find(id);
            if (product != null)
            {
                _context.Products.Remove(product);
                _context.SaveChanges();
                return Json(new { success = true });
            }
            return Json(new { success = false });
        }
    }
}

Step 10: So let's create views for performing operations.

Index.cshtml: Display a list of products with links to create, edit, and delete.

Create.cshtml: Form to create a new product.

Edit.cshtml: Form to edit an existing product.

Index.cshtml

@{
    ViewData["Title"] = "Product Management"; ;
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<a href="/Product/Create" class="btn btn-info">Add Product</a>
<table id="productTable" class="table table-bordered">
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Price</th>
            <th>Created At</th>
            <th>Action</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

@section scripts {
    <script>
        $(document).ready(function () {
            $('#productTable').DataTable({
                "ajax": {
                    "url": "/Product/GetAllProducts",
                    "type": "GET",
                    "datatype": "json"
                },
                "columns": [
                    { "data": "id" },
                    { "data": "name" },
                    { "data": "price" },
                    { "data": "createdAt" },
                    {
                        "data": null,
                        "render": function (data, type, row) {
                            return "<button class='btn btn-info' onclick='EditProduct(" + data.id + ")'>Edit</button> | <button class='btn btn-danger' onclick='DeleteProduct(" + data.id + ")'>Delete</button>";
                        }
                    }
                ]
            });
        });

        // Function to delete a product
        function DeleteProduct(id) {
            if (confirm('Are you sure you want to delete this product?')) {
                $.ajax({
                    type: "POST",
                    url: '/Product/DeleteProduct',
                    data: { id: id },
                    success: function (data) {
                        if (data.success) {
                            $('#productTable').DataTable().ajax.reload();
                        } else {
                            alert('Failed to delete product.');
                        }
                    }
                });
            }
        }

        // Function to edit a product
        function EditProduct(id) {
            window.location.href="/Product/Edit/"+id;
        }
    </script>
}

Create.cshtml

let's create the logic for the Create view in our ASP.NET MVC application. The Create view will allow users to input data for a new product and submit it. let's create the Create view file (Create.cshtml) in the Views/Product folder:

@model DemoWebApp.Models.Product

@{
    ViewData["Title"] = "Create";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<form asp-action="Create">
    <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="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">
        <input type="submit" value="Create" class="btn btn-primary" />
    </div>
</form>

In this Create view:

We create a form that submits to the Create action of the Product controller. Inside the form, we have input fields for the Name and Price properties of the Product model.
We include validation error messages for each input field using asp-validation-for.
Finally, we have a submit button to create the product.
We use the [ValidateAntiForgeryToken] attribute for security against cross-site request forgery attacks.

Edit.cshtml

Now,let's create the logic for the Edit view in our ASP.NET MVC application. The Edit view will allow users to edit the details of an existing product.let's create the Edit view file (Edit.cshtml) in the Views/Product folder:

@model MvcCrudWithDataTable.Models.Product

@{
    ViewData["Title"] = "Edit Product";
}

<h2>@ViewData["Title"]</h2>

<form asp-action="Edit">
    <input type="hidden" asp-for="Id" />
    <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="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">
        <input type="submit" value="Save" class="btn btn-primary" />
    </div>
</form>

In this Edit view:

We declare the model to be of type Product.
We create a form that submits to the Edit action of the Product controller. We include a hidden input field for the Id property of the Product model so that the controller knows which product to update.
Inside the form, we have input fields for the Name and Price properties of the Product model.
We include validation error messages for each input field using asp-validation-for. Finally, we have a submit button to save the changes made to the product.

Delete Product:

To delete a product, we have created a `DeleteProduct()` function which first shows a confirmation message to the user asking if they want to delete that record.

// Function to delete a product
        function DeleteProduct(id) {
            if (confirm('Are you sure you want to delete this product?')) {
                $.ajax({
                    type: "POST",
                    url: '/Product/DeleteProduct',
                    data: { id: id },
                    success: function (data) {
                        if (data.success) {
                            $('#productTable').DataTable().ajax.reload();
                        } else {
                            alert('Failed to delete product.');
                        }
                    }
                });
            }
        }

Crud operation in Asp.Net Core Mvc using jquery datatable with entity framework core database first

Read Similar Articles