SQLiteのデータをWeb APIから操作してみる

# はじめに
前回はSQLiteのデータをコマンドラインから操作してみましたが、今回はSQLiteのデータをWeb APIから操作してみます。
https://qiita.com/rawr/items/85abf5f646e20e3438a1

# 実行環境
下記バージョンで動作確認しています。
- Windows 10
- .NET Core 3.0

<pre>
$ dotnet --version
3.0.100
</pre>

# 学習方針
コマンドプロンプトから実行する事で、Mac、Linuxにおいてもそのままできると思います。

<pre>
$ mkdir myop
$ cd myop
$ dotnet new webapi
</pre>

必要なツールをインストールします。
<pre>
$ dotnet tool install --global dotnet-ef
$ dotnet tool install --global dotnet-aspnet-codegenerator
$ dotnet tool list --global
</pre>

必要なパッケージをインストールします。
<pre>
$ dotnet add package Microsoft.EntityFrameworkCore.Sqlite
$ dotnet add package Microsoft.EntityFrameworkCore.Design
</pre>

動作確認後にテンプレートのWeb APIは削除します。
https://localhost:5001/WeatherForecast/

<pre>
$ dotnet run
^C
$ del WeatherForecast.cs
$ del Controllers\WeatherForecastController.cs
$ mkdir Models
</pre>

テスト用なのでポート番号は5000のみで良いと思います。

```JSON:Properties/launchSetting.json
{
  "iisSettings": {
    "windowsAuthentication": false, 
    "anonymousAuthentication": true, 
    "iisExpress": {
      "applicationUrl": "http://localhost:38239",
      "sslPort": 44320
    }
  },
  "profiles": {
    "IIS Express": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    },
    "MvcBasic": {
      "commandName": "Project",
      "launchBrowser": true,
      "applicationUrl": "http://localhost:5000",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    }
  }
}
```

サンプルにならってモデルを作成します。
http://www.wings.msn.to/index.php/-/A-03/978-4-7980-4179-7/

```C#:Models/Member.cs
using System;
using System.ComponentModel;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace myop.Models
{
  public class myopContext : DbContext
  {
//        public myopContext (DbContextOptions options) : base(options) {}
        public DbSet<Member> Members { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlite("Data Source=members.db");
  }
  public class Member
  {
    public int Id { get; set; }

    [DisplayName("氏名")]
    public string Name { get; set; }

    [DisplayName("メールアドレス")]
    public string Email { get; set; }

    [DisplayName("生年月日")]
    public DateTime Birth { get; set; }

    [DisplayName("既婚")]
    public bool Married { get; set; }

    [DisplayName("自己紹介")]
    public string Memo { get; set; }
  }
}
```

モデルからデータベースを生成します。今回データベースにはSQLiteを使います。
https://docs.microsoft.com/ja-jp/ef/core/get-started/?tabs=netcore-cli

<pre>
$ dotnet ef migrations add InitialCreate
$ dotnet ef database update
</pre>

前々回のスキャフォールドのソースコードを参考に実装します。
http://localhost:5000/Members/

```C#:Controllers/MembersController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using myop.Models;

namespace myop.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class MembersController : ControllerBase
    {
        private readonly myopContext _context;

        public MembersController(myopContext context)
        {
            _context = context;
        }

        // GET: Members/
        [HttpGet]
        public ActionResult<List<Member>> GetAll()
        {
            List<Member> members =  _context.Members.ToList();
            return members;
        }

        // GET: Members/5
        [HttpGet("{id}")]
        public ActionResult<Member> Get(int id)
        {
            List<Member> members =  _context.Members.ToList(); 
            var member = members.Find(i => i.Id == id);
            if (member == null)
            {
                return NotFound();
            }
            return member;
        }

        // POST: Members/
        [HttpPost]
        public ActionResult<Member> Post([Bind("Id,Name,Email,Birth,Married,Memo")] Member member)
        {
            _context.Add(member);
            _context.SaveChangesAsync();
            return member;
        }

        // PUT: Members/5
        [HttpPut("{id}")]
        public ActionResult<Member> Put(int id,[Bind("Id,Name,Email,Birth,Married,Memo")] Member member)
        {
            _context.Update(member);
            _context.SaveChangesAsync();
            List<Member> members =  _context.Members.ToList(); 
            var m = members.Find(i => i.Id == id);
            if (m == null)
            {
                return NotFound();
            }
            return NoContent();
        }

        // DELETE: Members/5
        [HttpDelete("{id}")]
        public ActionResult<Member> Delete(int id)
        {
            List<Member> members =  _context.Members.ToList(); 
            var member = members.Find(i => i.Id == id);
            if (member == null)
            {
                return NotFound();
            }
            _context.Members.Remove(member);
            _context.SaveChangesAsync();
            return NoContent();
        }
    }
}
```

```C#:Models/Member.cs
using System;
using System.ComponentModel;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace myop.Models
{
  public class myopContext : DbContext
  {
        public myopContext (DbContextOptions options) : base(options) {}
        public DbSet<Member> Members { get; set; }
//        protected override void OnConfiguring(DbContextOptionsBuilder options)
//            => options.UseSqlite("Data Source=members.db");
  }
  public class Member
  {
    public int Id { get; set; }

    [DisplayName("氏名")]
    public string Name { get; set; }

    [DisplayName("メールアドレス")]
    public string Email { get; set; }

    [DisplayName("生年月日")]
    public DateTime Birth { get; set; }

    [DisplayName("既婚")]
    public bool Married { get; set; }

    [DisplayName("自己紹介")]
    public string Memo { get; set; }
  }
}
```

```C#:Startup.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using myop.Models;
using Microsoft.EntityFrameworkCore;

namespace myop
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<myopContext>(options => options.UseSqlite("Data Source=members.db"));
            services.AddControllers();
        }
（省略）
```