ASP.NET CRUD

まだまだ改善したい点はあるが、一通りコードを書けたのでここに記す。

DTOはFruitクラスを使う。ここは特にいうことはなし。

DTO

Fruit.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace asp_smp.Models
{
    public class Fruit
    {
        public override string ToString()
        {
            return "Id = " + Id + ", Name = " + Name + ", Number = " + Number + ", Price = " + Price;
        }
        public int Id { get; set; }
        public string Name { get; set; }
        public int Number { get; set; }
        public int Price { get; set; }
    }
}

Controller

Controllerに関して。

• [Route("xxxx")]でルーティングを追加。
• [HttpPost]でPostメソッドを受け付ける。
• [Route("xxx/{id}")]でidの部分にidが入る。
• ApplicationScope?SessionScope?のViewBag。あまり使わない方がいいのか。
• Redirect時に値を渡すことができるDictionary型のTempData
• Redirectを行うことができるRedirectメソッド。
• ShowメソッドではView()の中にFruit型の変数を入れているが、cshtmlで@model Fruitと宣言することによってcshtml側でView()の()の中に入れたものを使用できる。

TopController.cs

using asp_smp.Models;
using asp_smp.Service;
using Microsoft.AspNetCore.Mvc;

// For more information on enabling MVC for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace asp_smp.Controllers
{
    [Route("")]
    public class TopController : Controller
    {
        private FruitSqlService service = new FruitSqlService();

        [Route("")]
        public IActionResult Index()
        {
            ViewBag.list = service.All();
            return View();
        }

        [Route("/add")]
        public IActionResult Add()
        {
            return View();
        }

        [HttpPost]
        [Route("/add")]
        public IActionResult AddPost(int Id, string Name, int Number, int Price)
        {
            var fruit = new Fruit();
            fruit.Id = Id;
            fruit.Name = Name;
            fruit.Number = Number;
            fruit.Price = Price;
            service.INSERT(fruit);
            TempData.Add("result", "追加に成功しました。");
            return Redirect("/");
        }

        [HttpPost]
        [Route("/delete")]
        public IActionResult DeletePost(int Id)
        {
            service.DELETE(Id);
            TempData.Add("result", "削除に成功しました。");
            return Redirect("/");
        }

        [Route("/show/{id}")]
        public IActionResult Show(int Id)
        {
            var fruit = service.FindById(Id);
            return View(fruit);
        }
        [HttpPost]
        [Route("/update")]
        public IActionResult Update(int Id, string Name, int Number, int Price)
        {
            var fruit = new Fruit();
            fruit.Id = Id;
            fruit.Name = Name;
            fruit.Number = Number;
            fruit.Price = Price;
            service.UPDATE(fruit);
            TempData.Add("result", "更新に成功しました。");
            return Redirect("/");
        }
    }
}

Repository

SQLiteを使用。とりあえず全件、1件、追加、更新、削除を実装。

FruitSqlService.cs

using asp_smp.Models;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Threading.Tasks;

namespace asp_smp.Service
{
    public class FruitSqlService
    {
        /// <summary>
        /// テーブル作成
        /// </summary>
        public void Create()
        {
            var ConnectionStr = new SQLiteConnectionStringBuilder() { DataSource = "test.db" };
            using (var Connection = new SQLiteConnection(ConnectionStr.ToString()))
            {
                Connection.Open();

                using (var Command = new SQLiteCommand(Connection))
                {
                    Command.CommandText = "CREATE TABLE IF NOT EXISTS fruit(id INTEGER, name TEXT, number INTEGER, price INTEGER)";
                    Command.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 削除
        /// </summary>
        public void DELETE(int Id)
        {
            var ConnectionStr = new SQLiteConnectionStringBuilder() { DataSource = "test.db" };
            using (var Connection = new SQLiteConnection(ConnectionStr.ToString()))
            {
                Connection.Open();

                using (var Command = new SQLiteCommand(Connection))
                {
                    Command.CommandText = "DELETE FROM fruit WHERE id = :id";
                    Command.Parameters.Add(new SQLiteParameter("id", Id));
                    Command.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// データ追加
        /// </summary>
        /// <param name="fruit"></param>
        public void INSERT(Fruit fruit)
        {
            var ConnectionStr = new SQLiteConnectionStringBuilder() { DataSource = "test.db" };
            using (var Connection = new SQLiteConnection(ConnectionStr.ToString()))
            {
                Connection.Open();

                using (var Command = new SQLiteCommand(Connection))
                {
                    Command.CommandText = "INSERT INTO fruit(id, name, number, price)VALUES(:id, :name, :number, :price)";
                    Command.Parameters.Add(new SQLiteParameter("id", fruit.Id));
                    Command.Parameters.Add(new SQLiteParameter("name", fruit.Name));
                    Command.Parameters.Add(new SQLiteParameter("number", fruit.Number));
                    Command.Parameters.Add(new SQLiteParameter("price", fruit.Price));
                    Command.ExecuteNonQuery();
                }
            }
        }

        public void UPDATE(Fruit fruit)
        {
            var ConnectionStr = new SQLiteConnectionStringBuilder() { DataSource = "test.db" };
            using(var Connection = new SQLiteConnection(ConnectionStr.ToString()))
            {
                Connection.Open();

                using (var Command = new SQLiteCommand(Connection))
                {
                    Command.CommandText = "UPDATE fruit SET name = :name, number = :number, price = :price WHERE id = :id";
                    Command.Parameters.Add(new SQLiteParameter("id", fruit.Id));
                    Command.Parameters.Add(new SQLiteParameter("name", fruit.Name));
                    Command.Parameters.Add(new SQLiteParameter("number", fruit.Number));
                    Command.Parameters.Add(new SQLiteParameter("price", fruit.Price));
                    Command.ExecuteNonQuery();

                }
            }
        }

        public Fruit FindById(int Id)
        {
            var fruit = new Fruit();
            var ConnectionStr = new SQLiteConnectionStringBuilder() { DataSource = "test.db" };
            using(var Connection = new SQLiteConnection(ConnectionStr.ToString()))
            {
                Connection.Open();
                using(var Command = new SQLiteCommand(Connection))
                {
                    Command.CommandText = "SELECT id, name, number, price FROM fruit WHERE id = :id";
                    Command.Parameters.Add(new SQLiteParameter("id", Id));
                    using (var Reader = Command.ExecuteReader())
                    {
                        if (Reader.Read())
                        {
                            fruit.Id = Reader.GetInt32(0);
                            fruit.Name = Reader.GetString(1);
                            fruit.Number = Reader.GetInt32(2);
                            fruit.Price = Reader.GetInt32(3);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }

            return fruit;
        }

        public List<Fruit> All()
        {
            var list = new List<Fruit>();
            var ConnectionStr = new SQLiteConnectionStringBuilder() { DataSource = "test.db" };
            using (var Connection = new SQLiteConnection(ConnectionStr.ToString()))
            {
                Connection.Open();

                using (var Command = new SQLiteCommand(Connection))
                {
                    Command.CommandText = "SELECT id, name, number, price FROM fruit";
                    using (var Reader = Command.ExecuteReader())
                    {
                        while (Reader.Read())
                        {
                            var fruit = new Fruit();
                            fruit.Id = Reader.GetInt32(0);
                            fruit.Name = Reader.GetString(1);
                            fruit.Number = Reader.GetInt32(2);
                            fruit.Price = Reader.GetInt32(3);
                            list.Add(fruit);
                        }
                    }
                }
            }

            return list;
        }
    }
}

View

基本的にコントローラーでreturn View()で返された時、/Folder名/〇〇.cshtmlとなるが、これは/class名/メソッド名+.cshtmlに対応する。
例えば、TopControllerのIndexメソッドであれば/Top/Index.cshtmlがブラウザ側に返される。
bootstrap使用。

Index.cshtml

@*
    For more information on enabling MVC for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860
*@
@{
}
<h1>This is Top Page</h1>
@{ 
    if(TempData.TryGetValue("result", out var val))
    {
        <h3 class="text-success">@val</h3>
    }
}
<a href="/add" class="btn btn-primary">新規追加</a>
<table class="table table-bordered">
    <thead>
        <tr>
            <th>Id</th><th>Name</th><th>Number</th><th>Price</th>
        </tr>
    </thead>
    <tbody>
        @{ 
            foreach(var fruit in ViewBag.list)
            {
                <tr>
                    <td>@fruit.Id</td>
                    <td><a href="/show/@fruit.Id">@fruit.Name</a></td>
                    <td>@fruit.Number</td>
                    <td>@fruit.Price</td>
                    <td>
                        <form action="/delete" method="post">
                            <input type="hidden" value="@fruit.Id" name="Id"/>
                            <input type="submit" value="削除" class="btn btn-danger"/>
                        </form>
                    </td>
                </tr>
            }
        }
    </tbody>
</table>

実行結果

Add.cshtml

@*
    For more information on enabling MVC for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860
*@
@{
}

<h1>This is Add Page</h1>

<div class="container">
    <form action="/add" method="post">
        <table class="table table-bordered">
            <tbody>
                <tr>
                    <th>Id</th>
                    <td><input type="text" name="Id" /></td>
                </tr>
                <tr>
                    <th>Name</th>
                    <td><input type="text" name="Name"></td>
                </tr>
                <tr>
                    <th>Number</th>
                    <td><input type="text" name="Number"></td>
                </tr>
                <tr>
                    <th>Price</th>
                    <td><input type="text" name="Price"></td>
                </tr>
                <tr>
                    <td colspan="2"><input type="submit" value="追加" class="btn btn-info" /></td>
                </tr>
            </tbody>
        </table>
    </form>
</div>

実行結果

Show.cshtml

@*
    For more information on enabling MVC for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860
*@
@{
}

@model Fruit

<h1>This is Update Page</h1>
<form action="/update" method="post">
    <table class="table table-bordered">
        <tbody>
            <tr>
                <th>Id</th>
                <td><input type="text" value="@Model.Id" name="Id"/></td>
            </tr>
            <tr>
                <th>Name</th>
                <td><input type="text" value="@Model.Name" name="Name"/></td>
            </tr>
            <tr>
                <th>Number</th>
                <td><input type="text" value="@Model.Number" name="Number"/></td>
            </tr>
            <tr>
                <th>Price</th>
                <td><input type="text" value="@Model.Price" name="Price"/></td>
            </tr>
            <tr>
                <td colspan="2"><input type="submit" value="更新する" /></td>
            </tr>
        </tbody>
    </table>
</form>

実行結果