Friday, 24 April 2020

CRUD operations using CORE(3.1) MVC, Repository & Dependency Injection, EF code first approach



Mvc Code :

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication4.Models
{
    public class COUNTRY
    {
        [Key]
        public int CID { get; set; }
        [MaxLength(50)]
        public string CNAME { get; set; }
    }
}
--
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication4.Models
{
    public class STATE
    {
        [Key]
        public int SID { get; set; }
        [MaxLength(50)]
        public string SNAME { get; set; }
        public int CID { get; set; }
    }
}
--
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication4.Models
{
    public class HOBBY
    {
        [Key]
        public int HID { get; set; }
        [MaxLength(50)]
        public string HNAME { get; set; }
    }
}
--
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication4.Models
{
    public class EMP
    {
        [Key]
        public int EID { get; set; }
        [MaxLength(50)]
        public string NAME { get; set; }
        [MaxLength(50)]
        public string GENDER { get; set; }
        public int CID { get; set; }
        public int SID { get; set; }
       

    }
}
--
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication4.Models
{
    public class HMAP
    {
        [Key]
        public int ID { get; set; }
        public int EID { get; set; }
        public int HID { get; set; }
    }
}
--
using Microsoft.AspNetCore.Mvc.Rendering;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication4.Models
{
    public class EMPVM
    {
        public EMPVM()
        {
            LCOUNTRY = new List<SelectListItem>();
            LSTATE = new List<SelectListItem>();
            LHOBBY = new List<SelectListItem>();
        }
        public int EID { get; set; }
        [Required(ErrorMessage ="Name should not be blank.")]
        public string NAME { get; set; }
        [Required(ErrorMessage = "Please select a gender.")]
        public string GENDER { get; set; }
        [Required(ErrorMessage = "Please select a country.")]
        public int COUNTRY { get; set; }
        public List<SelectListItem> LCOUNTRY { get; set; }
        [Required(ErrorMessage = "Please select a state.")]
        public int STATE { get; set; }
        public List<SelectListItem> LSTATE { get; set; }
        [Required(ErrorMessage = "Please select a hobby.")]
        public List<string> HOBBY { get; set; }
        public List<SelectListItem> LHOBBY { get; set; }
        public void fillddl(List<SelectListItem> lCOUNTRY, List<SelectListItem> lHOBBY, List<SelectListItem> lSTATE=null)
        {
            LCOUNTRY = lCOUNTRY;
            LHOBBY = lHOBBY;
            LSTATE = lSTATE;
        }
    }
}
--
Install the following 2 NuGet packages.

Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools
--
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication4.Models
{
    public class AppDbContext:DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options):base(options)
        {      
        }
        public DbSet<COUNTRY> Countries { get; set; }
        public DbSet<STATE> States { get; set; }
        public DbSet<HOBBY> Hobbies { get; set; }
        public DbSet<HMAP> Hmaps { get; set; }
        public DbSet<EMP> Emps { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<COUNTRY>().HasData(new COUNTRY { CID = 1, CNAME = "X" }, new COUNTRY { CID = 2, CNAME = "Y" }, new COUNTRY { CID = 3, CNAME = "Z" });
            modelBuilder.Entity<STATE>().HasData(new STATE { SID = 1, SNAME = "A", CID = 1 }, new STATE { SID = 2, SNAME = "B", CID = 1 }, new STATE { SID = 3, SNAME = "C", CID = 2 }, new STATE { SID = 4, SNAME = "D", CID = 2 }, new STATE { SID = 5, SNAME = "E", CID = 3 }, new STATE { SID = 6, SNAME = "F", CID = 3 });
            modelBuilder.Entity<HOBBY>().HasData(new HOBBY { HID = 1, HNAME = "Cricket" }, new HOBBY { HID = 2, HNAME = "Football" }, new HOBBY { HID = 3, HNAME = "Baseball" }, new HOBBY { HID = 4, HNAME = "Hockey" });
        }
    }
}
--
Add connection string in appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DbConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=DBTest789;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
  }

}
--
Call the connection in Startup class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using WebApplication4.Models;

namespace WebApplication4
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllersWithViews();
            services.AddDbContextPool<AppDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DbConnection")));
            services.AddScoped<IDAL, DAL>();
            services.AddControllersWithViews()
          .AddJsonOptions(options =>
          {
              options.JsonSerializerOptions.PropertyNamingPolicy = null;
          });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }
            app.UseStaticFiles();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllerRoute(
                    name: "default",
                    pattern: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}
--
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication4.Models
{
  public interface IDAL
    {
        Task<IEnumerable<EMP>> Gete();
        Task<IEnumerable<COUNTRY>> Getc();
        Task<IEnumerable<STATE>> Gets(int CID);
        Task<IEnumerable<HOBBY>> Geth();
        Task<EMPVM> Get(int EID);
        Task<EMP> Save(EMPVM eMPVM);
        Task<EMP> Update(EMPVM eMPVM);
        Task<EMP> Delete(int EID);

    }
}
--
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication4.Models
{
    public class DAL : IDAL
    {
        private readonly AppDbContext dbContext;
        public DAL(AppDbContext DbContext)
        {
            this.dbContext = DbContext;
        }

        public async Task<EMP> Delete(int EID)
        {
            EMP emp = await dbContext.Emps.FindAsync(EID);
            dbContext.Entry(emp).State = EntityState.Deleted;
            dbContext.Hmaps.RemoveRange(dbContext.Hmaps.Where(m => m.EID == EID).ToList());
            await dbContext.SaveChangesAsync();
            return emp;
        }

        public async Task<EMPVM> Get(int EID)
        {
            EMPVM eMPVM = await dbContext.Emps.Select(m => new EMPVM
            {
                EID = m.EID,
                NAME = m.NAME,
                GENDER = m.GENDER,
                COUNTRY = m.CID,
                STATE = m.SID,
                HOBBY = dbContext.Hmaps.Where(p => p.EID == EID).Select(t => t.HID.ToString()).ToList()
            }).FirstOrDefaultAsync(q => q.EID == EID);
            return eMPVM;
        }

        public async Task<IEnumerable<COUNTRY>> Getc()
        {
            return await dbContext.Countries.ToListAsync();
        }

        public async Task<IEnumerable<EMP>> Gete()
        {
            return await dbContext.Emps.ToListAsync();
        }

        public async Task<IEnumerable<HOBBY>> Geth()
        {
            return await dbContext.Hobbies.ToListAsync();
        }

        public async Task<IEnumerable<STATE>> Gets(int CID)
        {
            return await dbContext.States.Where(m => m.CID == CID).ToListAsync();
        }

        public async Task<EMP> Save(EMPVM eMPVM)
        {
            EMP emp = new EMP()
            {
                NAME = eMPVM.NAME,
                GENDER = eMPVM.GENDER,
                CID = eMPVM.COUNTRY,
                SID = eMPVM.STATE
            };
            dbContext.Entry(emp).State = EntityState.Added;
            await dbContext.SaveChangesAsync();
            dbContext.Hmaps.AddRange(eMPVM.HOBBY.Select(m => new HMAP { EID = emp.EID, HID = Convert.ToInt32(m) }));
            await dbContext.SaveChangesAsync();
            return emp;
        }

        public async Task<EMP> Update(EMPVM eMPVM)
        {
            EMP emp = dbContext.Emps.Find(eMPVM.EID);
            emp.NAME = eMPVM.NAME;
            emp.GENDER = eMPVM.GENDER;
            emp.CID = eMPVM.COUNTRY;
            emp.SID = eMPVM.STATE;
            dbContext.Entry(emp).State = EntityState.Modified;
            dbContext.Hmaps.RemoveRange(dbContext.Hmaps.Where(m => m.EID == eMPVM.EID).ToList());
            dbContext.Hmaps.AddRange(eMPVM.HOBBY.Select(m => new HMAP { EID = eMPVM.EID, HID = Convert.ToInt32(m) }));
            await dbContext.SaveChangesAsync();
            return emp;
        }
    }
}
--
Create and execute database migrations
Use the following 2 commands to create and execute the initial database migration

Add-Migration 1st
Update-Database
below commands for removing  migration
Update-Database  <Migration Name>
remove-migration
--
Controller code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using WebApplication4.Models;

namespace WebApplication4.Controllers
{
    public class EmpsController : Controller
    {
        private readonly IDAL dal;
        public EmpsController(IDAL dal)
        {
            this.dal = dal;
        }
        [HttpGet]
        public async Task<IActionResult> Index()
        {
            return View(await dal.Gete());
        }
        [HttpGet]
        public async Task<IActionResult> Create()
        {
            EMPVM eMPVM = new EMPVM();
            var data = await dal.Getc();
            eMPVM.fillddl(data.Select(m => new SelectListItem() { Value = m.CID.ToString(), Text = m.CNAME }).ToList(), dal.Geth().Result.Select(n => new SelectListItem { Value = n.HID.ToString(), Text = n.HNAME }).ToList());
            return View(eMPVM);
        }
        [HttpPost]
        public async Task<IActionResult> Create(EMPVM vm)
        {
            if (ModelState.IsValid)
            {
                EMP emp = await dal.Save(vm);
                return RedirectToAction("Index");
            }
            else
            {
                vm.fillddl(dal.Getc().Result.Select(m => new SelectListItem() { Value = m.CID.ToString(), Text = m.CNAME }).ToList(), dal.Geth().Result.Select(n => new SelectListItem { Value = n.HID.ToString(), Text = n.HNAME }).ToList(), dal.Gets(vm.COUNTRY).Result.Select(r => new SelectListItem { Value = r.SID.ToString(), Text = r.SNAME}).ToList());
                return View(vm);
            }
           
        }
        [HttpGet]
        public async Task<IActionResult> Edit(int id)
        {
            EMPVM eMPVM =await dal.Get(id);
            eMPVM.fillddl(dal.Getc().Result.Select(m => new SelectListItem() { Value = m.CID.ToString(), Text = m.CNAME }).ToList(), dal.Geth().Result.Select(n => new SelectListItem { Value = n.HID.ToString(), Text = n.HNAME }).ToList(), dal.Gets(eMPVM.COUNTRY).Result.Select(r => new SelectListItem { Value = r.SID.ToString(), Text = r.SNAME }).ToList());
            return View(eMPVM);
        }
        [HttpPost]
        public async Task<IActionResult> Edit(EMPVM vm)
        {
            if (ModelState.IsValid)
            {
                EMP emp = await dal.Update(vm);
                return RedirectToAction("Index");
            }
            else
            {
                vm.fillddl(dal.Getc().Result.Select(m => new SelectListItem() { Value = m.CID.ToString(), Text = m.CNAME }).ToList(), dal.Geth().Result.Select(n => new SelectListItem { Value = n.HID.ToString(), Text = n.HNAME }).ToList(), dal.Gets(vm.COUNTRY).Result.Select(r => new SelectListItem { Value = r.SID.ToString(), Text = r.SNAME }).ToList());
                return View(vm);
            }

        }
        [HttpGet]
        public async Task<IActionResult> Delete(int id)
        {
            EMPVM eMPVM = await dal.Get(id);
            return View(eMPVM);
        }
        [HttpPost]
        public async Task<IActionResult> Delete(EMPVM vm)
        {
            EMP emp = await dal.Delete(vm.EID);
            return RedirectToAction("Index");
        }
        [HttpGet]
        public async Task<IActionResult> fx(int CID)
        {
            return Json(await dal.Gets(CID));
        }

    }
}
--
Index View :

@model IEnumerable<EMP>

@{
    ViewData["Title"] = "Index";
}


<p>
    <a asp-action="Create" class="btn btn-primary">Add New</a>
</p>
<table class="table table-bordered table-hover table-striped">
    <thead class="bg bg-primary">
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.EID)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.NAME)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.GENDER)
            </th>
            <th>ACTION</th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.EID)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.NAME)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.GENDER)
            </td>
            <td>
                @Html.ActionLink("Edit", "Edit", new { id=item.EID }) |
                @Html.ActionLink("Delete", "Delete", new {  id=item.EID })
            </td>
        </tr>
}
    </tbody>
</table>
--
Create View :

@model EMPVM

@{
    ViewData["Title"] = "Create";
}


<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" />
                <input asp-for="EID" type="hidden" />
                <span asp-validation-for="NAME" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="GENDER" class="control-label"></label>
                <input type="radio" asp-for="GENDER" value="Male" />Male
                <input type="radio" asp-for="GENDER" value="Female" />Female
                <span asp-validation-for="GENDER" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="COUNTRY" class="control-label"></label>
                <select asp-for="COUNTRY" class="form-control" asp-items="Model.LCOUNTRY">
                    <option value="" >Select</option>
                </select>
                <span asp-validation-for="COUNTRY" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="STATE" class="control-label"></label>
                <select asp-for="STATE" class="form-control" asp-items="Model.LSTATE">
                    <option value="" >Select</option>
                </select>
                <span asp-validation-for="STATE" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="HOBBY" class="control-label"></label>
                <select asp-for="HOBBY" multiple class="form-control" asp-items="Model.LHOBBY">
                </select>
                <span asp-validation-for="HOBBY" 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");}
<script type="text/javascript">
    $(function () {
        $('#COUNTRY').change(function () {
            $('#STATE').empty();
            $('#STATE').append("<option>Select</option>");
            $.ajax({
                url: '@Url.Action("fx","Emps")',
                 type: 'GET',
                dataType: 'json',
                contentType: 'application/json; charset=utf-8',
                data: { CID: $(this).val() },
                success: function (data) {
                    $.each(data, function (i, v) {
                        $('#STATE').append("<option value='"+v.SID+"'>"+v.SNAME+"</option>");
                    })
                }
            })
        });
    });
</script>
}

No comments:

Post a Comment