Create Update Read Delete Operation In MVC With ADO.NET

Introduction

This article will explain how to CURD (Create, Update, Read, and Delete) records from a database without using Entity Framework. I am going to use ADO.NET for this article.

Step 1

Open the SQL Server with a version of your choice and create a database table and related stored procedure.

CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Gender] [char](10) NULL,
[Age] [int] NULL,
[Position] [nvarchar](50) NULL,
[Office] [nvarchar](50) NULL,
[Salary] [int] NULL,
CONSTRAINT [PK__Employee__3214EC07BA0DD227] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Create procedure [dbo].[spGetEmployees]
as
begin
SELECT* FROM Employee
end
Create procedure [dbo].[ spInsertNew]
(
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Gender char(10),
@Age int,
@Position nvarchar(50),
@Office nvarchar(50),
@Salary int
)
as
begin
insert into Employee(FirstName,LastName,Gender,Age,Position,Office,Salary)
values(@FirstName,@LastName,@Gender,@Age,@Position,@Office,@Salary)
end
Create procedure [dbo].[spUpdateRecord]
(
@Id int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Gender char(10),
@Age int,
@Position nvarchar(50),
@Office nvarchar(50),
@Salary int
)
as
begin
update Employee
set FirstName=@FirstName,LastName=@LastName,Gender=@Gender,Age=@Age,
Position=@Position,Office=@Position,
Salary=@Salary where Id=@Id
end
Create procedure [dbo].[spDeleteRecord]
(
@Id int
)
as
begin
delete from Employee where Id=@Id
end

Step 2

Open Visual Studio with a version of your choice and create a new project with an appropriate name.

Create Update Read Delete Operation In MVC With ADO.NET

Step 3

Choose an "Empty" template, check MVC under "Add folders & core references", and click "Create".

Step 4

Right-click on the Models folder, select “Add”. Under “Add”, a "Choose class" window will appear. From that window, select Visual C# and give a class name as “Employee”. Then, click "Add".

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace MvcCURDWithoutEntityFramework_Demo.Models
{
public class Employee
{
[Key]
public int Id { get; set; }
[Required(ErrorMessage ="Enter first name")]
[Display(Name ="First Name")]
public string FirstName { get; set; }
[Required(ErrorMessage = "Enter last name")]
[Display(Name = "Last Name")]
public string LastName { get; set; }
[Required(ErrorMessage = "Choose Gender")]
public string Gender { get; set; }
[Required(ErrorMessage = "Enter your age")]
public int Age { get; set; }
[Required(ErrorMessage = "Enter your position")]
public string Position { get; set; }
[Required(ErrorMessage = "Enter your office")]
public string Office { get; set; }
[Required(ErrorMessage = "Enter your salary")]
public int Salary { get; set; }
}
}

Step 5

Similarly, add another class in Models folder named as “EmployeeContext”. This class will be a data access layer.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace MvcCURDWithoutEntityFramework_Demo.Models
{
public class EmployeeContext
{
private readonly string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
public List<Employee> GetEmployees()
{
List<Employee> employees = new List<Employee>();
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spGetEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var employee = new Employee();
employee.Id = Convert.ToInt32(rdr["Id"]);
employee.FirstName = rdr["FirstName"].ToString();
employee.LastName = rdr["LastName"].ToString();
employee.Gender = rdr["Gender"].ToString();
employee.Age = Convert.ToInt32(rdr["Age"]);
employee.Position = rdr["Position"].ToString();
employee.Office = rdr["Office"].ToString();
employee.Salary = Convert.ToInt32(rdr["Salary"]);
employees.Add(employee);
}
}
return (employees);
}
public bool AddNewEmployee(Employee employee)
{
using (SqlConnection con = new SqlConnection(CS))
{
var cmd = new SqlCommand("spInsertNew", con);
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
cmd.Parameters.AddWithValue("@LastName", employee.LastName);
cmd.Parameters.AddWithValue("@Gender", employee.Gender);
cmd.Parameters.AddWithValue("@Age", employee.Age);
cmd.Parameters.AddWithValue("@Position", employee.Position);
cmd.Parameters.AddWithValue("@Office", employee.Office);
cmd.Parameters.AddWithValue("@Salary", employee.Salary);
int i = cmd.ExecuteNonQuery();
if (i >= 1)
return true;
else
return false;
}
}
public bool UpdateEmployee(Employee employee)
{
using (SqlConnection con = new SqlConnection(CS))
{
var cmd = new SqlCommand("spUpdateRecord", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.AddWithValue("@Id", employee.Id);
cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
cmd.Parameters.AddWithValue("@LastName", employee.LastName);
cmd.Parameters.AddWithValue("@Gender", employee.Gender);
cmd.Parameters.AddWithValue("@Age", employee.Age);
cmd.Parameters.AddWithValue("@Position", employee.Position);
cmd.Parameters.AddWithValue("@Office", employee.Office);
cmd.Parameters.AddWithValue("@Salary", employee.Salary);
int i = cmd.ExecuteNonQuery();
if (i >= 1)
return true;
else
return false;
}
}
public bool DeleteEmployee(Employee employee)
{
using (SqlConnection con = new SqlConnection(CS))
{
var cmd = new SqlCommand("spDeleteRecord", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.AddWithValue("@Id", employee.Id);
int i = cmd.ExecuteNonQuery();
if (i >= 1)
return true;
else
return false;
}
}
}
}

Step 6

Open the WebConfig file and add a database connection string.

<connectionStrings>
<add name="DBCS" connectionString="data source=farhan\sql2014; database=SampleDB; integrated security=true;" providerName="System.SqlClient"/>
</connectionStrings>

Step 7

Now, right-click on the Controllers folder and add a controller.

A window will appear. Choose MVC5 Controller-Empty and click "Add".

After clicking on "Add", another window will appear with DefaultController. Change the name to HomeController and click "Add". The HomeController will be added under the Controllers folder. Don’t change the Controller suffix for all controllers, change only the highlight, and instead of Default, just change Home.

Controller Class code

using MvcCURDWithoutEntityFramework_Demo.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
namespace MvcCURDWithoutEntityFramework_Demo.Controllers
{
public class HomeController : Controller
{
private EmployeeContext db = new EmployeeContext();
public ActionResult Index()
{
var employee = db.GetEmployees();
return View(employee);
}
public ActionResult Details(int id)
{
var employee = db.GetEmployees().Find(e=>e.Id==id);
return View(employee);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create(Employee employee)
{
if (ModelState.IsValid)
{
db.AddNewEmployee(employee);
return RedirectToAction("Index");
}
return View();
}
public ActionResult Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Employee employee = db.GetEmployees().Find(e=>e.Id==id);
if (employee == null)
{
return HttpNotFound();
}
return View(employee);
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit(Employee employee)
{
if (ModelState.IsValid)
{
db.UpdateEmployee(employee);
return RedirectToAction("Index");
}
return View(employee);
}
public ActionResult Delete(int? id)
{
if (id==null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
var employee = db.GetEmployees().Find(e=>e.Id==id);
if (employee==null)
{
return HttpNotFound();
}
return View(employee);
}
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
var employee = db.GetEmployees().Find(e=>e.Id==id);
db.DeleteEmployee(employee);
return RedirectToAction("Index");
}
}
}

Step 8

Right-click on the index of ActionResult, choose “Add View”, and click on it. Now, you will get another window that has a default view name as ActionResult name. Check the "Use a lay page" option and click on “Add”.

Index View

@model IEnumerable<MvcCURDWithoutEntityFramework_Demo.Models.Employee>
@{
ViewBag.Title = "Index";
}
<h2 class="text-center text-uppercase">List of employee</h2>
<div style="margin-bottom:10px;">
@Html.ActionLink("Create New", "Create", "Home", "", new { @class = "btn btn-primary rounded-0" })
</div>
<table class="table table-bordered">
<thead class="thead-dark">
<tr>
<th>@Html.DisplayNameFor(m=>m.FirstName)</th>
<th>@Html.DisplayNameFor(m=>m.LastName)</th>
<th>@Html.DisplayNameFor(m=>m.Gender)</th>
<th>@Html.DisplayNameFor(m=>m.Age)</th>
<th>@Html.DisplayNameFor(m=>m.Position)</th>
<th>@Html.DisplayNameFor(m=>m.Office)</th>
<th>@Html.DisplayNameFor(m=>m.Salary)</th>
<th>Action(s)</th>
</tr>
</thead>
<tbody>
@foreach (var emp in Model)
{
<tr>
<td>@emp.FirstName</td>
<td>@emp.LastName</td>
<td>@emp.Gender</td>
<td>@emp.Age</td>
<td>@emp.Position</td>
<td>@emp.Office</td>
<td>@emp.Salary</td>
<td>
@Html.ActionLink("Details","Details","Home",new {id=emp.Id},new { @class="btn btn-primary rounded-0"})
@Html.ActionLink("Edit","Edit","Home",new {id=emp.Id},new { @class="btn btn-info rounded-0"})
@Html.ActionLink("Delete","Delete","Home",new {id=emp.Id},new { @class="btn btn-danger rounded-0"})
</td>
</tr>
}
</tbody>
</table>

Output

Step 9

Now similarly, right-click on Details of ActionResult and choose “Add View” and click on it. Now, you will get another window that has a default view named as ActionResult name. Check the "Use a lay page" option and click on “Add”.

Details View

@model MvcCURDWithoutEntityFramework_Demo.Models.Employee
@{
ViewBag.Title = "Details";
}
<div class="card">
<div class="card-header">
<h3>Employee Details</h3>
</div>
<div class="card-body">
<dl class="row">
<dt class="col-md-3">@Html.DisplayNameFor(m => m.FirstName)</dt>
<dd class="col-md-3">@Html.DisplayFor(m => m.FirstName)</dd>
<dt class="col-md-3">@Html.DisplayNameFor(m => m.LastName)</dt>
<dd class="col-md-3">@Html.DisplayFor(m => m.LastName)</dd>
</dl>
<dl class="row">
<dt class="col-md-3">@Html.DisplayNameFor(m => m.Gender)</dt>
<dd class="col-md-3">@Html.DisplayFor(m => m.Gender)</dd>
<dt class="col-md-3">@Html.DisplayNameFor(m => m.Age)</dt>
<dd class="col-md-3">@Html.DisplayFor(m => m.Age)</dd>
</dl>
<dl class="row">
<dt class="col-md-2">@Html.DisplayNameFor(m => m.Position)</dt>
<dd class="col-md-2">@Html.DisplayFor(m => m.Position)</dd>
<dt class="col-md-2">@Html.DisplayNameFor(m => m.Office)</dt>
<dd class="col-md-2">@Html.DisplayFor(m => m.Office)</dd>
<dt class="col-md-2">@Html.DisplayNameFor(m => m.Salary)</dt>
<dd class="col-md-2">@Html.DisplayFor(m => m.Salary)</dd>
</dl>
<div>
@Html.ActionLink("Back to list", "Index", "Home", new { @class = "btn btn-primary rounded-0" })
@Html.ActionLink("Edit", "Edit", "Home", new { id = Model.Id }, new { @class = "btn btn-info rounded-0" })
</div>
</div>
</div>

Step 10

Click on "Create ActionResult", choose “Add View”, and click on it. Similarly, you will get another window with default view name as ActionResult name. Check the "Use a lay page" option and click on “Add”.

Create View

@model MvcCURDWithoutEntityFramework_Demo.Models.Employee
@{
ViewBag.Title = "Create";
}
<div class="card">
<div class="card-header bg-dark text-white">
<h3>Create New</h3>
</div>
<div class="card-body">
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="row">
<div class="col-md-6">
<div class="form-group">
@Html.LabelFor(m => m.FirstName)
@Html.TextBoxFor(m => m.FirstName, new { @class = "form-control" })
@Html.ValidationMessageFor(m => m.FirstName)
</div>
</div>
<div class="col-md-6">
<div class="form-group">
@Html.LabelFor(m => m.LastName)
@Html.TextBoxFor(m => m.LastName, new { @class = "form-control" })
@Html.ValidationMessageFor(m => m.LastName)
</div>
</div>
</div>
<div class="row">
<div class="col-md-6">
<div class="form-group">
@Html.LabelFor(m => m.Gender)
@Html.DropDownList("Gender",new List<SelectListItem> {
new SelectListItem { Text="Male",Value="Male" },
new SelectListItem { Text="Female", Value="Female" }
},"Choose Gender",new { @class="form-control"})
@Html.ValidationMessageFor(m => m.Gender)
</div>
</div>
<div class="col-md-6">
<div class="form-group">
@Html.LabelFor(m => m.Age)
@Html.TextBoxFor(m => m.Age, new { @class = "form-control" })
@Html.ValidationMessageFor(m => m.Age)
</div>
</div>
</div>
<div class="row">
<div class="col-md-4">
<div class="form-group">
@Html.LabelFor(m => m.Position)
@Html.TextBoxFor(m => m.Position, new { @class = "form-control" })
@Html.ValidationMessageFor(m => m.Position)
</div>
</div>
<div class="col-md-4">
<div class="form-group">
@Html.LabelFor(m => m.Office)
@Html.TextBoxFor(m => m.Office, new { @class = "form-control" })
@Html.ValidationMessageFor(m => m.Office)
</div>
</div>
<div class="col-md-4">
<div class="form-group">
@Html.LabelFor(m => m.Salary)
@Html.TextBoxFor(m => m.Salary, new { @class = "form-control" })
@Html.ValidationMessageFor(m => m.Salary)
</div>
</div>
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary rounded-0">Submit</button>
</div>
}
</div>
</div>

Output

Step 11

Right-click on "Edit" of ActionResult, choose “Add View”, and click on it. Now, you will get another window with a default view named as ActionResult name. Check "Use a lay page" and click on “Add”.

Edit View

@model MvcCURDWithoutEntityFramework_Demo.Models.Employee
@{
ViewBag.Title = "Edit";
}
<div class="card">
<div class="card-header">
<h3>Edit Employee</h3>
</div>
<div class="card-body">
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m=>m.Id)
<div class="row">
<div class="col-md-6">
<div class="form-group">
@Html.LabelFor(m=>m.FirstName)
@Html.EditorFor(m=>m.FirstName,new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(m=>m.FirstName)
</div>
</div>
<div class="col-md-6">
<div class="form-group">
@Html.LabelFor(m => m.LastName)
@Html.EditorFor(m => m.LastName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(m => m.LastName)
</div>
</div>
</div>
<div class="row">
<div class="col-md-6">
<div class="form-group">
@Html.LabelFor(m => m.Gender)
@Html.DropDownList("Gender",new List<SelectListItem> {
new SelectListItem { Text="Male",Value="Male"},
new SelectListItem { Text="Female",Value="Female"}
},"Choose Gender",new { @class="form-control"})
@Html.ValidationMessageFor(m => m.LastName)
</div>
</div>
<div class="col-md-6">
<div class="form-group">
@Html.LabelFor(m => m.Age)
@Html.EditorFor(m => m.Age, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(m => m.Age)
</div>
</div>
</div>
<div class="row">
<div class="col-md-4">
<div class="form-group">
@Html.LabelFor(m => m.Position)
@Html.EditorFor(m => m.Position, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(m => m.Position)
</div>
</div>
<div class="col-md-4">
<div class="form-group">
@Html.LabelFor(m => m.Office)
@Html.EditorFor(m => m.Office, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(m => m.Office)
</div>
</div>
<div class="col-md-4">
<div class="form-group">
@Html.LabelFor(m => m.Salary)
@Html.EditorFor(m => m.Salary, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(m => m.Salary)
</div>
</div>
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary rounded-0">Update</button>
</div>
}
</div>
</div>

Step 12

Right-click on Delete ActionResult, choose “Add View”, and click on it. Now, you will get another window that has a default view named as ActionResult name. Check the "Use a lay page" option and click on “Add”.

Delete View

@model MvcCURDWithoutEntityFramework_Demo.Models.Employee
@{
ViewBag.Title = "Delete";
}
<h3 class="alert alert-danger text-center text-uppercase">Are you sure you want to delete this?</h3>
<div class="card">
<div class="card-header">
<h3 class="text-center text-uppercase">Delete Employee Record</h3>
</div>
<div class="card-body">
<dl class="row">
<dt class="col-md-3">@Html.DisplayNameFor(m => m.FirstName)</dt>
<dd class="col-md-3">@Html.DisplayFor(m => m.FirstName)</dd>
<dt class="col-md-3">@Html.DisplayNameFor(m => m.LastName)</dt>
<dd class="col-md-3">@Html.DisplayFor(m => m.LastName)</dd>
</dl>
<dl class="row">
<dt class="col-md-3">@Html.DisplayNameFor(m => m.Gender)</dt>
<dd class="col-md-3">@Html.DisplayFor(m => m.Gender)</dd>
<dt class="col-md-3">@Html.DisplayNameFor(m => m.Age)</dt>
<dd class="col-md-3">@Html.DisplayFor(m => m.Age)</dd>
</dl>
<dl class="row">
<dt class="col-md-2">@Html.DisplayNameFor(m => m.Position)</dt>
<dd class="col-md-2">@Html.DisplayFor(m => m.Position)</dd>
<dt class="col-md-2">@Html.DisplayNameFor(m => m.Office)</dt>
<dd class="col-md-2">@Html.DisplayFor(m => m.Office)</dd>
<dt class="col-md-2">@Html.DisplayNameFor(m => m.Salary)</dt>
<dd class="col-md-2">@Html.DisplayFor(m => m.Salary)</dd>
</dl>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-group">
@Html.ActionLink("Back to List", "Index", "", new { @class = "btn btn-primary rounded-0" })
<button type="submit" class="btn btn-danger rounded-0">Delete</button>
</div>
}
</div>
</div>

Step 13

Build your project and run by pressing ctrl+F5

Summary

In this article, I have explained how to perform CURD (Create, Update, Read and Delete) operations using ADO.NET where I have created a data access layer for database interaction like retrieving data, inserting data, updating existing data, and deleting data from the SQL database table.