CRUD operation using Ajax jQuery without Entity Framework in ASP.Net MVC

Last Reply one month ago By dharmendr

Posted one month ago

I am new to MVC .i want to know how can i perform crud operation in mvc using jquery without entityframework.

i want to perform crud operation using jquery and stored procedure .

i don't want to use entity framework like data model.

 

Posted one month ago

Hi iammann,

Here i have created sample using jQuery without entity framework.

For this sample i have used NorthWind database which you can get by clicking on the link below.

Download Northwind Database

Or you can create the table like below.

CREATE TABLE Employees
(
	EmployeeID INT IDENTITY(1,1) NOT NULL,
	LastName NVARCHAR(20) NOT NULL,
	FirstName NVARCHAR(10) NOT NULL,
	City NVARCHAR(15) NULL,
	ReportsTo INT NULL
)

Now follow the below steps.

Step 1: As usual create a MVC project.

Step 2: Create Model Class by right clicking on model folder. Here i have created with the name Employee.

public class Employee
{
    public int EmployeeID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string City { get; set; }
    public int? ReportsTo { get; set; }
}

Step 3 : Create Controller. Here i have created with the name Home.

public class HomeController : Controller
{
    string str = ConfigurationManager.ConnectionStrings[1].ConnectionString;
        
    public List<Employee> GetEmployees()
    {
        List<Employee> employees = new List<Employee>();
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = str;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = "SELECT TOP 10 EmployeeID,LastName,FirstName,City,ReportsTo FROM Employees ORDER BY EmployeeID DESC";
                con.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    if (sdr.HasRows)
                    {
                        while (sdr.Read())
                        {
                            Employee emp = new Employee()
                            {
                                EmployeeID = Convert.ToInt32(sdr["EmployeeId"]),
                                FirstName = sdr["FirstName"].ToString(),
                                LastName = sdr["LastName"].ToString(),
                                City = sdr["City"].ToString(),
                                ReportsTo = Convert.ToInt32(sdr["ReportsTo"] == DBNull.Value ? null : sdr["ReportsTo"])
                            };
                            employees.Add(emp);
                        }
                    }
                }
                con.Close();
            }
        }
        return employees;
    }

    public void Add(Employee employee)
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = str;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = "INSERT INTO Employees(LastName,FirstName,City,ReportsTo) VALUES(@LastName,@FirstName,@City,@ReportsTo)";
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
                cmd.Parameters.AddWithValue("@LastName", employee.LastName);
                cmd.Parameters.AddWithValue("@City", employee.City);
                cmd.Parameters.AddWithValue("@ReportsTo", employee.ReportsTo);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

    public void Update(Employee employee)
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = str;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = "UPDATE Employees SET LastName = @LastName,FirstName = @FirstName,City = @City WHERE EmployeeID = @EmployeeID";
                cmd.Parameters.AddWithValue("@EmployeeID", employee.EmployeeID);
                cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
                cmd.Parameters.AddWithValue("@LastName", employee.LastName);
                cmd.Parameters.AddWithValue("@City", employee.City);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

    public void Delete(int id)
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = str;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
                cmd.Parameters.AddWithValue("@EmployeeID", id);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

    public ActionResult Index()
    {
        return View();
    }

    public JsonResult GetAllEmployees()
    {
        return Json(GetEmployees().ToList(), JsonRequestBehavior.AllowGet);
    }

    public ActionResult AddEmployee()
    {
        return View();
    }

    [HttpPost]
    public JsonResult AddEmployee(Employee emp)
    {
        try
        {
            Add(emp);
            return Json("Records added Successfully.");
        }
        catch
        {
            return Json("Records not added,");
        }
    }

    public ActionResult UpdateEmployee(int? id)
    {
        return View(GetEmployees().Find(e => e.EmployeeID == id));
    }

    [HttpPost]
    public JsonResult UpdateEmployee(Employee emp)
    {
        Update(emp);
        return Json("Records updated successfully.", JsonRequestBehavior.AllowGet);
    }

    [HttpPost]
    public JsonResult DeleteEmployee(int id)
    {
        Delete(id);
        return Json("Records deleted successfully.", JsonRequestBehavior.AllowGet);
    }

    [HttpGet]
    public PartialViewResult EmployeeDetails()
    {
        return PartialView("_EmployeeDetails");
    }
}

Step 4: Create Views.

To view the employee details let us create the partial view named _EmployeeDetails inside Home folder.

_EmployeeDetails.cshtml

<script type="text/javascript">
    $(document).ready(function () {
        var tr;
        $.getJSON("/Home/GetAllEmployees", function (json) {
            $.each(json, function (i, emp) {
                var empid = emp.EmployeeID;
                tr = $('<tr/>');
                tr.append("<td class='FirstName'>" + emp.FirstName + "</td>");
                tr.append("<td class='LastName'>" + emp.LastName + "</td>");
                tr.append("<td class='City'>" + emp.City + "</td>");
                tr.append("<td class='ReportsTo'>" + emp.ReportsTo + "</td>");
                tr.append("<td>" + "<a Onclick='return false;' class='DeleteCss' href=/Home/DeleteEmployee/" + empid + ">Delete</a>" + " | " + "<a class='EditCss' href=/Home/UpdateEmployee/" + empid + ">Edit</a>" + "</td>");
                $('#tblEmployee').append(tr);
            });
        });
        $('#tblEmployee').on('click', 'td a.DeleteCss', function () {
            var deleteUrl = $(this).attr("href");
            if (confirm("Are you sure wants to delete ?.")) {
                $.ajax({
                    url: deleteUrl, dataType: "json", type: "POST", contentType: "application/json",
                    error: function (err) { alert('Unable to delete record.'); },
                    success: function (response) { $('#employeeList').load("/Home/EmployeeDetails"); }
                });
            }
        });
    });
</script>
<table id="tblEmployee" class="table  table-bordered table-hover">
    <thead>
        <tr>
            <th>First Name</th>
            <th>Last Name</th>
            <th>City</th>
            <th>Report To</th>
            <th>Action</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

To create the View for add and update, right click on view folder and then click Add view. Now specify the view name as I have AddEmployee/UpdateEmployee or as you wish, select appropriate template name(For AddEmployee select Create, for UpdateEmployee select Edit) and select model class(Employee (CrudWithADONetAjaxjQuery.Models)) and click on Add button.

AddEmployee.cshtml

@model CrudWithADONetAjaxjQuery.Models.Employee

@{
    ViewBag.Title = "Add Employee";
}

<h2>Add Employee</h2>
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
<script>
    $(document).ready(function () {
        $("#btnAdd").click(function () {
            var employee =
                {
                    FirstName: $("#FirstName").val(),
                    LastName: $("#LastName").val(),
                    City: $("#City").val(),
                    ReportsTo: $("#ReportsTo").val()
                };
            $.ajax({
                type: "POST", URL: "/Home/AddEmployee", dataType: "json", contentType: "application/json",
                data: JSON.stringify({ emp: employee }),
                success: function (response) { $('#employeeList').load("/Home/EmployeeDetails"); },
                error: function (response) { alert(response.responseText); }
            });

        });
    });
</script>
<div class="form-horizontal">
    <h4></h4>
    <hr />
    @Html.ValidationSummary(true, "", new { @class = "text-danger" })
    <div class="form-group">
        @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.ReportsTo, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.ReportsTo, new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.ReportsTo, "", new { @class = "text-danger" })
        </div>
    </div>

    <div class="form-group">
        <div class="col-md-offset-2 col-md-10">
            <input type="submit" id="btnAdd" value="Add" class="btn btn-default" />
        </div>
    </div>
    <div class="form-group" id="employeeList">
        <div class="col-md-12">
            @Html.Partial("_EmployeeDetails")
        </div>
    </div>
</div>

UpdateEmployee.cshtml

@model CrudWithADONetAjaxjQuery.Models.Employee
@{
    ViewBag.Title = "Update Employee";
}
<h2>Update Employee</h2>
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
<script>
    $(document).ready(function () {
        $("#btnUpdate").click(function () {
            var employee =
                {
                    EmployeeID: $("#hfEmployeeID").val(),
                    FirstName: $("#FirstName").val(),
                    LastName: $("#LastName").val(),
                    City: $("#City").val()
                };
            $.ajax({
                type: "POST", URL: "/Home/UpdateEmployee", dataType: "json", contentType: "application/json",
                data: JSON.stringify({ emp: employee }),
                success: function (response) { window.location.href = "/Home/AddEmployee"; },
                error: function (response) { alert(response.responseText); }
            });
        });
    });
</script>
<div class="form-horizontal">
    <h4></h4>
    <hr />
    @Html.ValidationSummary(true, "", new { @class = "text-danger" })
    @Html.HiddenFor(model => model.EmployeeID, new { @id = "hfEmployeeID" })

    <div class="form-group">
        @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
        </div>
    </div>
    <div class="form-group">
        <div class="col-md-offset-2 col-md-10">
            <input type="submit" id="btnUpdate" value="Update" class="btn btn-default" />
        </div>
    </div>
</div>

Step 9: Now Run the Application.

I agree, here is the link: http://e-iceblue.com/free-api