Introduction
In this tutorial we are going to explain CRUD Operation Using Asp.Net Core Mvc with Repository Pattern. We will be creating a simple employee record management system and performing CRUD operations on it using ado.net.
Can you also learn pervious tutorial : CRUD Operation Using Razor Page (asp.net core) with Ado.Net
Step 1: Creating the Table and Stored Procedures
create a table named “Employee”
CREATE TABLE [dbo].[Employee]( [UserID] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) NULL, [City] [nvarchar](50) NULL, [State] [nvarchar](50) NULL, [Mobile] [nvarchar](50) NULL, [Profile] [nvarchar](50) NULL )
Now, we will create stored procedures to add, delete, update, and get student data.
StoreProcedure for Insert & Update Employee Record
CREATE PROCEDURE [dbo].[Employee_InsertUpdate]
@UserID int=0,
@UserName nvarchar(50),
@City nvarchar(50) ,
@State nvarchar(50) ,
@Mobile nvarchar(50) ,
@Profile nvarchar(50)
AS
IF(@UserID>0)
BEGIN
update Employee set UserName=@UserName,City=@City,State=@State,Mobile=@Mobile,Profile=@Profile where userid=@UserID
END
ELSE
BEGIN
Insert into Employee (UserName,City,State,Mobile,Profile)
VALUES (@UserName,@City,@State,@Mobile,@Profile)
END
Store procedure for Delete a Employee Record
CREATE PROCEDURE [dbo].[Employee_Delete] @UserID int=0 AS IF(@UserID>0) BEGIN delete from Employee where UserID=@UserID END
Store procedure for View all Employee Records
CREATE PROCEDURE [dbo].[EMPLOYEE_SELECT] @UserID int =0 AS if(@UserID>0) begin select * from Employee where UserID=@UserID end else begin select * from Employee end
Step 2 : Create the ASP.NET MVC Web Application
Step 3 : Create model class
After Creating web application we will create a model class named “EmployeeModel” within model folder
public class EmployeeModel
{
public int UserID { get; set; }
public string UserName { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Mobile { get; set; }
public string Profile { get; set; }
}
Step 4 : Now Create one folder and named it repository
For data access layer we have used repository patter, so can you create one folder with named “Repository” and add interface with named “IEmployeeRepository.cs” also create one class with named “EmployeeRepository.cs” this class will implement interface methods.
Interface : IEmployeeRepository.cs
public interface IEmployeeRepository
{
void AddUpdateEmployee(EmployeeModel employee);
List GetAllEmployee();
EmployeeModel GetEmployee(int id);
void DeleteEmployee(int id);
}
Now we will create class named “EmployeeRepository.cs” here in this class we will implement all interface method.
EmployeeRepository.cs
namespace DotNetCoreRepository.Repository
{
public class EmployeeRepository : IEmployeeRepository
{
private readonly IConfiguration configuration;
public EmployeeRepository(IConfiguration configuration)
{
this.configuration = configuration;
}
public void AddUpdateEmployee(EmployeeModel em)
{
using (var con = new SqlConnection(this.Sqlconnection()))
{
try
{
SqlCommand cmd;
con.Open();
cmd = new SqlCommand("Employee_InsertUpdate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID", em.UserID);
cmd.Parameters.AddWithValue("@UserName", em.UserName);
cmd.Parameters.AddWithValue("@City", em.City);
cmd.Parameters.AddWithValue("@State", em.State);
cmd.Parameters.AddWithValue("@Mobile", em.Mobile);
cmd.Parameters.AddWithValue("@Profile", em.Profile);
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception)
{
throw;
}
}
}
public void DeleteEmployee(int id)
{
try
{
using (var con = new SqlConnection(this.Sqlconnection()))
{
SqlCommand cmd;
con.Open();
cmd = new SqlCommand("Employee_Delete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID", id);
cmd.ExecuteNonQuery();
con.Close();
}
}
catch (Exception)
{
throw;
}
}
public List GetAllEmployee()
{
try
{
using (var con = new SqlConnection(this.Sqlconnection()))
{
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter("EMPLOYEE_SELECT", con);
adp.Fill(dt);
List lst = new List();
foreach (DataRow dr in dt.Rows)
{
EmployeeModel em = new EmployeeModel();
em.UserID = Convert.ToInt32(dr["UserID"]);
em.UserName = Convert.ToString(dr["UserName"]);
em.Mobile = Convert.ToString(dr["Mobile"]);
em.Profile = Convert.ToString(dr["Profile"]);
em.State = Convert.ToString(dr["State"]);
em.City = Convert.ToString(dr["City"]);
lst.Add(em);
}
return lst;
}
}
catch (Exception)
{
throw;
}
}
public EmployeeModel GetEmployee(int id)
{
try
{
using (var con = new SqlConnection(this.Sqlconnection()))
{
EmployeeModel em = new EmployeeModel();
con.Open();
SqlCommand cmd = new SqlCommand("EMPLOYEE_SELECT", con);
cmd.Parameters.AddWithValue("UserID", id);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
em.UserID = Convert.ToInt32(dr["UserID"]);
em.UserName = Convert.ToString(dr["UserName"]);
em.Mobile = Convert.ToString(dr["Mobile"]);
em.Profile = Convert.ToString(dr["Profile"]);
em.State = Convert.ToString(dr["State"]);
em.City = Convert.ToString(dr["City"]);
}
con.Close();
return em;
}
}
catch (Exception)
{
throw;
}
}
private string Sqlconnection()
{
return configuration.GetConnectionString("MySqlCon").ToString();
}
}
}
STEP 5: Create Connection String and get it on Repository Class
Now we have to define our database connection string. As we know Asp.Net Core uses appsettings.json to define your app related all configurations. So, we are going to add our connection string inside the appsettings.json file as follows.
Can you also read previous Tutorial : Asp.Net Core: Read Connection String dynamically from AppSettings.json file
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"MySqlCon": "server=Myserver;database=MyCoredb;Trusted_Connection=true;"
}
}
To get this connection string on Repository we have already made code with above repository using “SqlConnection()” method that gives us “Connection String” as following code shown.
private string Sqlconnection()
{
return configuration.GetConnectionString("MySqlCon").ToString();
}
Step 6: Create Razor View for CRUD Operations
Now create Razor view for CRUD operations, so now we will creating below three razor view inside the view folder.
- Index.cshtml
- AddEmployee.cshtml
- EditEmployee.cshtml
Step 7 : Implement Code in Razor view for performing CRUD Operations
Before adding the code for the CRUD operations, you have to register your dependency class in Startup.cs as follows inside the ConfigurService method.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllersWithViews();
services.AddSingleton(Configuration);
services.AddTransient<IEmployeeRepository, EmployeeRepository>();
}
First can you add controller action method for crud operation,
Home Controller :
public class HomeController : Controller
{
private readonly IEmployeeRepository _employeeRepository;
public HomeController(IEmployeeRepository employeeRepository)
{
_employeeRepository = employeeRepository;
}
public IActionResult Index()
{
return View(_employeeRepository.GetAllEmployee());
}
public IActionResult AddEmployee()
{
return View();
}
[HttpPost]
public IActionResult AddEmployee(EmployeeModel em)
{
_employeeRepository.AddUpdateEmployee(em);
return RedirectToAction("Index");
}
public IActionResult EditEmployee(int id = 0)
{
return View(_employeeRepository.GetEmployee(id));
}
[HttpPost]
public IActionResult EditEmployee(EmployeeModel em)
{
_employeeRepository.AddUpdateEmployee(em);
return RedirectToAction("Index");
}
public IActionResult DeleteEmployee(int id=0)
{
_employeeRepository.DeleteEmployee(id);
return RedirectToAction("Index");
}
}
As you have noticed two things with above .cs file that we are using Employee dependency injection as following code shown
private readonly IEmployeeRepository _employeeRepository;
public HomeController(IEmployeeRepository employeeRepository)
{
_employeeRepository = employeeRepository;
}
Now Time has come to write CRUD operation code, so add the following code to corresponding files which will handle the CRUD operations.
AddEmployee.cshtml File
This file is responsible to create UI for adding a new Employee data with required validation.
@model DotNetCoreRepository.Models.EmployeeModel
@{
ViewData[“Title”] = “”;
}
<h2>Add New Employee</h2>
<hr />
<div class=”row”>
<div class=”col-md-4″>
<form asp-action=”AddEmployee”>
<div asp-validation-summary=”ModelOnly” class=”text-danger”></div>
<div class=”form-group”>
<label asp-for=”UserName” class=”control-label”></label>
<input asp-for=”UserName” class=”form-control” />
<span asp-validation-for=”UserName” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”City” class=”control-label”></label>
<input asp-for=”City” class=”form-control” />
<span asp-validation-for=”City” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”State” class=”control-label”></label>
<input asp-for=”State” class=”form-control” />
<span asp-validation-for=”State” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”Mobile” class=”control-label”></label>
<input asp-for=”Mobile” class=”form-control” />
<span asp-validation-for=”Mobile” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”Profile” class=”control-label”></label>
<input asp-for=”Profile” class=”form-control” />
<span asp-validation-for=”Profile” class=”text-danger”></span>
</div>
<div class=”form-group”>
<input type=”submit” value=”Create” class=”btn btn-primary” />
</div>
</form>
</div>
</div>
<div>
<a asp-action=”Index”>Back to List</a>
</div>
@section Scripts {
@{await Html.RenderPartialAsync(“_ValidationScriptsPartial”);}
}
Index.cshtml File
This file will contain the code to display the list of employee with the corresponding button to delete and edit the data as well as one button for adding a new record
@model IEnumerable<DotNetCoreRepository.Models.EmployeeModel>
@{
ViewData[“Title”] = “Index”;
}
<h1>Index</h1>
<p>
<a asp-action=”AddEmployee”>Add New Employee</a>
</p>
<table class=”table”>
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.UserName)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.State)
</th>
<th>
@Html.DisplayNameFor(model => model.Mobile)
</th>
<th>
@Html.DisplayNameFor(model => model.Profile)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.UserName)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.DisplayFor(modelItem => item.State)
</td>
<td>
@Html.DisplayFor(modelItem => item.Mobile)
</td>
<td>
@Html.DisplayFor(modelItem => item.Profile)
</td>
<td>
@Html.ActionLink(“Edit”, “EditEmployee”, new { id=item.UserID }) |
@Html.ActionLink(“Delete”, “DeleteEmployee”, new { id=item.UserID })
</td>
</tr>
}
</tbody>
</table>
EditEmployee.cshtml File
This is responsible for editing the existing record and validates the data.
@model DotNetCoreRepository.Models.EmployeeModel
@{
ViewData[“Title”] = “EditEmployee”;
}
<h1>Update Employee Data</h1>
<hr />
<div class=”row”>
<div class=”col-md-4″>
<form asp-action=”EditEmployee”>
<div asp-validation-summary=”ModelOnly” class=”text-danger”></div>
<div class=”form-group”>
<input asp-for=”UserID” class=”form-control” type=”hidden” />
<label asp-for=”UserName” class=”control-label”></label>
<input asp-for=”UserName” class=”form-control” />
<span asp-validation-for=”UserName” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”City” class=”control-label”></label>
<input asp-for=”City” class=”form-control” />
<span asp-validation-for=”City” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”State” class=”control-label”></label>
<input asp-for=”State” class=”form-control” />
<span asp-validation-for=”State” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”Mobile” class=”control-label”></label>
<input asp-for=”Mobile” class=”form-control” />
<span asp-validation-for=”Mobile” class=”text-danger”></span>
</div>
<div class=”form-group”>
<label asp-for=”Profile” class=”control-label”></label>
<input asp-for=”Profile” class=”form-control” />
<span asp-validation-for=”Profile” class=”text-danger”></span>
</div>
<div class=”form-group”>
<input type=”submit” value=”Save” class=”btn btn-primary” />
</div>
</form>
</div>
</div>
<div>
<a asp-action=”Index”>Back to List</a>
</div>
@section Scripts {
@{await Html.RenderPartialAsync(“_ValidationScriptsPartial”);}
}
Now, All set up is done and now we can run the application. To run the application just presses F5 and application is ready for run. once application is run , you have ready to perform crude operation as mentioned below screenshot.
Run application : Press F5
Add Employee :
Employee List (Index page):
Employee Edit Opearation :
Summary :
So, In this tutorial we have learned CRUD Operation Using Asp.Net Core Mvc with Repository Pattern and Ado.Net.
I hope this tutorial will help you. Please put your feedback using comment box which helps me to improve myself for upcomming tutorial. If you have any doubts please ask your doubts or query in the comment section and If you like this post then please share it with your friends.
6 Comments