Dot Net By Vickypedia: CRUD Operations In .NET Core 3.0 With Visual Studio 2019

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.cs

using 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.cs

using 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.cs

using 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.cs
using 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.cs
using 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.cs
using 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.cs
using 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 View












Download the source code from below link.
https://github.com/vijaykumarvicky/DotNetCoreApplication

Dot Net By Vickypedia

Sunday, 27 September 2020

CRUD Operations In .NET Core 3.0 With Visual Studio 2019

CRUD Operations In .NET Core 3.0 With Visual Studio 2019

Index.cshtml

Factorial of a Number

Recently Viewed