【.NET | Restful API】Integrate with database

本文介绍.NET开发中如何集成数据库，本文将使用microsoft entity framework，利用dotnet-ef来生成对应的migration 脚本，再通过端口来实现CRUD。我们通过6个步骤来实现：

• 创建Models
• 创建DbContext
• 指定DbContext的依赖注入
• 使用 dotnet-ef CLI工具来创建migration的脚本并执行创建
• 创建Repository and IRepository
• 在controller中使用repository进行测试

1.创建对应的模型

namespace webapi.Models.blog;
public class Blog
{
    public int Id { get; set; }
    public string? Title { get; set; }
}

• 字段必须是public的
• 这里的Id根据名称会默认地被指定为主键

2.创建DbContext

using Microsoft.EntityFrameworkCore;

namespace webapi.Data.Blog;

public class BlogContext : DbContext
{
    public BlogContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<Models.blog.Blog> Blogs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>().ToTable("Blog");
    }
}

需要定义属性Blogs，后续通过这个BbSet的的对象来操作数据库
用OnModelCreating方法来指定映射到哪张表

3. 指定DbContext的依赖注入

指定数据库连接
在文件appsettings.json中加入连接信息(和Logging同级)：

  "ConnectionStrings": {
    "DefaultConnection": "Server=your_host_name,1433;Database=blog;User Id={your_user_name};Password={your_pass_word}"
  }

我在写demo的时候本地的容器起数据库遇到了蛮多问题最后失败了，所以用的是云数据库:azure SQL server(logical server),等我搞清楚了再来写一篇如何用容器起数据库和troubleshooting的文章

指定依赖注入
在program.cs中，在var builder = WebApplication.CreateBuilder(args) 和var app = builder.Build()之间添加代码：

builder.Services.AddDbContext<BlogContext>(options =>
{
   options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
});

4.使用 dotnet-ef CLI工具来创建migration

创建migration，这个命令会生成两个文件20221004080651_InitiateCreateBlog.cs和BlogContextModelSnapshot.cs

 dotnet ef migrations add InitiateCreateBlog --context BlogContext

执行migratin

  dotnet ef migrations update InitiateCreateBlog --context BlogContext

然后连接到数据库，发现表已经建好了

5.创建Repository and IRepository

添加 IBlogRepository

public interface IBlogRepository
{
    public IEnumerable<Models.Blog.Blog> GetAll();
    
    public Models.Blog.Blog GetById(int id);

    public Models.Blog.Blog Create(Models.Blog.Blog blog);

    public Models.Blog.Blog Update(Models.Blog.Blog updatedBlog);

    public void Delete(int id);
}

添加 BlogRepository

using webapi.Data.Blog;

namespace webapi.Repositories.Blog;

public class BlogRepository : IBlogRepository
{
    private readonly BlogContext _blogContext;

    public BlogRepository(BlogContext blogContext)
    {
        _blogContext = blogContext;
    }

    public IEnumerable<Models.Blog.Blog> GetAll()
    {
        return _blogContext.Blogs.ToList();
    }

    public Models.Blog.Blog GetById(int id)
    {
        return _blogContext.Blogs.FirstOrDefault(blog => blog.Id == id);
    }

    public Models.Blog.Blog Create(Models.Blog.Blog blog)
    {
        var createBlog = _blogContext.Blogs.Add(blog).Entity.CastTo<Models.Blog.Blog>();
        _blogContext.SaveChanges();
        return createBlog;
    }


    public Models.Blog.Blog Update(Models.Blog.Blog updatedBlog)
    {
        var existedBlog = _blogContext.Blogs.FirstOrDefault(b => b.Id == updatedBlog.Id);
        if (existedBlog == null)
        {
            return null;
        }

        existedBlog.Title = updatedBlog.Title;
        _blogContext.Blogs.Update(existedBlog);
        _blogContext.SaveChanges();
        return existedBlog;
    }

    public void Delete(int id)
    {
        var existedBlog = _blogContext.Blogs.FirstOrDefault(b => b.Id == id);
        if (existedBlog == null)
        {
            return;
        }

        _blogContext.Blogs.DeleteByKey(id);
        _blogContext.SaveChanges();
    }
}

在program.cs中添加映射

builder.Services.AddScoped<IBlogRepository, BlogRepository>();

否则调接口的时候会发生错误

System.InvalidOperationException: Unable to resolve service for type 'webapi.Repositories.Blog.BlogRepository' while attempting to activate 'webapi.Controllers.BlogController'.
   at Microsoft.Extensions.DependencyInjection.ActivatorUtilities.GetService(IServiceProvider sp, Type type, Type requiredBy, Boolean isDefaultParameterRequired)
   at lambda_method3(Closure , IServiceProvider , Object[] )

6.在controller中使用repository进行测试

using Microsoft.AspNetCore.Mvc;
using webapi.Models.Blog;
using webapi.Repositories.Blog;

namespace webapi.Controllers;

[ApiController]
[Route("[controller]")]
public class BlogController : Controller
{
    private readonly IBlogRepository _blogRepository;

    public BlogController(IBlogRepository blogRepository)
    {
        _blogRepository = blogRepository;
    }

    [HttpGet]
    public IEnumerable<Blog> GetAll()
    {
        return _blogRepository.GetAll();
    }

    [HttpGet("{id}")]
    public IActionResult GetById(int id)
    {
        return Ok(_blogRepository.GetById(id));
    }

    [HttpPost]
    public IActionResult CreateBlog([FromBody] Blog blog)
    {
        return Ok(_blogRepository.Create(blog));
    }

    [HttpPut("{id}")]
    public IActionResult UpdateBlog(int id, [FromBody] Blog blog)
    {
        if (blog.Id != id)
        {
            return BadRequest("Given id and blog do not match");
        }
        if (_blogRepository.GetById(id) == null)
        {
            return BadRequest("Given Blog id not exist");
        }

        return Ok(_blogRepository.Update(blog));
    }
    
    [HttpDelete("{id}")]
    public IActionResult DeleteBlog(int id)
    {
        if (_blogRepository.GetById(id) == null)
        {
            return BadRequest("Given Blog id not exist");
        }

        _blogRepository.Delete(id);
        return NoContent();
    }
}

以上就是本文的内容，目前暂无时间润色，等年底再说啦😄
如果有写得不清楚的地方，欢迎在评论区讨论和交流