CRUD Operation Using Asp.Net Core Mvc with Repository Pattern

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

CRUD Operation Using Asp.Net Core Mvc with Repository Pattern

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.

Asp.Net core

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.

Facebook

@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 :

Asp.Net core add employee
Employee List (Index page):

Asp.Net core razor view Read operation
Employee Edit Opearation :

Asp.Net core Crude operation
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.

4 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *