--CRUD Operation With ASP.NET Core MVC Using ADO.NET and Visual Studio 2017 #mesameergaikwad

  sameer gaikwad blog #mesameergaikwad

Introduction

In this article, I am going to explain how to create an MVC web application in ASP.NET Core 2.0 using ADO.NET. We will be creating a sample Employee Record Management System and performing CRUD operations on it.

We will be using Visual Studio 2017 (Version 15.3.5 or above) and SQL Server.

Prerequisites

• Install .NET Core 2.0.0 or above SDK 
• Install Visual Studio 2017 Community Edition (Version 15.3.5 or above) 

Now, we are ready to proceed with the creation of our MVC web application.

Source Code

Before proceeding further i would recommend to download the source code from Github.

Creating Table and Stored Procedures

We will be using a DB table to store all the records of employees.

Open SQL Server and use the following script to create tblEmployee table.

Create table tblEmployee(

EmployeeId int IDENTITY(1,1) NOT NULL,

Name varchar(20) NOT NULL,

City varchar(20) NOT NULL,

Department varchar(20) NOT NULL,

Gender varchar(6) NOT NULL

)

Now, we will create stored procedures to add, delete, update, and get employee data.

To insert an Employee Record

Create procedure spAddEmployee

(

@Name VARCHAR(20),

@City VARCHAR(20),

@Department VARCHAR(20),

@Gender VARCHAR(6)

)

as

Begin

Insert into tblEmployee (Name,City,Department, Gender)

Values (@Name,@City,@Department, @Gender)

End

To update an Employee Record

Create procedure spUpdateEmployee

(

@EmpId INTEGER ,

@Name VARCHAR(20),

@City VARCHAR(20),

@Department VARCHAR(20),

@Gender VARCHAR(6)

)

as

begin

Update tblEmployee

set Name=@Name,

City=@City,

Department=@Department,

Gender=@Gender

where EmployeeId=@EmpId

End

For deleting an Employee Record

Create procedure spDeleteEmployee

(

@EmpId int

)

as

begin

Delete from tblEmployee where EmployeeId=@EmpId

End

Fetch all Employee Records

Create procedure spGetAllEmployees

as

Begin

select *

from tblEmployee

order by EmployeeId

End

Now, our Database part has been completed. So, we will proceed to create the MVC application using Visual Studio.

Create MVC Web Application

Open Visual Studio and select File >> New >> Project.

After selecting the project, a “New Project” dialog will open. Select .NET Core inside Visual C# menu from the left panel.
Then, select “ASP.NET Core Web Application” from available project types. Put the name of the project as MVCDemoApp and press OK. Refer to this image.

After clicking on OK, a new dialog will open asking to select the project template. You can observe two drop-down menus at the top left of the template window. Select “.NET Core” and “ASP.NET Core 2.0” from these dropdowns. Then, select “Web application(Model-View-Controller)” template and press OK.

Now our project will open. You can observe that we have Models, Views and Controllers folders already created. We will be adding our files to these folders only.

Adding the Controller to the Application

Right click on Controllers folder and select Add >> New Item

An “Add New Item” dialog box will open. Select Web from the left panel, then select “MVC Controller Class” from templates panel, and put the name as EmployeeController.cs. Press OK.

Now our EmployeeController has been created. We will put all our business logic in this controller.

Adding the Model to the Application

Right click on Models folder and select Add >> Class. Name your class Employee.cs. This class will contain our Employee model properties.

Add one more class file to Models folder. Name it as EmployeeDataAccessLayer.cs . This class will contain our Database related operations.

Now, the Models folder has the following structure.

Open Employee.cs and put the following code in it. Since we are adding the required validators to the fields of Employee class, so we need to use System.ComponentModel.DataAnnotations at the top.

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.Linq;

using System.Threading.Tasks;

namespace MVCDemoApp.Models

{

public class Employee

{

public int ID { get; set; }

[Required]

public string Name { get; set; }

[Required]

public string Gender { get; set; }

[Required]

public string Department { get; set; }

[Required]

public string City { get; set; }

}

}

Open EmployeeDataAccessLayer.cs and put the following code to handle database operations. Make sure to put your connection string.

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Threading.Tasks;

namespace MVCDemoApp.Models

{

public class EmployeeDataAccessLayer

{

string connectionString = "Put Your Connection string here";

//To View all employees details

public IEnumerable<Employee> GetAllEmployees()

{

List<Employee> lstemployee = new List<Employee>();

using (SqlConnection con = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand("spGetAllEmployees", con);

cmd.CommandType = CommandType.StoredProcedure;

con.Open();

SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())

{

Employee employee = new Employee();

employee.ID = Convert.ToInt32(rdr["EmployeeID"]);

employee.Name = rdr["Name"].ToString();

employee.Gender = rdr["Gender"].ToString();

employee.Department = rdr["Department"].ToString();

employee.City = rdr["City"].ToString();

lstemployee.Add(employee);

}

con.Close();

}

return lstemployee;

}

//To Add new employee record

public void AddEmployee(Employee employee)

{

using (SqlConnection con = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand("spAddEmployee", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@Name", employee.Name);

cmd.Parameters.AddWithValue("@Gender", employee.Gender);

cmd.Parameters.AddWithValue("@Department", employee.Department);

cmd.Parameters.AddWithValue("@City", employee.City);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

}

}

//To Update the records of a particluar employee

public void UpdateEmployee(Employee employee)

{

using (SqlConnection con = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand("spUpdateEmployee", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@EmpId", employee.ID);

cmd.Parameters.AddWithValue("@Name", employee.Name);

cmd.Parameters.AddWithValue("@Gender", employee.Gender);

cmd.Parameters.AddWithValue("@Department", employee.Department);

cmd.Parameters.AddWithValue("@City", employee.City);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

}

}

//Get the details of a particular employee

public Employee GetEmployeeData(int? id)

{

Employee employee = new Employee();

using (SqlConnection con = new SqlConnection(connectionString))

{

string sqlQuery = "SELECT * FROM tblEmployee WHERE EmployeeID= " + id;

SqlCommand cmd = new SqlCommand(sqlQuery, con);

con.Open();

SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())

{

employee.ID = Convert.ToInt32(rdr["EmployeeID"]);

employee.Name = rdr["Name"].ToString();

employee.Gender = rdr["Gender"].ToString();

employee.Department = rdr["Department"].ToString();

employee.City = rdr["City"].ToString();

}

}

return employee;

}

//To Delete the record on a particular employee

public void DeleteEmployee(int? id)

{

using (SqlConnection con = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand("spDeleteEmployee", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@EmpId", id);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

}

}

}

}

Now, we will proceed to create our Views.

Adding Views to the Application

To add views for our controller class, we need to create a folder inside Views folder with the same name as our controller and then add our views to that folder.

Right-click on the Views folder, and then Add >> New Folder and name the folder as Employee.

Now Right click on the Views/Employee folder, and then select Add >> New Item.

An “Add New Item” dialog box will open. Select Web from the left panel, then select “MVC View Page” from templates panel, and put the name as Index.cshtml. Press OK.

Thus we have created our first view. Similarly add 4 more views in Views/Employee folder, Create.cshtml, Delete.cshtml, Details.cshtml, and Edit.cshtml.

Now, our Views folder will look like this

Since our Views has been created, we will put codes in View and Controller for performing CRUD operations.

Create View

This view will be used to Add new employee data to the database.

Open Create.cshtml and put following code into it.

@model MVCDemoApp.Models.Employee

@{

ViewData["Title"] = "Create";

}

<h2>Create</h2>

<h4>Employees</h4>

<hr />

<div class="row">

<div class="col-md-4">

<form asp-action="Create">

<div asp-validation-summary="ModelOnly" class="text-danger"></div>

<div class="form-group">

<label asp-for="Name" class="control-label"></label>

<input asp-for="Name" class="form-control" />

<span asp-validation-for="Name" class="text-danger"></span>

</div>

<div class="form-group">

<label asp-for="Gender" class="control-label"></label>

<select asp-for="Gender" class="form-control">

<option value="">-- Select Gender --</option>

<option value="Male">Male</option>

<option value="Female">Female</option>

</select>

<span asp-validation-for="Gender" class="text-danger"></span>

</div>

<div class="form-group">

<label asp-for="Department" class="control-label"></label>

<input asp-for="Department" class="form-control" />

<span asp-validation-for="Department" 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">

<input type="submit" value="Create" class="btn btn-default" />

</div>

</form>

</div>

</div>

<div>

<a asp-action="Index">Back to List</a>

</div>

@section Scripts {

@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}

}

To handle database operations, we will create an object of EmployeeDataAccessLayer class inside the EmployeeController class.

public class EmployeeController : Controller

{

EmployeeDataAccessLayer objemployee = new EmployeeDataAccessLayer();

// GET: /<controller>/

public IActionResult Index()

{

}

}

To handle the business logic of create, open EmployeeController.cs and put following code into it.

[HttpGet]

public IActionResult Create()

{

return View();

}

[HttpPost]

[ValidateAntiForgeryToken]

public IActionResult Create([Bind] Employee employee)

{

if (ModelState.IsValid)

{

objemployee.AddEmployee(employee);

return RedirectToAction("Index");

}

return View(employee);

}

The [Bind] attribute is used with parameter “employee” to protect against over-posting.To know more about over-posting visit here

Index View

This view will be displaying all the employee records present in the database. Additionally, we will also be providing action methods Edit, Details and Delete on each record.

Open Index.cshtml and put following code in it

@model IEnumerable<MVCDemoApp.Models.Employee>

@{

ViewData["Title"] = "Index";

}

<h2>Index</h2>

<p>

<a asp-action="Create">Create New</a>

</p>

<table class="table">

<thead>

<tr>

<th>

@Html.DisplayNameFor(model => model.Name)

</th>

<th>

@Html.DisplayNameFor(model => model.Gender)

</th>

<th>

@Html.DisplayNameFor(model => model.Department)

</th>

<th>

@Html.DisplayNameFor(model => model.City)

</th>

<th></th>

</tr>

</thead>

<tbody>

@foreach (var item in Model)

{

<tr>

<td>

@Html.DisplayFor(modelItem => item.Name)

</td>

<td>

@Html.DisplayFor(modelItem => item.Gender)

</td>

<td>

@Html.DisplayFor(modelItem => item.Department)

</td>

<td>

@Html.DisplayFor(modelItem => item.City)

</td>

<td>

<a asp-action="Edit" asp-route-id="@item.ID">Edit</a> |

<a asp-action="Details" asp-route-id="@item.ID">Details</a> |

<a asp-action="Delete" asp-route-id="@item.ID">Delete</a>

</td>

</tr>

}

</tbody>

</table>

To handle the business logic of Index view, open EmployeeController.cs and add following code in Index method.

public IActionResult Index()

{

List<Employee> lstEmployee = new List<Employee>();

lstEmployee = objemployee.GetAllEmployees().ToList();

return View(lstEmployee);

}

Edit View

This view will enable us to edit an existing employee data.

Open Edit.cshtml and put following code into it.

@model MVCDemoApp.Models.Employee

@{

ViewData["Title"] = "Edit";

}

<h2>Edit</h2>

<h4>Employees</h4>

<hr />

<div class="row">

<div class="col-md-4">

<form asp-action="Edit">

<div asp-validation-summary="ModelOnly" class="text-danger"></div>

<input type="hidden" asp-for="ID" />

<div class="form-group">

<label asp-for="Name" class="control-label"></label>

<input asp-for="Name" class="form-control" />

<span asp-validation-for="Name" class="text-danger"></span>

</div>

<div class="form-group">

<label asp-for="Gender" class="control-label"></label>

<select asp-for="Gender" class="form-control">

<option value="">-- Select Gender --</option>

<option value="Male">Male</option>

<option value="Female">Female</option>

</select>

<span asp-validation-for="Gender" class="text-danger"></span>

</div>

<div class="form-group">

<label asp-for="Department" class="control-label"></label>

<input asp-for="Department" class="form-control" />

<span asp-validation-for="Department" 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">

<input type="submit" value="Save" class="btn btn-default" />

</div>

</form>

</div>

</div>

<div>

<a asp-action="Index">Back to List</a>

</div>

@section Scripts {

@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}

}

To handle the business logic of Edit view, open EmployeeController.cs and add following code to it.

[HttpGet]

public IActionResult Edit(int? id)

{

if (id == null)

{

return NotFound();

}

Employee employee = objemployee.GetEmployeeData(id);

if (employee == null)

{

return NotFound();

}

return View(employee);

}

[HttpPost]

[ValidateAntiForgeryToken]

public IActionResult Edit(int id, [Bind]Employee employee)

{

if (id != employee.ID)

{

return NotFound();

}

if (ModelState.IsValid)

{

objemployee.UpdateEmployee(employee);

return RedirectToAction("Index");

}

return View(employee);

}

As you can observe that we have two Edit action methods, one for HttpGet and another for HttpPost.The HttpGet Edit action method will fetch the employee data and populates the fields of edit view. Once the user clicks on Save button after editing the record, a Post request will be generated which is handled by HttpPost Edit action method.

Details View

This view will display the details of a particular employee.

Open Details.cshtml and put following code into it.

@model MVCDemoApp.Models.Employee

@{

ViewData["Title"] = "Details";

}

<h2>Details</h2>

<div>

<h4>Employees</h4>

<hr />

<dl class="dl-horizontal">

<dt>

@Html.DisplayNameFor(model => model.Name)

</dt>

<dd>

@Html.DisplayFor(model => model.Name)

</dd>

<dt>

@Html.DisplayNameFor(model => model.Gender)

</dt>

<dd>

@Html.DisplayFor(model => model.Gender)

</dd>

<dt>

@Html.DisplayNameFor(model => model.Department)

</dt>

<dd>

@Html.DisplayFor(model => model.Department)

</dd>

<dt>

@Html.DisplayNameFor(model => model.City)

</dt>

<dd>

@Html.DisplayFor(model => model.City)

</dd>

</dl>

</div>

<div>

<a asp-action="Edit" asp-route-id="@Model.ID">Edit</a> |

<a asp-action="Index">Back to List</a>

</div>

To handle the business logic of Details view,open EmployeeController.cs and add following code to it.

[HttpGet]

public IActionResult Details(int? id)

{

if (id == null)

{

return NotFound();

}

Employee employee = objemployee.GetEmployeeData(id);

if (employee == null)

{

return NotFound();

}

return View(employee);

}

Delete View

This view will help us to remove employee data .

Open Delete.cshtml and put following code into it.

@model MVCDemoApp.Models.Employee

@{

ViewData["Title"] = "Delete";

}

<h2>Delete</h2>

<h3>Are you sure you want to delete this?</h3>

<div>

<h4>Employees</h4>

<hr />

<dl class="dl-horizontal">

<dt>

@Html.DisplayNameFor(model => model.Name)

</dt>

<dd>

@Html.DisplayFor(model => model.Name)

</dd>

<dt>

@Html.DisplayNameFor(model => model.Gender)

</dt>

<dd>

@Html.DisplayFor(model => model.Gender)

</dd>

<dt>

@Html.DisplayNameFor(model => model.Department)

</dt>

<dd>

@Html.DisplayFor(model => model.Department)

</dd>

<dt>

@Html.DisplayNameFor(model => model.City)

</dt>

<dd>

@Html.DisplayFor(model => model.City)

</dd>

</dl>

<form asp-action="Delete">

<input type="hidden" asp-for="ID" />

<input type="submit" value="Delete" class="btn btn-default" /> |

<a asp-action="Index">Back to List</a>

</form>

</div>

To handle the business logic of Delete view, open EmployeeController.cs and add following code to it.

[HttpGet]

public IActionResult Delete(int? id)

{

if (id == null)

{

return NotFound();

}

Employee employee = objemployee.GetEmployeeData(id);

if (employee == null)

{

return NotFound();

}

return View(employee);

}

[HttpPost, ActionName("Delete")]

[ValidateAntiForgeryToken]

public IActionResult DeleteConfirmed(int? id)

{

objemployee.DeleteEmployee(id);

return RedirectToAction("Index");

}

To complete Delete operation we need two Delete methods accepting same parameter (Employee Id). But two methods with same name and method signature will create a compile time error and if we rename the Delete method then routing won’t be able to find it as asp.net maps URL segments to action methods by name. So, to resolve this issue we put ActionName(“Delete”) attribute to the DeleteConfirmed method. That attribute performs mapping for the routing system so that a URL that includes /Delete/ for a POST request will find the DeleteConfirmed method.

When we click on Delete link on the Index page, it will send a Get request and return a View of the employee using HttpGet Delete method. When we click on Delete button on this view, it will send a Post request to delete the record which is handled by the HttpPost DeleteConfirmed method. Performing a delete operation in response to a Get request (or for that matter, performing an edit operation, create operation, or any other operation that changes data) opens up a security hole. Hence, we have two separate methods.

And that’s it. We have created our first ASP.NET Core MVC web application. Before launching the application, we will configure route URLs. Open Startup.cs file to set the format for routing.Scroll down to app.UseMvc method, where you can set the route url.Make sure that your route url is set like this

app.UseMvc(routes =>

{

routes.MapRoute(

name: "default",

template: "{controller=Home}/{action=Index}/{id?}");

});

This url pattern sets HomeController as default controller and Index method as default action method, whereas Id parameter is optional. Default and optional route parameters need not be present in the URL path for a match. If we do not append any controller name in the URL then it will take HomeController as default controller and Index method of HomeController as default action method. Similarly, if we append only Controller name in the URL, it will navigate to Index action method of that controller.

Execution Demo

Now press F5 to launch the application and navigate to Employee controller by appending /Employeein the URL.

You can see the page as shown below.

Click on CreateNew to navigate to Create view. Add a new Employee record as shown in the image below.

If we miss the data in any field while creating employee record, we will get a required field validation error message.

After inserting the data in all the fields, click on “Create” button. The new employee record will be created and you will be redirected to the Index view, displaying records of all the employees. Here, we can also see action methods Edit, Details, and Delete.

If we want to edit an existing employee record, then click Edit action link. It will open Edit View as below where we can change the employee data.

Here we have changed the Department of employee Swati from Finance to HR.Click on “Save” to return to the Index view to see the updated changes as highlighted in the image below.

If we miss any fields while editing employee records, then Edit view will also throw required field validation error message

If you want to see the details of any Employee, then click on Details action link, which will open the Details view, as shown in the image below.

Click on “Back to List” to go back to Index view. Now, we will perform Delete operation on an employee named Venkat. Click on Delete action link which will open Delete view asking for a confirmation to delete.

Once we click on Delete button, it will send HttpPost request to delete employee record and we will be redirected to the Index view. Here, we can see that the employee with name Venkat has been removed from our record.

After awesome response of an published by me in the year 2013: Insert, Update, Delete In GridView Using ASP.Net C#. It now has more than 140 K views, therefore to help beginners I decided to rewrite the article i with stepbystep approach using ASP.NET MVC, since it is a hot topic in the market today. I have written this article focusing on beginners so they can understand the basics of MVC. Please read my previous article using the following links to understand the basics about MVC:

• ActionResult in ASP.NET MVC
• Creating an ASP.NET MVC Application

Step 1 : Create an MVC Application.

Now let us start with a stepbystep approach from the creation of simple MVC application as in the following:

1. "Start", then "All Programs" and select "Microsoft Visual Studio 2015".
   
   
2. "File", then "New" and click "Project..." then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click on OK. After clicking, the following window will appear:
   
   
   
   
3. As shown in the preceding screenshot, click on Empty template and check MVC option, then click OK. This will create an empty MVC web application whose Solution Explorer will look like the following:
   
   
   

Step 2: Create Model Class

Now let us create the model class named EmpModel.cs by right clicking on model folder as in the following screenshot:

 

Note: It is not mandatory that Model class should be in Model folder, it is just for better readability you can create this class anywhere in the solution explorer. This can be done by creating different folder name or without folder name or in a separate class library.

EmpModel.cs class code snippet:

1. public class EmpModel  
2.   {  
3.       [Display(Name = "Id")]  
4.       public int Empid { get; set; }  
5.   
6.       [Required(ErrorMessage = "First name is required.")]  
7.       public string Name { get; set; }  
8.   
9.       [Required(ErrorMessage = "City is required.")]  
10.       public string City { get; set; }  
11.   
12.       [Required(ErrorMessage = "Address is required.")]  
13.       public string Address { get; set; }  
14.   
15.   } 
    

In the above model class we have added some validation on properties with the help of DataAnnotations.

Step 3:  Create Controller.

Now let us add the MVC 5 controller as in the following screenshot:

 

After clicking on Add button it will show the following window. Now specify the Controller name as Employee with suffix Controller as in the following screenshot:

 

Note: The controller name must be having suffix as 'Controller' after specifying the name of controller.

 

After clicking on Add button controller is created with by default code that support CRUD operations and later on we can configure it as per our requirements.

Step 4 : Create Table and Stored procedures.

Now before creating the views let us create the table name Employee in database according to our model fields to store the details:

 

I hope you have created the same table structure as shown above. Now create the stored procedures to insert, update, view and delete the details as in the following code snippet:

To Insert Records 

1. Create procedure [dbo].[AddNewEmpDetails]  
2. (  
3.    @Name varchar (50),  
4.    @City varchar (50),  
5.    @Address varchar (50)  
6. )  
7. as  
8. begin  
9.    Insert into Employee values(@Name,@City,@Address)  
10. End 
    

To View Added Records

1. Create Procedure [dbo].[GetEmployees]  
2. as  
3. begin  
4.    select *from Employee  
5. End 
   

To Update Records

1. Create procedure [dbo].[UpdateEmpDetails]  
2. (  
3.    @EmpId int,  
4.    @Name varchar (50),  
5.    @City varchar (50),  
6.    @Address varchar (50)  
7. )  
8. as  
9. begin  
10.    Update Employee   
11.    set Name=@Name,  
12.    City=@City,  
13.    Address=@Address  
14.    where Id=@EmpId  
15. End 
    

To Delete Records

1. Create procedure [dbo].[DeleteEmpById]  
2. (  
3.    @EmpId int  
4. )  
5. as   
6. begin  
7.    Delete from Employee where Id=@EmpId  
8. End 
   

Step 5: Create Repository class.

Now create Repository folder and Add EmpRepository.cs class for database related operations, after adding the solution explorer will look like the following screenshot:

 

Now create methods in EmpRepository.cs to handle the CRUD operation as in the following screenshot:

EmpRepository.cs

1. public class EmpRepository    
2. {    
3.   
4.     private SqlConnection con;    
5.     //To Handle connection related activities    
6.     private void connection()    
7.     {    
8.         string constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();    
9.         con = new SqlConnection(constr);    
10.   
11.     }    
12.     //To Add Employee details    
13.     public bool AddEmployee(EmpModel obj)    
14.     {    
15.   
16.         connection();    
17.         SqlCommand com = new SqlCommand("AddNewEmpDetails", con);    
18.         com.CommandType = CommandType.StoredProcedure;    
19.         com.Parameters.AddWithValue("@Name", obj.Name);    
20.         com.Parameters.AddWithValue("@City", obj.City);    
21.         com.Parameters.AddWithValue("@Address", obj.Address);    
22.           
23.         con.Open();    
24.         int i = com.ExecuteNonQuery();    
25.         con.Close();    
26.         if (i >= 1)    
27.         {    
28.   
29.             return true;    
30.   
31.         }    
32.         else    
33.         {    
34.   
35.             return false;    
36.         }    
37.   
38.   
39.     }    
40.     //To view employee details with generic list     
41.     public List<EmpModel> GetAllEmployees()    
42.     {    
43.         connection();    
44.         List<EmpModel> EmpList =new List<EmpModel>();    
45.            
46.   
47.         SqlCommand com = new SqlCommand("GetEmployees", con);    
48.         com.CommandType = CommandType.StoredProcedure;    
49.         SqlDataAdapter da = new SqlDataAdapter(com);    
50.         DataTable dt = new DataTable();    
51.           
52.         con.Open();    
53.         da.Fill(dt);    
54.         con.Close();    
55.         //Bind EmpModel generic list using dataRow     
56.         foreach (DataRow dr in dt.Rows)    
57.         {    
58.   
59.             EmpList.Add(    
60.   
61.                 new EmpModel {    
62.   
63.                     Empid = Convert.ToInt32(dr["Id"]),    
64.                     Name =Convert.ToString( dr["Name"]),    
65.                     City = Convert.ToString( dr["City"]),    
66.                     Address = Convert.ToString(dr["Address"])    
67.   
68.                 }   
69.                 );
70.         }    
71.   
72.         return EmpList;
73.     }    
74.     //To Update Employee details    
75.     public bool UpdateEmployee(EmpModel obj)    
76.     {    
77.   
78.         connection();    
79.         SqlCommand com = new SqlCommand("UpdateEmpDetails", con);    
80.            
81.         com.CommandType = CommandType.StoredProcedure;    
82.         com.Parameters.AddWithValue("@EmpId", obj.Empid);    
83.         com.Parameters.AddWithValue("@Name", obj.Name);    
84.         com.Parameters.AddWithValue("@City", obj.City);    
85.         com.Parameters.AddWithValue("@Address", obj.Address);    
86.         con.Open();    
87.         int i = com.ExecuteNonQuery();    
88.         con.Close();    
89.         if (i >= 1)    
90.         {    
91.                 
92.             return true;
93.         }    
94.         else    
95.         {
96.             return false;    
97.         }
98.     }    
99.     //To delete Employee details    
100.     public bool DeleteEmployee(int Id)    
101.     {    
102.   
103.         connection();    
104.         SqlCommand com = new SqlCommand("DeleteEmpById", con);    
105.   
106.         com.CommandType = CommandType.StoredProcedure;    
107.         com.Parameters.AddWithValue("@EmpId", Id);    
108.            
109.         con.Open();    
110.         int i = com.ExecuteNonQuery();    
111.         con.Close();    
112.         if (i >= 1)    
113.         {
114.             return true;
115.         }    
116.         else    
117.         {    
118.   
119.             return false;    
120.         }
121.     }    
122. }   

Step 6 : Create Methods into the EmployeeController.cs file.

Now open the EmployeeController.cs and create the following action methods:

1. public class EmployeeController : Controller    
2. {    
3.       
4.     // GET: Employee/GetAllEmpDetails    
5.     public ActionResult GetAllEmpDetails()    
6.     {    
7.           
8.         EmpRepository EmpRepo = new EmpRepository();    
9.         ModelState.Clear();    
10.         return View(EmpRepo.GetAllEmployees());    
11.     }    
12.     // GET: Employee/AddEmployee    
13.     public ActionResult AddEmployee()    
14.     {    
15.         return View();    
16.     }    
17.   
18.     // POST: Employee/AddEmployee    
19.     [HttpPost]    
20.     public ActionResult AddEmployee(EmpModel Emp)    
21.     {    
22.         try    
23.         {    
24.             if (ModelState.IsValid)    
25.             {    
26.                 EmpRepository EmpRepo = new EmpRepository();    
27.   
28.                 if (EmpRepo.AddEmployee(Emp))    
29.                 {    
30.                     ViewBag.Message = "Employee details added successfully";    
31.                 }    
32.             }    
33.               
34.             return View();    
35.         }    
36.         catch    
37.         {    
38.             return View();    
39.         }    
40.     }    
41.   
42.     // GET: Employee/EditEmpDetails/5    
43.     public ActionResult EditEmpDetails(int id)    
44.     {    
45.         EmpRepository EmpRepo = new EmpRepository();    
46.   
47.           
48.   
49.         return View(EmpRepo.GetAllEmployees().Find(Emp => Emp.Empid == id));    
50.   
51.     }    
52.   
53.     // POST: Employee/EditEmpDetails/5    
54.     [HttpPost]    
55.       
56.     public ActionResult EditEmpDetails(int id,EmpModel obj)    
57.     {    
58.         try    
59.         {    
60.                 EmpRepository EmpRepo = new EmpRepository();    
61.                   
62.                 EmpRepo.UpdateEmployee(obj);
63.             return RedirectToAction("GetAllEmpDetails");    
64.         }    
65.         catch    
66.         {    
67.             return View();    
68.         }    
69.     }    
70.   
71.     // GET: Employee/DeleteEmp/5    
72.     public ActionResult DeleteEmp(int id)    
73.     {    
74.         try    
75.         {    
76.             EmpRepository EmpRepo = new EmpRepository();    
77.             if (EmpRepo.DeleteEmployee(id))    
78.             {    
79.                 ViewBag.AlertMsg = "Employee details deleted successfully";    
80.   
81.             }    
82.             return RedirectToAction("GetAllEmpDetails");    
83.   
84.         }    
85.         catch    
86.         {    
87.             return View();    
88.         }    
89.     }
90. }    

Step 7: Create Views.

Create the Partial view to Add the employees

To create the Partial View to add Employees, right click on ActionResult method and then click Add view. Now specify the view name, template name and model class in EmpModel.cs and click on Add button as in the following screenshot:

 

After clicking on Add button it generates the strongly typed view whose code is given below:

AddEmployee.cshtml

1. @model CRUDUsingMVC.Models.EmpModel  
2.   
3.   
4. @using (Html.BeginForm())  
5. {  
6.   
7.     @Html.AntiForgeryToken()  
8.   
9.     <div class="form-horizontal">  
10.         <h4>Add Employee</h4>  
11.         <div>  
12.             @Html.ActionLink("Back to Employee List", "GetAllEmpDetails")  
13.         </div>  
14.         <hr />  
15.         @Html.ValidationSummary(true, "", new { @class = "text-danger" })  
16.   
17.   
18.         <div class="form-group">  
19.             @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })  
20.             <div class="col-md-10">  
21.                 @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })  
22.                 @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })  
23.             </div>  
24.         </div>  
25.   
26.         <div class="form-group">  
27.             @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })  
28.             <div class="col-md-10">  
29.                 @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })  
30.                 @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })  
31.             </div>  
32.         </div>  
33.   
34.         <div class="form-group">  
35.             @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })  
36.             <div class="col-md-10">  
37.                 @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })  
38.                 @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })  
39.             </div>  
40.         </div>  
41.   
42.         <div class="form-group">  
43.             <div class="col-md-offset-2 col-md-10">  
44.                 <input type="submit" value="Save" class="btn btn-default" />  
45.             </div>  
46.         </div>  
47.         <div class="form-group">  
48.             <div class="col-md-offset-2 col-md-10" style="color:green">  
49.                 @ViewBag.Message  
50.   
51.             </div>  
52.         </div>  
53.     </div>  
54.   
55. }  
56.   
57. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
58. <script src="~/Scripts/jquery.validate.min.js"></script>  
59. <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script> 
    

To View Added Employees

To view the employee details let us create the partial view named GetAllEmpDetails:

 

Now click on add button, it will create GetAllEmpDetails.cshtml strongly typed view whose code is given below:

GetAllEmpDetails.CsHtml

1. @model IEnumerable<CRUDUsingMVC.Models.EmpModel>  
2.   
3. <p>  
4.     @Html.ActionLink("Add New Employee", "AddEmployee")  
5. </p>  
6.   
7. <table class="table">  
8.     <tr>  
9.   
10.         <th>  
11.             @Html.DisplayNameFor(model => model.Name)  
12.         </th>  
13.         <th>  
14.             @Html.DisplayNameFor(model => model.City)  
15.         </th>  
16.         <th>  
17.             @Html.DisplayNameFor(model => model.Address)  
18.         </th>  
19.         <th></th>  
20.     </tr>  
21.   
22.     @foreach (var item in Model)  
23.     {  
24.         @Html.HiddenFor(model => item.Empid)  
25.         <tr>  
26.   
27.             <td>  
28.                 @Html.DisplayFor(modelItem => item.Name)  
29.             </td>  
30.             <td>  
31.                 @Html.DisplayFor(modelItem => item.City)  
32.             </td>  
33.             <td>  
34.                 @Html.DisplayFor(modelItem => item.Address)  
35.             </td>  
36.             <td>  
37.                 @Html.ActionLink("Edit", "EditEmpDetails", new { id = item.Empid }) |  
38.                 @Html.ActionLink("Delete", "DeleteEmp", new { id = item.Empid }, new { onclick = "return confirm('Are sure wants to delete?');" })  
39.             </td>  
40.         </tr>  
41.   
42.     }  
43.   
44. </table> 
    

To Update Added Employees

Follow the same procedure and create EditEmpDetails view to edit the employees. After creating the view the code will be like the following:

EditEmpDetails.cshtml 

1. @model CRUDUsingMVC.Models.EmpModel  
2.   
3.   
4. @using (Html.BeginForm())  
5. {  
6.     @Html.AntiForgeryToken()  
7.   
8.     <div class="form-horizontal">  
9.         <h4>Update Employee Details</h4>  
10.         <hr />  
11.         <div>  
12.             @Html.ActionLink("Back to Details", "GetAllEmployees")  
13.         </div>  
14.         <hr />  
15.         @Html.ValidationSummary(true, "", new { @class = "text-danger" })  
16.         @Html.HiddenFor(model => model.Empid)  
17.   
18.         <div class="form-group">  
19.             @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })  
20.             <div class="col-md-10">  
21.                 @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })  
22.                 @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })  
23.             </div>  
24.         </div>  
25.   
26.         <div class="form-group">  
27.             @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })  
28.             <div class="col-md-10">  
29.                 @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })  
30.                 @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })  
31.             </div>  
32.         </div>  
33.   
34.         <div class="form-group">  
35.             @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })  
36.             <div class="col-md-10">  
37.                 @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })  
38.                 @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })  
39.             </div>  
40.         </div>  
41.   
42.         <div class="form-group">  
43.             <div class="col-md-offset-2 col-md-10">  
44.                 <input type="submit" value="Update" class="btn btn-default" />  
45.             </div>  
46.         </div>  
47.     </div>  
48. }  
49. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
50. <script src="~/Scripts/jquery.validate.min.js"></script>  
51. <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script> 
    

Step 8 : Configure Action Link to Edit and delete the records as in the following figure:

 

The above ActionLink I have added in GetAllEmpDetails.CsHtml view because from there we will delete and update the records.

 

Step 9: Configure RouteConfig.cs to set default action as in the following code snippet:

1. public class RouteConfig  
2.  {  
3.      public static void RegisterRoutes(RouteCollection routes)  
4.      {  
5.          routes.IgnoreRoute("{resource}.axd/{*pathInfo}");  
6.   
7.          routes.MapRoute(  
8.              name: "Default",  
9.              url: "{controller}/{action}/{id}",  
10.              defaults: new { controller = "Employee", action = "AddEmployee", id = UrlParameter.Optional }  
11.          );  
12.      }  
13.  } 
    

From the above RouteConfig.cs the default action method we have set is AddEmployee. It means that after running the application the AddEmployee view will be executed first.

Now after adding the all model, views and controller our solution explorer will be look like as in the following screenshot:

 

 

Step 10: Run the Application

 

Now run the application the AddEmployee view will appear as:

 

 

Click on save button, the model state validation will fire, as per validation we have set into the EmpModel.cs class:

 

 

Now enter the details and on clicking save button, the records get added into the database and the following message appears.

 

Now click on Back to Employee List hyperlink, it will be redirected to employee details grid as in the following screenshot:

 

 

Now similar to above screenshot, add another record, then the list will be as in the following screenshot:

 

 

Now click on Edit button of one of the record, then it will be redirected to Edit view as in the following screenshot:

 

 

Now click on Update button, on clicking, the records will be updated into the database. Click Back to Details hyperlink then it will be redirected to the Employee list table with updated records as in the following screenshot:

 

 

Now click on delete button for one of the records, then the following confirmation box appears (we have set configuration in ActionLink):

 

 

Now click on OK button, then the updated Employee list table will be like the following screenshot:

 

From the preceding examples we have learned how to implement CRUD operations in ASP.NET MVC using ADO.NET.

Note:

• Configure the database connection in the web.config file depending on your database server location.
• Download the Zip file of the sample application for a better understanding.
• Since this is a demo, it might not be using proper standards, so improve it depending on your skills
• This application is created completely focusing on beginners.

In this post, I’m going to discuss the CRUD (Create, Read, Update, and Delete) Operations in an ASP.NET MVC application by using raw ADO.NET. Most of the new learners, who started to learn MVC asked this frequently. That’s the main reason I wrote this tutorial. In this article, I'm going to explain step by step procedure from DB table creation to all MVC files.

 

Software Requirements

 

For this particular application, I have used the following configuration:

1. Visual Studio 2015
2. SQL Server 2008

In order to keep my hand clean, I have used a simple table to do the CRUD operation.

 

Step 1: Execute the following script on your DB.

1. SET ANSI_NULLS ON  
2. GO  
3.   
4. SET QUOTED_IDENTIFIER ON  
5. GO  
6.   
7. SET ANSI_PADDING ON  
8. GO  
9.   
10. CREATE TABLE[dbo]. [tblStudent](  
11.   [student_id][int] IDENTITY(1, 1) NOT NULL,  
12.   [student_name][varchar](50) NOT NULL,  
13.   [stduent_age][int] NOT NULL,  
14.   [student_gender][varchar](6) NOT NULL,  
15.   CONSTRAINT[PK_tblStudent] PRIMARY KEY CLUSTERED(  
16.     [student_id] ASC  
17.   ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]  
18. ) ON[PRIMARY]  
19.   
20. GO  
21.   
22. SET ANSI_PADDING OFF  
23. GO 

Step 2: Create an “Empty” ASP.NET MVC application in Visual Studio 2015.

 

 

Step 3:

 

Add an “Empty” Controller by right-clicking on the “Controller” folder. Select “Add” then select “Controller..”. In the popup select “MVC 5 Controller”, then click “Add”. In the next popup, you should give the name  “CRUDController”.

 

 

After adding the controller, you may notice as per the “convention” in ASP.NET MVC under the “Views” folder, a new folder named “CRUD” also created.

 

 

 

Step 4:

 

Our DB access code is going to be placed inside the “Models” folder. The model is just a “Class” file. So we are going to create a Model class for our purpose as below:

1. Right-click on the “Model” folder. In the context menu select “Add” then choose “New item..”.
2. In the popup, select “Code” then choose “Class” and name the class file as “CRUDModel” then click “Add”. That’s all!

 

Step 5:

 

Till now we created classes for “Controller” and “Model”. We didn’t create any views till now. In this step, we are going to create a view, which is going to act as a “home” page for our application. In order to create the view:

1. Right-click on the “CRUD” folder under the “Views” folder in the context menu select “Add” then choose “View..”.
2. In the popup, give “View name” and uncheck the “Use a layout page” checkbox. Finally, click the “Add” button.

 

Step 6:

 

We don’t have a controller named “Default”, which is specified in the “RouteConfig.cs” file. We need to change the controller’s name to “CRUD”, in the default route values.

 

Route.config

1. using System;  
2. using System.Collections.Generic;  
3. using System.Linq;  
4. using System.Web;  
5. using System.Web.Mvc;  
6. using System.Web.Routing;  
7.   
8. namespace MVCWithADO {  
9.     public class RouteConfig {  
10.         public static void RegisterRoutes(RouteCollection routes) {  
11.             routes.IgnoreRoute("{resource}.axd/{*pathInfo}");  
12.   
13.             routes.MapRoute(  
14.                 name: "Default",  
15.                 url: "{controller}/{action}/{id}",  
16.                 defaults: new { controller = "CRUD", action = "Index", id = UrlParameter.Optional }  
17.             );  
18.         }  
19.     }  
20. } 

After the above change, just press F5 in Visual Studio, to verify that our application works fine without any error.

 

Step 7:

 

In order to achieve the complete CRUD operation, I’m going to add a number of views as described in Step 5. Here is the complete list of views and it’s purpose.

View	Purpose
Home.cshtml	This is the default view. Loaded when the application launched. Will display all the records in the table
Create.cshtml	Displays control’s to insert the record. Will be rendered when the “Add New Record” button clicked on the “Home.cshtml” view.
Edit.cshtml	Displays control’s to edit the record. Will be rendered when the “Edit” button clicked on the “Home.cshtml” view.

 

Step 8:

 

The model class contains all the “Data Access” logic. In other words, it will interact with the Database and give it back to “View” through “Controller”.

 

CRUDModel.cs

1. using System.Data;  
2. using System.Data.SqlClient;  
3.   
4. namespace MVCWithADO.Models {  
5.     public class CRUDModel {  
6.         /// <summary>    
7.         /// Get all records from the DB    
8.         /// </summary>    
9.         /// <returns>Datatable</returns>    
10.         public DataTable GetAllStudents() {  
11.             DataTable dt = new DataTable();  
12.             string strConString = @ "Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";  
13.             using(SqlConnection con = new SqlConnection(strConString)) {  
14.                 con.Open();  
15.                 SqlCommand cmd = new SqlCommand("Select * from tblStudent", con);  
16.                 SqlDataAdapter da = new SqlDataAdapter(cmd);  
17.                 da.Fill(dt);  
18.             }  
19.             return dt;  
20.         }  
21.   
22.         /// <summary>    
23.         /// Get student detail by Student id    
24.         /// </summary>    
25.         /// <param name="intStudentID"></param>    
26.         /// <returns></returns>    
27.         public DataTable GetStudentByID(int intStudentID) {  
28.             DataTable dt = new DataTable();  
29.   
30.             string strConString = @ "Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";  
31.   
32.             using(SqlConnection con = new SqlConnection(strConString)) {  
33.                 con.Open();  
34.                 SqlCommand cmd = new SqlCommand("Select * from tblStudent where student_id=" + intStudentID, con);  
35.                 SqlDataAdapter da = new SqlDataAdapter(cmd);  
36.                 da.Fill(dt);  
37.             }  
38.             return dt;  
39.         }  
40.   
41.         /// <summary>    
42.         /// Update the student details    
43.         /// </summary>    
44.         /// <param name="intStudentID"></param>    
45.         /// <param name="strStudentName"></param>    
46.         /// <param name="strGender"></param>    
47.         /// <param name="intAge"></param>    
48.         /// <returns></returns>    
49.         public int UpdateStudent(int intStudentID, string strStudentName, string strGender, int intAge) {  
50.             string strConString = @ "Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";  
51.   
52.             using(SqlConnection con = new SqlConnection(strConString)) {  
53.                 con.Open();  
54.                 string query = "Update tblStudent SET student_name=@studname, student_age=@studage , student_gender=@gender where student_id=@studid";  
55.                 SqlCommand cmd = new SqlCommand(query, con);  
56.                 cmd.Parameters.AddWithValue("@studname", strStudentName);  
57.                 cmd.Parameters.AddWithValue("@studage", intAge);  
58.                 cmd.Parameters.AddWithValue("@gender", strGender);  
59.                 cmd.Parameters.AddWithValue("@studid", intStudentID);  
60.                 return cmd.ExecuteNonQuery();  
61.             }  
62.         }  
63.   
64.         /// <summary>    
65.         /// Insert Student record into DB    
66.         /// </summary>    
67.         /// <param name="strStudentName"></param>    
68.         /// <param name="strGender"></param>    
69.         /// <param name="intAge"></param>    
70.         /// <returns></returns>    
71.         public int InsertStudent(string strStudentName, string strGender, int intAge) {  
72.             string strConString = @ "Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";  
73.   
74.             using(SqlConnection con = new SqlConnection(strConString)) {  
75.                 con.Open();  
76.                 string query = "Insert into tblStudent (student_name, student_age,student_gender) values(@studname, @studage , @gender)";  
77.                 SqlCommand cmd = new SqlCommand(query, con);  
78.                 cmd.Parameters.AddWithValue("@studname", strStudentName);  
79.                 cmd.Parameters.AddWithValue("@studage", intAge);  
80.                 cmd.Parameters.AddWithValue("@gender", strGender);  
81.                 return cmd.ExecuteNonQuery();  
82.             }  
83.         }  
84.   
85.         /// <summary>    
86.         /// Delete student based on ID    
87.         /// </summary>    
88.         /// <param name="intStudentID"></param>    
89.         /// <returns></returns>    
90.         public int DeleteStudent(int intStudentID) {  
91.             string strConString = @ "Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";  
92.   
93.             using(SqlConnection con = new SqlConnection(strConString)) {  
94.                 con.Open();  
95.                 string query = "Delete from tblStudent where student_id=@studid";  
96.                 SqlCommand cmd = new SqlCommand(query, con);  
97.                 cmd.Parameters.AddWithValue("@studid", intStudentID);  
98.                 return cmd.ExecuteNonQuery();  
99.             }  
100.         }  
101.     }  
102. } 

CRUDController.cs

 

In an MVC application, controller is the entry point. The following code contains all the action methods for the complete CRUD operation.

1. using System;  
2. using System.Collections.Generic;  
3. using System.Linq;  
4. using System.Web;  
5. using System.Web.Mvc;  
6. using System.Data;  
7. using System.Data.SqlClient;  
8. using MVCWithADO.Models;  
9. namespace MVCWithADO.Controllers {  
10.     public class CRUDController: Controller {  
11.         /// <summary>    
12.         /// First Action method called when page loads    
13.         /// Fetch all the rows from DB and display it    
14.         /// </summary>    
15.         /// <returns>Home View</returns>    
16.         public ActionResult Index() {  
17.             CRUDModel model = new CRUDModel();  
18.             DataTable dt = model.GetAllStudents();  
19.             return View("Home", dt);  
20.         }  
21.   
22.         /// <summary>    
23.         /// Action method, called when the "Add New Record" link clicked    
24.         /// </summary>    
25.         /// <returns>Create View</returns>    
26.         public ActionResult Insert() {  
27.             return View("Create");  
28.         }  
29.   
30.         /// <summary>    
31.         /// Action method, called when the user hit "Submit" button    
32.         /// </summary>    
33.         /// <param name="frm">Form Collection  Object</param>    
34.         /// <param name="action">Used to differentiate between "submit" and "cancel"</param>    
35.         /// <returns></returns>    
36.         public ActionResult InsertRecord(FormCollection frm, string action) {  
37.             if (action == "Submit") {  
38.                 CRUDModel model = new CRUDModel();  
39.                 string name = frm["txtName"];  
40.                 int age = Convert.ToInt32(frm["txtAge"]);  
41.                 string gender = frm["gender"];  
42.                 int status = model.InsertStudent(name, gender, age);  
43.                 return RedirectToAction("Index");  
44.             } else {  
45.                 return RedirectToAction("Index");  
46.             }  
47.         }  
48.   
49.         /// <summary>    
50.         /// Action method called when the user click "Edit" Link    
51.         /// </summary>    
52.         /// <param name="StudentID">Student ID</param>    
53.         /// <returns>Edit View</returns>    
54.         public ActionResult Edit(int StudentID) {  
55.             CRUDModel model = new CRUDModel();  
56.             DataTable dt = model.GetStudentByID(StudentID);  
57.             return View("Edit", dt);  
58.         }  
59.   
60.         /// <summary>    
61.         /// Actin method, called when users update the record or cancel the update.    
62.         /// </summary>    
63.         /// <param name="frm">Form Collection</param>    
64.         /// <param name="action">Denotes the action</param>    
65.         /// <returns>Home view</returns>    
66.         public ActionResult UpdateRecord(FormCollection frm, string action) {  
67.             if (action == "Submit") {  
68.                 CRUDModel model = new CRUDModel();  
69.                 string name = frm["txtName"];  
70.                 int age = Convert.ToInt32(frm["txtAge"]);  
71.                 string gender = frm["gender"];  
72.                 int id = Convert.ToInt32(frm["hdnID"]);  
73.                 int status = model.UpdateStudent(id, name, gender, age);  
74.                 return RedirectToAction("Index");  
75.             } else {  
76.                 return RedirectToAction("Index");  
77.             }  
78.         }  
79.   
80.         /// <summary>    
81.         /// Action method called when the "Delete" link clicked    
82.         /// </summary>    
83.         /// <param name="StudentID">Stutend ID to edit</param>    
84.         /// <returns>Home view</returns>    
85.         public ActionResult Delete(int StudentID) {  
86.             CRUDModel model = new CRUDModel();  
87.             model.DeleteStudent(StudentID);  
88.             return RedirectToAction("Index");  
89.         }  
90.     }  
91. } 

Views

 

Views are a combination of markup as well as server-side code. As you noticed the views "Home" and "Edit" take the ADO.NET object Datatable as a model. Also, for simplicity, I don't use "Layout".

 

Home.cshtml

1. @using System.Data  
2. @using System.Data.SqlClient  
3. @model System.Data.DataTable  
4. @{  
5. Layout = null;  
6. }  
7. <!DOCTYPE html>  
8. <html>  
9.   
10.      <head>  
11.           <meta name="viewport" content="width=device-width" />  
12.           <title>Home</title>  
13.      </head>  
14.   
15.      <body>  
16.           <form method="post" name="Display">  
17.                <h2>Home</h2>  
18.                @Html.ActionLink("Add New Record", "Insert")  
19.                <br />  
20.                @{  
21.                if (Model.Rows.Count > 0)  
22.                {  
23.                <table border="1">  
24.                     <thead>  
25.                          <tr>  
26.                               <td>  
27.                                    Student ID  
28.                               </td>  
29.                               <td>  
30.                                    Name  
31.                               </td>  
32.                               <td>  
33.                                    Age  
34.                               </td>  
35.                               <td>Gender</td>  
36.                          </tr>  
37.                     </thead>  
38.                     @foreach (DataRow dr in Model.Rows)  
39.                     {  
40.                     <tr>  
41.                          <td>@dr["student_id"].ToString() </td>  
42.                          <td>@dr["student_name"].ToString() </td>  
43.                          <td>@dr["student_age"].ToString() </td>  
44.                          <td>@dr["student_gender"].ToString() </td>  
45.                          <td>@Html.ActionLink("Edit ", "Edit", new { StudentID = dr["student_id"].ToString() })</td>  
46.                          <td>@Html.ActionLink("| Delete", "Delete", new { StudentID = dr["student_id"].ToString() })</td>  
47.                     </tr>  
48.                     }  
49.                </table>  
50.                <br />  
51.                }  
52.                else  
53.                {  
54.                <span> No records found!!</span>  
55.                }  
56.                }  
57.           </form>  
58.      </body>  
59.   
60. </html> 

Create.cshtml

1. <!DOCTYPE html>  
2. <html>  
3.   
4.     <head>  
5.         <meta name="viewport" content="width=device-width" />  
6.         <title>Insert</title>  
7.     </head>  
8.   
9.     <body>  
10.         <form id="frmDetail" method="post" action="@Url.Action(" InsertRecord")">  
11.             Enter Name:<input name="txtName" />  
12.             <br />  
13.             Enter Age:<input name="txtAge" />  
14.             <br />  
15.             Select Gender: <input type="radio" name="gender" value="male" checked>Male  
16.             <input type="radio" name="gender" value="female">Female  
17.             <br />  
18.             <input type="submit" value="Submit" name="action" />  
19.             <input type="submit" value="Cancel" name="action" />  
20.         </form>  
21.     </body>  
22.   
23. </html> 

Edit.cshtml

1. @using System.Data  
2. @using System.Data.SqlClient  
3. @model System.Data.DataTable  
4. @{  
5. ViewBag.Title = "EditView";  
6. }  
7. <html>  
8.   
9.     <head>  
10.         <script type="text/javascript">  
11.         </script>  
12.     </head>  
13.   
14.     <body>  
15.         <form id="frmDetail" method="post" action="@Url.Action(" UpdateRecord")">  
16.             Enter Name:<input name="txtName" value="@Model.Rows[0][" student_name"]" />  
17.             <br />  
18.             Enter Age:<input name="txtAge" value="@Model.Rows[0][" student_age"]" />  
19.             <br />  
20.             Select Gender:  
21.             @if (Model.Rows[0]["student_gender"].ToString().ToLower() == "male")  
22.             {  
23.             <input type="radio" name="gender" value="male" checked /> @Html.Raw("Male")  
24.             <input type="radio" name="gender" value="female" /> @Html.Raw("Female")  
25.             }  
26.             else  
27.             {  
28.             <input type="radio" name="gender" value="male"> @Html.Raw("Male")  
29.             <input type="radio" name="gender" value="female" checked /> @Html.Raw("Female")  
30.             }  
31.             <input type="hidden" name="hdnID" value="@Model.Rows[0][" student_id"]" />  
32.             <br />  
33.             <input type="submit" value="Submit" name="action" />  
34.             <input type="submit" value="Cancel" name="action" />  
35.         </form>  
36.     </body>  
37.   
38. </html> 

Readers, I hope you like this article. Let me know your thoughts as comments.