crud operations in asp.net mvc

Description:
Here i will explain how to insert update delete and select operation in asp.net mvc. We explained step by step to do this like the following below:

Step 1: Create new Asp.net MVC empty application.

Step 2: Create new Model Class by right click on Models => Add => Class named "EmpModel.cs"
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

public class EmpModel  
  {  
      [Display(Name = "Id")]  
      public int Empid { get; set; }  
  
      [Required(ErrorMessage = "First name is required.")]  
      public string Name { get; set; }  
  
      [Required(ErrorMessage = "City is required.")]  
      public string City { get; set; }  
  
      [Required(ErrorMessage = "Address is required.")]  
      public string Address { get; set; }  
  }

Step 3: Create new cotroller with read/write action with named "EmployeeController".

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 as per requirement. 

- Create table "Employee" with id, name, city and address columns.

- Create stored procedures for Insert record like the below:

Create procedure [dbo].[AddNewEmpDetails]  
(  
   @Name varchar (50),  
   @City varchar (50),  
   @Address varchar (50)  
)  
as  
begin  
   Insert into Employee values(@Name,@City,@Address)  
End

- Create stored procedures for view/select record like the below:

Create Procedure [dbo].[GetEmployees]  
as  
begin  
   select *from Employee  
End

- Create stored procedures for update record like the below:

Create procedure [dbo].[UpdateEmpDetails]  
(  
   @EmpId int,  
   @Name varchar (50),  
   @City varchar (50),  
   @Address varchar (50)  
)  
as  
begin  
   Update Employee   
   set Name=@Name,  
   City=@City,  
   Address=@Address  
   where Id=@EmpId  
End 

- Create stored procedures for delete record like the below:

Create procedure [dbo].[DeleteEmpById]  
(  
   @EmpId int  
)  
as   
begin  
   Delete from Employee where Id=@EmpId  
End

Step 5: Create Repository folder and Add EmpRepository.cs class for database related operations.
Now create methods in EmpRepository.cs to handle the CRUD operation like the below:
EmpRepository.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using CRUDUsingMVC.Models;
using System.Linq;

namespace CRUDUsingMVC.Repository
{
    public class EmpRepository
    {
        private SqlConnection con;
        //To Handle connection related activities
        private void connection()
        {
            string constr = ConfigurationManager.ConnectionStrings["get            conn"].ToString();
            con = new SqlConnection(constr);

        }
        //To Add Employee details
        public bool AddEmployee(EmpModel obj)
        {
            connection();
            SqlCommand com = new SqlCommand("AddNewEmpDetails", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@Name", obj.Name);
            com.Parameters.AddWithValue("@City", obj.City);
            com.Parameters.AddWithValue("@Address", obj.Address);
          
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //To view employee details with generic list 
        public List GetAllEmployees()
        {
            connection();
            List EmpList =new List();
            SqlCommand com = new SqlCommand("GetEmployees", con);
            com.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            con.Open();
            da.Fill(dt);
            con.Close();

            //Bind EmpModel generic list using LINQ 
            EmpList = (from DataRow dr in dt.Rows
                       select new EmpModel()
                       {
                           Empid = Convert.ToInt32(dr["Id"]),
                           Name = Convert.ToString(dr["Name"]),
                           City = Convert.ToString(dr["City"]),
                           Address = Convert.ToString(dr["Address"])
                       }).ToList();
            return EmpList;
        }
        //To Update Employee details
        public bool UpdateEmployee(EmpModel obj)
        {
            connection();
            SqlCommand com = new SqlCommand("UpdateEmpDetails", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@EmpId", obj.Empid);
            com.Parameters.AddWithValue("@Name", obj.Name);
            com.Parameters.AddWithValue("@City", obj.City);
            com.Parameters.AddWithValue("@Address", obj.Address);
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //To delete Employee details
        public bool DeleteEmployee(int Id)
        {
            connection();
            SqlCommand com = new SqlCommand("DeleteEmpById", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@EmpId", Id);
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
            {
               return true;
            }
            else
            {
                return false;
            }
        }
    }
}

Step 6: Open the EmployeeController.cs and create the following action methods:
EmployeeController.cs

public class EmployeeController : Controller    
    {     
        // GET: Employee/GetAllEmpDetails    
        public ActionResult GetAllEmpDetails()    
        {    
              
            EmpRepository EmpRepo = new EmpRepository();    
            ModelState.Clear();    
            return View(EmpRepo.GetAllEmployees());    
        }    
        // GET: Employee/AddEmployee    
        public ActionResult AddEmployee()    
        {    
            return View();    
        }    
      
        // POST: Employee/AddEmployee    
        [HttpPost]    
        public ActionResult AddEmployee(EmpModel Emp)    
        {    
            try    
            {    
                if (ModelState.IsValid)    
                {    
                    EmpRepository EmpRepo = new EmpRepository();    
      
                    if (EmpRepo.AddEmployee(Emp))    
                    {    
                        ViewBag.Message = "Employee details added 
                        successfully";    
                    }    
                }    
                  
                return View();    
            }    
            catch    
            {    
                return View();    
            }    
        } 
        // GET: Employee/EditEmpDetails/5    
        public ActionResult EditEmpDetails(int id)    
        {    
            EmpRepository EmpRepo = new EmpRepository();    
            return View(EmpRepo.GetAllEmployees().Find
(Emp => Emp.Empid == id));    
      
        }    
      
        // POST: Employee/EditEmpDetails/5    
        [HttpPost]    
        public ActionResult EditEmpDetails(int id,EmpModel obj)    
        {    
            try    
            {    
                    EmpRepository EmpRepo = new EmpRepository();    
                    EmpRepo.UpdateEmployee(obj);    
                    return RedirectToAction("GetAllEmpDetails");    
            }    
            catch    
            {    
                return View();    
            }    
        }    
        // GET: Employee/DeleteEmp/5    
        public ActionResult DeleteEmp(int id)    
        {    
            try    
            {    
                EmpRepository EmpRepo = new EmpRepository();    
                if (EmpRepo.DeleteEmployee(id))    
                {    
                    ViewBag.AlertMsg = "Employee details deleted succes                    sfully";    
      
                }    
                return RedirectToAction("GetAllEmpDetails");    
            }    
            catch    
            {    
                return View();    
            }    
        }  
} 

Step 7: Create views to add, get, update and delete operation.

- Create view to add employee:

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.

- Create view to get 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.

- Create view to edit employee:

Follow the same procedure and create EditEmpDetails view to edit the employees.

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:

public class RouteConfig  
 {  
     public static void RegisterRoutes(RouteCollection routes)  
     {  
         routes.IgnoreRoute("{resource}.axd/{*pathInfo}");  
  
         routes.MapRoute(  
             name: "Default",  
             url: "{controller}/{action}/{id}",  
             defaults: new { controller = "Employee", action = "AddEmployee", id = UrlParameter.Optional }  
         );  
     }   
}

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.