In this article, we’ll learn how to perform CRUD operations with .NET Core 3.0 and Visual Studio 2019. We will use a dapper to perform CRUD operations.
Recommended Prerequisites
- .NET Core 3.0 (Download from here)
- Visual Studio 2019. (Download from here)
Create a Database, Tables, and Stored Procedures.
First, we need to create a SQL Server database, tables, and stored procedure which we need to use in the application.
Here, I’m creating a database, “CoreMaster” and a table “Jobs”.
CREATE TABLE [dbo].[Job](
[JobID] [int] IDENTITY(1,1) NOT NULL,
[JobTitle] [nchar](250) NULL,
[JobImage] [nvarchar](max) NULL,
[CityId] [int] NULL,
[IsActive] [bit] NULL,
[CreatedBY] [nvarchar](50) NULL,
[CreatedDateTime] [datetime] NULL,
[UpdatedBY] [nvarchar](50) NULL,
[UpdatedDateTime] [datetime] NULL,
CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED
(
[JobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Now, I’m going to create stored procedures for adding jobs, fetching job lists, and updating jobs.
The “Add Job” stored procedure is named “[SP_Add_Job]” which returns the inserted record’s job Id.
CREATE PROCEDURE [dbo].[SP_Add_Job]
@JobTitle NVARCHAR(250) ,
@JobImage NVARCHAR(Max) ,
@CityId int ,
@IsActive BIT ,
@CreatedBY NVARCHAR(50) ,
@CreatedDateTime DATETIME ,
@UpdatedBY NVARCHAR(50),
@UpdatedDateTime DATETIME
AS
BEGIN
DECLARE @JobId as BIGINT
INSERT INTO [Job]
(JobTitle ,
JobImage ,
CityId ,
IsActive ,
CreatedBY ,
CreatedDateTime ,
UpdatedBY ,
UpdatedDateTime
)
VALUES ( @JobTitle ,
@JobImage ,
@CityId ,
@IsActive ,
@CreatedBY ,
@CreatedDateTime ,
@UpdatedBY ,
@UpdatedDateTime
);
SET @JobId = SCOPE_IDENTITY();
SELECT @JobId AS JobId;
END;
The “Update job” stored procedure is “[SP_Update_Job]”
CREATE PROCEDURE [dbo].[SP_Update_Job]
@JobId INT,
@JobTitle NVARCHAR(250) ,
@JobImage NVARCHAR(Max) ,
@CityId INT ,
@IsActive BIT ,
@UpdatedBY NVARCHAR(50),
@UpdatedDateTime DATETIME
AS
BEGIN
UPDATE job
SET
job.JobTitle = @JobTitle,
job.JobImage = @JobImage ,
job.CityId = @CityId ,
job.IsActive = @IsActive ,
job.UpdatedBY = @UpdatedBY ,
job.UpdatedDateTime = @UpdatedDateTime
FROM [Job] job
WHERE JobId = @JobId
END;
The “Fetch job list” store procedure is [SP_Job_List].
CREATE PROCEDURE [dbo].[SP_Job_List]
AS
BEGIN
SET NOCOUNT ON;
select * from [Job]
END
Open Visual Studio 2019
Go to the Start menu on your Windows desktop and type Visual studio 2019; open it.
Create a new project
Click on the “ASP.NET Core Web Application” and press “Next”.
From the wizard, select “Web Application
(Model-View-Controller)”. The framework must be selected as .NET Core 3.0.
Then, click on the “OK” button.
Put an appropriate project name and select the location where
you want to create this project. Again, click the “Create” button.
Now “Build” the project. It will install .NET Core 3.0 runtime
(if it not installed on the machine).
Install NuGet packages.
We need to install the below packages.
1.
Microsoft.EntityFrameworkCore.SqlServer
2. Microsoft.EntityFrameworkCore.SqlServer.Design
3. Microsoft.EntityFrameworkCore.Tools
4. Dapper
Create Dapper Class and Interface
Now, create two folders –
1.
Helper
2.
Interface
In the Interface folder, add a new interface namely
“IDapperHelper” and copy the below code and paste in that class.
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
namespace CoreDemo_3_0.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);
}
}
In the Helper folder, add a new class namely “DapperHelper” and copy the below code and paste in that class.
using System;
using System.Collections.Generic;
using System.Linq;
using Dapper;
using Microsoft.Extensions.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq.Expressions;
using static Dapper.SqlMapper;
using CoreDemo_3_0.Interfaces;
namespace CoreDemo_3_0.Helper
{
public class DapperHelper : IDapperHelper
{
private readonly IConfiguration _config;
public DapperHelper(IConfiguration config)
{
_config = config;
}
public DbConnection GetConnection()
{
return new SqlConnection(_config.GetConnectionString("DefaultConnection"));
}
public T Get<T>(string sp, DynamicParameters
parms, CommandType commandType = CommandType.StoredProcedure)
{
using (IDbConnection db = new SqlConnection(_config.GetConnectionString("DefaultConnection")))
{
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("DefaultConnection")))
{
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("DefaultConnection")))
{
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("DefaultConnection")))
{
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("DefaultConnection")))
{
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()
{
throw new NotImplementedException();
}
}
}
Here, we created a Dapper helper which we will use to
communicate with the database.
Create a Context class
Create a new folder named “Context” and add one class
“DataContext” which extends from the DbContext class.
Copy the below code and paste inside the “DataContext” class.
using CoreDemo_3_0.Entities;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace CoreDemo_3_0.Context
{
public class DataContext : DbContext
{
public DataContext() { }
public DataContext(DbContextOptions<DataContext> options) : base(options) { }
}
}
Create one folder named “Entities” and add a class, namely “Job”.
Copy the below code and paste into the “Job” class.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace CoreDemo_3_0.Entities
{
public class Job
{
[Key]
public int JobID { get; set; }
public string JobTitle { get; set; }
public int CityId { get; set; }
public string JobImage { get; set; }
public bool IsActive { get; set; }
public string CreatedBY { get; set; }
public DateTime? CreatedDateTime { get; set; }
public string UpdatedBY { get; set; }
public DateTime? UpdatedDateTime { get; set; }
}
}
Create one folder named “Models” and add a class “JobModel”.
Copy the below code and paste into the “JobModel” class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace CoreDemo_3_0.Models
{
public class JobModel
{
public int JobID { get; set; }
public string JobTitle { get; set; }
public int CityId { get; set; }
public string JobImage { get; set; }
public bool IsActive { get; set; }
public string CreatedBY { get; set; }
public DateTime? CreatedDateTime { get; set; }
public string UpdatedBY { get; set; }
public DateTime? UpdatedDateTime { get; set; }
public int JobState { get; set; }
}
}
Add IJob interface
Add IJob interface, “IJobService” inside the interface folder
which we created before. Below is the code of the interface.
using CoreDemo_3_0.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace CoreDemo_3_0.Interfaces
{
public interface IJobService
{
int Delete(int JobId);
Job GetByJobId(int JobId);
string Update(Job job);
int Create(Job JobDetails);
List<Job> ListAll();
}
}
Add Job service
Add a new folder named “Services” and one class inside that
folder. The class name will be “JobService”. Below is the JobService class
code.
using CoreDemo_3_0.Entities;
using CoreDemo_3_0.Interfaces;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using System.Transactions;
namespace CoreDemo_3_0.Services
{
public class JobService : IJobService
{
private readonly IDapperHelper _dapperHelper;
public JobService(IDapperHelper dapperHelper)
{
this._dapperHelper = dapperHelper;
}
public int Create(Job job)
{
var dbPara = new DynamicParameters();
dbPara.Add("JobTitle", job.JobTitle, DbType.String);
dbPara.Add("JobImage", job.JobImage, DbType.String);
dbPara.Add("CityId", job.CityId, DbType.Int32);
dbPara.Add("IsActive", job.IsActive, DbType.String);
dbPara.Add("CreatedBY", "1", DbType.String);
dbPara.Add("CreatedDateTime", DateTime.Now, DbType.DateTime);
dbPara.Add("UpdatedBY", "1", DbType.String);
dbPara.Add("UpdatedDateTime", DateTime.Now, DbType.DateTime);
#region using dapper
var data = _dapperHelper.Insert<int>("[dbo].[SP_Add_Job]",
dbPara,
commandType: CommandType.StoredProcedure);
return data;
#endregion
}
public Job GetByJobId(int JobId)
{
#region using dapper
var data = _dapperHelper.Get<Job>($"select * from job where
JobId={JobId}", null,
commandType: CommandType.Text);
return data;
#endregion
}
public int Delete(int JobId)
{
var data = _dapperHelper.Execute($"Delete [Job] where
JObId={JobId}", null,
commandType: CommandType.Text);
return data;
}
public List<Job> ListAll()
{
var data = _dapperHelper.GetAll<Job>("[dbo].[SP_Job_List]", null, commandType: CommandType.StoredProcedure);
return data.ToList();
}
public string Update(Job job)
{
var dbPara = new DynamicParameters();
dbPara.Add("JobTitle", job.JobTitle, DbType.String);
dbPara.Add("JobId", job.JobID);
dbPara.Add("JobImage", job.JobImage, DbType.String);
dbPara.Add("CityId", job.CityId, DbType.Int32);
dbPara.Add("IsActive", job.IsActive, DbType.String);
dbPara.Add("UpdatedBY", "1", DbType.String);
dbPara.Add("UpdatedDateTime", DateTime.Now, DbType.DateTime);
var data = _dapperHelper.Update<string>("[dbo].[SP_Update_Job]",
dbPara,
commandType: CommandType.StoredProcedure);
return data;
}
}
}
Add Connection String
Add a connection string into the appsettings.json file.
Here is the code of the appsettings.json file.
{
"ConnectionStrings": {
"DefaultConnection": "data
source=TheCodeHubs-PC;initial catalog=CoreMaster;User
Id=sa;Password=******;"
},
"Logging": {
"LogLevel": {
"Default": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*"
}
Changes in Startup class
Now, we need to add a connection string context and
register our services.
Code to add connection string –
services.AddDbContext<DataContext>(options =>
options.UseSqlServer(
Configuration.GetConnectionString("DefaultConnection")));
Add a service to scoped.
//Job service
services.AddScoped<IJobService, JobService>();
//Register dapper in scope
services.AddScoped<IDapperHelper, DapperHelper>();
Add Controller
It’s now time to add a Job Controller inside the Controller
folder. Below is the full code of JobController.
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net.Http.Headers;
using System.Threading.Tasks;
using CoreDemo_3_0.Entities;
using CoreDemo_3_0.Interfaces;
using CoreDemo_3_0.Models;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
namespace CoreDemo_3_0.Controllers
{
public class JobController : Controller
{
private readonly IJobService _jobManager;
private readonly IHostingEnvironment _hostingEnvironment;
public JobController(IJobService jobManager,
IHostingEnvironment hostingEnvironment)
{
_jobManager = jobManager;
_hostingEnvironment =
hostingEnvironment;
}
public ActionResult Index()
{
var data = _jobManager.ListAll();
string baseUrl = $"{this.Request.Scheme}://{this.Request.Host}{this.Request.PathBase}";
foreach (var item in data)
{
if (!string.IsNullOrEmpty(item.JobImage))
item.JobImage = Path.Combine(baseUrl, "Images", item.JobImage);
else
item.JobImage = Path.Combine(baseUrl, "Images", "404.png");
}
return View(data);
}
#region Add Job
public ActionResult Add()
{
return View("Form", new JobModel());
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Add(JobModel model)
{
if (ModelState.IsValid)
{
var fileName = "";
if (Request.Form.Files.Count > 0)
{
var file = Request.Form.Files[0];
var webRootPath =
_hostingEnvironment.WebRootPath;
var newPath = Path.Combine(webRootPath, "images");
if (!Directory.Exists(newPath)) Directory.CreateDirectory(newPath);
if (file.Length > 0)
{
fileName =
ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');
var fullPath = Path.Combine(newPath, fileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
}
}
}
var job = new Job()
{
CityId = model.CityId,
JobImage = fileName,
CreatedBY = "1",
CreatedDateTime = DateTime.Now,
JobTitle = model.JobTitle,
UpdatedBY = "1",
IsActive = model.IsActive,
UpdatedDateTime = DateTime.Now
};
_jobManager.Create(job);
return RedirectToAction("Index", "Job");
}
return View("Form", model);
}
#endregion
#region Edit Job
public ActionResult Edit(int JobId)
{
var jobEntity = _jobManager.GetByJobId(JobId);
var jobModel = new JobModel
{
JobID = jobEntity.JobID,
CityId = jobEntity.CityId,
JobImage = jobEntity.JobImage,
CreatedBY = jobEntity.CreatedBY,
CreatedDateTime = jobEntity.CreatedDateTime,
JobTitle = jobEntity.JobTitle,
UpdatedBY = jobEntity.UpdatedBY,
IsActive = jobEntity.IsActive,
UpdatedDateTime = jobEntity.UpdatedDateTime
};
return View("Form", jobModel);
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit(JobModel model)
{
if (ModelState.IsValid)
{
var fileName = model.JobImage ?? "";
if (Request.Form.Files.Count > 0)
{
var file = Request.Form.Files[0];
var webRootPath =
_hostingEnvironment.WebRootPath;
var newPath = Path.Combine(webRootPath, "images");
if (!Directory.Exists(newPath)) Directory.CreateDirectory(newPath);
if (file.Length > 0)
{
fileName =
ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');
var fullPath = Path.Combine(newPath, fileName);
using (var stream = new FileStream(fullPath, FileMode.Create))
{
file.CopyTo(stream);
}
}
}
var job = new Job()
{
JobID = model.JobID,
CityId = model.CityId,
JobImage = fileName,
JobTitle = model.JobTitle,
UpdatedBY = "1",
IsActive = model.IsActive,
};
_jobManager.Update(job);
return RedirectToAction("Index", "Job");
}
return View("Form", model);
}
#endregion
#region Delete Job
public ActionResult Delete(int JobId)
{
var jobEntity = _jobManager.Delete(JobId);
return RedirectToAction("Index", "Job");
}
#endregion
}
}
Add Views
It is time to add a ViewController inside Views > Job
folder. Below is the code for all views of JobController.
Form.cshtml
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers
@model CoreDemo_3_0.Models.JobModel
@{
ViewData["Title"] = "Add Job";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<div class="row">
<!-- left column -->
<div class="col-md-12">
<!-- general form elements -->
<div class="box box-primary">
<!-- form start -->
@using (Html.BeginForm(Model.JobID == 0 ? "Add" : "Edit", "Job", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div class="box-body">
@Html.AntiForgeryToken()
<div class="row">
@Html.HiddenFor(model => model.JobID)
<div class="col-xs-12" f>
<label>Job Title</label>
@Html.TextBoxFor(model => model.JobTitle, new { @class = "form-control", @placeholder = "Job Title" })
</div>
</div>
<br />
<div class="row">
<div class="col-xs-12">
<label>Enter City ID</label>
@Html.TextBoxFor(model => model.CityId, new { @class = "form-control" })
</div>
</div>
<br />
<div class="row">
<div class="col-xs-12">
<label>Job Image</label>
<input type="file" id="file" name="file">
@Html.HiddenFor(model => model.JobImage)
</div>
</div>
<div class="row">
<div class="col-xs-12">
@Html.CheckBoxFor(model => model.IsActive, new { @class = "col-form-checkbox" }) Vacancy Available
</div>
</div>
</div><!-- /.box-body -->
<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<CoreDemo_3_0.Entities.Job>
@{
ViewData["Title"] = "Job List";
}
<a href="/Job/Add" class="btn btn-primary">Add</a>
<br />
<table id="_DataTable" class="table compact
table-striped table-bordered nowrap dataTable" aria-describedby="_DataTable_info">
<thead>
<tr role="row">
<th class="sorting_asc" role="columnheader" tabindex="0" aria-controls="_DataTable" rowspan="1" colspan="1" aria-sort="ascending" aria-label="Image: activate to sort
column descending" style="width: 127px;">Image</th>
<th class="sorting" role="columnheader" tabindex="0" aria-controls="_DataTable" rowspan="1" colspan="1" aria-label="Title: activate to sort
column ascending" style="width: 209px;">Title</th>
<th class="sorting" role="columnheader" tabindex="0" aria-controls="_DataTable" rowspan="1" colspan="1" aria-label="City: activate to sort
column ascending" style="width: 116px;">City</th>
<th class="sorting" role="columnheader" tabindex="0" aria-controls="_DataTable" rowspan="1" colspan="1" aria-label="Vacancy: activate to sort
column ascending" style="width: 127px;">Vacancy</th>
<th class="sorting" role="columnheader" tabindex="0" aria-controls="_DataTable" rowspan="1" colspan="1" aria-label="Created Date: activate to
sort column ascending" style="width: 190px;">Created Date</th>
<th style="width: 38px;" class="sorting" role="columnheader" tabindex="0" aria-controls="_DataTable" rowspan="1" colspan="1" aria-label=" Action : activate to
sort column ascending"> Action </th>
</tr>
</thead>
<tbody role="alert" aria-live="polite" aria-relevant="all">
@foreach (var item in Model)
{
<tr class="even">
<td style="text-align:left"><img src="@item.JobImage" alt="Image" width="50" height="50"></td>
<td style="text-align:left">@item.JobTitle</td>
<td style="text-align:left">@item.CityId</td>
<td style="text-align:left">@(item.IsActive == true ? "Yes" : "No")</td>
<td style="text-align:right">@item.CreatedDateTime</td>
<td class="text-center ">
<a href="/Job/Edit?JobID=@item.JobID" title="Edit">Edit <i class="fa fa-edit"></i></a><a href="/Job/Delete?&JobID=@item.JobID" class="" onclick="return confirm(" Are you sure you want to
delete this job?");" title="Delete">Delete <i class="fa fa-times"></i></a>
</td>
</tr>
}
</tbody>
</table>
Output
1. 1.Index Page
2. Add Page
3. Edit Page
No comments:
Post a Comment