CRUD Operations In .NET Core 3.0 With Visual Studio 2019
Follow the simple steps
Step-1
- Open visual studio >> New Project >> Select "ASP.NET Core Web Application" >> Go to Next >>"Enter Project Name". e.g. DotNetCoreApplication.
- Install NuGet packages as below.
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.SqlServer.Design
- Microsoft.EntityFrameworkCore.Tools
- Microsoft.EntityFrameworkCore.Tools
Step-2
- Create the new folder for Context, Entities, Interfaces, Models, Services and Utility
- Create all the classes as you see in below image.
IDapperHelper.cs
using Dapper; using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Linq; using System.Threading.Tasks; namespace DotNetCoreApplication.Interfaces { public interface IDapperHelper : IDisposable { DbConnection GetConnection(); T Get<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure); List<T> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure); int Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure); T Insert<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure); T Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure); } }
DapperHelper.csusing Dapper; using DotNetCoreApplication.Interfaces; using Microsoft.Data.SqlClient; using Microsoft.Extensions.Configuration; using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Linq; using System.Threading.Tasks; namespace DotNetCoreApplication.Utility { public class DapperHelper : IDapperHelper { private readonly IConfiguration _config; public DapperHelper(IConfiguration config) { _config = config; } public DbConnection GetConnection() { return new SqlConnection(_config.GetConnectionString("DBSqlConnection")); } public T Get<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure) { using (IDbConnection db = new SqlConnection(_config.GetConnectionString("DBSqlConnection"))) { return db.Query<T>(sp, parms, commandType: commandType).FirstOrDefault(); } } public List<T> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure) { using (IDbConnection db = new SqlConnection(_config.GetConnectionString("DBSqlConnection"))) { return db.Query<T>(sp, parms, commandType: commandType).ToList(); } } public int Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure) { using (IDbConnection db = new SqlConnection(_config.GetConnectionString("DBSqlConnection"))) { return db.Execute(sp, parms, commandType: commandType); } } public T Insert<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure) { T result; using (IDbConnection db = new SqlConnection(_config.GetConnectionString("DBSqlConnection"))) { try { if (db.State == ConnectionState.Closed) db.Open(); using (var tran = db.BeginTransaction()) { try { result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault(); tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw ex; } } } catch (Exception ex) { throw ex; } finally { if (db.State == ConnectionState.Open) db.Close(); } return result; } } public T Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure) { T result; using (IDbConnection db = new SqlConnection(_config.GetConnectionString("DBSqlConnection"))) { try { if (db.State == ConnectionState.Closed) db.Open(); using (var tran = db.BeginTransaction()) { try { result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault(); tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw ex; } } } catch (Exception ex) { throw ex; } finally { if (db.State == ConnectionState.Open) db.Close(); } return result; } } public void Dispose() { } } }
Employee.csusing System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Linq; using System.Threading.Tasks; namespace DotNetCoreApplication.Entities { public class Employee { [Key] public int EmployeeId { get; set; } public string EmployeeName { get; set; } public string EmployeeCode { get; set; } public string ContactNo { get; set; } public string CompanyName { get; set; } public DateTime CreatedOn { get; set; } } }
EmployeeModel.csusing System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Linq; using System.Threading.Tasks; namespace DotNetCoreApplication.Models { public class EmployeeModel { public int EmployeeId { get; set; } [Display(Name = "Employee Name")] public string EmployeeName { get; set; } [Display(Name = "Employee Code")] public string EmployeeCode { get; set; } [Display(Name = "Contact No")] public string ContactNo { get; set; } [Display(Name = "Company Name")] public string CompanyName { get; set; } [Display(Name = "Created On")] public DateTime CreatedOn { get; set; } = DateTime.Now; } }IEmployeeService.csusing DotNetCoreApplication.Entities; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace DotNetCoreApplication.Interfaces { public interface IEmployeeService : IDisposable { int Delete(int EmployeeId); Employee GetEmployeeById(int EmployeeId); string Update(Employee employee); int Create(Employee employee); List<Employee> GetAll(); } }EmployeeService.csusing Dapper; using DotNetCoreApplication.Entities; using DotNetCoreApplication.Interfaces; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Threading.Tasks; namespace DotNetCoreApplication.Services { public class EmployeeService : IEmployeeService { private readonly IDapperHelper _dapperHelper; public EmployeeService(IDapperHelper dapperHelper) { this._dapperHelper = dapperHelper; } /// <summary> /// This method is used for save the employee details /// </summary> /// <param name="employee"></param> /// <returns>employeeId</returns> public int Create(Employee employee) { var dbParameter = new DynamicParameters(); dbParameter.Add("EmployeeName", employee.EmployeeName, DbType.String); dbParameter.Add("EmployeeCode", employee.EmployeeCode, DbType.String); dbParameter.Add("ContactNo", employee.ContactNo, DbType.String); dbParameter.Add("CompanyName", employee.CompanyName, DbType.String); dbParameter.Add("CreatedOn", employee.CreatedOn, DbType.DateTime); var result = _dapperHelper.Insert<int>("SP_Employee_Create", dbParameter, commandType: CommandType.StoredProcedure); return result; } /// <summary> /// This method is used for delete the employee details /// </summary> /// <param name="EmployeeId"></param> /// <returns></returns> public int Delete(int EmployeeId) { var data = _dapperHelper.Execute($"Delete [Employee] where EmployeeId={EmployeeId}", null, commandType: CommandType.Text); return data; } public void Dispose() { } /// <summary> /// This method is used for get all employees /// </summary> /// <returns>employees</returns> public List<Employee> GetAll() { var employees = _dapperHelper.GetAll<Employee>("SP_Employee_GetAll", null, commandType: CommandType.StoredProcedure); return employees.ToList(); } /// <summary> /// This method is used for get employee details by id /// </summary> /// <param name="EmployeeId"></param> /// <returns></returns> public Employee GetEmployeeById(int EmployeeId) { var dbParameter = new DynamicParameters(); dbParameter.Add("EmployeeId", EmployeeId, DbType.String); var result = _dapperHelper.Get<Employee>("SP_Employee_GetbyId", dbParameter, commandType: CommandType.StoredProcedure); return result; } /// <summary> /// This method is used for update employee details /// </summary> /// <param name="employee"></param> /// <returns></returns> public string Update(Employee employee) { var dbParameter = new DynamicParameters(); dbParameter.Add("EmployeeId", employee.EmployeeId, DbType.Int32); dbParameter.Add("EmployeeName", employee.EmployeeName, DbType.String); dbParameter.Add("EmployeeCode", employee.EmployeeCode, DbType.String); dbParameter.Add("ContactNo", employee.ContactNo, DbType.String); dbParameter.Add("CompanyName", employee.CompanyName, DbType.String); var result = _dapperHelper.Update<string>("SP_Employee_Update", dbParameter, commandType: CommandType.StoredProcedure); return result; } } }DataContext.csusing Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace DotNetCoreApplication.Context { public class DataContext : DbContext { public DataContext() { } public DataContext(DbContextOptions<DataContext> options) : base(options) { } } }EmployeeController.csusing System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using DotNetCoreApplication.Entities; using DotNetCoreApplication.Interfaces; using DotNetCoreApplication.Models; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.Mvc; namespace DotNetCoreApplication.Controllers { public class EmployeeController : Controller { private readonly IEmployeeService _employeeManager; private readonly IHostingEnvironment _hostingEnvironment; public EmployeeController(IEmployeeService employeeManager, IHostingEnvironment hostingEnvironment) { _employeeManager = employeeManager; _hostingEnvironment = hostingEnvironment; } /// <summary> /// This action method is used for show employee details /// </summary> /// <returns></returns> public IActionResult Index() { var data = _employeeManager.GetAll(); return View(data); } /// <summary> /// This action method is used for open employee form /// </summary> /// <returns></returns> public ActionResult Add() { return View("FormEmployee", new EmployeeModel()); } /// <summary> /// This action method is used for save employee details /// </summary> /// <param name="model"></param> /// <returns></returns> [HttpPost] [ValidateAntiForgeryToken] public ActionResult Add(EmployeeModel model) { if (ModelState.IsValid) { var employee = new Employee() { EmployeeCode = model.EmployeeCode, EmployeeName = model.EmployeeName, CompanyName = model.CompanyName, ContactNo = model.ContactNo, CreatedOn = DateTime.Now }; _employeeManager.Create(employee); return RedirectToAction("Index", "Employee"); } return View("FormEmployee", model); } #region Edit Employee /// <summary> /// This action method is used for open view for update employee details /// </summary> /// <param name="EmployeeId"></param> /// <returns></returns> public ActionResult Edit(int EmployeeId) { var employee = _employeeManager.GetEmployeeById(EmployeeId); var employeeModel = new EmployeeModel { EmployeeCode = employee.EmployeeCode, EmployeeName = employee.EmployeeName, CompanyName = employee.CompanyName, ContactNo = employee.ContactNo, CreatedOn = employee.CreatedOn, EmployeeId = employee.EmployeeId }; return View("FormEmployee", employeeModel); } /// <summary> /// This action method is used for edit the employee details /// </summary> /// <param name="model"></param> /// <returns></returns> [HttpPost] [ValidateAntiForgeryToken] public ActionResult Edit(EmployeeModel model) { if (ModelState.IsValid) { var employee = new Employee() { EmployeeCode = model.EmployeeCode, EmployeeName = model.EmployeeName, CompanyName = model.CompanyName, ContactNo = model.ContactNo, EmployeeId = model.EmployeeId }; _employeeManager.Update(employee); return RedirectToAction("Index", "Employee"); } return View("FormEmployee", model); } #endregion #region Delete Employee /// <summary> /// This action method is used for delete the employee details /// </summary> /// <param name="EmployeeId"></param> /// <returns></returns> public ActionResult Delete(int EmployeeId) { var result = _employeeManager.Delete(EmployeeId); return RedirectToAction("Index", "Employee"); } #endregion } }FormEmployee.cshtml@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers @model DotNetCoreApplication.Models.EmployeeModel @{ ViewData["Title"] = "Employee"; Layout = "~/Views/Shared/_Layout.cshtml"; } <h1>Employee</h1> <div class="row"> <div class="col-md-12"> <div class="box box-primary"> @using (Html.BeginForm(Model.EmployeeId == 0 ? "Add" : "Edit", "Employee", FormMethod.Post, new { enctype = "multipart/form-data" })) { <div class="box-body"> @Html.AntiForgeryToken() <div class="row"> @Html.HiddenFor(model => model.EmployeeId) <div class="col-xs-12" f> <label>Employee Name</label> @Html.TextBoxFor(model => model.EmployeeName, new { @class = "form-control", @placeholder = "Employee Name" }) </div> </div> <br /> <div class="row"> <div class="col-xs-12"> <label>Employee Code</label> @Html.TextBoxFor(model => model.EmployeeCode, new { @class = "form-control" }) </div> </div> <br /> <div class="row"> <div class="col-xs-12"> <label>Contact No</label> @Html.TextBoxFor(model => model.ContactNo, new { @class = "form-control" }) </div> </div> <br /> <div class="row"> <div class="col-xs-12"> <label>Company Name</label> @Html.TextBoxFor(model => model.CompanyName, new { @class = "form-control" }) </div> </div> </div> <div class="box-footer"> <button type="submit" class="btn btn-primary"><i class="fa fa-save"></i> Save</button> </div> } </div> </div> </div>Index.cshtml
@model List<DotNetCoreApplication.Entities.Employee> @{ ViewData["Title"] = "Index"; Layout = "~/Views/Shared/_Layout.cshtml"; } <h1>Employee</h1> <br /> <a href="/Employee/Add" class="btn btn-primary">Add</a> <br /> <table id="tblEmployee" class="table"> <thead> <tr> <th class="col-lg-2">Employee Name</th> <th class="col-lg-2">Employee Code</th> <th class="col-lg-2">Contact No</th> <th class="col-lg-2">Company Name</th> <th class="col-lg-2">Created Date</th> <th class="col-lg-2">Action</th> </tr> </thead> <tbody> @foreach (var employee in Model) { <tr> <td class="col-lg-2">@employee.EmployeeName</td> <td class="col-lg-2">@employee.EmployeeCode</td> <td class="col-lg-2">@employee.ContactNo</td> <td class="col-lg-2">@employee.CompanyName</td> <td class="col-lg-2">@employee.CreatedOn</td> <td class="col-lg-2 text-center"> <a href="/Employee/Edit?EmployeeId=@employee.EmployeeId" title="Edit">Edit <i class="fa fa-edit"></i></a><a href="/Employee/Delete?&EmployeeId=@employee.EmployeeId" class="" onclick="return confirm(" Are you sure to delete this employee?");" title="Delete">Delete<i class="fa fa-times"></i></a> </td> </tr> } </tbody> </table>Configure the connection string in appsettings.json, see in below image.Register the interface & connection string into startup.cs//Configure Connection String services.AddDbContext<DataContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DBSqlConnection"))); //Employee service services.AddScoped<IEmployeeService, EmployeeService>(); //Register dapper in scope services.AddScoped<IDapperHelper, DapperHelper>();Employee List view
Add & Edit ViewDownload the source code from below link.https://github.com/vijaykumarvicky/DotNetCoreApplication
