ASP.NET MVCのDBファーストでのMySQLとの連携

概要

ASP.NET MVC + MySQLを使用してDBファーストでWebアプリケーションを作成します。
今回はDBファーストでのMySQLとの連携とWebページの表示までを行います。

環境

• MySQL 8.0.20
• Visual Studio 2019
• .Net Framework 4.8
• MySql.Data 8.0.20
• MySql.Data.EntityFramework 8.0.20

DB

今回使用するDBは以下のようなテーブル構成です。
各データは下記リンク先から取得しました。
気象データ:http://www.data.jma.go.jp/obd/stats/etrn/index.php
都道府県:http://www007.upp.so-net.ne.jp/s124/jis.html
市区町村:https://www.soumu.go.jp/denshijiti/code.html
※市区町村は都道府県名をprefectureのidになるように加工しています。

テーブル構成

テーブル	内容
city	市区町村マスタ
prefecture	都道府県マスタ
weather	気象データ

SQL

スキーマ作成

CREATE DATABASE `weather` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

city.sql

CREATE TABLE `city` (
  `id` int NOT NULL,
  `prefecture_id` int NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `prefecture_fkey_idx_idx` (`prefecture_id`),
  CONSTRAINT `prefecture_fkey_idx` FOREIGN KEY (`prefecture_id`) REFERENCES `prefecture` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

prefecture.sql

CREATE TABLE `prefecture` (
  `id` int NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

weather.sql

CREATE TABLE `weather` (
  `observational_day` datetime NOT NULL,
  `city_id` int NOT NULL,
  `temperature_ave` decimal(5,2) NOT NULL,
  `temperature_min` decimal(5,2) NOT NULL,
  `temperature_max` decimal(5,2) NOT NULL,
  `precipitation` decimal(5,2) NOT NULL,
  `wind_speed` decimal(5,2) NOT NULL,
  PRIMARY KEY (`observational_day`,`city_id`),
  KEY `city_fkey_idx_idx` (`city_id`),
  CONSTRAINT `city_fkey_idx` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Web アプリの作成

Visual Studio から [新しいプロジェクトの作成] を選択します。
[ASP.NET Web アプリケーション] を選択し、 [次へ] をクリックします。

プロジェクト名等を設定し、[作成] をクリックします。

MVCを選択し、[作成]をクリックします。

MySQLとの連携

コンポーネントのインストール

[NuGet パッケージの管理]から以下をインストールします。
- MySql.Data
- MySql.Data.EntityFramework
※MySql.Data.Entityは不要です。

モデルの作成

[新しい項目の追加]から[ADO.NET Entry Data Model]を選択します。
今回は名前に「MySqlModel」を設定します。

[データベースからEF Designer]を選択し、[次へ]をクリックします。

[Entry Data Modelウィザード]の[新しい接続]をクリックします。
[接続のプロパティ]のデータソースを[MySQL Database (MySQL Data Provider)]に変更し、DB接続情報を設定後、[OK]をクリックします。

パスワードを接続文字列に含めるかどうかを選択し、[次へ] をクリックします。

モデルに含めるオブジェクトを選択し、[完了]をクリックします。
今回はテーブルを全て選択します。

完了後、[Web.config]に接続文字列が追加されます。

完了後、DBの情報を元にしたモデルが作成されます。

コントローラー作成

ソリューションエクスプローラーのControllersディレクトリ上で右クリック→[追加]→[コントローラー]を選択します。

[Entity Frameworkを使用した、ビューがあるMVC5コントローラー]を選択し、[追加]をクリックします。

[モデルクラス]で使用するモデル(weather)を選択する。
[データコンテキストクラス]は先ほど作成したweatherEntitiesを選択する。
[コントローラー名]のデフォルト値をweathersControllerからWeatherControllerに変更する。
※変えなくてもOK
入力が完了したら、[追加]ボタンをクリックします。

完了後、コントローラーとビューが自動で作成されます。

「プロジェクトをビルドしなおしてください。」というエラーが出た場合は、ビルド後に再実行してください。

自動生成されたコントローラーのIndex()

WeatherController.Index()

public ActionResult Index()
{
    var weather = db.weather.Include(w => w.city);
    return View(weather.ToList());
}

自動作成されたIndexビュー

Weather/Index.cshtml

@model IEnumerable<MySqlTestWebApp.weather>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.temperature_ave)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.temperature_min)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.temperature_max)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.precipitation)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.wind_speed)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.city.name)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_ave)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_min)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_max)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.precipitation)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.wind_speed)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.city.name)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
        </td>
    </tr>
}

</table>

ページ

Index.cshtmlを表示した状態でCtrl + F5（またはデバッグを実行）でページを表示します。

weatherテーブル

DBのweatherテーブルのデータを取得できていることが確認できます。
weatherテーブルの[city_id]はcityテーブルの[id]を外部キーに設定しているため、自動で名前が表示されます。

ビュー(Index.cshtml)の修正

• 主キーである[observational_day]が表示されていないため追加します。
• 市区町村が2列目になるようにします。
• Edit、Details、Deleteのリンク部分のキーを設定します。
  ※主キーが[id]であれば自動で設定されますが、今回は違うので修正します。

修正後

Weather/Index.cshtml

@model IEnumerable<MySqlTestWebApp.weather>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.observational_day)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.city.name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.temperature_ave)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.temperature_min)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.temperature_max)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.precipitation)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.wind_speed)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.observational_day)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.city.name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_ave)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_min)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.temperature_max)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.precipitation)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.wind_speed)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { observationalDay = item.observational_day, cityID = item.city_id }) |
            @Html.ActionLink("Details", "Details", new { observationalDay = item.observational_day, cityID = item.city_id }) |
            @Html.ActionLink("Delete", "Delete", new { observationalDay = item.observational_day, cityID = item.city_id }) |
        </td>
    </tr>
}

</table>

コントローラー(WeatherController)の修正

• 主キーである[observational_day]、[city_id]を使用するように各メソッドを修正します。
  ※引数の変数名とビューのActionLinkに設定した変数名を揃える必要があります。

修正前

WeatherController.cs(一部抜粋)

public ActionResult Details(DateTime id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(id);
    if (weather == null)
    {
        return HttpNotFound();
    }
    return View(weather);
}

public ActionResult Edit(DateTime id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(id);
    if (weather == null)
    {
        return HttpNotFound();
    }
    ViewBag.city_id = new SelectList(db.city, "id", "name", weather.city_id);
    return View(weather);
}

public ActionResult Delete(DateTime id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(id);
    if (weather == null)
    {
        return HttpNotFound();
    }
    return View(weather);
}

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(DateTime id)
{
    weather weather = db.weather.Find(id);
    db.weather.Remove(weather);
    db.SaveChanges();
    return RedirectToAction("Index");
}

修正後

WeatherController.cs(一部抜粋)

public ActionResult Details(DateTime observationalDay, int? cityID)
{
    if (observationalDay == null || cityID == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(observationalDay, cityID);
    if (weather == null)
    {
        return HttpNotFound();
    }
    return View(weather);
}

public ActionResult Edit(DateTime observationalDay, int? cityID)
{
    if (observationalDay == null || cityID == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(observationalDay, cityID);
    if (weather == null)
    {
        return HttpNotFound();
    }
    ViewBag.city_id = new SelectList(db.city, "id", "name", weather.city_id);
    return View(weather);
}

public ActionResult Delete(DateTime observationalDay, int? cityID)
{
    if (observationalDay == null || cityID == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    weather weather = db.weather.Find(observationalDay, cityID);
    if (weather == null)
    {
        return HttpNotFound();
    }
    return View(weather);
}

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(DateTime observationalDay, int? cityID)
{
    weather weather = db.weather.Find(observationalDay, cityID);
    db.weather.Remove(weather);
    db.SaveChanges();
    return RedirectToAction("Index");
}

モデルの修正

日本語の列名を表示させるため、[DisplayName]を設定します。
それから、[observational_day]には[DisplayFormat]を使用して日付のフォーマットを設定します。

修正前

weather.cs

namespace MySqlTestWebApp
{
    using System;
    using System.Collections.Generic;

    public partial class weather
    {
        public System.DateTime observational_day { get; set; }
        public int city_id { get; set; }
        public decimal temperature_ave { get; set; }
        public decimal temperature_min { get; set; }
        public decimal temperature_max { get; set; }
        public decimal precipitation { get; set; }
        public decimal wind_speed { get; set; }

        public virtual city city { get; set; }
    }
}

city.cs

namespace MySqlTestWebApp
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;

    public partial class city
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public city()
        {
            this.weather = new HashSet<weather>();
        }

        public int id { get; set; }
        public int prefecture_id { get; set; }
        public string name { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<weather> weather { get; set; }
        public virtual prefecture prefecture { get; set; }
    }
}

修正後

weather.cs

namespace MySqlTestWebApp
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;

    public partial class weather
    {
        [DisplayName("観測日")]
        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:yyyy/MM/dd}")]
        public System.DateTime observational_day { get; set; }
        public int city_id { get; set; }
        [DisplayName("平均気温 ℃")]
        public decimal temperature_ave { get; set; }
        [DisplayName("最低気温 ℃")]
        public decimal temperature_min { get; set; }
        [DisplayName("最高気温 ℃")]
        public decimal temperature_max { get; set; }
        [DisplayName("降水量 mm")]
        public decimal precipitation { get; set; }
        [DisplayName("最大瞬間風速 m/s")]
        public decimal wind_speed { get; set; }

        public virtual city city { get; set; }
    }
}

city.cs

namespace MySqlTestWebApp
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;

    public partial class city
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public city()
        {
            this.weather = new HashSet<weather>();
        }

        public int id { get; set; }
        public int prefecture_id { get; set; }
        [DisplayName("市区町村")]
        public string name { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<weather> weather { get; set; }
        public virtual prefecture prefecture { get; set; }
    }
}

修正後のページ

見た目も多少それらしくなったのではないかと思います。

まとめ

今回はDBファーストでのMySQLとの連携とWebページの表示までを行いました。
普段はWeb系の開発はやらないので難しい印象がありましたが、DBの情報を使用してコントローラやビューを自動で作成してくれるので、かなり助かりました。

参考

https://docs.microsoft.com/ja-jp/aspnet/mvc/overview/getting-started/database-first-development/
https://qiita.com/mrpero/items/607c31895d77815a77cb