【ASP.NET Core MVC】Microsoft.Data.SqlClientを使用してSQLを直接実行する

ASP.NET Core
2022.02.05

今回はASP.NET Core MVCで、Entity Framework Coreを使わずにADO.NETの仕組みを利用してデータを操作する方法を紹介します。

業務上の都合などでO/Rマッパーを使いたくないケースもあるかと思うので、簡単なToDoアプリを題材に実装してみました。

DBにはSQL Server Local DBを使用します。

スポンサーリンク

環境

  • Visual Studio Community 2022
  • .NET 6
  • Windows10

Microsoft.Data.SqlClientとは

DBへの接続やSQLの実行に必要なクラスが集められた名前空間で、.NET Framework 及び .NET Coreに対応しています。

従来は「System.Data.SqlClient」名前空間のクラスを使用することが一般的でしたが、現在は「Microsoft.Data.SqlClient」名前空間が最新となっています。

※「System.Data.SqlClient」は今後もメンテナンスはされるが新機能の追加などは行われないとのことなので、今後作るアプリに関しては積極的に「Microsoft.Data.SqlClient」を使うべきだと思います。

Microsoft.Data.SqlClient 名前空間の概要 - ADO.NET Provider for SQL Server
Microsoft.Data.SqlClient 名前空間と .NET アプリケーション向け SQL に接続する方法としてそれが推奨される理由について説明します。
learn.microsoft.com

準備

まずはVisual StudioでASP.NET Core Webアプリ(Model-View-Controller)を作成します。
フレームワークは.NET 6を選択しています。

次に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')

作成したデータベース内で上のクエリを実行すると、以下のようなテーブルが作成できます。

※もしLocalDBでデータベースとテーブルを作成する方法がわからない場合は以下の記事をご参照ください。

【C#】SQL ServerのLocal DBに接続してSELECT文を実行する
今回は初心者の方向けに、C#でSQL Server Express Local DBに接続する方法についてまとめました。操作はVisual Studioだけで完結するので、SQL Serverのインストールは不要です。※L...
hirahira.blog
2023.02.26

パッケージのインストールと接続文字列の設定

.NET Coreで「Microsoft.Data.SqlClient」を使うにはNuGetパッケージマネージャーでのインストールが必要です。

プロジェクト名を右クリック→「NuGetパッケージの管理」からパッケージマネージャーを開き、以下の手順でインストールします。

また、appsettings.jsonにLocalDBの接続文字列を追加しておきましょう。

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

コードの説明

Controller

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

※本来ロジックやデータ取得処理などはControllerではなくDAOやRepositoryといった別クラスに書くべきですが、今回は簡略化のためにすべてControllerに処理を書いています。

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

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);
            using var command = connection.CreateCommand();

            connection.Open();
            command.CommandText = "SELECT * FROM TodoItems";

            // テーブルのデータを全て読み取ってモデルのリストに格納
            var models = new List<TodoItemViewModel>();
            using var reader = command.ExecuteReader();
            while (await reader.ReadAsync())
            {
                models.Add(new TodoItemViewModel
                {
                    Id = (int)reader[nameof(TodoItemViewModel.Id)],
                    Name = reader[nameof(TodoItemViewModel.Name)].ToString(),
                    IsComplete = (bool)reader[nameof(TodoItemViewModel.IsComplete)],
                });
            }
            return View(new TodoItemViewModel { Items = models });
        }

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

                connection.Open();
                command.CommandText = "INSERT INTO TodoItems Values(@Name, 'false');";

                // フォームに入力されたタスク名をパラメータに設定
                command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = todoItem.Name;

                // INSERT文の実行
                await command.ExecuteNonQueryAsync();
            }
            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);
                using var command = connection.CreateCommand();

                connection.Open();
                command.CommandText = "DELETE FROM TodoItems WHERE Id = @Id";
                command.Parameters.Add("@Id", SqlDbType.Int).Value = todoItem.Id;

                await command.ExecuteNonQueryAsync();
            }
            return RedirectToAction(nameof(Index));
        }
    }
}

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

※各メソッドのusing文はC#8.0で導入された「単純なusingステートメント」を使っていますが、可読性を考えると従来の書き方の方がいいかもしれません。

接続文字列は依存関係の挿入(DI)の仕組みを使ってappsettings.jsonファイルから取得しています。DIの詳細については以下を参照してください。

ASP.NET Core でのコントローラーへの依存関係の挿入
ASP.NET Core の MVC コントローラーが、ASP.NET Core でそれらのコンストラクターと依存関係の挿入を使用して、明示的にそれらの依存関係を要求する方法について説明します。
learn.microsoft.com

Model

DBから取得したデータを格納し、Viewに渡すために使用するビューモデルです。
取得したデータをリストに格納して渡すためにItemsというプロパティを保持しています。

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>

デバッグ実行

それでは実行してみましょう。起動すると以下のような画面が表示されるはずです。

試しにフォームにタスク名を入力して「タスクを登録」ボタンをクリックしてみると、ちゃんとデータが追加されることがわかります。

さらに「完了」ボタンをクリックすると、その行のタスクがテーブルから削除されます。

以上、ASP.NET Core MVCでEntity Frameworkを使わずにデータを操作する方法を紹介しました。

今回はかなりシンプルなアプリでしたが、興味のある人はぜひ色々な機能を追加してみてください。

【参考書籍】

.NET 5プログラミング入門
著:増田 智明
¥3,080 （2023/03/16 14:08時点 | Amazon調べ）
Amazon
楽天市場
Yahooショッピング

コメント

タイトルとURLをコピーしました