数据库创建“用户表”“角色表”“用户角色关系表”
create table roles
(
RId int identity,
RName varchar(),
Remark varchar()
)
create table UserRole
(
Users_UId int,
roles_Rid int
)
create table Users
(
UId int identity,
UName varchar(),
UPwd varchar()
)
数据库创建一个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) == )
{
return conn.Execute($"insert into UserRole values('{UId}','{RId}')");
}
else
{
return ;
} }
} 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" rel="external nofollow" >设置角色</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 > ) {
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 > ) {
alert('成功');
}
else {
alert('用户已存在');
}
} })
}</script>
实现效果
(1) 右侧选择了,再点击中部的一个按钮可以删除
(2) 左侧的选择了,再点击中部的另一个按钮可以添加到左侧