Performing CRUD (Insert Update Delete) Operation in MVC ASP.NET C# with Database Example

ASH

6 years ago

In this post we will see how we can implement CRUD functionality (Insert Update Delete) in MVC ASP.NET C#.

Here Employee information will be used as primary data. Employee name, Designation, Department can be added into SQL database table and Updated or deleted using the MVC View. Hence implementing our CRUD functionality of MVC. A single C# method in MVC controller will be used to perform all three actions (CRUD) in web application. Also the Data will be display using HTML table using MVC model object which will be a List<T> object.

Performing CRUD operations in MVC ASP.NET C#:

Download source code

So let’s begin.

Database scripts:

Database name : ESource

create database ESource

Table name: tblEmployeeInfo

CREATE TABLE [dbo].[tblEmployeeInfo1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Designation] [varchar](50) NOT NULL,
[Department] [varchar](50) NOT NULL
)

Stored Procedure: get_Employees

CREATE procedure [dbo].[get_Employees]
(
@Id int
)
as begin

If @Id>0
Begin
Select * from tblEmployeeInfo where Id=@Id
End

else
Begin
Select * from tblEmployeeInfo order by Id desc
End
End

GO

Stored Procedure: sp_Employees

CREATE procedure [dbo].[sp_Employees](
@StmtType nvarchar(50),
@Id int,
@Name varchar(50),
@Designation varchar(50),
@Department varchar(50)
)

as begin

If @StmtType='Insert'
Begin
Insert into tblEmployeeInfo (Name,Designation,Department) values (@Name,@Designation,@Department)
Select SCOPE_IDENTITY() as 'ID'
End

If @StmtType='Update'
Begin
Update tblEmployeeInfo set Name=@Name,Designation=@Designation,Department=@Department where Id=@Id
select @Id as 'ID'
End

If @StmtType='Delete'
Begin
Delete from tblEmployeeInfo where Id=@Id
select @Id as 'ID'
End

End

GO

Performing CRUD (Insert Update Delete) Operation in MVC ASP.NET C# with Database Example

CRUD stands for Create, Read, Update and Delete.

Create a new ASP.NET MVC project in Visual studio with namespace MVCEmployeesApp

In the project add a new folder named “Classes”.

Now, add two classes with names

Employees.cs
TrnEmployees.cs

Edit the Employees.cs class as below

Classes > Employees.cs:

using System.Collections.Generic;

namespace MVCEmployeesApp.Classes
{
public class Employees
{
public int Id { get; set; }
public string Name { get; set; }
public string Designation { get; set; }
public string Department { get; set; }
public List<Employees> lstEmployees { get; set; }
}
}

And TrnEmployees.cs class as below

Classes > TrnEmployees.cs:

using MVCEmployeesApp.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace MVCEmployeesApp.Classes
{
public class TrnEmployees
{

SqlDataAdapter adapter;
DataTable dt;
SqlCommand cmd;

public Employee EmployeesCRUDOperation(Employee employee)
{
Employee result = new Employee();
try
{
var connection = sqlConnection();
connection.Open();
cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_Employees";
cmd.Parameters.AddWithValue("@Name", employee.Name);
cmd.Parameters.AddWithValue("@Designation", employee.Designation);
cmd.Parameters.AddWithValue("@Department", employee.Department);
cmd.Parameters.AddWithValue("@StmtType", employee.StmtType);
cmd.Parameters.AddWithValue("@Id", employee.Id);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
result.Message = "success";
result.Id = Convert.ToInt32(reader[0].ToString());
}
reader.Close();
cmd.Dispose();
connection.Close();
}
catch (Exception ex)
{
result.Message= "Error:" + ex.Message.ToString();
}
return result;
}

public SqlConnection sqlConnection()
{
return (new SqlConnection(@"Data Source=192.168.0.192;Initial Catalog=ESource;User ID=sa;Password=789"));
}

public Employee GetEmployees(int id)
{
try
{
var connection = sqlConnection();
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "get_Employees";
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@Id", id);
connection.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);
Employee emp = new Employee();
emp.lstEmployees = new List<Employees>();
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
if (id == 0)
{
Employees em = new Employees();
em.Id = Convert.ToInt32(row["Id"].ToString());
em.Name = row["Name"].ToString();
em.Designation = row["Designation"].ToString();
em.Department = row["Department"].ToString();
emp.lstEmployees.Add(em);
}
else
{
emp.Name = row["Name"].ToString();
emp.Designation = row["Designation"].ToString();
emp.Department = row["Department"].ToString();
emp.Id = Convert.ToInt32(row["Id"].ToString());
}
}
}
return emp;
}
catch (Exception ex)
{
return null;
}

}
}
}

Now add a new Model class named Employee.cs and edit it as below

Models > Employee.cs:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace MVCEmployeesApp.Models
{
public class Employee
{
[Key]
public int Id { get; set; }

[Required(ErrorMessage = "Please enter Employee name")]
public string Name { get; set; }

[Required(ErrorMessage = "Please enter Designation name")]
public string Designation { get; set; }

[Required(ErrorMessage = "Please enter Department name")]
public string Department { get; set; }
public string StmtType { get; set; }
public string buttonText { get; set; }
public string Message { get; set; }

public List<Classes.Employees> lstEmployees { get; set; }
}
}

Now add a controller named Employee and edit it as below:
Controller > EmployeeController.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MVCEmployeesApp.Classes;
using MVCEmployeesApp.Models;

namespace MVCEmployeesApp.Controllers
{
public class EmployeeController : Controller
{
//
// GET: /Employee/

[HttpGet]
public ActionResult Index(int id = 0, String type = null)
{
Employee emp = new Employee();
TrnEmployees trn = new TrnEmployees();

if (id > 0 && !String.IsNullOrWhiteSpace(type))
{
try
{
if (type.ToLower().Equals("d"))
{
emp.Id = id;
emp.StmtType = "Delete";
emp.Name = "";
emp.Designation = "";
emp.Department = "";
emp.buttonText = "Add";
emp = trn.EmployeesCRUDOperation(emp);

if (emp.Message.Equals("success"))
{
emp.Message = "Employee deleted successfully.";
}

emp.lstEmployees = trn.GetEmployees(0).lstEmployees;
emp.buttonText = "Add";
emp.StmtType = "Insert";
}
else if (type.ToLower().Equals("e"))
{
emp = trn.GetEmployees(id);
emp.StmtType = "Update";
emp.buttonText = "Update";
emp.Message = "";
emp.lstEmployees = trn.GetEmployees(0).lstEmployees;
}
}
catch (Exception ex)
{
emp.lstEmployees = null;
emp.buttonText = "Add";
emp.StmtType = "Insert";
emp.Message = ex.Message.ToString();
}
}
else
{
emp.Message = "";
emp.StmtType = "Insert";
emp.buttonText = "Add";
emp.lstEmployees = trn.GetEmployees(0).lstEmployees;
}

if (emp.lstEmployees == null || emp.lstEmployees.Count == 0)
{
emp.lstEmployees.Add(new Employees
{
Name = "",
Id = 0,
Designation = "Error",
Department = "Error",
lstEmployees = null

});
}
return View(emp);
}

[HttpPost]
public ActionResult Index(Employee emp)
{
TrnEmployees trn = new TrnEmployees();

if (ModelState.IsValid)
{
Employee e = trn.EmployeesCRUDOperation(emp);
if (!String.IsNullOrWhiteSpace(e.Message) && e.Message.Equals("success"))
{
var c = trn.GetEmployees(0);
if (c != null)
{
e.lstEmployees = c.lstEmployees;
}
e.buttonText = "Add";
if (e.Message.ToLower().Equals("success"))
{
e.Message = "Employee saved successfully";
}
e.StmtType = "Insert";
}
else
{
e.Department = emp.Department;
e.Designation = emp.Designation;
e.Id = emp.Id;
e.Name = emp.Name;
e.StmtType = emp.StmtType;
e.Message = emp.Message;
e.buttonText = "Update";
}
return View(e);
}
else
{
var c = trn.GetEmployees(0);
if (c != null)
{
emp.lstEmployees = c.lstEmployees;
}
emp.buttonText = "Add";
emp.StmtType = "Insert";
return View(emp);
}

}

}
}

Now add a View for our Controller with strongly-view and model class as our Models>Employee.cs class and edit it as below:

Views>Employee>Index.cshtml:

@model MVCEmployeesApp.Models.Employee
@{
Layout = null;
}

<!DOCTYPE html>

<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" />
<link href="https://fonts.googleapis.com/css?family=Open+Sans" rel="stylesheet">
<style>
body
{
font-family: 'Open Sans', sans-serif;
color: black;
}
</style>
</head>
<body>

<div>
<div style="background-color: #191919; width: 100%; padding: 5px">
<h2 style="color: #fff; text-transform: uppercase">Employee Records</h2>
</div>
<div style="background-color: #fff; width: 100%; padding: 10px 50px;">
@using (Html.BeginForm())
{
<table class="table-condensed">
<tr>
<td>
@Html.TextBoxFor(m => m.Id, new { id = "txtId", Value = @Model.Id, style = "display:none" })
</td>
</tr>
<tr>
<td>
<span>Name:</span>
</td>
<td>
@Html.TextBoxFor(m => m.Name, new { id = "txtName", Value = @Model.Name })
&nbsp;&nbsp;
@Html.ValidationMessageFor(m => m.Name)
</td>
</tr>
<tr>
<td>
<span>Department:</span>
</td>
<td>
@Html.TextBoxFor(m => m.Department, new { id = "txtDepartment", Value = @Model.Department })
&nbsp;&nbsp;
@Html.ValidationMessageFor(m => m.Department)
</td>
</tr>
<tr>
<td>
<span>Designation:</span>
</td>
<td>
@Html.TextBoxFor(m => m.Designation, new { id = "txtDesignation", Value = @Model.Designation })
&nbsp;&nbsp;
@Html.ValidationMessageFor(m => m.Designation)
</td>
</tr>
<tr>
<td>
@Html.TextBoxFor(m => m.StmtType, new { id = "txtStmtType", Value = @Model.StmtType, style = "display:none" })
</td>
</tr>
<tr>
<td colspan="2" align="center">
<button value="submit" class="btn btn-primary" title="submit">@Model.buttonText</button>
</td>
</tr>
</table>

<h5>@Model.Message</h5>
<table class="table-condensed table-bordered">
<tr>
<th align="center" class="text-center">ID</th>
<th align="center" class="text-center">Name</th>
<th align="center" class="text-center">Designation</th>
<th align="center" class="text-center">Department</th>
<th align="center" class="text-center">Edit</th>
<th align="center" class="text-center">Delete</th>
</tr>
@foreach (var e in Model.lstEmployees)
{
<tr>
<td align="center"><b>@e.Id</b></td>
<td align="center">@e.Name</td>
<td align="center">@e.Designation</td>
<td align="center">@e.Department</td>
<td align="center"><a class="btn btn-success" href="http://localhost:8508/employee?id=@e.Id&type=e"
><i class="fa fa-edit"></i></a></td>
<td align="center"><a class="btn btn-danger" href="http://localhost:8508/employee?id=@e.Id&type=d"
><i class="fa fa-trash"></i></a></td>
</tr>
}
</table>

}
</div>

</div>
</body>
</html>

Now run your MVC application to perform MVC Crud Operation:

MVC CRUD Operation output screen: