CRUD Operations In MVC Sagar Jaybhay

In series of this post you will able to understand CRUD Operations In MVC by Sagar Jaybhay.

CRUD Operations In MVC

In this, we have a scenario we need to display the top 25 employees and perform Edit, Display and Delete Operation on Employee.

Display Employee List
Display Employee List

To achieve above this we need to create a method in Business class to get the top 25 employees for that method is below.

public IEnumerable<Employee> GetEmployees()
        {
            List<Employee> employees = new List<Employee>();
            string Query = "select top 25 * from Employee";
            var data = this.dataAccess.GetTable(Query);
            if(data!=null&&data.Rows.Count>0)
            {
                foreach(DataRow dataRow in data.Rows)
                {
                    var emp = new Employee()
                    {
                        EmpID = Convert.ToInt32(dataRow["EmpID"]),
                        EmpCity = dataRow["EmpCity"].ToString(),
                        EmpEmail = dataRow["EmpEmail"].ToString(),
                        EmpGender = dataRow["EmpGender"].ToString(),
                        EmpName = dataRow["EmpName"].ToString(),
                        EmpSalary = Convert.ToDouble(dataRow["EmpSalary"].ToString()),
                        DepartmentID = Convert.ToInt32(dataRow["DepartmentID"].ToString())


                    };
                    employees.Add(emp);

                }

            }
            return employees;


        }

The above method will return the Employee enumerable list and this list we pass to our view. For that, we create an action method in our Employee Controller and the method is below.

public ActionResult DisplayCompleteEmployee()
        {
            var emplist = new BusinessLogic.Business().GetEmployees();

            return View(emplist);
        }

Now we need to create a Strongly-typed model that takes Ienumberable<Employee> list. Now our view looks like below.

@model  IEnumerable<WebApplication1.Models.Employee>
@using WebApplication1.Models

@{
    ViewBag.Title = "DisplayCompleteEmployee";
}

<h2>Display Complete Employee</h2>

<br />
<div class="row" style="margin-left:0px !important">
    @Html.ActionLink("Create Employee", "Create")
</div>
<br />
<hr />
<br />

<table class="table table-bordered table-responsive">
    <thead>
        <tr>
            <td>Name</td>
            <td>Gender</td>
            <td>City</td>
            <td>Email</td>
            <td>Salary</td>
            <td>Take Action</td>
        </tr>
    </thead>
    <tbody>
        @foreach (var emp in Model)
        {
        <tr>
            <td>@emp.EmpName</td>
            <td>@emp.EmpGender</td>
            <td>@emp.EmpCity</td>
            <td>@emp.EmpEmail</td>
            <td>@emp.EmpSalary</td>
            <td>
                @Html.ActionLink("Edit", "EditEmpDetails", new { EmpID = emp.EmpID }) |
                @Html.ActionLink("Details", "Index", new { EmpID = emp.EmpID }) |
                @Html.ActionLink("Delete", "EditEmpDetails", new { EmpID = emp.EmpID }) |
            </td>
        </tr>
        }

    </tbody>

</table>

In the above code, we add action link create employee but we don’t have a method for that in our controller so we need to create this method which response to create get request.

Below the method, we add in our controller and that method mark with HttpGet attribute means it should respond only get create method.

        [HttpGet]
        public ActionResult Create()
        {
            return View();
        }

Now below image is that we add a view which uses scaffolding template for create

Add Create Employee View
Add Create Employee View

When we run our application and click on create employee action link we got view which is shown below

Create Employee View
Create Employee View

For the above view, we use the auto-generated code.

@model WebApplication1.Models.Employee

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>

@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>Employee</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.EmpID, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmpID, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmpID, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmpName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmpName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmpName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmpSalary, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmpSalary, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmpSalary, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmpGender, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmpGender, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmpGender, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmpCity, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmpCity, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmpCity, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EmpEmail, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmpEmail, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmpEmail, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.DepartmentID, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.DepartmentID, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.DepartmentID, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

Attributes in MVC

But if you see the image it will generate textbox for gender column and we don’t want this. We need a dropdown list for that and also we seen in the above create an image that contains textbox name empid, empname this good for a developer but for end-user we need to change this so we use DisplayName attribute to change the display name of an employee.

Now our model class becomes

public class Employee
    {
        [DisplayName("ID")]
        public int EmpID { get; set; }
        [DisplayName("Name")]
        public string EmpName { get; set; }
        [DisplayName("Salary")]
        public double EmpSalary { get; set; }
        [DisplayName("Gender")]
        public string EmpGender { get; set; }
        [DisplayName("City")]
        public string EmpCity { get; set; }
        [DisplayName("Email")]
        public string EmpEmail { get; set; }
        public int DepartmentID { get; set; }
    }

To use DisplayName attribute we need to import using System.ComponentModel; This namespace in our class and our UI looks like this

Use Display Name Attribute
Use Display Name Attribute

To change gender textbox we need to write below code instead of textbox for empgender.

@Html.DropDownList("Gender",new List<SelectListItem> { new SelectListItem { Text="Male" ,Value="Male"},
           new SelectListItem { Text="Female" ,Value="female"}},"Select Gender")
                @Html.ValidationMessageFor(model => model.EmpGender, "", new { @class = "text-danger" })
DropDownList in MVC
DropDownList in MVC

After doing the create form we have to Create a button on that form and if you click on that you will get a 404 error why because there is no method to respond post request in our EmployeeController.

Post Function Create Employee
Post Function Create Employee

To create first we need to Hide EmpId from create form because it is unique and needs to be dynamic so we will hide this from. We set to display: none property in style and from UI empid is not displayed. Now our UI looks like below

Complete Create Employee UI
Complete Create Employee UI

To create employee we created a stored procedure for that and we call this stored procedure from our business class. So first we need to create a HttpPost enable create method in our controller, code for this is below.

Below is the method we created in business class and which in turn calls a method that is written in a database class.

public void CreateEmployee(Employee employee)
        {
            this.dataAccess.ExecuteProcedure(employee.EmpName, employee.EmpEmail, employee.EmpGender, employee.EmpSalary, employee.DepartmentID, employee.EmpCity);
        }

Below is the method in the Database class.

public void ExecuteProcedure(string Name,string Email,string Gender,double salary,int deptid,string city)
        {
            using(var con=new SqlConnection(this._ConnectionString))
            {
                con.Open();
                var cmd = new SqlCommand("spCreateEmployee", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("name", SqlDbType.NVarChar, 20));
                cmd.Parameters.Add(new SqlParameter("salary", SqlDbType.Float, 50));
                cmd.Parameters.Add(new SqlParameter("gender", SqlDbType.NVarChar, 50));
                cmd.Parameters.Add(new SqlParameter("city", SqlDbType.NVarChar, 50));
                cmd.Parameters.Add(new SqlParameter("email", SqlDbType.NVarChar, 50));
                cmd.Parameters.Add(new SqlParameter("deptid", SqlDbType.Int, 50));

                cmd.Parameters["name"].Value = Name;
                cmd.Parameters["salary"].Value = salary;
                cmd.Parameters["gender"].Value = Gender;
                cmd.Parameters["city"].Value = city;
                cmd.Parameters["email"].Value = Email;
                cmd.Parameters["deptid"].Value = deptid;
                cmd.ExecuteNonQuery();
            }             

        }

For the above code, we have written a stored procedure in our SQL Server database which is below.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[spCreateEmployee]
@name nvarchar(20),
@salary float,
@gender nvarchar(20),
@city nvarchar(20),
@email nvarchar(30),
@deptid int
as
begin
declare @empid int
select @empid=count(*) from Employee
set @empid=@empid+1
insert into Employee values(@empid,@name,@salary,@gender,@city,@email,@deptid)
end
GO

GitHub :- https://github.com/Sagar-Jaybhay/MVC5


Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer at Software Company. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Sagar Jaybhay

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer at Software Company. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

You may also like...