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

李郑

发布于 2019-12-10 17:21:13

6760

发布于 2019-12-10 17:21:13

文章被收录于专栏：漫漫全栈路

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

数据库相关

为领域模型（Domain Model）添加约束

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

-- 查询数据库
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';

查询结果如下：

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

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

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

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

dotnet ef migrations add AddConstraints
dotnet ef database update

编辑migrations添加种子数据

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

命令行添加一个空的migration:

dotnet ef migrations add SeedData

编辑创建的 migration 文件，参考如下：

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:

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

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.cs和 TvShowViewModel.cs:

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; }
    }
}

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, 一共有两个包:

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

别忘了还要执行dotnet restore.

安装成功后:

在Startup.cs里面注册AutoMapper,

services.AddAutoMapper();

AutoMapper还需要知道Domain Model和ViewModel的对应关系和方向.

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

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获取数据如下：