【ASP.NET Core MVC】Dapperを使ってSQLを実行する

ASP.NET Core MVCで、Micro-ORMとして有名なDapperを使ってDBを操作する方法を紹介します。

Dapperは、一般的にEntity Framework Coreよりも高速で、ADO.NETよりもシンプルにコードを書けるのがメリットです。

下記の記事で作成したTodoアプリを再利用しています。ADO.NETを使用した場合と比較してみてください。

接続先はSQL Server Local DBで、フレームワークには.NET 6を使用しています。

環境
準備
1. Dapperのインストールと接続文字列の設定
コードの説明
デバッグ実行

環境

Visual Studio Community 2022
.NET 6
Windows10

準備

まずLocalDBにデータベースを作成し、テーブルと適当なデータを追加します。
例としてTodoアイテムを管理するシンプルなテーブルを用意しました。

CREATE TABLE [dbo].[TodoItems] (
  [Id] INT IDENTITY NOT NULL PRIMARY KEY,
  [Name] NVARCHAR(50) NOT NULL,
  [IsComplete] BIT NOT NULL
);

INSERT INTO TodoItems VALUES(N'部屋の掃除', 'false')
INSERT INTO TodoItems VALUES(N'買い物', 'false')
INSERT INTO TodoItems VALUES(N'プログラミング学習', 'false')

上のSQLを実行すると、以下のようなテーブルが作成できます。

Dapperのインストールと接続文字列の設定

Dapperを使用するためにはパッケージのインストールが必要です。

ソリューション名を右クリック→「ソリューションのNuGetパッケージの管理」からパッケージマネージャーを開き、Dapperをインストールします。

LocalDBの接続文字列はappsettings.jsonに記載しています。

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "TodoItemsConnection": "Server=(localdb)\\mssqllocaldb;Database=Todo;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

コードの説明

Controller

プロジェクト作成時に用意されるHomeControllerを次のように書き換えます。

using Dapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using SqlApplication.Models;

namespace SqlApplication.Controllers
{
    public class HomeController : Controller
    {
        private readonly string _connectionString;

        public HomeController(IConfiguration configuration)
        {
            // appsettings.jsonファイルから接続文字列を取得
            _connectionString = configuration.GetConnectionString("TodoItemsConnection");
        }

        /// <summary>
        /// ToDoアイテムを全件検索する
        /// </summary>
        public async Task<IActionResult> Index()
        {
            using var connection = new SqlConnection(_connectionString);

            // 実行するSQL
            var sql = "SELECT * FROM TodoItems";

            // SELECT文を実行して取得したデータを型にマッピング
            var Items = await connection.QueryAsync<TodoItemViewModel>(sql);

            return View(new TodoItemViewModel { Items = Items.ToList() });
        }

        /// <summary>
        /// ToDoアイテムを登録する
        /// </summary>
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create([Bind("Name")] TodoItemViewModel todoItem)
        {
            if (ModelState.IsValid)
            {
                using var connection = new SqlConnection(_connectionString);

                var sql = "INSERT INTO TodoItems Values(@Name, 'false');";

                // パラメータに設定する値
                var param = new TodoItemViewModel { Name = todoItem.Name };

                // INSERT文の実行
                await connection.ExecuteAsync(sql, param);
            }
            return RedirectToAction(nameof(Index));
        }

        /// <summary>
        /// 「完了」がクリックされた場合にToDoアイテムを削除する
        /// </summary>
        [HttpPost]
        public async Task<IActionResult> Delete([Bind("Id")] TodoItemViewModel todoItem)
        {
            if (ModelState.IsValid)
            {
                using var connection = new SqlConnection(_connectionString);

                var sql = "DELETE FROM TodoItems WHERE Id = @Id";
                var param = new TodoItemViewModel { Id = todoItem.Id };

                // Delete文の実行
                await connection.ExecuteAsync(sql, param);
            }
            return RedirectToAction(nameof(Index));
        }
    }
}

Todoアイテムの読み取り(Indexメソッド)・登録(Createメソッド)・削除(Deleteメソッド)のみが可能なシンプルな作りとなっています。

SELECT文の場合はQuery（非同期の場合はQueryAsync）メソッドを、INSERT・UPDATE・DELETE文を実行する場合はExecute（非同期の場合はExecuteAsync）メソッドを使用します。

読み取りの場合は取得したデータを型にマッピングさせる処理が1行で書けるので、ADO.NETを使用する場合に比べてシンプルに書くことができます。

Model

DBから取得したデータを格納し、Viewに渡すために使用するビューモデルです。

using System.ComponentModel.DataAnnotations;

namespace SqlApplication.Models
{
    public class TodoItemViewModel
    {
        public int Id { get; set; }

        [Display(Name = "タスク")]
        public string? Name { get; set; } = string.Empty;

        [Display(Name = "完了チェック")]
        public bool IsComplete { get; set; }

        public List<TodoItemViewModel>? Items { get; set; }
    }
}

View

ViewはTodoタスクの登録を行うフォーム、タスクの一覧を表示する表、タスクを削除するボタンで構成されます。

@model SqlApplication.Models.TodoItemViewModel;

@{
    ViewData["Title"] = "ToDoリスト";
}

<h1>ToDoリスト</h1>

<p>
    <form asp-action="Create" method="post">
        <div class="form-group">
            <input asp-for="Name" />
            <input type="submit" value="タスクを登録" class="btn btn-primary" />
        </div>
    </form>
</p>

<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Id)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.IsComplete)
            </th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model.Items)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.Id)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Name)
                </td>
                <td>
                    <form asp-action="Delete" asp-route-id="@item.Id">
                        <input type="hidden" asp-for="@item.Id" />
                        <input type="submit" value="完了" class="btn btn-warning" />
                    </form>
                </td>
            </tr>
        }
    </tbody>
</table>