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
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>
so How to get data on view GetAllEmpDetails when drop down statusselect selected index changed ?
Please move the employee list to a partial view. So the GetAllEmpDetails.cshtml view will be like the below. The ajax call on the main view to render the partial view from the ActionResult method GetAllEmpDetailsByStatus of Employee Controller. Also I add value to the option of the select input. Because we need to pass this value to the GetAllEmpDetailsByStatus method and filter the employee data.
@model IEnumerable<Ado.netMvc.Models.EmpModel>
@{
ViewBag.Title = "GetAllEmpDetails";
}
<script type="text/javascript">
$(document).ready(function () {
$("#statusselect").change(function () {
var postData = {
empStatus: $(this).val()
};
//addAntiForgeryToken(postData);
$.ajax({
cache: false,
type: 'POST',
url: '../Employee/GetAllEmpDetailsByStatus',
data: postData,
dataType: 'json',
success: function (data) {
$("#employeeContainer").html(data.html);
},
failure: function () {
}
});
});
});
</script>
<h2>GetAllEmpDetails</h2>
<th>
<select class="form-control" id="statusselect" name="statusselectName">
<option value="1">Select Status</option>
<option value="2">Pending Request</option>
<option value="0">All requests </option>
</select>
</th>
<div id="employeeContainer">
@Html.Partial("_AllEmployee", Model)
</div>
The Partial View below
@model IEnumerable<Ado.netMvc.Models.EmpModel>
<table class="table">
<tr>
<th>
Employee Id
</th>
<th>
Employee Name
</th>
<th>
Employee Status
</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>
The GetAllEmpDetailsByStatus() ActionResult method of EmployeeController is like the below.
public class EmployeeController : Controller
{
[HttpPost]
public ActionResult GetAllEmpDetailsByStatus(int empStatus)
{
EmpRepository EmpRepo = new EmpRepository();
ModelState.Clear();
var theData= EmpRepo.LoadDropDownLists(empStatus);
return Json(new
{
html = RenderPartialViewToString("_AllEmployee", theData)
});
}
// Better to move the base class, like BaseController.
public virtual string RenderPartialViewToString(string viewName, object model)
{
if (string.IsNullOrEmpty(viewName))
viewName = this.ControllerContext.RouteData.GetRequiredString("action");
this.ViewData.Model = model;
using (var sw = new StringWriter())
{
ViewEngineResult viewResult = System.Web.Mvc.ViewEngines.Engines.FindPartialView(this.ControllerContext, viewName);
var viewContext = new ViewContext(this.ControllerContext, viewResult.View, this.ViewData, this.TempData, sw);
viewResult.View.Render(viewContext, sw);
return sw.GetStringBuilder().ToString();
}
}
}