I am trying to get data in my DataTable by ID, the data row coming from SQL Server to my controller but I am confused: how to pass this data to my DataTable in view?
I am using this code - my model:
public class EmployeesModel
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public int Age { get; set; }
public string Position { get; set; }
public string Office { get; set; }
[Required(ErrorMessage ="Please enter date")]
public DateTime HiringDate { get; set; }
public int Salary { get; set; }
}
My controller
public JsonResult GetEmpByID(int id)
{
List<EmployeesModel> employeeList = new List<EmployeesModel>();
string CS = ConfigurationManager.ConnectionStrings["SQLConn"].ConnectionString;
using (SqlConnection conn = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("SP_GetEmpByID", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@EmpID", SqlDbType.NVarChar).Value = id; //Added Parameter
conn.Open();
// Get
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
EmployeesModel employee = new EmployeesModel();
employee.EmployeeId = Convert.ToInt32(reader["EmployeeId"]);
employee.Name = reader["Name"].ToString();
employee.Gender = reader["Gender"].ToString();
employee.Age = Convert.ToInt32(reader["Age"]);
employee.Position = reader["Position"].ToString();
employee.Office = reader["Office"].ToString();
employee.HiringDate = Convert.ToDateTime(reader["HiringDate"]);
employee.Salary = Convert.ToInt32(reader["Salary"]);
employeeList.Add(employee);
}
}
//return View(employeeList); Commented out
//return RedirectToAction("GetEmpByID"); Commented out
return Json(new { data = employeeList }, JsonRequestBehavior.AllowGet);
}
My view
@model IEnumerable<SQLWithoutEF.EmployeesModel>
<input type="text" id="tablename" /> //Here I enter Employee ID and below is button
<asp:Button runat="server" Text="Button" id="IDbtn" onclick="GetByName($('#tablename').val())">Get Data By ID</asp:Button>
@*Data Table ==============*@
<table id="MyTable">
<thead>
<tr>
<th style="display:none">@Html.DisplayNameFor(m => m.EmployeeId)</th>
<th>@Html.DisplayNameFor(m => m.Name)</th>
<th>@Html.DisplayNameFor(m => m.Gender)</th>
<th>@Html.DisplayNameFor(m => m.Age)</th>
<th>@Html.DisplayNameFor(m => m.Position)</th>
<th>@Html.DisplayNameFor(m => m.Office)</th>
<th>@Html.DisplayNameFor(m => m.HiringDate)</th>
<th>@Html.DisplayNameFor(m => m.Salary)</th>
<th>Action</th>
</tr>
</thead>
<tbody>
@foreach (var employee1 in Model)
{
<tr>
<td style="display:none">@employee1.EmployeeId</td>
<td>@employee1.Name</td>
<td>@employee1.Gender</td>
<td>@employee1.Age</td>
<td>@employee1.Position</td>
<td>@employee1.Office</td>
<td>
@if (employee1.HiringDate != null)
{
@employee1.HiringDate
}
</td>
<td>@employee1.Salary</td>
<td>
<asp:button >UpdateEmp</asp:button> |
<asp:button data-name="@employee1.Name" id="deletebtn" onclick="deleteF(@employee1.EmployeeId)">DeleteEmp</asp:button>
</td>
</tr>
}
</tbody>
</table>
My jQuery / Ajax - here I need help:
function GetByName(id) {
if (confirm("Are You Sure to Get " id " " " ?")) {
$('#MyTable tbody tr').remove();
$.ajax({
type: "GET",
url: '/Home/GetEmpByID?id=' id,
//data: JSON.stringify({ id: id }),
//contentType: application/json, charset: utf-8,
processData: false,
dataType: 'json',
bprocessing: true,
success: function (data) { // Till here data coming but it's not working further
var items = '';
$.each(data, function (item) {
debugger;
var rows = '';
for (var i = 0; i < item.length[0]; i ) {
rows = "<tr><td>" data "</td>"
'<td><asp:button >UpdateEmp</asp:button> | <asp:button data-name="@employee1.Name" id="deletebtn" onclick="deleteF(@employee1.EmployeeId)">DeleteEmp</asp:button></td>'
"</tr>";
$('#MyTable tbody').append(rows);
}
})
},
}).catch(function (xhr, status, error) {
var errorMeassage = xhr.status ': ' xhr.statusText;
alert('Error - ' errorMeassage);
})
};
};
Here is the screenshot what output I am getting