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>