How to Insert Update Delete Data Using ADO.Net in ASP.Net MVC4

Introduction
This article explains how to insert Update Delete using ADO.NET and SQL in ASP.NET MVC 4.Step 1 : Create Database & Table
I already have a database with the name TEST, and inside that I have a table with the name Employee_Info.
CREATE TABLE [dbo].[Employee_Info](
[EmpID] [int] IDENTITY(1,1) NOT NULL Primary Key,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Department] [varchar](50) NULL,
[City] [varchar](50) NULL,
[IsActive] [bit] NULL Default(1)
)

Step 2 : Create New Project
Go to “File” -> “New” -> “Project…” then Select Visual C# ->t ASP.NET MVC4 web application then enter the Application Name & Location then click OK then select Empty Template then select View Engine Razor then click OK. Refer below screen cast.

Add Project1

Add Project2

Step 3 : Add Model Class
Go to solution explorer Right Click on Model folder and add new class.

Model1

 

After the completion of this step build the solution.

STEP 4 :Add Controller
Go to solution explorer Right Click on Controller folder and Select add -> Controller.

Controller1

After clicking on Controller you will see a popup window like this.With default name. [DefaultController].

Controller2

After giving a appropriate name to the Controller the word “Controller” should be suffixed always. Click on Add button. After this step you will see a Controller created.With the default action method Index ()

public ActionResult Index()
{
return View();
}

In this step I will change the Method name Index to Employee.

public ActionResult Employee()
{
return View();
}

STEP 5 : Add View
Right click on Employee method & select add View Once clicking on “Add View” open new PopUp window.

View1

In this window you will see View Name that will be the name of the method on which you right-clicked.There is a drop-down list named View Engine in Razor selected as the default. And check box create a strongly-typed view that is disabled.
Check that check-box & select the model class “Employee”

View2

There is a one another drop-down list named “Scaffold Template” ,  There are many scaffolding templates available in MVC. from that list select “Create”. It will create design for creating employee data as per selected model.

View3

There is one more checkbox named “Use a layout or Master Page”. This is default checked. Un-check the checkbox before click on add button.

View4

After the step view is added in View Folder named “Employee.Cshtml” with ready made code.

@model MvcApplication1.Models.Employee
@{ Layout = null;}
<!DOCTYPE html>
<html>
<head>
<meta name=”viewport” content=”width=device-width” />
<title>Employee</title>
</head>
<body>
<script src=”~/Scripts/jquery-1.7.1.min.js”></script>
<script src=”~/Scripts/jquery.validate.min.js”></script>
<script src=”~/Scripts/jquery.validate.unobtrusive.min.js”></script>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Employee</legend>
<div class=”editor-label”>
@Html.LabelFor(model => model.FirstName)
</div>
<div class=”editor-field”>
@Html.EditorFor(model => model.FirstName)
@Html.ValidationMessageFor(model => model.FirstName)
</div>
<div class=”editor-label”>
@Html.LabelFor(model => model.LastName)
</div>
<div class=”editor-field”>
@Html.EditorFor(model => model.LastName)
@Html.ValidationMessageFor(model => model.LastName)
</div>
<div class=”editor-label”>
@Html.LabelFor(model => model.Department)
</div>
<div class=”editor-field”>
@Html.EditorFor(model => model.Department)
@Html.ValidationMessageFor(model => model.Department)
</div>
<div class=”editor-label”>
@Html.LabelFor(model => model.City)
</div>
<div class=”editor-field”>
@Html.EditorFor(model => model.City)
@Html.ValidationMessageFor(model => model.City)
</div>
<p>
<input type=”submit” value=”Create” />
</p>
</fieldset>
}
<div>
@Html.ActionLink(“Back to List”, “GetEmployee”)
</div>
</body>
</html>

STEP 6 : Data Connectivity
Add new stored procedure for insert data into database table as well as same sp for update employee info.

CREATE PROCEDURE [dbo].[Usp_AddUpdateEmployee] @EmpID int=0,
@FirstName varchar(50),
@LastName varchar(50),
@Department varchar(50),
@City varchar(50)
AS
IF (@EmpID > 0)
BEGIN
UPDATE Employee_Info
SET FirstName = @FirstName,
LastName = @LastName,
Department = @Department,
City = @City
WHERE EmpID = @EmpID
END
ELSE
BEGIN
INSERT INTO Employee_Info (FirstName, LastName, Department, City)
VALUES (@FirstName, @LastName, @Department, @City)
END

Open model class and import Namespaces for data connection. then declare one public method named “AddEmployee”  with one parameter. the type of parameter is model class, then declare the connection object & command object in the method. Add write a code for insert data into Database. see the below code.

public void AddEmployee(Employee objEmployee)
{
SqlConnection con = new SqlConnection
 (@”Data Source=TECH-VAIBHAV\SQLEXPRESS;Initial Catalog=TEST;user id=sa;pwd=sa”);
SqlCommand cmd = new SqlCommand(“[Usp_AddUpdateEmployee]”, con);
try
{
cmd.Parameters.AddWithValue(“@FirstName”, objEmployee.FirstName);
cmd.Parameters.AddWithValue(“@LastName”, objEmployee.LastName);
cmd.Parameters.AddWithValue(“@Department”, objEmployee.Department);
cmd.Parameters.AddWithValue(“@City”, objEmployee.City);
cmd.CommandType = CommandType.StoredProcedure;
if (con.State != ConnectionState.Open)
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ throw ex; }
finally
{
if (con.State != ConnectionState.Open)
con.Close();
}
}

STEP 7 : Add post method in Controller
Add post method in Controller and pass the model as parameter to the post method. the name of method is same as get() method.
write a code for call function of model which created for insert data into DB and pass the parameter.

[HttpPost]
public ActionResult Employee(Employee objEmployee)
{
if (ModelState.IsValid)
{
objEmployee.AddEmployee(objEmployee);
ModelState.Clear();
return RedirectToAction(“GetEmployee”);
}
return View();
}

Declare one more function for get employee list, before that create stored procedure for get emp list. then create function for execute sp and get list.

CREATE PROC [dbo].[Usp_GetEmployeeList]
@EmpID int=null
AS
SELECT
E.EmpID,
E.FirstName,
E.LastName,
E.Department,
E.City
FROM Employee_Info E
Where (E.EmpID =@EmpID OR @EmpID =0 OR @EmpID IS NULL )AND ISNULL(IsActive,1)=1 

Function :

public List<Employee> GetEmployee(int EmpID = 0)
{
SqlConnection con = new SqlConnection
 (@”Data Source=TECH-VAIBHAV\SQLEXPRESS;Initial Catalog=TEST;user id=sa;pwd=sa”);
SqlCommand cmd = new SqlCommand(“[Usp_GetEmployeeList]”, con);
cmd.Parameters.AddWithValue(“@EmpID”, EmpID);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter Da = new SqlDataAdapter();
Da.SelectCommand = cmd;
DataTable dt = new DataTable();
Da.Fill(dt);
return dt.AsEnumerable().Select(dr => new Employee
{
EmpID = Convert.ToInt32(dr[“EmpID”]),
FirstName = Convert.ToString(dr[“FirstName”]),
LastName = Convert.ToString(dr[“LastName”]),
Department = Convert.ToString(dr[“Department”]),
City = Convert.ToString(dr[“City”]),
}).ToList();
}

After that go to the Employee controller and add one action method named “GetEmployee” and call the method from model for get list of employee.

[HttpGet]
public ActionResult GetEmployee()
{
Employee objEmployee = new Models.Employee();
return View(objEmployee.GetEmployee());
}

after adding action add the view for view employee list. When adding select the scaffolding template is “List”. after adding the view yow will get below code.

@model IEnumerable<MvcApplication1.Models.Employee>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name=”viewport” content=”width=device-width” />
<title>GetEmployee</title>
</head>
<body>
<p>
@Html.ActionLink(“Create New”, “Employee”)
</p>
<table border=”1″ style=”font-family: Arial;”>
<tr>
<th>
@Html.DisplayNameFor(model => model.EmpID)
</th>
<th>
@Html.DisplayNameFor(model => model.FirstName)
</th>
<th>
@Html.DisplayNameFor(model => model.LastName)
</th>
<th>
@Html.DisplayNameFor(model => model.Department)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th></th>
</tr>

@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.EmpID)
</td>
<td>
@Html.DisplayFor(modelItem => item.FirstName)
</td>
<td>
@Html.DisplayFor(modelItem => item.LastName)
</td>
<td>
@Html.DisplayFor(modelItem => item.Department)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.ActionLink(“Edit”, “Edit”, new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink(“Details”, “Details”, new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink(“Delete”, “Delete”, new { /* id=item.PrimaryKey */ })
</td>
</tr>
}</table>
</body>
</html>

After completion of this step go to the model and add two more methods for Update and delete employee.

Method For Update Employee : 

public void EditEmployee(Employee objEmployee)
{try
{
SqlCommand cmd = new SqlCommand(“[Usp_AddUpdateEmployee]”, con);
cmd.Parameters.AddWithValue(“@EmpID”, objEmployee.EmpID);
cmd.Parameters.AddWithValue(“@FirstName”, objEmployee.FirstName);
cmd.Parameters.AddWithValue(“@LastName”, objEmployee.LastName);
cmd.Parameters.AddWithValue(“@Department”, objEmployee.Department);
cmd.Parameters.AddWithValue(“@City”, objEmployee.City);
cmd.CommandType = CommandType.StoredProcedure;
if (con.State != ConnectionState.Open)
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ throw ex; }
finally
{
if (con.State != ConnectionState.Open)
con.Close();
}
}

Stored procedure for delete employee :

ALTER proc [dbo].[Usp_DeleteEmployee]
@EmpID Int
as
UPDATE Employee_Info SET IsActive=0
WHERE EmpID=@EmpID 

Method For Delete Employee :

public void DeleteEmployee(int ID)
{
SqlConnection con = new SqlConnection
 (@”Data Source=TECH-VAIBHAV\SQLEXPRESS;Initial Catalog=TEST;user id=sa;pwd=sa”);
SqlCommand cmd = new SqlCommand(“Usp_DeleteEmployee”, con);
try
{
cmd.Parameters.AddWithValue(“@EmpID”, ID);
cmd.CommandType = CommandType.StoredProcedure;
if (con.State != ConnectionState.Open)
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ throw ex; }
finally
{
if (con.State != ConnectionState.Open)
con.Close();
}
}

Then add get and post method for update.

[HttpGet]
public ActionResult EditEmployee(int id)
{
Employee objEmployee = new Employee();
objEmployee = objEmployee.GetEmployee(id)[0];
return View(objEmployee);
}

[HttpPost]
public ActionResult EditEmployee(Employee objEmployee)
{
if (ModelState.IsValid)
{
objEmployee.EditEmployee(objEmployee);
return RedirectToAction(“GetEmployee”);
}
return View();
}

Then add Get method for delete employee

public ActionResult DeleteEmployee(int id)
{
if (id != null)
{
Employee objEmployee = new Employee();
objEmployee.DeleteEmployee(id);
}
return RedirectToAction(“GetEmployee”);
}

after adding action add the view for view employee list. When adding select the scaffolding template is “Edit”. after adding the view yow will get below code.

@model MvcApplication1.Models.Employee
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name=”viewport” content=”width=device-width” />
<title>EditEmployee</title>
</head>
<body>
<script src=”~/Scripts/jquery-1.7.1.min.js”></script>
<script src=”~/Scripts/jquery.validate.min.js”></script>
<script src=”~/Scripts/jquery.validate.unobtrusive.min.js”></script>

@using (Html.BeginForm())
{
@Html.ValidationSummary(true)

<fieldset>
<legend>Employee</legend>

<div class=”editor-label”>
@Html.LabelFor(model => model.EmpID)
</div>
<div class=”editor-field”>
@Html.EditorFor(model => model.EmpID)
@Html.ValidationMessageFor(model => model.EmpID)
</div>
<div class=”editor-label”>
@Html.LabelFor(model => model.FirstName)
</div>
<div class=”editor-field”>
@Html.EditorFor(model => model.FirstName)
@Html.ValidationMessageFor(model => model.FirstName)
</div><div class=”editor-label”>
@Html.LabelFor(model => model.LastName)
</div>
<div class=”editor-field”>
@Html.EditorFor(model => model.LastName)
@Html.ValidationMessageFor(model => model.LastName)
</div>

<div class=”editor-label”>
@Html.LabelFor(model => model.Department)
</div>
<div class=”editor-field”>
@Html.EditorFor(model => model.Department)
@Html.ValidationMessageFor(model => model.Department)
</div>

<div class=”editor-label”>
@Html.LabelFor(model => model.City)
</div>
<div class=”editor-field”>
@Html.EditorFor(model => model.City)
@Html.ValidationMessageFor(model => model.City)
</div>

<p>
<input type=”submit” value=”Save” />
</p>
</fieldset>
}
<div>
@Html.ActionLink(“Back to List”, “GetEmployee”)
</div>
</body>
</html>

after the remove the blow code and replace with hidden for employee Id field . Because empid is not editable so that it is required to show to user.

Old Code :

<div class=”editor-label”>
@Html.LabelFor(model => model.EmpID)
</div>
<div class=”editor-field”>
@Html.EditorFor(model => model.EmpID)
@Html.ValidationMessageFor(model => model.EmpID)
</div>

New Code :
@Html.HiddenFor(model => model.EmpID)

Once complete this all step then go to the “GetEmployee.cshtml” view and change the

Old Code : 

@Html.ActionLink(“Edit”, “Edit”, new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink(“Details”, “Details”, new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink(“Delete”, “Delete”, new { /* id=item.PrimaryKey */ })

@Html.ActionLink(“Create New”, “Create”)

New Code :

@Html.ActionLink(“Edit”, “EditEmployee”, new { id = item.EmpID }) |
@Html.ActionLink(“Delete”, “DeleteEmployee”, new { id = item.EmpID })

@Html.ActionLink(“Create New”, “Employee”)

After that to to the Solution Explores -> ProjectName->App_Start->RouteConfig.cs and change the default page for the project. Set the “GetEmployee” action of “Employee”  controller as default page.Then build the project & run.

public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute(“{resource}.axd/{*pathInfo}”);

routes.MapRoute(
name: “Default”,
url: “{controller}/{action}/{id}”,
defaults: new { controller = “Employee”, action = “GetEmployee”, id = UrlParameter.Optional }
);
}

Output :

Output0

Output1

Output2

Output3

Output4