学生数据库小结

Startup.cs ConfigureServices
appsettings.json

问题

• 考虑如何比较好的命名

Troubleshooting

• 表格名 与 LocalContext 里的类名一致
• 在 Startup.cs 添加 connectionstring 的引用 service.AddContext
  connectionstring 里中文字符 数据表格
• 拷贝表格里的数据到 txt 再次引用
• Code First 的 增 改删
  Add-Migration Initial
  Add-Migration AddModelName
  Add-Migration ModifyModelName
  Update-Database
• Authentication的使用
  在 Startup.cs 里添加 认证cookie信息 以及 app.useAuthentication()
  在 login 里添加 执行
• 实体的增改删加使用
  _context.Student_DB Add 以及 Remove
  _context.SaveChanges()

一些有用的命令

Startup.cs
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
//此处增加数据上下文，不知道在哪可以查官方资料
services.AddDbContext<LocalContext>(options => options.UseSqlServer(Configuration.GetConnectionString(“Db”)));

	// 加认证Cookie信息
	services.AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
		 .AddCookie(options =>
		 {
	  		options.LoginPath = new PathString("/login/");
       		options.AccessDeniedPath = new PathString("/login/denied");
		 })
  		services.AddMvc();

}
//验证中间件
app.UseAuthentication();

services.AddDbContext(options =>
options.UseSqlServer(Configuration.GetConnectionString(“StudentsDb”)));
services.AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
.AddCookie(options =>
{
options.LoginPath = new PathString("/login/");
options.AccessDeniedPath = new PathString("/login/denied");
});
app.UseAuthentication();

appsettings.json
“ConnectionStrings”: {
“StudentsDb”: “Data Source=DESKTOP-61L5TCQ\MSSQLSERVER2017;Initial Catalog=StudentsDb;User ID=sa;Password=123456;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False”
}

LocalContext.cs

public LocalContext(DbContextOptions<LocalContext> options) : base(options) { }
public DbSet<Student> Students_DB { get; set; }
public DbSet<Course> Courses_DB { get; set; }

public class LocalContext : DbContext
{
//注入连接字符串
public LocalContext(DbContextOptions options) : base(options) { }

    ///绑定实体的映射关系，CodeFirst会用到。如果不会配置这里，可以不用从代码生成数据库，自己从数据库管理工具
    ///如：sql managerment studio,在图形化界面手动插入。
    /// 这里可以加载实体的相关配置，以及定义实体键的关联关系和映射。

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new StudentConfiguration());

        modelBuilder.Entity<ClassDepartment>().HasKey(ud => new { ud.ClassId, ud.DepartmentId });
        modelBuilder.Entity<ClassDepartment>().HasOne(u => u.Class).WithMany(m => m.classDepartments);
        modelBuilder.Entity<ClassDepartment>().HasOne(um => um.Department).WithMany(m => m.classDepartments);
        modelBuilder.Entity<StudentClass>().HasKey(ud => new { ud.ClassId, ud.StudentId });
        modelBuilder.Entity<StudentClass>().HasOne(u => u.Class).WithMany(m => m.studentClasses);
        modelBuilder.Entity<StudentClass>().HasOne(um => um.Student).WithMany(m => m.studentClasses);
        modelBuilder.Entity<Student>().HasOne(u => u.department).WithMany(m => m.students).HasForeignKey(d=>d.departmentId);
    }

///绑定实体和数据库表名,为便于你理解，做些区分
public DbSet<Student> Student_DB { get; set; }
public DbSet<Class> Class_DB { get; set; }
public DbSet<Department> Department_DB { get; set; }
public DbSet<GainPoint> GainPoint_DB { get; set; }
public DbSet<StudentClass> StudentClass { get; set; }
public DbSet<ClassDepartment> ClassDepartment { get; set; }
}

Code First
Add-Migration Initial
Add-Migration AddModelName
Add-Migration ModifyModelName

// An highlighted block
var foo = 'bar';

//获取当前登录用户名
string username = Request.HttpContext.User.Claims.FirstOrDefault(s => s.Type== ClaimTypes.Sid).Value;
//获取当前角色         
string role = Request.HttpContext.User.Claims.FirstOrDefault(s => s.Type ==ClaimTypes.Role).Value;
//无include属性 级联删除
ViewBag.Stu= _context.Students_DB.FirstOrDefault(x => x.Username == username);

_context.Students_DB.Add(stu);
_context.SaveChanges();
return RedirectToAction(nameof(MainController.Index),“Main”);

Student stu = _context.Students_DB.Find(id);
_context.Students_DB.Remove(stu);
_context.SaveChanges();
return RedirectToAction(nameof(MainController.Index), “Main”);

<a href="add">添加</a> 和 <a href="/add">添加</a> 的区别

<form action="filter">
    <p>
        学号: <input type="text" name="identify">
        <input type="submit" value="filter" />
    </p>
</form>

public IActionResult Filter(int identify)
{
//Student stu = _context.Students_DB.Find(identify);
var stu = _context.Students_DB.Where(x => x.SNumber == identify);
ViewBag.Stu = stu;
return View();
}

//ToList 和Where的使用
var courses = _context.Courses_DB.Where(x => x.Stu.SNumber == identify).ToList();
ViewBag.Cos = courses;
ViewBag.List_1 = _context.GainPoint_DB.Include(x => x.Class).Where(x => x.Student.Id == Stu.Id).ToList();

// 导航属性的使用 include 以及 where 和 FirstOrDefault
ViewBag.stu = _context.Student_DB.Where(x => x.Id == Id).Include(x => x.department).ToList().FirstOrDefault(x=>x.Id == Id);

//导航属性 通过对象实体来赋值
var temp = _context.Department_DB.FirstOrDefault(x => x.Name == dptname);
int dId = temp.Id;
stu.departmentId = dId;

//############## EF级联删除 按照链接里 只修改 数据库里的表格属性就可以了 ########################
https://www.cnblogs.com/wangcq/p/3617809.html
https://www.cnblogs.com/HQFZ/p/4523761.html
然后这样就可以了
Student stu = _context.Student_DB.Find(Id);
_context.Student_DB.Remove(stu);
_context.SaveChanges();

// 使用多个include的例子
ViewBag.Stu = _context.Student_DB.Where(x => x.Id == id).Include(x => x.department).Include(x=>x.studentClasses).Include(x=>x.gainPoints).FirstOrDefault()

//input hidden属性的用法

 <input type="hidden" name="sid" value="@ViewBag.Stu.Id" />

解决传值id进行查询和authoritation冲突
//获取当前登录的用户名
string name = Request.HttpContext.User.Claims.FirstOrDefault(s => s.Type == ClaimTypes.Sid).Value;
//获取当前登录用户的所有信息
var stu = _context.Student_DB.Where(x => x.UserName == name).Include(x => x.department).FirstOrDefault();

ASP.NET MVC中获取URL地址参数的两种写法
https://www.cnblogs.com/M-LittleBird/p/5855967.html

用///<summary>然后enter标记代码段

22.08
//initController 初始化数据库
依次执行
add-migration init -context localcontext
update-database init -context localcontext

//时间格式的转换
ViewBag.time = DateTime.Now.ToString(“dd/MM/yyyy”);
return RedirectToAction(“welcome”);
return RedirectToAction(nameof(MainController.Welcome), “Main”);
return Content(“main/index 欢迎”);

//form提交数据的问题

 <form action="CheckMyPlan">
 	<input type="hidden" name="pid" value="@plan.id"  />
 	<input type="submit" value="查看" />
 </form>

//增加新条目的时候不要忘了使用add方法
_context.Add(plan);
_context.SaveChanges();

//设置input的style

<input type="text" name="name" style="height:50px; width:100px" >

//获取当前的角色
string role = Request.HttpContext.User.Claims.FirstOrDefault(s => s.Type == ClaimTypes.Role).Value;

//这里又少了一个include
var plan = _context.Companyplan_DB.Where(x => x.id == pid).Include(x => x.department).Include(x=>x.employee).FirstOrDefault();

//使用 textarea 传值 注意不要添加属性 text

<input type="text" name="text" style="height:300px;width:500px;" value="@ViewBag.plan.text" />*@
<textarea name="text" style="height:300px;width:500px;" value="">@ViewBag.plan.text</textarea>

// 下面这种情况只能在employee已经作为实体存在的情况下使用
//通常这个employee是从数据库里选择来的
plan.employee.name

23.08
//写在类里的属性和构造函数
protected readonly LocalContext _context;
public InitController(LocalContext context)
{
_context = context;
}

//读错误报告
Microsoft.AspNetCore.Mvc.Infrastructure.RedirectToActionResultExecutor.ExecuteAsync(ActionContext context, RedirectToActionResult result)
return RedirectToAction(nameof(EmployeeController.MyPlan), “Main”);
//return RedirectToAction(nameof(EmployeeController.MyPlan), “Employee”);

//添加搜索 Contains
movies = movies.Where(s => s.Title.Contains(searchString));

//使用Contains和where &&
var plan = _context.Companyplan_DB.Where(s => s.Title.Contains(searchString)).Where(t=> Calculate(t.STime)> Calculate(sTime) && Calculate(t.ETime) > Calculate(eTime));

//可以用辅助函数进行判断
var plan = _context.Companyplan_DB.Where(t => Calculate(t.STime) < Calculate(sTime)).ToList();
public int Calculate(string timeString){ }

//Form表单中使用select option select里的name属性获得select的值
https://blog.csdn.net/yejin191258966/article/details/7532667

 <select name="testselect">            
 		@foreach (var item in ViewBag.LD)
         {
                <option value="@item">@item</option>
         }           
</select>

24.08

Fluent API

一个举例

Code First -verbose

ToDictionary

26.08

//可以对string的日期直接OrderBy排序
ViewBag.plans = _context.Companyplan_DB.Where(x => x.Emp.Username == name)
.Include(x => x.Dptment)
.OrderBy(x=>x.STime)
.ToList();

27.08
//泛型的使用
//C#定义泛型方法错误-类型“T”必须是引用类型才能用作泛型类型或方法“System.Data.Linq.Table”中的参数“TEntity”
//在()后面加上where T : class即可，表示为泛型T为类型才能进行转换
public class Helper where T : class
{
public void WhereForDatabase (DbSet database, string searchString, string sTime, string eTime)
{
}
}

//添加helper

//关于数据类型的问题 注意

DbSet<Companyplan> 和 IQueryable<Companyplan>

28.08
//ajax传值cshtml mvc


//添加角色认证
[Authorize(Roles = “员工”)]

30.08
//删除登录时的cookie信息，退出登录
http://www.tnblog.net/aojiancc2/article/details/211

0309
//input可以再form表单里，添加id就可以

MVC中.js文件如何使用ViewBag、ViewData中的值