CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET

In this article you will learn about CRUD Operation with ASP.NET Core 2.1 MVC using ADO.NET.

Introduction

 
In this article, I am going to show you, how to Create, Read, Update, and Delete, also called a CRUD operation, in Asp.net core 2.1 MVC application with ADO.NET. We will be creating a sample customer record in order to perform the CRUD operation using Model in Asp.Net Core 2.1 MVC application uses stored procedure and ADO.Net.
 
We will be using Visual Studio 2017 (version 15.9.13 or above) and SQL Server 2017 or you can use SQL server 2008 or above version.
 
Prerequisites
  1. Install Visual studio 2017 Updated version 15.9.13
  2. Install .Net Core SDK 2.1 or above
  3. SQL Server 2017
Now we will create our Asp.Net Core 2.1 MVC Application.
 
First of all, we will create a Database, table, and a stored procedure
 

Creating Database, Table and Stored Procedures

 
Step 1 - To create a Database
 
Open your SQL server and use the following script to create “CoreMvcDB” Database
 
Create database CoreMvcDB
 
Step 2 - To Create a Table
 
Open your SQL Server and use the following script to create “tbl_Customer” table. 
  1. create Table tbl_Customer(  
  2.       
  3.    CustomerId int IDENTITY(1,1) NOT NULL primary key,      
  4.    Name Varchar(20) NOT NULL,   
  5.    Address varchar(100) not null,    
  6.    City Varchar(20) NOT NULL,      
  7.    Country Varchar(20) NOT NULL,      
  8.    Gender Varchar(6) NOT NULL ,  
  9.    MobileNo varchar(15) NOT NULL,  
  10.    MailId varchar(20) NOT NULL  
  11.       
  12. )  
Now we will be creating a stored procedure to add, delete, update, and get customer data.
 
Step 3 - To create a stored procedure
 
Now we will create a stored procedure to insert a customer record into the Database.
 
Open your SQL Server and use the following script to create the procedure:
 
To insert a CustomerRecord 
  1. Create procedure sp_AddCustomer       
  2. (      
  3.     @Name VARCHAR(20),    
  4.     @Address varchar(100),     
  5.     @City VARCHAR(20),      
  6.     @Country VARCHAR(20),      
  7.     @Gender VARCHAR(6),  
  8.     @MobileNo varchar(15),  
  9.     @MailId varchar(20)   
  10. )      
  11. As       
  12. Begin       
  13.     Insert into tbl_Customer (Name,Address,City,Country, Gender,MobileNo,MailId)       
  14.     Values (@Name,@Address,@City,@Country, @Gender,@MobileNo,@MailId)       
  15. End  
To Update a CustomerRecord
  1. Create procedure sp_UpdateCustomer        
  2. (        
  3.    @CustomerId INTEGER ,      
  4.    @Name VARCHAR(20),  
  5.    @Address varchar(100),        
  6.    @City VARCHAR(20),      
  7.    @Country VARCHAR(20),      
  8.    @Gender VARCHAR(6),  
  9.     @MobileNo varchar(15),  
  10.     @MailId varchar(20)  
  11. )        
  12. As        
  13. begin        
  14.    Update tbl_Customer         
  15.    set Name=@Name,    
  16.    Address=@Address,      
  17.    City=@City,        
  18.    Country=@Country,      
  19.    Gender=@Gender,   
  20.    MobileNo=@MobileNo,    
  21.    MailId= @MailId  
  22.   
  23.    where CustomerId=@CustomerId        
  24. End  
To Delete a CustomerRecord
  1. Create procedure sp_DeleteCustomer       
  2. (        
  3.    @CustomerId int        
  4. )        
  5. As         
  6. begin        
  7.    Delete from tbl_Customer where CustomerId=@CustomerId        
  8. End  
To Get a Single CustomerRecord
  1. Create procedure sp_GetCustomerByID  
  2. (      
  3.     @CustomerId int  
  4. )      
  5. As       
  6. Begin       
  7.     SELECT * FROM tbl_Customer WHERE CustomerID= @CustomerId  
  8. End  
To View All CustomerRecords
  1. Create procedure sp_GetAllCustomers      
  2. As      
  3. Begin      
  4.     select   
  5.         CustomerId,  
  6.         Name,  
  7.         Address,  
  8.         City,  
  9.         Country,      
  10.         Gender,  
  11.         MobileNo,  
  12.         MailId  
  13.     from tbl_Customer      
  14. End  
Now, our database part has been completed. So we will create Asp.Net core MVC application.
 
Step 1 - Create an ASP.NET Core 2.1 MVC Project
 
Open Visual Studio and select File -> New -> Project.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
After selecting the project, a "New Project" dialog will open. Select .NET Core inside the Visual C# menu from the left side panel.
 
Then, select “ASP.NET Core web application“ from available project types. Give a name to the project as “AspNetCoreCURDMVC-­Demo” and press OK.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
After clicking on the OK button, a new dialog will open to select the project template. You can see two drop-down menus at the top left of the template window. Then, select “.NET Core” and “ ASP.NET Core 2.1” from these dropdowns. Select “ Web application (Model-View-Controller)” template and press OK to create Asp.Net Core MVC project.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
Step 2 - Add the model to the application
 
Right click on the solution and add a new folder named as “Models”. Now add a new class on Models folder.
 
Right click on Models folder and select add a new class, name of your class Customer.cs. This class will contain our Customer model properties.
 
Now open Customer.cs class and put the following code in it. We are also adding the required validators to the fields of Customer class, so we need to use System.ComponentModel.DataAnnotations at the top.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using System.ComponentModel.DataAnnotations;  
  6. namespace AspNetCoreCURDMVC_Demo.Models  
  7. {  
  8.     public class Customer  
  9.     {  
  10.         public int ID { getset; }  
  11.         [Required(ErrorMessage = "Enter Your Name")]  
  12.         [StringLength(15, ErrorMessage = "Name should be less than or equal to Fifteen characters.")]  
  13.         public string Name { getset; }  
  14.         [Required(ErrorMessage = "Enter Your Address")]  
  15.         [StringLength(50, ErrorMessage = "Name should be less than or equal to fifty characters.")]  
  16.         public string Address { getset; }  
  17.         [Required(ErrorMessage = "Enter Your Gender")]  
  18.         [StringLength(10, ErrorMessage = "Name should be less than or equal to ten characters.")]  
  19.         public string Gender { getset; }  
  20.         [Required(ErrorMessage = "Enter Your City")]  
  21.         [StringLength(10, ErrorMessage = "Name should be less than or equal to ten characters.")]  
  22.         public string City { getset; }  
  23.         [Required(ErrorMessage = "Enter Your Country")]  
  24.         [StringLength(10, ErrorMessage = "Name should be less than or equal to ten characters.")]  
  25.         public string Country { getset; }  
  26.         [Required(ErrorMessage = "Your must provide a Mobile Number")]  
  27.         [RegularExpression(@"^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Not a valid Mobile number")]  
  28.         public string Mobile { getset; }  
  29.         [Required(ErrorMessage = "Enter Your Mail ID")]  
  30.         [RegularExpression(@"^[\w-\._\+%]+@(?:[\w-]+\.)+[\w]{2,6}$", ErrorMessage = "Please enter a valid email address")]  
  31.         public string Email { getset; }  
  32.     }  
  33. }  
In the above code, I have used some attributes to check validation of controls, which are based on control values. For textbox name, Address, City and Country Validation, I have put [Required(ErrorMessage = "Enter Your Name")].
 
This code will be executed if your input is empty in controls.
 
Now, if the user puts something but this does not satisfy the standard validation, then the code will be given below.
  1. [StringLength(15, ErrorMessage = "Name should be less than or equal to fifteen characters.")]  
The user puts only 15 characters to control input values. Like this you can check for others.
 
In Mobile, I can enter only 10 digit valid mobile no.
  1. [RegularExpression(@"^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Not a valid Mobile number")]  
In email, I can enter only a valid Email Id with @ symbol.
  1. [RegularExpression(@"^[\w-\._\+%]+@(?:[\w-]+\.)+[\w]{2,6}$", ErrorMessage = "Please enter a valid email address")]  
Now add one more class file to Models folder. Name it as CustomerDataAccess_Layer.cs. This class will contain our database related operations.
 
Now, the Models folder has the following structure.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
Open CustomerDataAccess_Layer.cs and put the following code to handle the database operations. Make sure to put your connection string.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using System.Data;  
  6. using System.Data.SqlClient;  
  7.   
  8. namespace AspNetCoreCURDMVC_Demo.Models  
  9. {  
  10.     public class CustomerDataAccess_Layer  
  11.     {  
  12.         string connectionString = "put your connection string here";  
  13.         //To View all Customers details      
  14.         public IEnumerable<Customer> GetAllCustomers()  
  15.         {  
  16.             List<Customer> lstCustomer = new List<Customer>();  
  17.   
  18.             using (SqlConnection con = new SqlConnection(connectionString))  
  19.             {  
  20.                 SqlCommand cmd = new SqlCommand("sp_GetAllCustomers", con);  
  21.                 cmd.CommandType = CommandType.StoredProcedure;  
  22.   
  23.                 con.Open();  
  24.                 SqlDataReader sdr = cmd.ExecuteReader();  
  25.   
  26.                 while (sdr.Read())  
  27.                 {  
  28.                     Customer Customer = new Customer();  
  29.   
  30.                     Customer.ID = Convert.ToInt32(sdr["CustomerID"]);  
  31.                     Customer.Name = sdr["Name"].ToString();  
  32.                     Customer.Address = sdr["Address"].ToString();  
  33.                     Customer.Gender = sdr["Gender"].ToString();  
  34.                     Customer.Country = sdr["Country"].ToString();  
  35.                     Customer.City = sdr["City"].ToString();  
  36.                     Customer.Mobile = sdr["MobileNo"].ToString();  
  37.                     Customer.Email = sdr["MailId"].ToString();  
  38.                     lstCustomer.Add(Customer);  
  39.                 }  
  40.                 con.Close();  
  41.             }  
  42.             return lstCustomer;  
  43.         }  
  44.   
  45.         //To Add new Customer record      
  46.         public void AddCustomer(Customer Customer)  
  47.         {  
  48.             using (SqlConnection con = new SqlConnection(connectionString))  
  49.             {  
  50.                 SqlCommand cmd = new SqlCommand("sp_AddCustomer", con);  
  51.                 cmd.CommandType = CommandType.StoredProcedure;  
  52.   
  53.                 cmd.Parameters.AddWithValue("@Name", Customer.Name);  
  54.                 cmd.Parameters.AddWithValue("@Address", Customer.Address);  
  55.                 cmd.Parameters.AddWithValue("@Gender", Customer.Gender);  
  56.                 cmd.Parameters.AddWithValue("@Country", Customer.Country);  
  57.                 cmd.Parameters.AddWithValue("@City", Customer.City);  
  58.                 cmd.Parameters.AddWithValue("@MobileNo", Customer.Mobile);  
  59.                 cmd.Parameters.AddWithValue("@MailId", Customer.Email);  
  60.                 con.Open();  
  61.                 cmd.ExecuteNonQuery();  
  62.                 con.Close();  
  63.             }  
  64.         }  
  65.   
  66.         //To Update the records of a particluar Customer    
  67.         public void UpdateCustomer(Customer Customer)  
  68.         {  
  69.             using (SqlConnection con = new SqlConnection(connectionString))  
  70.             {  
  71.                 SqlCommand cmd = new SqlCommand("sp_UpdateCustomer", con);  
  72.                 cmd.CommandType = CommandType.StoredProcedure;  
  73.   
  74.                 cmd.Parameters.AddWithValue("@CustomerId", Customer.ID);  
  75.                 cmd.Parameters.AddWithValue("@Name", Customer.Name);  
  76.                 cmd.Parameters.AddWithValue("@Address", Customer.Address);  
  77.                 cmd.Parameters.AddWithValue("@Gender", Customer.Gender);  
  78.                 cmd.Parameters.AddWithValue("@Country", Customer.Country);  
  79.                 cmd.Parameters.AddWithValue("@City", Customer.City);  
  80.                 cmd.Parameters.AddWithValue("@MobileNo", Customer.Mobile);  
  81.                 cmd.Parameters.AddWithValue("@MailId", Customer.Email);  
  82.   
  83.                 con.Open();  
  84.                 cmd.ExecuteNonQuery();  
  85.                 con.Close();  
  86.             }  
  87.         }  
  88.   
  89.         //Get the details of a particular Customer    
  90.         public Customer GetCustomerData(int? id)  
  91.         {  
  92.             Customer Customer = new Customer();  
  93.   
  94.             using (SqlConnection con = new SqlConnection(connectionString))  
  95.             {  
  96.   
  97.                 SqlCommand cmd = new SqlCommand("sp_GetCustomerByID", con);  
  98.                 cmd.CommandType = CommandType.StoredProcedure;  
  99.   
  100.                 cmd.Parameters.AddWithValue("@CustomerId", id);  
  101.                 con.Open();  
  102.                 SqlDataReader sdr = cmd.ExecuteReader();  
  103.   
  104.                 while (sdr.Read())  
  105.                 {  
  106.                     Customer.ID = Convert.ToInt32(sdr["CustomerID"]);  
  107.                     Customer.Name = sdr["Name"].ToString();  
  108.                     Customer.Address = sdr["Address"].ToString();  
  109.                     Customer.Gender = sdr["Gender"].ToString();  
  110.                     Customer.Country = sdr["Country"].ToString();  
  111.                     Customer.City = sdr["City"].ToString();  
  112.                     Customer.Mobile = sdr["MobileNo"].ToString();  
  113.                     Customer.Email = sdr["MailId"].ToString();  
  114.                 }  
  115.             }  
  116.             return Customer;  
  117.         }  
  118.   
  119.         //To Delete the record on a particular Customer    
  120.         public void DeleteCustomer(int? id)  
  121.         {  
  122.   
  123.             using (SqlConnection con = new SqlConnection(connectionString))  
  124.             {  
  125.                 SqlCommand cmd = new SqlCommand("sp_DeleteCustomer", con);  
  126.                 cmd.CommandType = CommandType.StoredProcedure;  
  127.   
  128.                 cmd.Parameters.AddWithValue("@CustomerId", id);  
  129.   
  130.                 con.Open();  
  131.                 cmd.ExecuteNonQuery();  
  132.                 con.Close();  
  133.             }  
  134.         }  
  135.     }  
  136. }   
Step 3 - Adding the new controller to the application
 
Right click on Controllers folder and select add New Item.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
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 CustomerController.cs. Press OK.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
Now our CustomerController has been created. We will put our business logic in this controller.
 
Step 4 - 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 Customer.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
Now Right click on the Views/Customer folder, and then select Add >> New Item.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
An “Add New Item” dialog box will open. Select Web from the left panel, then select “Razor View Page” from templates panel, and put the name as Index.cshtml. Press OK.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
Similarly add 4 more views in Views/Customer folder, Create_customer.cshtml, Delete_customer.cshtml, customer_Details.cshtml, and Edit_customer.cshtml.
 
Now, our Views folder will look like this,
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
Let us put our codes in views and controller to perform the CRUD operation.
 
Create View
 
This view is for Adding new Customer data to the database. 
 
Open Create_customer.cshtml and put following code into it.
  1. @model AspNetCoreCURDMVC_Demo.Models.Customer  
  2. @{  
  3.     ViewData["Title"] = "Create";  
  4. }  
  5. <h2>Create</h2>  
  6. <h4>Customers</h4>  
  7. <hr />  
  8. <div class="row">  
  9.     <div class="col-md-4">  
  10.         <form asp-action="Create_customer">  
  11.             <div asp-validation-summary="ModelOnly" class="text-danger"></div>  
  12.             <div class="form-group">  
  13.                 <label asp-for="Name" class="control-label"></label>  
  14.                 <input asp-for="Name" class="form-control" />  
  15.                 <span asp-validation-for="Name" class="text-danger"></span>  
  16.             </div>  
  17.             <div class="form-group">  
  18.                 <label asp-for="Address" class="control-label"></label>  
  19.                 <input asp-for="Address" class="form-control" />  
  20.                 <span asp-validation-for="Address" class="text-danger"></span>  
  21.             </div>  
  22.             <div class="form-group">  
  23.                 <label asp-for="City" class="control-label"></label>  
  24.                 <input asp-for="City" class="form-control" />  
  25.                 <span asp-validation-for="City" class="text-danger"></span>  
  26.             </div>  
  27.             <div class="form-group">  
  28.                 <label asp-for="Country" class="control-label"></label>  
  29.                 <input asp-for="Country" class="form-control" />  
  30.                 <span asp-validation-for="Country" class="text-danger"></span>  
  31.             </div>  
  32.             <div class="form-group">  
  33.                 <label asp-for="Gender" class="control-label"></label>  
  34.                 <select asp-for="Gender" class="form-control">  
  35.                     <option value="">-- Select Gender --</option>  
  36.                     <option value="Male">Male</option>  
  37.                     <option value="Female">Female</option>  
  38.                 </select>  
  39.                 <span asp-validation-for="Gender" class="text-danger"></span>  
  40.             </div>  
  41.             <div class="form-group">  
  42.                 <label asp-for="Mobile" class="control-label"></label>  
  43.                 <input asp-for="Mobile" class="form-control" />  
  44.                 <span asp-validation-for="Mobile" class="text-danger"></span>  
  45.             </div>  
  46.             <div class="form-group">  
  47.                 <label asp-for="Email" class="control-label"></label>  
  48.                 <input asp-for="Email" class="form-control" />  
  49.                 <span asp-validation-for="Email" class="text-danger"></span>  
  50.             </div>  
  51.             <div class="form-group">  
  52.                 <input type="submit" value="Create" class="btn btn-primary" />  
  53.             </div>  
  54.         </form>  
  55.     </div>  
  56. </div>  
  57. <div>  
  58.     <a asp-action="Index">Back to List</a>  
  59. </div>  
  60. @section Scripts {  
  61.     @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}  
  62. }   
To handle database operations, we will create an object of CustomerDataAccess_Layer class inside the CustomerController class.
  1. namespace AspNetCoreCURDMVC_Demo.Controllers  
  2. {  
  3.     public class CustomerController : Controller  
  4.     {  
  5.         CustomerDataAccess_Layer objCustomer = new CustomerDataAccess_Layer();  
To handle the business logic of Create_customer, open CustomerController.cs and put the following code into it.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using Microsoft.AspNetCore.Mvc;  
  6. using AspNetCoreCURDMVC_Demo.Models;  
  7.   
  8. namespace AspNetCoreCURDMVC_Demo.Controllers  
  9. {  
  10.     public class CustomerController : Controller  
  11.     {  
  12.         CustomerDataAccess_Layer objCustomer = new CustomerDataAccess_Layer();  
  13.   
  14.         [HttpGet]  
  15.         public IActionResult Create_customer()  
  16.         {  
  17.             return View();  
  18.         }  
  19.   
  20.         [HttpPost]  
  21.         [ValidateAntiForgeryToken]  
  22.         public IActionResult Create_customer([Bind] Customer cust)  
  23.         {  
  24.             if (ModelState.IsValid)  
  25.             {  
  26.                 objCustomer.AddCustomer(cust);  
  27.                 return RedirectToAction("Index");  
  28.             }  
  29.             return View(objCustomer);  
  30.         }  
The [Bind] attribute is used with parameter “cust” to protect against over-posting.
 
Index View
 
This view is for displaying all the Customer records available in the database. Also, all the operation, Edit, Details and Delete on each record can be done form this view.
 
Open Index.cshtml and puts the following codes.
  1. @model IEnumerable<AspNetCoreCURDMVC_Demo.Models.Customer>  
  2.   
  3. @{  
  4.     ViewData["Title"] = "Index";  
  5. }  
  6. <h2>Index</h2>  
  7. <p>  
  8.     <a asp-action="Create_customer">Create New</a>  
  9. </p>  
  10. <table class="table table-bordered table-responsive">  
  11.     <thead>  
  12.         <tr>  
  13.             <th>  
  14.                 @Html.DisplayNameFor(model => model.Name)  
  15.             </th>  
  16.             <th>  
  17.                 @Html.DisplayNameFor(model => model.Address)  
  18.             </th>  
  19.             <th>  
  20.                 @Html.DisplayNameFor(model => model.City)  
  21.             </th>  
  22.             <th>  
  23.                 @Html.DisplayNameFor(model => model.Country)  
  24.             </th>  
  25.             <th>  
  26.                 @Html.DisplayNameFor(model => model.Gender)  
  27.             </th>  
  28.             <th>  
  29.                 @Html.DisplayNameFor(model => model.Mobile)  
  30.             </th>  
  31.             <th>  
  32.                 @Html.DisplayNameFor(model => model.Email)  
  33.             </th>  
  34.             <th>Actions</th>  
  35.         </tr>  
  36.     </thead>  
  37.     <tbody>  
  38.         @foreach (var item in Model)  
  39.         {  
  40.         <tr>  
  41.             <td>  
  42.                 @Html.DisplayFor(modelItem => item.Name)  
  43.             </td>  
  44.             <td>  
  45.                 @Html.DisplayFor(modelItem => item.Address)  
  46.             </td>  
  47.             <td>  
  48.                 @Html.DisplayFor(modelItem => item.City)  
  49.             </td>  
  50.             <td>  
  51.                 @Html.DisplayFor(modelItem => item.Country)  
  52.             </td>  
  53.             <td>  
  54.                 @Html.DisplayFor(modelItem => item.Gender)  
  55.             </td>  
  56.             <td>  
  57.                 @Html.DisplayFor(modelItem => item.Mobile)  
  58.             </td>  
  59.             <td>  
  60.                 @Html.DisplayFor(modelItem => item.Email)  
  61.             </td>  
  62.             <td>  
  63.                 <a asp-action="Edit_customer" asp-route-id="@item.ID">Edit</a> |  
  64.                 <a asp-action="customer_Details" asp-route-id="@item.ID">Details</a> |  
  65.                 <a asp-action="Delete_customer" asp-route-id="@item.ID">Delete</a>  
  66.             </td>  
  67.         </tr>  
  68.         }  
  69.     </tbody>  
  70. </table>    
To handle the business logic of Index view, open CustomerController.cs and add the following code in Index method.
  1. public IActionResult Index()  
  2.         {  
  3.             List<Customer> customers = new List<Customer>();  
  4.             customers = objCustomer.GetAllCustomers().ToList();  
  5.             return View(customers);  
  6.         }  
Edit View
 
This view will enable us to edit an existing Customer data.
 
Open Edit_customer.cshtml and put the following code into it.
  1. @model AspNetCoreCURDMVC_Demo.Models.Customer  
  2.   
  3. @{  
  4.     ViewData["Title"] = "Edit";  
  5. }  
  6. <h2>Edit</h2>  
  7. <h4>Customer</h4>  
  8. <hr />  
  9. <div class="row">  
  10.     <div class="col-md-4">  
  11.         <form asp-action="Edit_customer">  
  12.             <div asp-validation-summary="ModelOnly" class="text-danger"></div>  
  13.             <input type="hidden" asp-for="ID" />  
  14.             <div class="form-group">  
  15.                 <label asp-for="Name" class="control-label"></label>  
  16.                 <input asp-for="Name" class="form-control" />  
  17.                 <span asp-validation-for="Name" class="text-danger"></span>  
  18.             </div>  
  19.             <div class="form-group">  
  20.                 <label asp-for="Address" class="control-label"></label>  
  21.                 <input asp-for="Address" class="form-control" />  
  22.                 <span asp-validation-for="Address" class="text-danger"></span>  
  23.             </div>  
  24.             <div class="form-group">  
  25.                 <label asp-for="Gender" class="control-label"></label>  
  26.                 <select asp-for="Gender" class="form-control">  
  27.                     <option value="">-- Select Gender --</option>  
  28.                     <option value="Male">Male</option>  
  29.                     <option value="Female">Female</option>  
  30.                 </select>  
  31.                 <span asp-validation-for="Gender" class="text-danger"></span>  
  32.             </div>  
  33.             <div class="form-group">  
  34.                 <label asp-for="Country" class="control-label"></label>  
  35.                 <input asp-for="Country" class="form-control" />  
  36.                 <span asp-validation-for="Country" class="text-danger"></span>  
  37.             </div>  
  38.             <div class="form-group">  
  39.                 <label asp-for="City" class="control-label"></label>  
  40.                 <input asp-for="City" class="form-control" />  
  41.                 <span asp-validation-for="City" class="text-danger"></span>  
  42.             </div>  
  43.             <div class="form-group">  
  44.                 <label asp-for="Mobile" class="control-label"></label>  
  45.                 <input asp-for="Mobile" class="form-control" />  
  46.                 <span asp-validation-for="Mobile" class="text-danger"></span>  
  47.             </div>  
  48.             <div class="form-group">  
  49.                 <label asp-for="Email" class="control-label"></label>  
  50.                 <input asp-for="Email" class="form-control" />  
  51.                 <span asp-validation-for="Email" class="text-danger"></span>  
  52.             </div>  
  53.             <div class="form-group">  
  54.                 <input type="submit" value="Save" class="btn btn-primary" />  
  55.             </div>  
  56.         </form>  
  57.     </div>  
  58. </div>  
  59. <div>  
  60.     <a asp-action="Index">Back to List</a>  
  61. </div>  
  62. @section Scripts {  
  63.     @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}  
  64. }  
To handle the business logic of Edit_customer view, open CustomerController.cs and add following code to it.
  1. public IActionResult Edit_customer(int? id)  
  2.         {  
  3.             if (id == null)  
  4.             {  
  5.                 return NotFound();  
  6.             }  
  7.             Customer customer = objCustomer.GetCustomerData(id);  
  8.   
  9.             if (customer == null)  
  10.             {  
  11.                 return NotFound();  
  12.             }  
  13.             return View(customer);  
  14.         }  
  15.         [HttpPost]  
  16.         [ValidateAntiForgeryToken]  
  17.         public IActionResult Edit_customer(int id, [Bind] Customer cust)  
  18.         {  
  19.             if (id != cust.ID)  
  20.             {  
  21.                 return NotFound();  
  22.             }  
  23.             if (ModelState.IsValid)  
  24.             {  
  25.                 objCustomer.UpdateCustomer(cust);  
  26.                 return RedirectToAction("Index");  
  27.             }  
  28.             return View(objCustomer);  
  29.         }  
As we can see, we have two Edit_customer action methods, the first one is for HttpGet and the second one is for HttpPost. The HttpGet Edit_customer action method will fetch the Customer data and fills the fields of edit view. After the user clicks Save by editing the record, a Post request will be generated which is handled by HttpPost Edit_customer action method.
 
Details View
 
This view will display the details of a particular Customer. Add following codes on customer_Details .cshtml.
  1. @model AspNetCoreCURDMVC_Demo.Models.Customer  
  2. @{  
  3.     ViewData["Title"] = "Details";  
  4. }  
  5. <h2>Details</h2>  
  6. <div>  
  7.     <h4>Customer</h4>  
  8.     <hr />  
  9.     <dl class="dl-horizontal">  
  10.         <dt>  
  11.             @Html.DisplayNameFor(model => model.Name)  
  12.         </dt>  
  13.         <dd>  
  14.             @Html.DisplayFor(model => model.Name)  
  15.         </dd>  
  16.         <dt>  
  17.             @Html.DisplayNameFor(model => model.Address)  
  18.         </dt>  
  19.         <dd>  
  20.             @Html.DisplayFor(model => model.Address)  
  21.         </dd>  
  22.         <dt>  
  23.             @Html.DisplayNameFor(model => model.City)  
  24.         </dt>  
  25.         <dd>  
  26.             @Html.DisplayFor(model => model.City)  
  27.         </dd>  
  28.         <dt>  
  29.             @Html.DisplayNameFor(model => model.Country)  
  30.         </dt>  
  31.         <dd>  
  32.             @Html.DisplayFor(model => model.Country)  
  33.         </dd>  
  34.         <dt>  
  35.             @Html.DisplayNameFor(model => model.Mobile)  
  36.         </dt>  
  37.         <dd>  
  38.             @Html.DisplayFor(model => model.Mobile)  
  39.         </dd>  
  40.         <dt>  
  41.             @Html.DisplayNameFor(model => model.Email)  
  42.         </dt>  
  43.         <dd>  
  44.             @Html.DisplayFor(model => model.Email)  
  45.         </dd>  
  46.     </dl>  
  47. </div>  
  48. <div>  
  49.     <a asp-action="Edit_customer" asp-route-id="@Model.ID">Edit</a> |  
  50.     <a asp-action="Index">Back to List</a>  
  51. </div>  
To handle the business logic of Details view, open CustoemrController.cs and add the following code to it.
  1. [HttpGet]  
  2.         public IActionResult customer_Details(int? id)  
  3.         {  
  4.             if (id == null)  
  5.             {  
  6.                 return NotFound();  
  7.             }  
  8.             Customer objcustomer = objCustomer.GetCustomerData(id);  
  9.   
  10.             if (objcustomer == null)  
  11.             {  
  12.                 return NotFound();  
  13.             }  
  14.             return View(objcustomer);  
  15.         }  
Delete View
 
This view will help us to remove Customer data. Open Delete_customer.cshtml and put the following code into it.
  1. @model AspNetCoreCURDMVC_Demo.Models.Customer  
  2.   
  3. @{  
  4.     ViewData["Title"] = "Delete";  
  5. }  
  6. <h2>Delete</h2>  
  7. <h3>Are you sure you want to delete this?</h3>  
  8. <div>  
  9.     <h4>Customer</h4>  
  10.     <hr />  
  11.     <dl class="dl-horizontal">  
  12.         <dt>  
  13.             @Html.DisplayNameFor(model => model.Name)  
  14.         </dt>  
  15.         <dd>  
  16.             @Html.DisplayFor(model => model.Name)  
  17.         </dd>  
  18.         <dt>  
  19.             @Html.DisplayNameFor(model => model.Address)  
  20.         </dt>  
  21.         <dd>  
  22.             @Html.DisplayFor(model => model.Address)  
  23.         </dd>  
  24.         <dt>  
  25.             @Html.DisplayNameFor(model => model.City)  
  26.         </dt>  
  27.         <dd>  
  28.             @Html.DisplayFor(model => model.City)  
  29.         </dd>  
  30.         <dt>  
  31.             @Html.DisplayNameFor(model => model.Country)  
  32.         </dt>  
  33.         <dd>  
  34.             @Html.DisplayFor(model => model.Country)  
  35.         </dd>  
  36.         <dt>  
  37.             @Html.DisplayNameFor(model => model.Mobile)  
  38.         </dt>  
  39.         <dd>  
  40.             @Html.DisplayFor(model => model.Mobile)  
  41.         </dd>  
  42.         <dt>  
  43.             @Html.DisplayNameFor(model => model.Email)  
  44.         </dt>  
  45.         <dd>  
  46.             @Html.DisplayFor(model => model.Email)  
  47.         </dd>  
  48.     </dl>  
  49.   
  50.     <form asp-action="Delete_customer">  
  51.         <input type="hidden" asp-for="ID" />  
  52.         <input type="submit" value="Delete" class="btn btn-primary" /> |  
  53.         <a asp-action="Index">Back to List</a>  
  54.     </form>  
  55. </div>  
To handle the business logic of Delete_customer view, open CustomerController.cs and add the following code to it.
  1. [HttpGet]  
  2.         public IActionResult Delete_customer(int? id)  
  3.         {  
  4.             if (id == null)  
  5.             {  
  6.                 return NotFound();  
  7.             }  
  8.             Customer objcustomer = objCustomer.GetCustomerData(id);  
  9.   
  10.             if (objcustomer == null)  
  11.             {  
  12.                 return NotFound();  
  13.             }  
  14.             return View(objcustomer);  
  15.         }  
  16.   
  17.         [HttpPost, ActionName("Delete_customer")]  
  18.         [ValidateAntiForgeryToken]  
  19.         public IActionResult DeleteConfirmed(int? id)  
  20.         {  
  21.             objCustomer.DeleteCustomer(id);  
  22.             return RedirectToAction("Index");  
  23.         }  
To perform the Delete operation we need two Delete methods accepting the same parameter (Customer Id). But both the methods with the same name and method signature can create a compile time error and if we rename the Delete method then routing will not be able to find it as asp.net maps URL segments to action methods by its name. So, to resolve this issue we put ActionName("Delete_customer") attribute to the DeleteConfirmed method. That attribute performs mapping for the routing system so that a URL that includes /Delete_customer/ for a POST request will find the DeleteConfirmed method.
 
When we click the Delete link on the Index page, it will send a Get request and return a View of the Customer using HttpGet Delete_customer method. When we click Delete 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.
 
Step 5 - Add a new menu
 
Edit the Views/Shared/_Layout page and add a new menu as “AddCustomer”. For that, add the below code.
  1. <div class="navbar-collapse collapse">  
  2.                 <ul class="nav navbar-nav">  
  3.                     <li><a asp-area="" asp-controller="Home" asp-action="Index">Home</a></li>  
  4.                     <li><a asp-area="" asp-controller="Home" asp-action="About">About</a></li>  
  5.                     <li><a asp-area="" asp-controller="Home" asp-action="Contact">Contact</a></li>  
  6.   
  7.                     <li><a asp-area="" asp-controller="Customer" asp-action="Index">ADDCustomer</a></li>  
  8.                 </ul>  
  9.             </div>  
Step 6
 
Now press F5 to launch the application or Run the application and you can see the output page as shown below.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
Click on AddCustomer menu and navigate to Index view. It shows all the available customer records already saved in the database. Below is the list of records.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
Click Create New to navigate Create view. Add a new Customer record as shown in the image below.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
We can check by missing any field while creating Customer Record, we can see the required field validation error message.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET 
 
After inserting the data in all the fields, click "Create". The new Customer record will be created and you will be redirected to the Index view, displaying records of all the Customers. Here, we can also see action methods Edit, Details, and Delete.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET 
 
If we want to edit an existing Customer record, then click Edit action link. It will open Edit View as below where we can change the Customer data.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET 
 
If we miss any fields while editing Customer records, then Edit view will also throw the required field validation error message.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET 
 
If you want to see the details of any Customer, then click Details action link, which will open the customer_Details view, as shown in the image below.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET 
 
Click "Back to List" to go back to Index view. Now, we will perform Delete operation on a Customer named Chittaranjan. Click Delete action link which will open Delete view asking for a confirmation to delete.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET 
 
Once we click Delete, it will send HttpPost request to delete Customer record and we will be redirected to the Index view. Here, we can see that the Customer with name Chittaranjan has been removed from our record.
 
CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET
 

Conclusion 

 
We have learned about creating a sample MVC web application with ASP.NET Core 2.1 using ADO.NET and SQL Server with the help of Visual Studio 2017. We have used stored procedures to handle CRUD operations at the database level. Post your valuable feedback in the comments section.