ProudMonkey

Building Web Application using Entity Framework and MVC 5: Part 3

This is part 3 of the series on Building Web Application in ASP.NET MVC 5. In Part 1, we’ve learned about creating a simple database from scratch using MS SQL Server, a brief overview about ASP.NET MVC in general, creating a data access using Entity Framework database first approach and a simple implementation of a Signup page. 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 the following links below:

In this article I’m going to talk about how to perform 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 particular demo, I’m going to use jQuery and jQuery AJAX to perform asynchronous operation in our page.

Let’s get started!

Fetching and Displaying 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 are using WebForms before then you can think of partial views as user-controls (.ascx).

STEP 1: Adding View Models

The first thing we need is to create view models for our view. Add the following code below within "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 classes that house some properties that we only need for the view or page.

STEP 2: Adding the ManageUserPartial view

Open "UserManager" class and declare the namespace below:

using System.Collections.Generic;  

The namespace above contains interfaces and classes that define generic collections, which allow us to create strongly-typed collections. Now add the following code below within your "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 "HomeController" class and add the following namespaces below:

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 make 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="javascript:void(0)" class="lnkEdit">Edit</a></td>
                    <td><a href="javascript: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>  

The markup above loads the ManageUserPartial view in the main view using the Action HTML helper.

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 on to the next step.

Editing and Updating Data

Since we are going to 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 in your project under the "Script" folder:

STEP 1: Referencing jQueryUI

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:

<link href="~/Content/themes/base/all.css" rel="stylesheet" />  
STEP 2: Adding the UpdateUserAccount() method

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

Add the following code below within "UserManager" 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. After that it then looks for the associated LOOKUPRoleID for a certain user. If the user doesn’t have a role assigned to it then it adds a new record to the database otherwise just update the table.

If you also noticed, I used a simple transaction within that method. This is because the table SYSUser, SYSUserProfile and SYSUserRole are pretty much related to each other and we need to make 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: Adding the UpdateUserData Action method

Add the following code within "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 pass the UserProfileView model view as the parameter. The UpdateUserData() method will be called through an AJAX request.

STEP 4: Modifying the UserManagePartial view

Now add the following HTML markup within "UserManagePartial.cshtml" view:

<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: Integrating jQuery and jQuery AJAX

Before we go to the implementation it’s important to know what these technologies are:

  • jQuery is a light weight and feature-rich JavaScript library that enable DOM manipulation, even 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 "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 variable initializes the jQueryUI dialog by customizing the dialog. We customized it by adding our own Save and Cancel button for us to write custom code implementation for each event. In the Save function we extracted each values from the edit form and pass 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 which 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 certain process if the request at the server 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 the 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: Modifying the ManageUserPartial Action method

If you remember, we added the new parameter "status" to the ManageUserPartial 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: Displaying the Status result

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

<span class="alert-success">@ViewBag.Message</span>  
STEP 8: Running the page

Here are the outputs below:

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: Adding the DeleteUser() method

Add the following method in "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 particular user in the SYSUserRole, SYSUserProfile and SYSUser tables.

STEP 2: Adding the DeleteUser Action method

Add the following code within "HomeController" class:

[AuthorizeRoles("Admin")]
 public ActionResult DeleteUser(int userID) {
    UserManager UM = new UserManager();
    UM.DeleteUser(userID);
    return Json(new { success = true });
}
STEP 3: Integrating jQuery and jQuery AJAX

Add the following script within the script tag in "ManageUserPartial" 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: Running the page

Here are the outputs below:

After clicking the delete link
After deletion

That’s it! I hope you enjoyed and learned something from this series of articles. Check-out the next article here: Building Web Application using Entity Framework and MVC 5: Part 4

Source Code

Source code for this series can be found here. You can also download it at CodeProject here.