ASP.net MVC razor checkbox for select and insert of role-permission

Question

I am really lost on this as to how I am going to present and submit the data

I have the following database table

role table

role_id (pk)
role_name

permission table

permission_id (pk)
permission

role_permission table:

role_permission_id (pk)
role_id (fk from role table)
perm_id (fk from permission table)
edit_perm (bit)
view_perm (bit) 

And based on the table, I have created the below view.

@using (Html.BeginForm("Update", "RolePermission", FormMethod.Post))
{
<div>
    <table border="1">
        <tr>
            <th colspan="2">RoleName</th>

            @if (Model.ListOfPermission.Count > 0)
            {
                foreach (var permitem in Model.ListOfPermission)
                {
                    <th>@permitem.permission</th>
                }
            }
        </tr>
        @if (Model.ListOfRoles.Count > 0)
        {
            foreach (var roleitem in Model.ListOfRoles)
            {
                <tr>
                    <th rowspan="2">@roleitem.role_name</th>
                    <td>Edit</td>
                    @if (Model.ListOfPermission.Count > 0)
                    {
                        foreach (var permitem in Model.ListOfPermission)
                        {
                            <td>@Html.CheckBox("EditCheckBox")</td>
                        }
                    }
                </tr>
                <tr>
                    <td>View</td>
                    @if (Model.ListOfPermission.Count > 0)
                    {
                        foreach (var permitem in Model.ListOfPermission)
                        {
                            <td>@Html.CheckBox("ViewCheckBox")</td>
                        }
                    }
                </tr>
            }
        }
    </table>
    <div>
        <button id="SaveBtn" type="submit">Save</button>
    </div>
</div>
}

and my model so far as below:

public class RolePermissionModel
{
    public List <tblRole> ListOfRoles { get; set; }
    public List <tblPermission> ListOfPermission { get; set; }
}

The above view gives the table format as the picture attached.

Sorry, I don't have enough reputation

My goal is to have check box checked depending on the role-permission table. above. For instance, if role-permission table has:

role_id: 1 -> (IT Admin role_id)
perm_id: 1 -> (Create User Permission)
edit_perm: 0
view_perm: 1

I need check checked on view sub-row of IT Admin row and Create User permission column.

Also, upon user clicking the checkbox and submit, I need to do insert.

The trouble here is, how do I pass the data from controller to view and how do I pass the view to controller to achieve that?

I am guessing I have to send the checkbox value along with column/row matching value to the controller on insert, but I have no idea how to achieve it. (Do I use jquery to get values of cell column value and row value?)

As for showing the checked value, I have no idea.

Your help will greatly be appreciated.

Answer 1

Here is the result for those who may need.

Edit: Edited when there is record initially.

Model:

public class RolePermissionModel
{
    public List<tblRole> ListOfRoles { get; set; }
    public List<tblPermission> ListOfPermission { get; set; }
    public List<tblRole_Permission> RolePermStatus { get; set; }
    public List<ColumnEditCheckBoxList> RowEditCheckBox { get; set; }
    public List<ColumnViewCheckBoxList> RowViewCheckBox { get; set; }
}

public class ColumnEditCheckBoxList
{
    public List<EditCheckBoxElement> ColEditCheckBox { get; set; }
}

public class ColumnViewCheckBoxList
{
    public List<ViewCheckBoxElement> ColViewCheckBox { get; set; }
}

public class EditCheckBoxElement
{
    public bool IsEditChecked { get; set; }
}

public class ViewCheckBoxElement
{
    public bool IsViewChecked { get; set; }
}

View:

@model RoadTex_MVC_Project.Models.RolePermission_Model.RolePermissionModel
@{
    ViewBag.Title = "RolePermission";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>ViewBag.Title</h2>
@using (Html.BeginForm("RolePermission", "RolePermission", FormMethod.Post))
{
    <div>
        <table border="1">
            <tr>
                <th colspan="2">RoleName</th>
                @if (Model.ListOfPermission.Count > 0)
                {
                    for (int i = 0; i< Model.ListOfPermission.Count; i++)
                    {
                        <th>@Model.ListOfPermission[i].permission</th>
                    }
                }
            </tr>
            @if (Model.ListOfRoles.Count > 0)
            {
                for (int i = 0; i < Model.ListOfRoles.Count; i++)
                {
                    <tr>
                        <th rowspan="2">@Model.ListOfRoles[i].role_name</th>
                        <th>Edit</th>
                        @if (Model.ListOfPermission.Count > 0)
                        {
                            for (int j=0; j < Model.ListOfPermission.Count; j++)
                            {
                                <td rowspan="2">
                                    @Html.HiddenFor(e => e.ListOfRoles[i].role_id)
                                    @Html.HiddenFor(e => e.ListOfPermission[j].perm_id)
                                    @Html.CheckBoxFor(e => e.RowEditCheckBox[i].ColEditCheckBox[j].IsEditChecked)
                                    <br />
                                    @Html.CheckBoxFor(e => e.RowViewCheckBox[i].ColViewCheckBox[j].IsViewChecked)
                                </td>
                            }
                        }
                    </tr>
                    <tr>
                        <th>View</th>
                    </tr>
                }
            }
        </table>
        <div>
            <button id="SaveBtn" type="submit">Save</button>
        </div>
    </div>
}

Controller:

public class RolePermissionController : Controller
{
    private static RoadTex_MVC_Model_Local DB;

    [NonAction]
    private RoadTex_MVC_Model_Local Connect()
    {
        DB = new RoadTex_MVC_Model_Local();
        return DB;
    }

    [NonAction]
    private void PopulateModel(RolePermissionModel Model)
    {
        using (Connect())
        {
            Model.ListOfRoles = DB.tblRoles.ToList();
            Model.ListOfPermission = DB.tblPermissions.ToList();
            Model.RolePermStatus = DB.tblRole_Permission.ToList();
        }
    }

    [NonAction]
    private void UpdateCheckBox(RolePermissionModel Model)
    {
        using (Connect())
        {
            for (int i = 0; i < Model.ListOfRoles.Count; i++)
            {
                for (int j = 0; j < Model.ListOfPermission.Count; j++)
                {
                    tblRole_Permission RolePermission = new tblRole_Permission
                    {
                        role_id = Model.ListOfRoles[i].role_id,
                        perm_id = Model.ListOfPermission[j].perm_id,
                        edit_perm = Model.RowEditCheckBox[i].ColEditCheckBox[j].IsEditChecked,
                        view_perm = Model.RowViewCheckBox[i].ColViewCheckBox[j].IsViewChecked
                    };
                    DB.tblRole_Permission.AddOrUpdate(e => new { e.role_id, e.perm_id }, RolePermission);
                }
            }
            DB.SaveChanges();
        }
    }
    [NonAction]
    private void GenerateCheckBox(RolePermissionModel Model)
    {
        using (Connect())
        { 
            for (int i = 0; i < Model.ListOfRoles.Count; i++)
            {
                for (int j = 0; j < Model.ListOfPermission.Count; j++)
                {
                    var temp_role = Model.ListOfRoles[i].role_id;
                    var temp_perm = Model.ListOfPermission[j].perm_id;
                    tblRole_Permission RolePermission = new tblRole_Permission
                    {
                        role_id = temp_role,
                        perm_id = temp_perm,
                        edit_perm = false,
                        view_perm = false
                    };
                    if (!DB.tblRole_Permission.Where(e => e.role_id == temp_role
                    && e.perm_id == temp_perm).Any())
                    {
                        DB.tblRole_Permission.Add(RolePermission);
                    }
                }
            }
            DB.SaveChanges();
            Model.RolePermStatus = DB.tblRole_Permission.ToList();
        }
    }


    [NonAction]
    private void ShowCheckBox(RolePermissionModel Model)
    {
        Model.RowEditCheckBox = new List<ColumnEditCheckBoxList>();
        Model.RowViewCheckBox = new List<ColumnViewCheckBoxList>();
        int k = 0;
        for (int i = 0; i < Model.ListOfRoles.Count; i++)
        {
            ColumnEditCheckBoxList ColEditList = new ColumnEditCheckBoxList()
            {
                ColEditCheckBox = new List<EditCheckBoxElement>()
            };
            ColumnViewCheckBoxList ColViewList = new ColumnViewCheckBoxList()
            {
                ColViewCheckBox = new List<ViewCheckBoxElement>()
            };
            for (int j = 0; j < Model.ListOfPermission.Count; j++)
            {
                EditCheckBoxElement EditEl = new EditCheckBoxElement();
                ViewCheckBoxElement ViewEl = new ViewCheckBoxElement();
                EditEl.IsEditChecked = Model.RolePermStatus[k].edit_perm;
                ViewEl.IsViewChecked = Model.RolePermStatus[k].view_perm;
                k++;
                ColEditList.ColEditCheckBox.Add(EditEl);
                ColViewList.ColViewCheckBox.Add(ViewEl);
            }
            Model.RowEditCheckBox.Add(ColEditList);
            Model.RowViewCheckBox.Add(ColViewList);
        }
    }

    [HttpGet]
    public ActionResult RolePermission()
    {
        //if (Session["UserName"] == null)
        //{
        //    return RedirectToAction("Login", "Home");
        //}
        RolePermissionModel Model = new RolePermissionModel();
        PopulateModel(Model);
        GenerateCheckBox(Model);
        ShowCheckBox(Model);
        return View("RolePermission", Model);
    }

    [HttpPost]
    public ActionResult RolePermission(RolePermissionModel Model)
    {
        UpdateCheckBox(Model);
        return RedirectToAction("RolePermission");
    }
}

Performance is really bad because controller is looping through number of times in order to achieve desired result.

Using sql stored procedure (possibly using pivot) and putting into webgrid will be much simpler and better for the performance.