楠槡

ANCWEB - 基于 ASP.NET CORE 2.0 的 WEB 开发 二

2018-06-13

上一篇成功在MSSQL中创建了数据库,本篇继续。

数据库相关

为领域模型(Domain Model)添加约束

在项目中新建一个test.sql 文件,查询数据库信息。

1
2
3
4
5
6
-- 查询数据库
Select Name FROM tvdb..SysObjects Where XType='U' orDER BY Name
-- 查询表信息
select * from information_schema.columns where table_name = 'TvNetworks';
select * from information_schema.columns where table_name = 'TvShows';

查询结果如下:

sqlr

这个结果和所需要的数据库结构有一定的差异,那么,可以通过为Domain Model的相应属性添加一些约束,来进行变更。

修改之前创建的两个 Model 类,为其添加约束。

yueshu

注意: 这里使用了DataAnnotation来建立约束,需要引用相关的类库。EF 约束等内容后续写篇文章专门说一下。

然后添加migrations 并执行数据库更新

1
2
dotnet ef migrations add AddConstraints
dotnet ef database update

编辑migrations添加种子数据

前面的操作,我们都是对Model 类进行修改,然后通过 EF 的migrations 的指令来对进行数据库进行操作。虽然生成了 migrations 文件,但都没有进行过了解,下面通过修改 migrations 文件来对数据库进行操作。

命令行添加一个空的migration:

1
dotnet ef migrations add SeedData

编辑创建的 migration 文件,参考如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
using Microsoft.EntityFrameworkCore.Migrations;
namespace ANCWEB.Migrations
{
public partial class SeedData : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("INSERT INTO TvNetworks (Name) VALUES ('Netflix')");
migrationBuilder.Sql("INSERT INTO TvNetworks (Name) VALUES ('HBO')");
migrationBuilder.Sql("INSERT INTO TvNetworks (Name) VALUES ('CBS')");
migrationBuilder.Sql("INSERT INTO TvNetworks (Name) VALUES ('NBC')");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('House of Cards', (SELECT Id FROM TvNetworks WHERE Name='Netflix'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('Altered Carbon', (SELECT Id FROM TvNetworks WHERE Name='Netflix'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('Marvel''s Daredevil', (SELECT Id FROM TvNetworks WHERE Name='Netflix'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('Game of Thrones', (SELECT Id FROM TvNetworks WHERE Name='HBO'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('Silicon Valley', (SELECT Id FROM TvNetworks WHERE Name='HBO'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('Veep', (SELECT Id FROM TvNetworks WHERE Name='HBO'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('NCIS', (SELECT Id FROM TvNetworks WHERE Name='CBS'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('The Big Bang Theory', (SELECT Id FROM TvNetworks WHERE Name='CBS'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('Criminal Minds', (SELECT Id FROM TvNetworks WHERE Name='CBS'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('Friends', (SELECT Id FROM TvNetworks WHERE Name='NBC'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('Chicago Fire', (SELECT Id FROM TvNetworks WHERE Name='NBC'))");
migrationBuilder.Sql("INSERT INTO TvShows (Name, TvNetworkId) VALUES ('Will & Grace', (SELECT Id FROM TvNetworks WHERE Name='NBC'))");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("DELETE FROM TvNetworks WHERE Name IN ('Netflix', 'HBO', 'CBS', 'NBC')");
}
}
}

编辑完成后保存,并更新到数据库。

注意:执行 dotnet ef database update 出现异常,发现之前在编辑字段时,错误的将TvNetworkId 写成了TvNetwordId ,此时需要移除当前 migration内容,修改后,提交一次修改 migration,然后更新数据库成功后,再进行操作。

建立WEB API

在Controllers文件夹下建立TvController.cs.

需要注入TvContext, 这时候聚焦到context变量上使用ctrl+.这个快捷键 生成一个field:

field

创建好后,添加一个Get 方式的API:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;
using System.Collections.Generic;
using ANCWEB.Models;
using ANCWEB.Database;
namespace ANCWEB.Controllers
{
public class TvContoller : Controller
{
private readonly TvContext context;
public TvContoller(TvContext context)
{
this.context = context;
}
[HttpGet("/api/tvnetworks")]
public async Task<IEnumerable<TvNetwork>> GetTvNetworks()
{
return await context.TvNetworks.Include(x => x.TvShows).ToListAsync();
}
}
}

运行,尝试请求会发现并没有返回结果,而且终端出现异常,原因在于一个Tvnetwork有个导航属性是多个TvShow, 而一个TvShow还有一个反向导航属性是TvNetwork, 所以dbcontext查询出来在进行json转化的时候, 会无限循环下去, 就引起了self referencing loop.

建立ViewModel

建立ViewModels/TvNetworkViewModel.csTvShowViewModel.cs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
using System.Collections.Generic;
using System.Collections.ObjectModel;
namespace Tv.ViewModels
{
public class TvNetworkViewModel
{
public TvNetworkViewModel()
{
TvShows = new Collection<TvShowViewModel>();
}
public int Id { get; set; }
public string Name { get; set; }
public ICollection<TvShowViewModel> TvShows { get; set; }
}
}
1
2
3
4
5
6
7
8
9
namespace Tv.ViewModels
{
public class TvShowViewModel
{
public int Id { get; set; }
public string Name { get; set; }
public int TvNetworkId { get; set; }
}
}

注意TvShowViewModel里面并没有反向的TvNetWork属性, 这也保证了不会发生上面的自身循环引用异常.

接下来需要做的就是在Controller里面把Domain Model的属性传递给ViewModel, 没人会去手写这个映射的过程, 所以应该使用AutoMapper等类似的库。

AutoMapper

首先添加AutoMapper, 一共有两个包:

1
2
dotnet add package AutoMapper
dotnet add package AutoMapper.Extensions.Microsoft.DependencyInjection

别忘了还要执行dotnet restore.

安装成功后:

  • Startup.cs里面注册AutoMapper,
1
services.AddAutoMapper();
  • AutoMapper还需要知道Domain Model和ViewModel的对应关系和方向.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
using AutoMapper;
using Tv.Models;
using Tv.ViewModels;
namespace Tv.Mapping
{
public class MappingProfile : Profile
{
public MappingProfile()
{
CreateMap<TvNetwork, TvNetworkViewModel>();
CreateMap<TvShow, TvShowViewModel>();
}
}
}
  • 然后在Controller里面需要注入AutoMapper
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
using System.Collections.Generic;
using System.Threading.Tasks;
using AutoMapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Tv.Database;
using Tv.Models;
using Tv.ViewModels;
namespace Tv.Controllers
{
public class TvController : Controller
{
private readonly TvContext context;
private readonly IMapper mapper;
public TvController(TvContext context, IMapper mapper)
{
this.context = context;
this.mapper = mapper;
}
[HttpGet("/api/tvnetworks")]
public async Task<IEnumerable<TvNetworkViewModel>> GetTvNetworks()
{
var models = await context.TvNetworks.Include(x => x.TvShows).ToListAsync();
var vms = mapper.Map<List<TvNetwork>, List<TvNetworkViewModel>>(models);
return vms;
}
}
}

修改后调试,使用Postman获取数据如下:

re

Tags: dotNET
使用支付宝打赏
使用微信打赏

若你觉得我的文章对您有帮助,点击上方按钮请我喝杯咖啡☕

若文章中存在问题,或您有任何意见和疑问,均可与我联系

扫描二维码,分享此文章 📱