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
)
as
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
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.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";
}
<style type="text/css">
.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 class="row">
<div class="col-md-4">
<form asp-action="Index" asp-antiforgery="true">
<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="ADDRESS" class="control-label"></label>
<input asp-for="ADDRESS" class="form-control" />
<span asp-validation-for="ADDRESS" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="GENDER" class="control-label"></label>
<input asp-for="GENDER" type="radio" value="Male" />Male
<input asp-for="GENDER" type="radio" value="Female" />Female
<span asp-validation-for="GENDER" class="text-danger"></span>
</div>
<div class="form-group">
<input type="submit" value="Create" style="width:80px" class="btn btn-primary" />
<input type="reset" value="Reset" style="width:80px" class="btn btn-primary" />
</div>
</form>
</div>
</div>
<div class="row">
<partial name="ListPV" model="Model.LTEST" />
</div>
<partial name="ConfirmPV" />
@{ if (Model.MESSAGE != "")
{
<partial name="AlertPV" model="new Alert() { Message=Model.MESSAGE }" />
}
}
@section Scripts {
@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}
ListPV Partilaview :
@model IEnumerable<TEST>
<table class="table table-bordered table-hover">
<thead class="xxx">
<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>
