Building Web Application Using Entity Framework and MVC 5: Part Three

This is Part 3 of the series on Building Web Applications in ASP.NET MVC 5. In Part 1, you've learned about creating a simple database from scratch using Microsoft SQL Server, a brief overview about ASP.NET MVC in general, creating a data access using the Entity Framework Database First approach and a simple implementation of a Signup page in MVC . Part 2 of the series talks about the step-by-step process on creating a basic login page and creating a simple role-based page authorization within the MVC application. If you haven't gone through my previous articles then you can refer to the following links:

• Building Web Application Using Entity Framework and MVC 5: Part 1
• Building Web Application Using Entity Framework and MVC 5: Part 2

This article shows how to do Fetch, Edit, Update and Delete (FEUD) operations in our application. The idea is to create a maintenance page where admin users can modify user profiles. There are many possible ways to implement FEUD operations in MVC depending on your business needs. For this specific demo, I'm going to use jQuery and jQuery AJAX to do asynchronous operations in our page.

Let's get started!

Fetching and Displaying the Data

For this example, I'm going to create a partial view for displaying the list of users from the database. Partial views allow you to define a view that will be rendered inside a main view. If you have used WebForms before then you can think of partial views as user controls (.ascx).

Step 1: Adding the View Models

The first thing we need is to create view models for our view. Add the following code within the “UserModel.cs” class:

public class UserProfileView
    {
        [Key]
        public int SYSUserID { get; set; }
        public int LOOKUPRoleID { get; set; }
        public string RoleName { get; set; }
        public bool? IsRoleActive { get; set; }
        [Required(ErrorMessage = "*")]
        [Display(Name = "Login ID")]
        public string LoginName { get; set; }
        [Required(ErrorMessage = "*")]
        [Display(Name = "Password")]
        public string Password { get; set; }
        [Required(ErrorMessage = "*")]
        [Display(Name = "First Name")]
        public string FirstName { get; set; }
        [Required(ErrorMessage = "*")]
        [Display(Name = "Last Name")]
        public string LastName { get; set; }
        public string Gender { get; set; }
    }

    public class LOOKUPAvailableRole
    {
        [Key]
        public int LOOKUPRoleID { get; set; }
        public string RoleName { get; set; }
        public string RoleDescription { get; set; }
    }

    public class Gender
    {
        public string Text { get; set; }
        public string Value { get; set; }
    }
    public class UserRoles
    {
        public int? SelectedRoleID { get; set; }
        public IEnumerable<LOOKUPAvailableRole> UserRoleList { get; set; }
    }

    public class UserGender
    {
        public string SelectedGender { get; set; }
        public IEnumerable<Gender> Gender { get; set; }
    }
    public class UserDataView
    {
        public IEnumerable<UserProfileView> UserProfile { get; set; }
        public UserRoles UserRoles { get; set; }
        public UserGender UserGender { get; set; }
    }

If you still remember, view models are models that house some properties that we only need for the view or page.

Step 2: Adding the ManageUserPartial view

Open the “UserManager” class and declare the namespace below:

using System.Collections.Generic;

The namespace above contains interfaces and classes that define generic collections that allow us to create strongly-typed collections. Now add the following code to the “UserManager” class:

public List < LOOKUPAvailableRole > GetAllRoles() {
    using(DemoDBEntities db = new DemoDBEntities()) {
        var roles = db.LOOKUPRoles.Select(o = > new LOOKUPAvailableRole {
            LOOKUPRoleID = o.LOOKUPRoleID,
            RoleName = o.RoleName,
            RoleDescription = o.RoleDescription
        }).ToList();

        return roles;
    }
}

public int GetUserID(string loginName) {
    using(DemoDBEntities db = new DemoDBEntities()) {
        var user = db.SYSUsers.Where(o = > o.LoginName.Equals(loginName));
        if (user.Any()) return user.FirstOrDefault().SYSUserID;
    }
    return 0;
}
public List < UserProfileView > GetAllUserProfiles() {
    List < UserProfileView > profiles = new List < UserProfileView > ();
    using(DemoDBEntities db = new DemoDBEntities()) {
        UserProfileView UPV;
        var users = db.SYSUsers.ToList();

        foreach(SYSUser u in db.SYSUsers) {
            UPV = new UserProfileView();
            UPV.SYSUserID = u.SYSUserID;
            UPV.LoginName = u.LoginName;
            UPV.Password = u.PasswordEncryptedText;

            var SUP = db.SYSUserProfiles.Find(u.SYSUserID);
            if (SUP != null) {
                UPV.FirstName = SUP.FirstName;
                UPV.LastName = SUP.LastName;
                UPV.Gender = SUP.Gender;
            }

            var SUR = db.SYSUserRoles.Where(o = > o.SYSUserID.Equals(u.SYSUserID));
            if (SUR.Any()) {
                var userRole = SUR.FirstOrDefault();
                UPV.LOOKUPRoleID = userRole.LOOKUPRoleID;
                UPV.RoleName = userRole.LOOKUPRole.RoleName;
                UPV.IsRoleActive = userRole.IsActive;
            }

            profiles.Add(UPV);
        }
    }

    return profiles;
}

public UserDataView GetUserDataView(string loginName) {
    UserDataView UDV = new UserDataView();
    List < UserProfileView > profiles = GetAllUserProfiles();
    List < LOOKUPAvailableRole > roles = GetAllRoles();

    int ? userAssignedRoleID = 0, userID = 0;
    string userGender = string.Empty;

    userID = GetUserID(loginName);
    using(DemoDBEntities db = new DemoDBEntities()) {
        userAssignedRoleID = db.SYSUserRoles.Where(o = > o.SYSUserID == userID) ? .FirstOrDefault().LOOKUPRoleID;
        userGender = db.SYSUserProfiles.Where(o = > o.SYSUserID == userID) ? .FirstOrDefault().Gender;
    }

    List < Gender > genders = new List < Gender > ();
    genders.Add(new Gender {
        Text = "Male", Value = "M"
    });
    genders.Add(new Gender {
        Text = "Female", Value = "F"
    });

    UDV.UserProfile = profiles;
    UDV.UserRoles = new UserRoles {
        SelectedRoleID = userAssignedRoleID, UserRoleList = roles
    };
    UDV.UserGender = new UserGender {
        SelectedGender = userGender, Gender = genders
    };
    return UDV;
}

The methods shown from the code above is pretty much self-explanatory as their method names suggest. The main method there is the GetUserDataView() and what it does is it gets all user profiles and roles. The UserRoles and UserGender properties will be used during the editing and updating of the user data. We will use these values to populate the dropdown lists for roles and gender.

Step 3: Adding the ManageUserPartial action method

Open the “HomeController.cs” class and add the following namespaces:

using System.Web.Security;
using MVC5RealWorld.Models.ViewModel;
using MVC5RealWorld.Models.EntityManager;

And then add the following action method below:

[AuthorizeRoles("Admin")]
public ActionResult ManageUserPartial() {
   if (User.Identity.IsAuthenticated) {
      string loginName = User.Identity.Name;
      UserManager UM = new UserManager();
      UserDataView UDV = UM.GetUserDataView(loginName);
      return PartialView(UDV);
   }
   return View();
}

The code above is decorated with the custom Authorize attribute so that only admin users can invoke that method. What it does is it calls the GetUserDataView() method by passing in the loginName as the parameter and return the result in the partial view.

Step 4: Adding the ManageUserPartial partial view

Now let's create the partial view. Right-click on the ManageUserPartial method and select “Add New” view. This will bring up the following dialog:



Since we will create a custom view for managing the users then just select an “Empty” template and be sure to tick the “Create as a partial view” option. Click Add and then copy the following HTML markup below:

@model MVC5RealWorld.Models.ViewModel.UserDataView

<div>
    <h1>List of Users</h1>
    <table class="table table-striped table-condensed table-hover">
        <thead>
            <tr>
                <th>ID</th>
                <th>Login ID</th>
                <th>Password</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Gender</th>
                <th colspan="2">Role</th>
                <th></th>
                <th></th>
            </tr>
        </thead>
        <tbody>
            @foreach (var i in Model.UserProfile) {
                <tr>
                    <td> @Html.DisplayFor(m => i.SYSUserID)</td>
                    <td> @Html.DisplayFor(m => i.LoginName)</td>
                    <td> @Html.DisplayFor(m => i.Password)</td>
                    <td> @Html.DisplayFor(m => i.FirstName)</td>
                    <td> @Html.DisplayFor(m => i.LastName)</td>
                    <td> @Html.DisplayFor(m => i.Gender)</td>
                    <td> @Html.DisplayFor(m => i.RoleName)</td>
                    <td> @Html.HiddenFor(m => i.LOOKUPRoleID)</td>
                    <td><a href="javacript:void(0)" class="lnkEdit">Edit</a></td>
                    <td><a href="javacript:void(0)" class="lnkDelete">Delete</a></td>
                </tr>
            }
        </tbody>
    </table>
</div>

Now open the “AdminOnly.cshtml” view and add the following markup:

<div id="divUserListContainer">
    @Html.Action("ManageUserPartial", "Home");
</div>

Step 5: Running the page

Now try to login to your web page then navigate to: http://localhost:15599/Home/AdminOnly. The output should look something like this:



Pretty easy, right? Now let's move to the next step.

Editing and Updating the Data

Since we will use jQueryUI for providing a dialog box for the user to edit the data then we need to add a reference to it first. To do that, just right-click on your project and then select Manage Nuget Packages. In the search box type in “jquery” and select “jQuery.UI.Combined” as shown in the image below:



Once installed the jQueryUI library should be added to your project under the “Script” folder:



Step 1

Now go to Views > Shared >_Layout.cshtml and add the jQueryUI reference in the following order:

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery-ui-1.11.4.min.js"></script>
<script src="~/Scripts/bootstrap.min.js"></script>

The jQueryUI should be added after the jQuery since jQueryUI uses the core jQuery library under the hood.

Now add the jQueryUI CSS reference as in the following:

<link href="~/Content/themes/base/all.css" rel="stylesheet" />

Step 2

Now to add the UpdateUserAccount() method.

Keep in mind that this demo is intended to make an app as simple as possible. In complex real scenarios I strongly suggest you use a Repository pattern and Unit-of-Work for your data access layer.

Add the following code within the “UserManager.cs” class:

public void UpdateUserAccount(UserProfileView user) {

    using(DemoDBEntities db = new DemoDBEntities()) {
        using(var dbContextTransaction = db.Database.BeginTransaction()) {
            try {

                SYSUser SU = db.SYSUsers.Find(user.SYSUserID);
                SU.LoginName = user.LoginName;
                SU.PasswordEncryptedText = user.Password;
                SU.RowCreatedSYSUserID = user.SYSUserID;
                SU.RowModifiedSYSUserID = user.SYSUserID;
                SU.RowCreatedDateTime = DateTime.Now;
                SU.RowMOdifiedDateTime = DateTime.Now;

                db.SaveChanges();

                var userProfile = db.SYSUserProfiles.Where(o = > o.SYSUserID == user.SYSUserID);
                if (userProfile.Any()) {
                    SYSUserProfile SUP = userProfile.FirstOrDefault();
                    SUP.SYSUserID = SU.SYSUserID;
                    SUP.FirstName = user.FirstName;
                    SUP.LastName = user.LastName;
                    SUP.Gender = user.Gender;
                    SUP.RowCreatedSYSUserID = user.SYSUserID;
                    SUP.RowModifiedSYSUserID = user.SYSUserID;
                    SUP.RowCreatedDateTime = DateTime.Now;
                    SUP.RowModifiedDateTime = DateTime.Now;

                    db.SaveChanges();
                }

                if (user.LOOKUPRoleID > 0) {
                    var userRole = db.SYSUserRoles.Where(o = > o.SYSUserID == user.SYSUserID);
                    SYSUserRole SUR = null;
                    if (userRole.Any()) {
                        SUR = userRole.FirstOrDefault();
                        SUR.LOOKUPRoleID = user.LOOKUPRoleID;
                        SUR.SYSUserID = user.SYSUserID;
                        SUR.IsActive = true;
                        SUR.RowCreatedSYSUserID = user.SYSUserID;
                        SUR.RowModifiedSYSUserID = user.SYSUserID;
                        SUR.RowCreatedDateTime = DateTime.Now;
                        SUR.RowModifiedDateTime = DateTime.Now;
                    } else {
                        SUR = new SYSUserRole();
                        SUR.LOOKUPRoleID = user.LOOKUPRoleID;
                        SUR.SYSUserID = user.SYSUserID;
                        SUR.IsActive = true;
                        SUR.RowCreatedSYSUserID = user.SYSUserID;
                        SUR.RowModifiedSYSUserID = user.SYSUserID;
                        SUR.RowCreatedDateTime = DateTime.Now;
                        SUR.RowModifiedDateTime = DateTime.Now;
                        db.SYSUserRoles.Add(SUR);
                    }

                    db.SaveChanges();
                }
                dbContextTransaction.Commit();
            } catch {
                dbContextTransaction.Rollback();
            }
        }
    }
}

The method above takes a UserProfileView object as the parameter. This parameter object is coming from a strongly-type view. What it does is it first issues a query to the database using the LINQ syntax to get the specific user data by passing the SYSUserID. It then updates the SYSUser object with the corresponding data from the UserProfileView object. The second query gets the associated SYSUserProfiles data and then updates the corresponding values. Then it then looks for the associated LOOKUPRoleID for a certain user. If the user doesn't have a role assigned to them then it adds a new record to the database otherwise just update the table.

If you also notice, I used a simple transaction within that method. This is because the tables SYSUser, SYSUserProfile and SYSUserRole are pretty much related to each other and we need to be sure that we only commit changes to the database if the operation for each table is successful. The Database.BeginTransaction() is only available in EF 6 onwards.

Step 3

Now to add the UpdateUserData Action Method.

Add the following code within the “HomeController” class:

[AuthorizeRoles("Admin")]
public ActionResult UpdateUserData(int userID, string loginName, string password, 
     string firstName, string lastName, string gender, int roleID = 0) {
    UserProfileView UPV = new UserProfileView();
    UPV.SYSUserID = userID;
    UPV.LoginName = loginName;
    UPV.Password = password;
    UPV.FirstName = firstName;
    UPV.LastName = lastName;
    UPV.Gender = gender;

    if (roleID > 0)
      UPV.LOOKUPRoleID = roleID;

      UserManager UM = new UserManager();
      UM.UpdateUserAccount(UPV);

      return Json(new { success = true });
}

The method above is responsible for collecting data that is sent from the view for update. It then calls the method UpdateUserAccount() and passes the UserProfileView model view as the parameter. The UpdateUserData method will be called using an AJAX request.

Step 4

Now to modify the UserManagePartial view.

Add the following HTML markup within UserManagePartial.cshtml:

<div id="divEdit" style="display:none">
    <input type="hidden" id="hidID" />
    <table>
        <tr>
            <td>Login Name</td>
            <td>
                <input type="text" id="txtLoginName" class="form-control" />
            </td>
        </tr>
        <tr>
            <td>Password</td>
            <td>
                <input type="text" id="txtPassword" class="form-control" />
            </td>
        </tr>
        <tr>
            <td>First Name</td>
            <td>
                <input type="text" id="txtFirstName" class="form-control" />
            </td>
        </tr>
        <tr>
            <td>Last Name</td>
            <td>
                <input type="text" id="txtLastName" class="form-control" />
            </td>
        </tr>
        <tr>
            <td>Gender</td>
            <td>@Html.DropDownListFor(o => o.UserGender.SelectedGender,
                       new SelectList(Model.UserGender.Gender, "Value", "Text"),
                       "",
                       new { id = "ddlGender", @class="form-control" })
                </td>
        </tr>
        <tr>
            <td>Role</td>
            <td>@Html.DropDownListFor(o => o.UserRoles.SelectedRoleID,
                       new SelectList(Model.UserRoles.UserRoleList, "LOOKUPRoleID", "RoleName"),
                       "",
                       new { id = "ddlRoles", @class="form-control" })
                </td>
        </tr>
    </table>
</div>

Step 5

Now to add the integrate jQuery and jQuery AJAX.

Before we go to the implementation it's important to understand what these technologies are.

• jQuery is a light weight and feature-rich JavaScript library that enables DOM manipulation, event handling, animation and Ajax much simpler with powerful API that works across all major browsers.
• jQueryUi provides a set of UI interactions, effects, widgets and themes built on top of the jQuery library.
• jQuery AJAX enables you to use functions and methods to communicate with your data from the server and loads your data to the client/browser.

Now switch back to the “UserManagePartial” view and add the following script block at the very bottom:

<script type="text/javascript">
    $(function () {

        var initDialog = function (type) {
            var title = type;
            $("#divEdit").dialog({
                autoOpen: false,
                modal: true,
                title: type + ' User',
                width: 360,
                buttons: {
                    Save: function () {
                        var id = $("#hidID").val();
                        var role = $("#ddlRoles").val();
                        var loginName = $("#txtLoginName").val();
                        var loginPass = $("#txtPassword").val();
                        var fName = $("#txtFirstName").val();
                        var lName = $("#txtLastName").val();
                        var gender = $("#ddlGender").val();

                        UpdateUser(id, loginName, loginPass, fName, lName, gender, role);
                        $(this).dialog("destroy");
                    },
                    Cancel: function () { $(this).dialog("destroy"); }
                }
            });
        }

        function UpdateUser(id, logName, logPass, fName, lName, gender, role) {
            $.ajax({
                type: "POST",
                url: "@(Url.Action("UpdateUserData","Home"))",
                data: { userID: id, loginName: logName, password: logPass, firstName: fName, lastName: lName, gender: gender, roleID: role },
                success: function (data) {
                    $("#divUserListContainer").load("@(Url.Action("ManageUserPartial","Home", new { status ="update" }))");
                },
                error: function (error) {
                    //to do:
                }
            });
        }

        $("a.lnkEdit").on("click", function () {
            initDialog("Edit");
            $(".alert-success").empty();
            var row = $(this).closest('tr');

            $("#hidID").val(row.find("td:eq(0)").html().trim());
            $("#txtLoginName").val(row.find("td:eq(1)").html().trim())
            $("#txtPassword").val(row.find("td:eq(2)").html().trim())
            $("#txtFirstName").val(row.find("td:eq(3)").html().trim())
            $("#txtLastName").val(row.find("td:eq(4)").html().trim())
            $("#ddlGender").val(row.find("td:eq(5)").html().trim())
            $("#ddlRoles").val(row.find("td:eq(7) > input").val().trim());

            $("#divEdit").dialog("open");
            return false;
        });
    });

</script>

The initDialog initializes the jQueryUI dialog by customizing the dialog. We customized it by adding our own Save and Cancel buttons for us to write custom code implemetation for each event. In the Save function we extracted each value from the edit form and passed these values to the UpdateUser() JavaScript function.

The UpdateUser() function issues an AJAX request using jQuery AJAX. The "type" parameter indicates what form method the request requires, in this case we set the type as "POST". The "url" is the path to the controller's method that we created in Step 3. Note that the value of url can be a web service, Web API or anything that host your data. The "data" is where we assign values to the method that requires parameter. If your method in the server doesn't require any parameter then you can leave this as empty "{}". The "success" function is usually used when you do a certain process if the request succeeds. In this case we load the partial view to reflect the changes on the view after we update the data. Keep in mind that we are passing a new parameter to the "ManageUserPartial" action that indicates the status of the request.

The last function is where we open the dialog when the user clicks on the "edit" link from the grid. This is also where we extract the data from the grid using jQuery selectors and populate the dialog fields with the extracted data.

Step 6

Now to add then modify the UserManagePartial action method.

If you remember, we added the new parameter “status” to the UserManagePartial method in our AJAX request so we need to update the method signature to accept a parameter. The new method should now look something like this:

[AuthorizeRoles("Admin")]
public ActionResult ManageUserPartial(string status = "") {
    if (User.Identity.IsAuthenticated) {
        string loginName = User.Identity.Name;
        UserManager UM = new UserManager();
        UserDataView UDV = UM.GetUserDataView(loginName);

        string message = string.Empty;
        if (status.Equals("update")) message = "Update Successful";
        else if (status.Equals("delete")) message = "Delete Successful";

        ViewBag.Message = message;

        return PartialView(UDV);
    }

    return RedirectToAction("Index", "Home");
}

Step 7

Now to add the display the Status result.

If you notice we are creating a message string based on a certain operation and store the result in ViewBag. This is to let the user see if a certain operation succeeds. Now add the following markup within the “ManageUserPartial” view:

<span class="alert-success">@ViewBag.Message</span>

Step 8

Now to run the page.

Here are the outputs.

After clicking the edit dialog:



Editing the data:



After updating the data:



If you've made it this far then congratulations, you're now ready for the next step. Now down to the last part of this series.

Deleting Data

Step 1

Now to add the DeleteUser method.

Add the following method to the “UserManager” class:

public void DeleteUser(int userID) {
    using(DemoDBEntities db = new DemoDBEntities()) {
        using(var dbContextTransaction = db.Database.BeginTransaction()) {
            try {

                var SUR = db.SYSUserRoles.Where(o = > o.SYSUserID == userID);
                if (SUR.Any()) {
                    db.SYSUserRoles.Remove(SUR.FirstOrDefault());
                    db.SaveChanges();
                }

                var SUP = db.SYSUserProfiles.Where(o = > o.SYSUserID == userID);
                if (SUP.Any()) {
                    db.SYSUserProfiles.Remove(SUP.FirstOrDefault());
                    db.SaveChanges();
                }

                var SU = db.SYSUsers.Where(o = > o.SYSUserID == userID);
                if (SU.Any()) {
                    db.SYSUsers.Remove(SU.FirstOrDefault());
                    db.SaveChanges();
                }

                dbContextTransaction.Commit();
            } catch {
                dbContextTransaction.Rollback();
            }
        }
    }
}

The method above deletes the record for a specific user in the SYSUserRole, SYSUserProfile and SYSUser tables.

Step 2

Now to add the DeleteUser action method.

Add the following code within the “HomeController” class:

[AuthorizeRoles("Admin")]
public ActionResult DeleteUser(int userID) {
   UserManager UM = new UserManager();
   UM.DeleteUser(userID);
   return Json(new { success = true });
}

Step 3

Now to add the integrate jQuery and jQuery AJAX.

Add the following script within the <script> tag in the "UserManagePartial" view:

function DeleteUser(id) {
    $.ajax({
        type: "POST",
        url: "@(Url.Action("
        DeleteUser ","
        Home "))",
        data: {
            userID: id
        },
        success: function(data) {
            $("#divUserListContainer").load("@(Url.Action("
            ManageUserPartial ","
            Home ", new { status ="
            delete " }))");
        },
        error: function(error) {}
    });
}

$("a.lnkDelete").on("click", function() {
    var row = $(this).closest('tr');
    var id = row.find("td:eq(0)").html().trim();
    var answer = confirm("You are about to delete this user with ID " + id + " . Continue?");
    if (answer) DeleteUser(id);
    return false;
});

Step 4

Now to run the page.

Here are the outputs.

After clicking the delete link:



After deletion:



That's it! I hope you enjoyed and learned something from this series of articles.