DOTNET POINT: CRUD operations using CORE(3.1) MVC,AutoMapper,Repository, Dependency Injection, EF core code first approach & implementing store procedure in EF core.

Dot net core :

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.Linq;

using System.Threading.Tasks;

namespace USINGSP.Models

{

public class TEST

{

[Key]

public int EID { get; set; }

[MaxLength(50)]

public string NAME { get; set; }

public string ADDRESS { get; set; }

[MaxLength(50)]

public string GENDER { get; set; }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

using Microsoft.EntityFrameworkCore;

namespace USINGSP.Models

{

public class AppDbContext:DbContext

{

public AppDbContext(DbContextOptions<AppDbContext> options):base(options)

{

}

public DbSet<TEST> Tests { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

{

optionsBuilder.UseSqlServer(@"Data Source=(localdb)\MSSQLLocalDB;database=SankarDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");

}

Add a migration using below command.

Add-Migration 3rd

Write ur store procedure in the 3rd.cs file. The file looks like.

using Microsoft.EntityFrameworkCore.Migrations;

namespace USINGSP.Migrations

{

public partial class _3rd : Migration

{

protected override void Up(MigrationBuilder migrationBuilder)

{

var sp = @"CREATE procedure dbo.SP_DML1

(

@TID int=null,

@NAME nvarchar(50)=null,

@ADDRESS nvarchar(max)=null,

@GENDER nvarchar(50)=null,

@MARK int

)

begin

begin Try

if @MARK=1

begin

insert into Tests(NAME,ADDRESS,GENDER) values(@NAME,@ADDRESS,@GENDER)

end

else if @MARK=2

begin

update Tests set NAME=@NAME,ADDRESS=@ADDRESS,GENDER=@GENDER where EID=@TID

end

else if @MARK=3

begin

delete from Tests where EID=@TID

end

end Try

begin catch

SELECT

ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

end catch

end";

migrationBuilder.Sql(sp);

}

protected override void Down(MigrationBuilder migrationBuilder)

{

}

}
after that you must use below command.
update-database

---

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

namespace USINGSP.Models
{
public class TESTVM
{
public TESTVM()
{
LTEST = new List<TEST>();
}
public int EID { get; set; }
[Required(ErrorMessage ="Name should not be blank.")]
public string NAME { get; set; }
[Required(ErrorMessage = "Address should not be blank.")]
[DataType(DataType.MultilineText)]
public string ADDRESS { get; set; }
[Required(ErrorMessage = "Gender should not be blank.")]
public string GENDER { get; set; }
public string MESSAGE { get; set; }
public List<TEST> LTEST { get; set; }
}
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

namespace USINGSP.Models

{

public interface IRepository

{

Task<IEnumerable<TEST>> Gets();

Task<TEST> Get(int EID);

Task<TEST> Save(TEST test);

Task<TEST> Update(TEST test);

Task<TEST> Delete(int EID);

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

using Microsoft.EntityFrameworkCore;

namespace USINGSP.Models

{

public class Repository : IRepository

{

#region

private readonly AppDbContext context;

#endregion

public Repository(AppDbContext context)

{

this.context = context;

}

public async Task<TEST> Delete(int EID)

{

await context.Database.ExecuteSqlRawAsync($"EXEC dbo.SP_DML1 @TID={EID},@MARK={3}");

return await context.Tests.FirstOrDefaultAsync();

}

public async Task<TEST> Get(int EID)

{

return await context.Tests.FromSqlRaw($"select * from Tests where EID={EID}").FirstAsync();

}

public async Task<IEnumerable<TEST>> Gets()

{

return await context.Tests.FromSqlRaw("select * from Tests").ToListAsync();

}

public async Task<TEST> Save(TEST test)

{

await context.Database.ExecuteSqlRawAsync($"EXEC dbo.SP_DML1 @NAME={test.NAME},@ADDRESS={test.ADDRESS},@GENDER={test.GENDER},@MARK={1}");

return await context.Tests.FirstOrDefaultAsync();

}

public async Task<TEST> Update(TEST test)

{

await context.Database.ExecuteSqlRawAsync($"exec dbo.SP_DML1 @TID={test.EID},@NAME={test.NAME},@ADDRESS={test.ADDRESS},@GENDER={test.GENDER},@MARK={2}");

return await context.Tests.FirstOrDefaultAsync();

}

using AutoMapper;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

namespace USINGSP.Models

{

public class MappingProfile:Profile

{

public MappingProfile()

{

CreateMap<TEST, TESTVM>();

CreateMap<TESTVM, TEST>();

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

using AutoMapper;

using Microsoft.AspNetCore.Builder;

using Microsoft.AspNetCore.Hosting;

using Microsoft.AspNetCore.HttpsPolicy;

using Microsoft.Extensions.Configuration;

using Microsoft.Extensions.DependencyInjection;

using Microsoft.Extensions.Hosting;

using USINGSP.Models;

namespace USINGSP

{

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.AddDbContext<AppDbContext>();

services.AddScoped<IRepository, Repository>();

services.AddAutoMapper(typeof(MappingProfile));

}

// 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");

// 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.UseEndpoints(endpoints =>

{

endpoints.MapControllerRoute(

name: "default",

pattern: "{controller=Home}/{action=Index}/{id?}");

});

}

Controller :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Threading.Tasks;

using AutoMapper;

using Microsoft.AspNetCore.Mvc;

using USINGSP.Models;

namespace USINGSP.Controllers

{

public class EmpController : Controller

{

#region

private readonly IRepository repository;

private readonly IMapper mapper;

private static string message;

#endregion

public EmpController(IRepository repository, IMapper mapper)

{

this.repository = repository;

this.mapper = mapper;

}

[HttpGet]

public async Task<IActionResult> Index(int? id)

{

TESTVM tESTVM = new TESTVM();

tESTVM.LTEST = (List<TEST>)await repository.Gets();

if (id > 0)

{

TEST test = await repository.Get(id.Value);

mapper.Map(test, tESTVM);

}

if (!string.IsNullOrEmpty(message))

{

tESTVM.MESSAGE = message;

message = "";

}

else

tESTVM.MESSAGE = "";

return View(tESTVM);

}

[HttpPost]

[ValidateAntiForgeryToken]

public async Task<IActionResult> Index(TESTVM tESTVM)

{

try

{

TEST test = new TEST();

if (ModelState.IsValid)

{

mapper.Map(tESTVM, test);

if (tESTVM.EID > 0)

{

await repository.Update(test);

message = "Data Updated Successfully.";

}

else

{

await repository.Save(test);

message = "Data Saved Successfully.";

}

return RedirectToAction("Index", new { id = 0 });

}

else

return View(tESTVM);

}

catch (Exception ex)

{

message = ex.Message;

throw;

}

[HttpGet]

public async Task<IActionResult> Delete(int id)

{

try

{

await repository.Delete(id);

message = "Data Deleted Successfully.";

return RedirectToAction("Index");

}

catch (Exception ex)

{

message = ex.Message;

throw;

}

Index View :

@model USINGSP.Models.TESTVM

ViewData["Title"] = "Index";

}

.xxx {

background-color: #0069d9;

border-color: #0062cc;

color: white

}

.modal-header {

display: block !important;

}

.modal-title {

float: left;

}

.modal-header .close {

float: right;

}

</style>

</div>

</div>

<input asp-for="GENDER" type="radio" value="Male" />Male

<input asp-for="GENDER" type="radio" value="Female" />Female

</div>

</div>

</form>

</div>

</div>

@{ if (Model.MESSAGE != "")

{

}

@section Scripts {

@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}

}

ListPV Partilaview :

@model IEnumerable<TEST>

<tr>

<th>

@Html.DisplayNameFor(model => model.EID)

</th>

<th>

@Html.DisplayNameFor(model => model.NAME)

</th>

<th>

@Html.DisplayNameFor(model => model.ADDRESS)

</th>

<th>

@Html.DisplayNameFor(model => model.GENDER)

</th>

<th>ACTION</th>

</tr>

</thead>

<tbody>

if (Model.Count() > 0)

{

@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.ADDRESS)

</td>

<td>

@Html.DisplayFor(modelItem => item.GENDER)

</td>

<td>

@Html.ActionLink("Edit", "Index", new { id = item.EID }) |

<a href="#" onclick="confirmDelete('@Url.Content("~/Emp/Delete/" + item.EID)')">Delete</a>

</td>

</tr>

}

else

{

<tr><td colspan="5" class="text-danger text-center">No Records Found.</td></tr>

}

</tbody>

</table>

DOTNET POINT

Friday, 22 May 2020

CRUD operations using CORE(3.1) MVC,AutoMapper,Repository, Dependency Injection, EF core code first approach & implementing store procedure in EF core.

No comments:

Post a Comment