Home > Software engineering >  Ajax & ASP.NET MVC : get data by ID without using Entity Framework
Ajax & ASP.NET MVC : get data by ID without using Entity Framework

Time:09-02

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

enter image description here

  • Related