Post
Ask Question

Simple CRUD Operations Using .NET with Entity Framework

.NET Core

This blog is related to a crud operation. In this blog, I show how to add, edit, delete, and get data from a database. I used Visual Studio 2022 and SQL Server 2019 for this crud operation. GitHub URL

Step 1. Create a .net core MVC project version 7 as well as an SQL Server database.

SQL Server database

Create Project .NET core MVC (version 7.0)

Create Project .NET core MVC

Create a database in SQL Server

Step 2. Put the default connection string in appsettings.js.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultString": "Server=***sqlservername***;database=EfCore;Trusted_connection=true"
  }
}

Step 3. Add database connection server setup integration in startup.cs class.

using EFCore.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllersWithViews();

builder.Services.AddDbContext<EFCoreDBContext>(service =>
    service.UseSqlServer(builder.Configuration.GetConnectionString("DefaultString")));

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
}

app.UseStaticFiles();
app.UseRouting();
app.UseAuthorization();

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

app.Run();

Step 4. Create two classes The first is a student, and the second is a pager (for pegging).

public class Pager
{
    public int TotalItems { get; set; }
    public int CurrentPage { get; set; }
    public int PageSize { get; set; }
    public int TotalPages { get; set; }
    public int StartPage { get; set; }
    public int EndPage { get; set; }

    public Pager()
    {
    }

    public Pager(int totalItems, int page, int pageSize = 10)
    {
        int totalPages = (int)Math.Ceiling((decimal)totalItems / (decimal)pageSize);
        int currentPage = page;

        int startpage = currentPage - 5;
        int endpage = currentPage + 5;

        if (startpage <= 0)
        {
            endpage = endpage - (startpage - 1);
            startpage = 1;
        }

        if (endpage > totalPages)
        {
            endpage = totalPages;
            if (endpage > 10)
            {
                startpage = endpage - 9;
            }
        }

        TotalItems = totalItems;
        CurrentPage = currentPage;
        PageSize = pageSize;
        TotalPages = totalPages;
        StartPage = startpage;
        EndPage = endpage;
    }
}

namespace EFCore.Models
{
    public class Student
    {
        public int Id { get; set; }
        public int RollNumber { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Class { get; set; }
        public DateTime BirthDate { get; set; }
    }
}

Step 5. Create a data folder in this application and add one more file named EFCoreDBContext.cs.

Add Nuget package for connection to the database

  • Microsoft.EntityFrameworkCore.SqlServer (7.0.0)
  • Microsoft.EntityFrameworkCore.Tools(7.0.0)
  • Microsoft.VisualStudio.Web.CodeGeneration.Design (7.0.0)

Create a student table in the SQL Server database. Following that, you can navigate to Tools. Nuget Package Manager => Write the following command in the Package Manager Console to connect to the database.

  1. Add-Migration “initial Migration”
  2. Update-Database
public class EFCoreDBContext : DbContext
{
    public EFCoreDBContext(DbContextOptions options) : base(options)
    {
    }

    // Write these commands:
    // Add-Migration "initial Migration"
    // Update-Database
    public DbSet<Student> Students { get; set; }
}

Step 6. Insert the following code into the Student Controller. cs file.

public class StudentController : Controller
{
    public readonly EFCoreDBContext dBContext;

    public StudentController(EFCoreDBContext _dBContext)
    {
        this.dBContext = _dBContext;
    }

    [HttpGet]
    public async Task<IActionResult> Students(string search, int page = 1, int pageSize = 10)
    {
        search = search ?? "";
        try
        {
            var students = await dBContext.Students
                .Where(t => t.Name.Contains(search) || t.Email.Contains(search) || t.Class.Contains(search))
                .ToListAsync();

            if (page < 1)
            {
                page = 1;
            }

            int recsCount = students.Count;
            var pager = new Pager(recsCount, page, pageSize);
            var recSkip = (page - 1) * pageSize;
            students = students.Skip(recSkip).Take(pager.PageSize).ToList();

            ViewBag.Pager = pager;
            ViewBag.Search = search;

            return View(students);
        }
        catch (Exception)
        {
            throw;
        }
    }

    [HttpGet]
    public async Task<IActionResult> StudentGrid(string search = "", int page = 1, int pageSize = 10)
    {
        search = search ?? "";
        try
        {
            var students = await dBContext.Students
                .Where(t => t.Name.Contains(search) || t.Email.Contains(search) || t.Class.Contains(search))
                .ToListAsync();

            if (page < 1)
            {
                page = 1;
            }

            int recsCount = students.Count;
            var pager = new Pager(recsCount, page, pageSize);
            var recSkip = (page - 1) * pageSize;
            students = students.Skip(recSkip).Take(pager.PageSize).ToList();

            ViewBag.Pager = pager;
            ViewBag.Search = search;

            return PartialView("StudentGrid", students);
        }
        catch (Exception)
        {
            throw;
        }
    }

    [HttpGet]
    public IActionResult AddStudent()
    {
        return View();
    }

    [HttpPost]
    public async Task<IActionResult> AddStudent(Student student)
    {
        if (student.Id == 0)
        {
            await dBContext.AddAsync(student);
        }
        else
        {
            var dstudent = await dBContext.Students.FindAsync(student.Id);
            if (dstudent != null)
            {
                dstudent.RollNumber = student.RollNumber;
                dstudent.Name = student.Name;
                dstudent.Email = student.Email;
                dstudent.Class = student.Class;
                dstudent.BirthDate = student.BirthDate;
            }
        }

        await dBContext.SaveChangesAsync();
        return RedirectToAction("Students");
    }

    [HttpGet]
    public async Task<IActionResult> EditStudent(int id)
    {
        var student = await dBContext.Students.FirstOrDefaultAsync(x => x.Id == id) ?? new Student();
        return View("AddStudent", student);
    }

    [HttpGet]
    public async Task<IActionResult> DeleteStudent(int id)
    {
        var student = await dBContext.Students.FindAsync(id) ?? new Student();
        if (student != null)
        {
            dBContext.Students.Remove(student);
            await dBContext.SaveChangesAsync();
        }
        return RedirectToAction("Students");
    }
}

Step 7. Add three files for the client-side view.

AddStudent.cshtml

@model EFCore.Models.Student
@{
    Layout = "_Layout";
    ViewData["Title"] = "Add Student";
}

<h1>Add Student</h1>
<form method="post" asp-controller="Student" asp-action="AddStudent" class="mt-5">
    <input type="hidden" asp-for="Id" />
    <div class="mb-3">
        <label class="form-label">Name</label>
        <input type="text" class="form-control" asp-for="Name">
    </div>
    <div class="mb-3">
        <label class="form-label">Email</label>
        <input type="email" class="form-control" asp-for="Email">
    </div>
    <div class="mb-3">
        <label class="form-label">Roll Number</label>
        <input type="number" class="form-control" asp-for="RollNumber">
    </div>
    <div class="mb-3">
        <label class="form-label">Class Number</label>
        <input type="text" class="form-control" asp-for="Class">
    </div>
    <div class="mb-3">
        <label class="form-label">Birth Date</label>
        <input type="date" class="form-control" asp-for="BirthDate">
    </div>
    <button type="submit" class="btn btn-primary">Submit</button>
</form>

StudentGrid.cshtml

@model List<EFCore.Models.Student>
@{
    Pager pager = ViewBag.Pager ?? new Pager();
    int pageNo = pager.CurrentPage;
}

<table class="table">
    <thead>
        <tr>
            <th>Id</th>
            <th>Roll #</th>
            <th>Name</th>
            <th>Email</th>
            <th>Class</th>
            <th>BirthDate</th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.Id</td>
                <td>@item.RollNumber</td>
                <td>@item.Name</td>
                <td>@item.Email</td>
                <td>@item.Class</td>
                <td>@item.BirthDate.ToString("MM/dd/yyyy")</td>
                <td>
                    <a asp-controller="Student" asp-route-search="@ViewBag.Search" asp-route-id="@item.Id" asp-action="EditStudent">Edit</a>
                    <a asp-controller="Student" asp-route-search="@ViewBag.Search" asp-route-id="@item.Id" asp-action="DeleteStudent">Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

<div class="container">
    <div>
        <span>Current page @pager.CurrentPage of Total Page @pager.TotalPages</span>
        <div class="btn-group">
            <button class="btn btn-secondary btn-sm dropdown-toggle" type="button" data-bs-toggle="dropdown" aria-expanded="false">
                Page Size
            </button>
            <ul class="dropdown-menu">
                <li><a class="page-link" asp-controller="Student" asp-route-search="@ViewBag.Search" asp-action="Students" asp-route-page="1" asp-route-pagesize="5">5</a></li>
                <li><a class="page-link" asp-controller="Student" asp-route-search="@ViewBag.Search" asp-action="Students" asp-route-page="1" asp-route-pagesize="10">10</a></li>
                <li><a class="page-link" asp-controller="Student" asp-route-search="@ViewBag.Search" asp-action="Students" asp-route-page="1" asp-route-pagesize="15">15</a></li>
            </ul>
        </div>
    </div>

    @if (pager.TotalPages > 0)
    {
        <ul class="pagination justify-content-end">
            @if (pager.CurrentPage > 1)
            {
                <li class="page-item">
                    <a class="page-link" asp-controller="Student" asp-route-search="@ViewBag.Search" asp-action="Students" asp-route-page="1" asp-route-pagesize="@pager.PageSize">First</a>
                </li>
                <li class="page-item">
                    <a class="page-link" asp-controller="Student" asp-route-search="@ViewBag.Search" asp-action="Students" asp-route-page="@(pager.CurrentPage - 1)" asp-route-pagesize="@pager.PageSize">Previous</a>
                </li>
            }
            @for (int i = pager.StartPage; i <= pager.EndPage; i++)
            {
                <li class="page-item @(i == pager.CurrentPage ? "active" : "")">
                    <a class="page-link" asp-controller="Student" asp-route-search="@ViewBag.Search" asp-action="Students" asp-route-page="@i" asp-route-pagesize="@pager.PageSize">@i</a>
                </li>
            }
            @if (pager.CurrentPage < pager.TotalPages)
            {
                <li class="page-item">
                    <a class="page-link" asp-controller="Student" asp-route-search="@ViewBag.Search" asp-action="Students" asp-route-page="@(pager.CurrentPage + 1)" asp-route-pagesize="@pager.PageSize">Next</a>
                </li>
                <li class="page-item">
                    <a class="page-link" asp-controller="Student" asp-route-search="@ViewBag.Search" asp-action="Students" asp-route-page="@pager.TotalPages" asp-route-pagesize="@pager.PageSize">Last</a>
                </li>
            }
        </ul>
    }
</div>

Students. cshtml

@model List<EFCore.Models.Student>
@{
    Layout = "_Layout";
    ViewData["Title"] = "Student List";

    Pager pager = ViewBag.Pager ?? new Pager();
    int pageNo = pager.CurrentPage;
}

<h1>Student List</h1>

<div>
    <input type="search" name="search" placeholder="Search.." id="search" value="@ViewBag.Search" />
    <button class="btn btn-primary pt-0 mb-1" id="btnSearch" type="submit">Search</button>
    <a class="btn btn-primary float-end" asp-controller="Student" asp-action="AddStudent">Add Student</a>
</div>

<div class="student-list">
    @{
        await Html.RenderPartialAsync("StudentGrid", Model);
    }
</div>

@section Scripts {
    <script>
        $('#search').keypress(function (e) {
            if (e.which == 13) {
                $('#btnSearch').click();
            }
        });

        $("#btnSearch").click(function () {
            $.ajax({
                cache: false,
                type: "GET",
                url: "/Student/StudentGrid?search=" + ($("#search").val() || "") + "&page=" + @pager.CurrentPage + "&pagesize=" + @pager.PageSize,
                dataType: "html",
                success: function (data) {
                    $(".student-list").html(data);
                }
            });
        });
    </script>
}

Layout. cshtml

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>@ViewData["Title"] - EFCore</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="~/EFCore.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="Student" asp-action="Students">Students</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>
        </nav>
    </header>
    <div class="container">
        <main role="main" class="pb-3">
            @RenderBody()
        </main>
    </div>

    <script src="~/lib/jquery/dist/jquery.min.js"></script>
    <script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script>
    <script src="~/js/site.js" asp-append-version="true"></script>
    @await RenderSectionAsync("Scripts", required: false)
</body>
</html>

Thanks for reading this blog.
Next Recommended Reading MySQL-Entity-Framework-Code-First For .NET Developers