数据库创建“用户表”“角色表”“用户角色关系表”
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视图(
- 页面显示雇员信息
- 点击“设置角色”跳转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/Shezhi?Uid=@item.UId">设置角色</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 οnclick="Zuo()">←</button> <br> <button οnclick="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) 左侧的选择了,再点击中部的另一个按钮可以添加到左侧