C#MVC實現為雇員配置角色(完整詳細+資料庫)

資料庫創建“用戶表”“角色表”“用戶角色關係表”

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)
)

資料庫創建一個view視圖

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 

然後打開VS創建MVC

添加一個控制器

控制器需要引用

using Dapper;
using System.Data.SqlClient;

控制器代碼如下

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; }

        }

然後創建Index視圖（

1. 頁面顯示雇員信息
2. 點擊“設置角色”跳轉Shezi頁面為以下部分賦值

(1) 右側顯示的是所有“角色”

(2) 左側顯示的是當前雇員 現有的角色）

）

@using 配置角色.Controllers
@model List<UserController.UserAndRole>
@{
    ViewBag.Title = "Index";
}

<table class="table-bordered table">
    <tr>
        <td>編號</td>
        <td>雇員姓名</td>
        <td>角色</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/[email protected]">設置角色</a></td>
        </tr>
    }
</table>

運行效果

再添加一個Shezhi視圖

@{
    ViewBag.Title = "Shezhi";
}
@using 配置角色.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>所有可選角色：</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>當前雇員所屬角色：</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('請選擇')
        }
        else {
            $.ajax({
                url: "/User/Delete?rid=" + id,
                success: function (d) {
                    if (d > 0) {
                        alert('成功');
                    }
                }

            })

        }

    }
    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('成功');
                }
                else {
                    alert('用戶已存在');
                }
            }

        })
    }

</script>

實現效果

(1) 右側選擇了，再點擊中部的一個按鈕可以刪除

(2) 左側的選擇了，再點擊中部的另一個按鈕可以添加到左側