CRUD Operations in MVC using Entity Framework, JQuery AJAX Part-2

Before getting started with this tutorial if you guys are not aware what are CRUD operations and how to perform them without using JQuery, Entity Framework (database) then please read this article first CRUD Operations in MVC Part-1

Prerequisites

• Visual Studio 2012 or higher
• MS SQL Server 2012 or higher 

Setting up Environment 

• Open Visual Studio (any version from 2012 onward), I'm working with Visual Studio Professional 2017.
• Create new project, select C#, select Web, select ASP.net Web Application Name your project then press "OK"
• Select MVC, change Authentication to "No Authentication" press OK and then OK

The above steps will create a ready-made MVC application for you with all of your basic needs including Bootstrap, Jquery, basic layout of website, a single controller and its related views. All you need now is to start coding

Setting up Database

I'm going to follow Database First Approach for using Entity Framework, in my opinion this approach is somewhat flexible for you to learn it at first instance.

• Open SQL Server and Create new Database, name it "Test" database
• Create a new table within newly created database naming "Employee" with the fields of Id, Name and Email and save the table
• Open Visual Studio, go to Server Explorer
• Right-click on Data Connections, click Add Connection
• Put dot '.' in place of Server name and then select newly created database
• Click on Test Connection Button, see image below

Congratulations! You are now connected with the database.

The next step is to create Entity Data Model

• Right-click on models, select "add new item"
• Click on Data in the left-pane
• Select ADO.NET Entity Data Model
• Give it appropriate name and click on Add button

A new dialog-box will appear, do the followings:

• Select EF Designer from Database Press Next
• The next dialog-box will ask to choose data connection, if you don't have existing connection then do nothing and press Next button
• Then choose version, I'm working with 6.x, if your project doesn't have one, install it using Nuget Package Manager, press Next button
• The next dialog-box is about database Objects and Settings check Tables box, rename Model Namespace if you require and press Finish button

Now wait for few seconds and let the Visual Studio do its heavy tasks, after that Entity Data Model Diagram page will open just like the image below

Now we are ready to go with Entity Framework. Notice your web.config file, a connection string will be automatically added there.

Insert some dummy records into Employee Table from Sql Server using the below query

insert into Employee values('James','james1@gmail.com'),('Lisa','lisa1@gmail.com'),
('lazy','lazy_entity@gmail.com'),('Anna','annna@gmail.com'),('Andrew','and1@gmail.com'),
('entity','someEntity@gmail.com'),('Smith','smith@gmail.com'),('Jakob','jakob@gmail.com')

Now change your Index method within HomeController.cs with the below code

TestEntities te = new TestEntities();  
public ActionResult Index()
{
    return View(te.Employees.ToList());
}

TestEntities is the name of the connection string I've set for my Entity Model

Now change your Index.cshtml code with the below code

@model IEnumerable<Blog.Models.Employee>
@{
    ViewBag.Title = "Home Page";
}
 
<h3>Employees</h3>
@Html.ActionLink("Create new Employee", "Create_New")
<div class="row">
    <div class="col-md-5">
        <table class="table table-hover">
            <tr>
                <td><b>First Name</b></td>
                <td><b>Last Name</b></td>
                <td><b>Ops</b></td>
            </tr>
            @foreach (var item in Model)
            {
                <tr>
                    <td>@item.Name</td>
                    <td>@item.Email</td>
                    <td>
                        @Html.ActionLink("Edit", "Edit", new {Id = item.ID}) |
                        @Html.ActionLink("Delete", "Delete", new { Id = item.ID })
                    </td>
                </tr>
            }
        </table>
    </div>
</div>

Now press Ctrl+F5 or simple F5 to run the project, it will get you to the default page of our project which is Index page and the output of that page would be this:

We've completed the R part of CRUD operations using Entity Framework, lets move to other parts now.

Create part of CRUD Operations:

Notice the below line of code in our Index.cshtml, this code means display text "Create new Employee" within browser and when the user clicks this text, call the method Create_New( ) within HomeController.cs that we have not defined yet. 

@Html.ActionLink("Create new Employee", "Create_New")

Create a method named Create_New within HomeController.cs like mentioned below:

[HttpGet]
public ActionResult Create_New()
{
    return View();
}
[HttpPost]
public ActionResult Create_New(Employee emp)
{
    te.Employees.Add(emp);
    te.SaveChanges();
    return View("Index", te.Employees.ToList());
}

The methods are decorated with HttpGet and HttpPost, these are for identification because method names are same, when a user views a page its a HttpGet call and when the user submits some data it is HttpPost call. Right-click on Create_New( ) method and create a new view just like the image below

It will automatically create a Create_New.cshtml within Home Folder within Views, give names carefully.

Now run the project and click on Create_New link on Index page, the following page will be displayed

C part of out CRUD Operations are completed using Entity Framework.

Update, Delete part of CRUD Operations:

Notice the below line of code in our Index.cshtml, this code means display text "Create new Employee" within browser and when the user clicks this text, call the method Create_New( ) within HomeController.cs that we have not defined yet. 

@Html.ActionLink("Edit", "Edit", new {Id = item.ID}) |
@Html.ActionLink("Delete", "Delete", new { Id = item.ID })

The above two lines of code calling Edit method within HomeController.cs and Delete Method of HomeController.cs but we've not defined both the methods yet, lets start with Edit. Creating both these methods are same just we do with Create_New() method

public ActionResult Edit(int ID)
{
    var employee = te.Employees.SingleOrDefault(x => x.ID == ID);
    return View(employee);
}
[HttpPost]
public ActionResult Edit(Employee emp)
{
    var employee = te.Employees.SingleOrDefault(x => x.ID == emp.ID);
    if (employee != null)
    {
        //Update the existing employee data
        employee.Name = emp.Name;
        employee.Email = emp.Email;
    }
    te.SaveChanges();
    return View("Index", te.Employees.ToList());
}

Explanation: The first method is not decorated with any attribute like HttpGet, if we don't do that, Visual Studio considers that method as HttpGet by default. It takes an ID which we are passing from Index page if you notice the link for edit there is also ID attribute defined. Applying SingleOrDefault which gets single record based on ID from database and sends it to the Edit.cshtml for Viewing that specific record

The second Edit with HttpPost means client is posting data with some changes to the server, we are simply updating that specific instance of employee within our database and calling savechanges() which means any pending changes tracked by Entity Framework are requested to be committed and finally calling the Index ActionMethod of HomeController.cs to display all the list

Delete:

The below code will make the link delete work properly, it will permanently delete the record from the database

public ActionResult Delete(int ID)
{
    var employee = te.Employees.SingleOrDefault(x => x.ID == ID);
    te.Employees.Remove(employee);
    te.SaveChanges();
    return View("Index", te.Employees.ToList());
}

JQuery AJAX: All the calls to the server-side methods were made by using the default behavior of set by the Microsoft in their templates which isn't a standard practice. So, we'll replace our existing calls with JQuery calls. For this purpose you need to verify whether the JQuery is installed within your project by just going to the script folder within your project, we'll just work with Index method which we used to display the list of employees.

Replace your Index.cshtml code with the below code

@model IEnumerable<Blog.Models.Employee>
@{
    ViewBag.Title = "Home Page";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
 
<h3 id="hideEmployee">Employees</h3>
<div class="row" id="changeDivUsingAJAX">
    <div class="col-md-8">
        <span onclick="AjaxCall()">Create new Employee</span>
    </div>
        <div class="col-md-5">
        <table class="table table-hover">
            <tr>
                <td><b>First Name</b></td>
                <td><b>Last Name</b></td>
                <td><b>Ops</b></td>
            </tr>
            @foreach (var item in Model)
            {
                <tr>
                    <td>@item.Name</td>
                    <td>@item.Email</td>
                    <td>
                        @Html.ActionLink("Edit", "Edit", new {Id = item.ID}) |
                        @Html.ActionLink("Delete", "Delete", new { Id = item.ID })
                    </td>
                </tr>
            }
        </table>
    </div>
</div>
 
<script type="text/javascript">
    function AjaxCall() {
        $('#hideEmployee').hide();
        $.ajax({
            type: "GET",
            url: "/Home/Create_New",
            success: function (response) {
                debugger;
                $('#changeDivUsingAJAX').html(response);
            },
        });
    }
</script>

Note: If you are using MVC-5 then you need to go to _Layout within Shared folder within Views and move  

@Scripts.Render("~/bundles/jquery")

from end of Body section into the end of head tag of your html then the Jquery will work, it was a problem with default Jquery bundling in MVC.

That's it for now, Try making rest of the calls using JQuery AJAX, if you face any problems please leave comment below, I hope you guys enjoyed this detailed tutorial. 

Provide your valuable feedback about how I'm going, it will be helpful for me in creating tutorial in more useful manners.

Stay Blessed!