C ා MVC implementation to configure roles for employees (full details + database)

Database create user table role table user role relationship table

create table roles
(
RId int identity,
RName varchar(50),
Remark varchar(50)
)
create table UserRole
(
Users_UId int,
roles_Rid int
)
create table Users
(
UId int identity,
UName varchar(50),
UPwd varchar(50)
)

Create a view view of the database

create view USER_SHOW
AS
select RName,RId,UName,UId from Users join UserRole on Users.UId=UserRole.Users_UId join roles on UserRole.roles_Rid=roles.RId 

Then open VS to create MVC

Add a controller

Controller needs reference

using Dapper;
using System.Data.SqlClient;

The controller code is as follows

public ActionResult Index()
        {
            using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Unit13;Integrated Security=True"))
            {
                List<UserAndRole> list = conn.Query<UserAndRole>("select UId,UName,stuff((select ','+RName from USER_SHOW where a.UId = UId for xml path('')),1,1,'') as RName from USER_SHOW as a group by UId,UName").ToList();
                return View(list);
            }
        }

        // GET: User
        public ActionResult Shezhi(int Uid)
        {
            using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Unit13;Integrated Security=True"))
            {
                Session["Uid"] = Uid;
                ViewBag.list = GetBind();
                List<UserAndRole> list = conn.Query<UserAndRole>($"select RId,RName from Users join UserRole on Users.UId = UserRole.Users_UId join roles on UserRole.roles_Rid = roles.RId where UId = {Uid}").ToList();
                return View(list);
            }
        }
        public List<UserAndRole> GetBind()
        {
            using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Unit13;Integrated Security=True"))
            {
                return conn.Query<UserAndRole>("select * from  roles ").ToList();
            }
        }

        public int Delete(int Rid)
        {
            using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Unit13;Integrated Security=True"))
            {
                return conn.Execute($"delete from UserRole where roles_Rid={Rid}");
            }
        }

        public int Add(string UId, string RId)
        {
            UId = Session["Uid"].ToString();
            using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Unit13;Integrated Security=True"))
            {
                object n = conn.ExecuteScalar($"select count(1) from UserRole where Users_UId={UId} and roles_Rid={RId}");
                if (Convert.ToInt32(n) == 0)
                {
                    return conn.Execute($"insert into UserRole values('{UId}','{RId}')");
                }
                else
                {
                    return 0;
                }

            }
        }

        public class UserAndRole
        {
            public int UId { get; set; }
            public string UName { get; set; }
            public string RName { get; set; }
            public int RId { get; set; }

        }

Then create the Index view(

  1. Page displays employee information
  2. Click "set role" to jump to Shezi page and assign values to the following parts

(1) All roles are shown on the right

(2) The existing roles of the current employee are shown on the left)

)

@using Configuration role.Controllers
@model List<UserController.UserAndRole>
@{
    ViewBag.Title = "Index";
}

<table class="table-bordered table">
    <tr>
        <td>number</td>
        <td>Employee name</td>
        <td>role</td>
        <td></td>
    </tr>
    @foreach (var item in Model)
    {
        <tr>
            <td>@item.UId</td>
            <td>@item.UName</td>
            <td>@item.RName</td>
            <td> <a href="/User/Shezhi?Uid=@item.UId">Setting roles</a></td>
        </tr>
    }
</table>

Operation effect

 

 

Add another view

@{
    ViewBag.Title = "Shezhi";
}
@using Configuration role.Controllers
@model List<UserController.UserAndRole>

<div id="app" style="height:250px;width:100%;border:double">
    <div style="height:150px;width:250px;border:double;float:left;margin-top:45px;margin-left:20px">
        <span>All optional roles:</span>
        <select id="Select1" multiple="true">
            @foreach (var item in ViewBag.list as List<UserController.UserAndRole>)
            {
                <option value="@item.RId">@item.RName</option>
            }

        </select>
    </div>
    <div style="height:150px;width:150px;float:left;margin-top:80px;margin-left:25%">
        <button onclick="Zuo()">←</button>
        <br>
        <button onclick="You()">→</button>
    </div>
    <div style="height:150px;width:250px;border:double;float:right;margin-top:45px;margin-right:20px">
        <span>Current employee's role:</span>
        <select id="Select2" multiple="true">
            @foreach (var item in Model)
            {
                <option value="@item.RId">@item.RName</option>
            }

        </select>

        <input id="Hidden1" type="@Session["Uid"]" />
    </div>
</div>

<script>
    function Zuo() {
        //alert(1);
        var id = $("#Select2").val();
        if (id == null) {
            alert('Please choose')
        }
        else {
            $.ajax({
                url: "/User/Delete?rid=" + id,
                success: function (d) {
                    if (d > 0) {
                        alert('Success');
                    }
                }

            })

        }

    }
    function You() {
        //alert(1);

        var UId = $("#Hidden1").val();
        var RId = $("#Select1").val();

        $.ajax({
            url: "/User/Add?Uid=" + UId + "&RId=" + RId,
            success: function (d) {
                if (d > 0) {
                    alert('Success');
                }
                else {
                    alert('User already exists');
                }
            }

        })
    }

</script>

Realization effect

 

 

(1) Select it on the right, and then click a button in the middle to delete it

(2) Select the one on the left, and then click another button in the middle to add it to the left

Tags: C# Session Database xml

Posted on Sun, 12 Jan 2020 06:20:14 -0800 by usmanmr