数据库脚本
USE [master]
GO
/****** Object: Database [RbacDB] Script Date: 2020/6/2 23:51:00 ******/
CREATE DATABASE [RbacDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'RbacDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\RbacDB.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'RbacDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\RbacDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [RbacDB] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [RbacDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [RbacDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [RbacDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [RbacDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [RbacDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [RbacDB] SET ARITHABORT OFF
GO
ALTER DATABASE [RbacDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [RbacDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [RbacDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [RbacDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [RbacDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [RbacDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [RbacDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [RbacDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [RbacDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [RbacDB] SET DISABLE_BROKER
GO
ALTER DATABASE [RbacDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [RbacDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [RbacDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [RbacDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [RbacDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [RbacDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [RbacDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [RbacDB] SET RECOVERY FULL
GO
ALTER DATABASE [RbacDB] SET MULTI_USER
GO
ALTER DATABASE [RbacDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [RbacDB] SET DB_CHAINING OFF
GO
ALTER DATABASE [RbacDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [RbacDB] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [RbacDB] SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N'RbacDB', N'ON'
GO
ALTER DATABASE [RbacDB] SET QUERY_STORE = OFF
GO
USE [RbacDB]
GO
/****** Object: Table [dbo].[Permissions] Script Date: 2020/6/2 23:51:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Permissions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Url] [nvarchar](50) NULL,
[Remark] [nvarchar](50) NULL,
CONSTRAINT [PK_Menus] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[RolePermissions] Script Date: 2020/6/2 23:51:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RolePermissions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RoleID] [int] NULL,
[PermissionID] [int] NULL,
CONSTRAINT [PK_R_Role_Menus] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Roles] Script Date: 2020/6/2 23:51:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Remark] [nvarchar](50) NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[UserInfos] Script Date: 2020/6/2 23:51:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserInfos](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[RoleID] [int] NULL,
[Account] [nvarchar](50) NULL,
[Pwd] [nvarchar](50) NULL,
[Sex] [bit] NULL,
[Hobby] [nvarchar](100) NULL,
[Photo] [nvarchar](100) NULL,
[Remark] [nvarchar](50) NULL,
CONSTRAINT [PK_UserInfos] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Permissions] ON
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (2, N'用户管理', N'/userinfo/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (14, N'角色管理', N'/role/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (17, N'权限管理', N'/permission/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (22, N'教师管理', N'/teacher/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (23, N'班级管理', N'/classInfo/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (24, N'学生管理', N'/student/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (38, N'题库管理', N'/question/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (46, N'班级考试', N'/ClassInfoTestInfo/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (49, N'我的考试', N'/studenttestInfo/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (55, N'部门管理', N'/Department/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (56, N'给角色设置权限', N'/RolePermission/index', NULL)
INSERT [dbo].[Permissions] ([ID], [Name], [Url], [Remark]) VALUES (57, N'测试', N'/test/index', NULL)
SET IDENTITY_INSERT [dbo].[Permissions] OFF
SET IDENTITY_INSERT [dbo].[RolePermissions] ON
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (516, 3, 23)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (517, 3, 24)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (518, 4, 38)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (519, 4, 46)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (535, 1, 2)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (536, 1, 14)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (537, 1, 17)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (538, 1, 22)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (539, 1, 23)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (540, 1, 24)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (541, 1, 38)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (542, 1, 46)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (543, 1, 55)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (544, 1, 56)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (552, 5, 49)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (553, 2, 22)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (554, 2, 23)
INSERT [dbo].[RolePermissions] ([ID], [RoleID], [PermissionID]) VALUES (555, 2, 38)
SET IDENTITY_INSERT [dbo].[RolePermissions] OFF
SET IDENTITY_INSERT [dbo].[Roles] ON
INSERT [dbo].[Roles] ([ID], [Name], [Remark]) VALUES (1, N'系统管理员', NULL)
INSERT [dbo].[Roles] ([ID], [Name], [Remark]) VALUES (2, N'人事主任', NULL)
INSERT [dbo].[Roles] ([ID], [Name], [Remark]) VALUES (3, N'教务主任', NULL)
INSERT [dbo].[Roles] ([ID], [Name], [Remark]) VALUES (4, N'教师', NULL)
INSERT [dbo].[Roles] ([ID], [Name], [Remark]) VALUES (5, N'学生', NULL)
SET IDENTITY_INSERT [dbo].[Roles] OFF
SET IDENTITY_INSERT [dbo].[UserInfos] ON
INSERT [dbo].[UserInfos] ([ID], [Name], [RoleID], [Account], [Pwd], [Sex], [Hobby], [Photo], [Remark]) VALUES (3, N'学院信息中心-彭主任', 1, N'admin', N'admin', 1, N'篮球,羽毛球', N'1.jpg', NULL)
INSERT [dbo].[UserInfos] ([ID], [Name], [RoleID], [Account], [Pwd], [Sex], [Hobby], [Photo], [Remark]) VALUES (10, N'学院人事处-尹主任', 2, N'yin', N'123', 1, N'游泳', N'2.jpg', N'很好')
INSERT [dbo].[UserInfos] ([ID], [Name], [RoleID], [Account], [Pwd], [Sex], [Hobby], [Photo], [Remark]) VALUES (12, N'学院教务处-李主任', 3, N'li', N'123', 0, N'跳舞', N'3.jpg', NULL)
INSERT [dbo].[UserInfos] ([ID], [Name], [RoleID], [Account], [Pwd], [Sex], [Hobby], [Photo], [Remark]) VALUES (14, N'胡瑶老师', 4, N'huyao', N'123', 0, N'跳舞', N'4.jpg', NULL)
INSERT [dbo].[UserInfos] ([ID], [Name], [RoleID], [Account], [Pwd], [Sex], [Hobby], [Photo], [Remark]) VALUES (15, N'韩冬敏老师', 4, N'handm', N'123', 0, N'唱歌', N'1.jpg', N'很靠谱')
INSERT [dbo].[UserInfos] ([ID], [Name], [RoleID], [Account], [Pwd], [Sex], [Hobby], [Photo], [Remark]) VALUES (20, N'全园园同学', 5, N'qyy', N'123', 0, N'健身', N'2.jpg', NULL)
INSERT [dbo].[UserInfos] ([ID], [Name], [RoleID], [Account], [Pwd], [Sex], [Hobby], [Photo], [Remark]) VALUES (21, N'易紫莹同学', 5, N'yizy', N'123', 0, N'演讲', N'3.jpg', NULL)
SET IDENTITY_INSERT [dbo].[UserInfos] OFF
ALTER TABLE [dbo].[RolePermissions] WITH CHECK ADD CONSTRAINT [FK_Role_Permissions_Permissions] FOREIGN KEY([PermissionID])
REFERENCES [dbo].[Permissions] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RolePermissions] CHECK CONSTRAINT [FK_Role_Permissions_Permissions]
GO
ALTER TABLE [dbo].[RolePermissions] WITH CHECK ADD CONSTRAINT [FK_Role_Permissions_Roles] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Roles] ([ID])
GO
ALTER TABLE [dbo].[RolePermissions] CHECK CONSTRAINT [FK_Role_Permissions_Roles]
GO
ALTER TABLE [dbo].[UserInfos] WITH CHECK ADD CONSTRAINT [FK_UserInfos_Roles] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Roles] ([ID])
GO
ALTER TABLE [dbo].[UserInfos] CHECK CONSTRAINT [FK_UserInfos_Roles]
GO
/****** Object: StoredProcedure [dbo].[cp_testClass_select_byTestID] Script Date: 2020/6/2 23:51:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[cp_testClass_select_byTestID]
(
@testID int
)
as
select c.ID as ClassInfoID,c.Name as ClassInfoName,ct.ID,ct.TestID,ct.Status,ct.StartTime,ct.EndTime,ct.Remark from ClassInfos c left join (select * from Test_ClassInfos where TestID= @testID) ct
on c.ID=ct.ClassInfoID
GO
USE [master]
GO
ALTER DATABASE [RbacDB] SET READ_WRITE
GO
数据库以Roles为例
控制器代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Com.Yan.Page.Models;
namespace Com.Yan.Page.Controllers
{
public class RolesController : Controller
{
RbacDBEntities db = new RbacDBEntities();
// GET: Roles
public ActionResult Index(string name="", int PageIndex=1,int PageSize=10)
{
var RowCount = db.Roles.Where(p => p.Name.Contains(name)).Count(); //总条数
var PageCount = Math.Ceiling(RowCount * 1.0 / PageSize); //总页数
//根据页码每次条数、姓名查询需要的数据
var Roles = db.Roles.Where(p=> p.Name.Contains(name))
.OrderBy(p => p.ID)
.Skip(PageSize * (PageIndex - 1))
.Take(PageSize)
.ToList();
ViewBag.pageCount = PageCount; //传值视图总页数
ViewBag.PageIndex = PageIndex; //传值视图页数
ViewBag.name = name;
ViewBag.pageSize = PageSize; //传值视图条数
return View(Roles);
}
}
}
视图代码
@{
ViewBag.Title = "Index";
}
@using Com.Yan.Page.Models
@model List<Role>
<div style="display:flex;justify-content:space-between">
<div class="btn-group">
<button type="button" class="btn btn-default">新增</button>
<button type="button" class="btn btn-default">删除</button>
</div>
<div class="input-group">
<label>名称</label>
<input type="text" id="txtCondName" value="@ViewBag.name" />
<input type="button" id="btnSearch" value="搜索" onclick="page(1);" />
</div>
</div>
<table class="table table-bordered table-hover">
<thead>
<tr>
<th>编号</th>
<th>名称</th>
<th>备注</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>@item.ID</td>
<td>@item.Name</td>
<td>@item.Remark</td>
</tr>
}
</tbody>
</table>
<nav aria-label="Page navigation" style="display:flex;justify-content:space-between">
<ul class="pagination">
<li><a href="#">共10页,第
<input type="text" value="1" id="pageIndex" width="100" />
页,每页显示
<select id="pageSize" onchange="page(1);">
@{
var pageIndexs = new List<int> { 5, 10, 20, 50, 100 };
}
@foreach (var item in pageIndexs)
{
if (@ViewBag.pageSize == @item)
{
<option value="@item" selected="selected">@item</option>
}
else
{
<option value="@item">@item</option>
}
}
</select>
条</a></li>
</ul>
<ul class="pagination">
<li><a href="javascript:page(1);">首页</a></li>
@if (@ViewBag.PageIndex > 1)
{
<li><a href="javascript:page(@ViewBag.PageIndex-1)">上页</a></li>
}
else
{
<li class="disabled"><a href="javascript:page(@ViewBag.PageIndex-1)">上页</a></li>
}
@if (@ViewBag.PageIndex < ViewBag.pageCount)
{
<li><a href="javascript:page(@ViewBag.PageIndex+1)">下页</a></li>
}
else
{
<li class="disabled"><a href="javascript:page(@ViewBag.PageIndex+1)">下页</a></li>
}
<li><a href="javascript:page(@ViewBag.pageCount)">末页</a></li>
<li><input type="button" value="go" onclick="go();" /></li>
</ul>
</nav>
@section scripts{
<script>
function page(pageIndex) {
var pageSize = $("#pageSize").val();
var name = $("#txtCondName").val();
window.location.href = "/roles/index?pageindex=" + pageIndex + "&pageSize=" + pageSize + "&name=" + name
}
function go() {
var pageIndex = $("#pageIndex").val();
page(pageIndex);
}
</script>
}