Simple Crud operations using.net 7 with Entity Framework

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 a SQL Server database.

Create Project .NET core MVC (version 7.0)

Create 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 3: Create two classes The first is a student, and the second is a pager (for pagging).

 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 > startpage)
            {
                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 4: Create a data folder in this application and add one more file named EFCoreDBContext.cs.

Add nuget package for connection to 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 “intial Migration”
2. Update-Database

  public class EFCoreDBContext : DbContext
    {
        public EFCoreDBContext(DbContextOptions options) : base(options)
        {
            
        }

        //write command
        //Add-Migration "intial Migration"
        //Update-Database
        public DbSet<Student> Students { get; set; }
    }

Step : 5: Insert the following code into the Studentcontroller.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 == null ? "" : search;
            try
            {
                List<Student> 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();
                this.ViewBag.Pager = pager;
                this.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 == null ? "" : search;
            try
            {
                List<Student> 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();
                this.ViewBag.Pager = pager;
                this.ViewBag.Search = search;
                return PartialView("StudentGrid", students);
            }
            catch (Exception)
            {
                throw;
            }

        }

        [HttpGet]
        public async Task<IActionResult> AddStudent()
        {
            return View();
        }

        [HttpPost]
        public async Task<IActionResult> AddStudent(Student student)
        {
            if (student.Id == 0)
            {
                await this.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 this.dBContext.SaveChangesAsync();
            return RedirectToAction("Students");
        }

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

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

Step : 6 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 = new Pager();
    int pageNo = 0;

    if (ViewBag.Pager != null)
    {
        pager = ViewBag.Pager;
        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">
                Small button
            </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 = new Pager();
    int pageNo = 0;

    if (ViewBag.Pager != null)
    {
        pager = ViewBag.Pager;
        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() == null || $("#search").val() == undefined ? "" : $("#search").val()) + "&page=" + @pager.CurrentPage + "&pagesize=" + @pager.PageSize,
                dataType: "html",
                success: function (data) {
                    $(".student-list").html("");
                    $(".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