How to show employee details based on selected drop down list changed ?

See more:

C#

ASP.NET4

MVC5

How to load data employee on view GetAllEmpDetails.cshtml based on drop down selected index changed?



I work on MVC web application using c# by ado.net I face issue I can't display employee data based on drop down selected index changed .

so

if user select Pending Request from drop down list it will select employee that have select employee status 1 .

if user select Done Request from drop down list it will select employee that have select employee status 2 .

my code as below :

Table structure

CREATE TABLE [dbo].[Employee](
    [EmployeeId] [int] NOT NULL,
    [EmployeeName] [nvarchar](100) NULL,
    [EmployeeStatus] [int] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1211, N'ahmed', 1)
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1222, N'eslam', 1)
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1223, N'adel', 2)
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1224, N'mohamed', 2)
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1225, N'mosh', 2)
INSERT [dbo].[Employee] ([EmployeeId], [EmployeeName], [EmployeeStatus]) VALUES (1227, N'ali', 1)

stored procedures have logic

create Procedure [dbo].[LoadDropDownEmployee] 
@EmployeeStatus int
as  
begin  
   select * from Employee  where EmployeeStatus=@EmployeeStatus
End 

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

so How to get data on view GetAllEmpDetails when drop down statusselect selected index changed ?

What I have tried:

Employee Model

public class EmpModel
{
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public int EmployeeStatus { get; set; }
}

Employee Repository have functions for controller

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();   
    foreach (DataRow dr in dt.Rows)
    {

        EmpList.Add(
            new EmpModel
            {
                EmployeeId = Convert.ToInt32(dr["EmployeeId"]),
                EmployeeName = Convert.ToString(dr["EmployeeName"]),
                EmployeeStatus = Convert.ToInt32(dr["EmployeeStatus"])
            }
            );
    }

    return EmpList;
}

public List<EmpModel> LoadDropDownLists(int EmployeeStatus)
{
    connection();
    List<EmpModel> EmpList = new List<EmpModel>();

    SqlCommand com = new SqlCommand("LoadDropDownEmployee", con);
    com.CommandType = CommandType.StoredProcedure;
    com.Parameters.Add("@EmployeeStatus", SqlDbType.VarChar, 50);
    com.Parameters["@EmployeeStatus"].Value = EmployeeStatus;
    SqlDataAdapter da = new SqlDataAdapter(com);
    DataTable dt = new DataTable();

    con.Open();
    da.Fill(dt);
    con.Close();  
    foreach (DataRow dr in dt.Rows)
    {
        EmpList.Add(
            new EmpModel
            {
                EmployeeId = Convert.ToInt32(dr["EmployeeId"]),
                EmployeeName = Convert.ToString(dr["EmployeeName"])
            }
            );
    }

    return EmpList;
}

controller Employee

public class EmployeeController : Controller
{
    public ActionResult LoadDropDownList(int EmployeeStatus)
    {
        EmpRepository EmpRepo = new EmpRepository();
        return View();
    }
    public ActionResult GetAllEmpDetails()
    {
        EmpRepository EmpRepo = new EmpRepository();
        ModelState.Clear();
        return View(EmpRepo.GetAllEmployees());
    }
}

view GetAllEmpDetails.cshtml

@model IEnumerable<Ado.netMvc.Models.EmpModel>

@{
    ViewBag.Title = "GetAllEmpDetails";
}

<h2>GetAllEmpDetails</h2>

<th>
    <select class="form-control" id="statusselect" name="statusselectName">
        <option>Select Status</option>
        <option>Pending Request</option>
        <option>All requests </option>
    </select>
</th>

<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.EmployeeId)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.EmployeeName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.EmployeeStatus)
        </th>
        <th></th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.EmployeeId)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.EmployeeName)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.EmployeeStatus)
            </td>
        </tr>
    }
</table>