asp.net MVC使用EF框架分页
- 数据库脚本
- 数据库以Roles为例
- 控制器代码
- 视图代码
数据库脚本
USE [master]GO/****** Object: Database [RbacDB] Script Date: 2020/6/2 23:51:00 ******/CREATE DATABASE [RbacDB]CONTAINMENT = NONEON 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 )GOALTER DATABASE [RbacDB] SET COMPATIBILITY_LEVEL = 140GOIF (1 = FULLTEXTSERVICEPROPERTY(\'IsFullTextInstalled\'))beginEXEC [RbacDB].[dbo].[sp_fulltext_database] @action = \'enable\'endGOALTER DATABASE [RbacDB] SET ANSI_NULL_DEFAULT OFFGOALTER DATABASE [RbacDB] SET ANSI_NULLS OFFGOALTER DATABASE [RbacDB] SET ANSI_PADDING OFFGOALTER DATABASE [RbacDB] SET ANSI_WARNINGS OFFGOALTER DATABASE [RbacDB] SET ARITHABORT OFFGOALTER DATABASE [RbacDB] SET AUTO_CLOSE OFFGOALTER DATABASE [RbacDB] SET AUTO_SHRINK OFFGOALTER DATABASE [RbacDB] SET AUTO_UPDATE_STATISTICS ONGOALTER DATABASE [RbacDB] SET CURSOR_CLOSE_ON_COMMIT OFFGOALTER DATABASE [RbacDB] SET CURSOR_DEFAULT GLOBALGOALTER DATABASE [RbacDB] SET CONCAT_NULL_YIELDS_NULL OFFGOALTER DATABASE [RbacDB] SET NUMERIC_ROUNDABORT OFFGOALTER DATABASE [RbacDB] SET QUOTED_IDENTIFIER OFFGOALTER DATABASE [RbacDB] SET RECURSIVE_TRIGGERS OFFGOALTER DATABASE [RbacDB] SET DISABLE_BROKERGOALTER DATABASE [RbacDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFFGOALTER DATABASE [RbacDB] SET DATE_CORRELATION_OPTIMIZATION OFFGOALTER DATABASE [RbacDB] SET TRUSTWORTHY OFFGOALTER DATABASE [RbacDB] SET ALLOW_SNAPSHOT_ISOLATION OFFGOALTER DATABASE [RbacDB] SET PARAMETERIZATION SIMPLEGOALTER DATABASE [RbacDB] SET READ_COMMITTED_SNAPSHOT OFFGOALTER DATABASE [RbacDB] SET HONOR_BROKER_PRIORITY OFFGOALTER DATABASE [RbacDB] SET RECOVERY FULLGOALTER DATABASE [RbacDB] SET MULTI_USERGOALTER DATABASE [RbacDB] SET PAGE_VERIFY CHECKSUMGOALTER DATABASE [RbacDB] SET DB_CHAINING OFFGOALTER DATABASE [RbacDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )GOALTER DATABASE [RbacDB] SET TARGET_RECOVERY_TIME = 60 SECONDSGOALTER DATABASE [RbacDB] SET DELAYED_DURABILITY = DISABLEDGOEXEC sys.sp_db_vardecimal_storage_format N\'RbacDB\', N\'ON\'GOALTER DATABASE [RbacDB] SET QUERY_STORE = OFFGOUSE [RbacDB]GO/****** Object: Table [dbo].[Permissions] Script Date: 2020/6/2 23:51:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOSET IDENTITY_INSERT [dbo].[Permissions] ONINSERT [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] OFFSET IDENTITY_INSERT [dbo].[RolePermissions] ONINSERT [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] OFFSET IDENTITY_INSERT [dbo].[Roles] ONINSERT [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] OFFSET IDENTITY_INSERT [dbo].[UserInfos] ONINSERT [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] OFFALTER TABLE [dbo].[RolePermissions] WITH CHECK ADD CONSTRAINT [FK_Role_Permissions_Permissions] FOREIGN KEY([PermissionID])REFERENCES [dbo].[Permissions] ([ID])ON DELETE CASCADEGOALTER TABLE [dbo].[RolePermissions] CHECK CONSTRAINT [FK_Role_Permissions_Permissions]GOALTER TABLE [dbo].[RolePermissions] WITH CHECK ADD CONSTRAINT [FK_Role_Permissions_Roles] FOREIGN KEY([RoleID])REFERENCES [dbo].[Roles] ([ID])GOALTER TABLE [dbo].[RolePermissions] CHECK CONSTRAINT [FK_Role_Permissions_Roles]GOALTER TABLE [dbo].[UserInfos] WITH CHECK ADD CONSTRAINT [FK_UserInfos_Roles] FOREIGN KEY([RoleID])REFERENCES [dbo].[Roles] ([ID])GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE proc [dbo].[cp_testClass_select_byTestID](@testID int)asselect 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) cton c.ID=ct.ClassInfoIDGOUSE [master]GOALTER DATABASE [RbacDB] SET READ_WRITEGO
数据库以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: Rolespublic 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>}
爱站程序员基地
