- Create the new folder for Context, Entities, Interfaces, Models, Services and Utility
- Create all the classes as you see in below image.
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);
}
}
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()
{
}
}
}
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