CRUD Operations In ASP.NET MVC 5 Using ADO.NET

Background

After an awesome response 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 with a step-by-step 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 of MVC.

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

Step 1. Create an MVC Application.

Now let us start with a step-by-step approach to the creation of a 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 the 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 the model folder as in the following screenshot.

Note. It is not mandatory that the Model class should be in the 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 names or without folder names or in a separate class library.

EmpModel.cs class code snippet.

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; }
}

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 the Add button it will show the following window. Now specify the Controller name as Employee with the suffix Controller as in the following screenshot.

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

After clicking on the Add button controller is created with by default code that supports 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 a 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

CREATE PROCEDURE [dbo].[AddNewEmpDetails]
(
   @Name varchar(50),
   @City varchar(50),
   @Address varchar(50)
)
AS
BEGIN
   INSERT INTO Employee VALUES(@Name, @City, @Address)
END

To View Added Records

CREATE PROCEDURE [dbo].[GetEmployees]
AS
BEGIN
    SELECT * FROM Employee
END

To Update Records

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

To Delete Records

CREATE PROCEDURE [dbo].[DeleteEmpById]
(
   @EmpId int
)
AS
BEGIN
   DELETE FROM Employee WHERE Id = @EmpId
END

Step 5. Create a Repository class.

Now create a 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

public class EmpRepository
{
    private SqlConnection con;

    // To Handle connection related activities
    private void connection()
    {
        string constr = ConfigurationManager.ConnectionStrings["getconn"].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<EmpModel> GetAllEmployees()
    {
        connection();
        List<EmpModel> EmpList = new List<EmpModel>();
        
        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 dataRow
        foreach (DataRow dr in dt.Rows)
        {
            EmpList.Add(
                new EmpModel
                {
                    Empid = Convert.ToInt32(dr["Id"]),
                    Name = Convert.ToString(dr["Name"]),
                    City = Convert.ToString(dr["City"]),
                    Address = Convert.ToString(dr["Address"])
                }
            );
        }
        
        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. Create Methods into the EmployeeController.cs file.

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

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 successfully";
            }
            return RedirectToAction("GetAllEmpDetails");
        }
        catch
        {
            return View();
        }
    }
}