Microsoft.Data.SqlClient(ADO.NETの仕組み)を使って、SQLを直接実行するMVCアプリの作成方法を解説します。
DBはSQL Server (LocalDB) を使用しています。
Entity Framework を使ったデータ操作方法については以下の記事などをご参照ください。
環境
- Visual Studio 2022
- .NET 8
Microsoft.Data.SqlClientとは
「Microsoft.Data.SqlClient」とは、DBへの接続やSQLの実行に必要なクラスが集まっている名前空間です。
以前は「System.Data.SqlClient」名前空間のクラスを使用することが一般的でしたが、現在は「Microsoft.Data.SqlClient」名前空間が最新となっています。
「System.Data.SqlClient」は今後もメンテナンスはされますが、新機能の追加などは行われないとのことなので、今後アプリを作る場合はなるべく「Microsoft.Data.SqlClient」を使うのがよいでしょう。
準備
まずはVisual StudioでASP.NET Core Webアプリ(Model-View-Controller)を作成します。
次に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でデータベースとテーブルを作成する方法がわからない場合は、以下の記事をご参照ください。
パッケージのインストールと接続文字列の設定
.NET Coreで「Microsoft.Data.SqlClient」を使うにはNuGetパッケージのインストールが必要です。
プロジェクト名を右クリック→「NuGetパッケージの管理」からパッケージマネージャーを開き、「Microsoft.Data.SqlClient」をインストールしてください。
また、appsettings.jsonにLocalDBの接続文字列を追加しておきましょう。
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"TodoConnection": "Server=(localdb)\\mssqllocaldb;Database=Todo;Trusted_Connection=True;MultipleActiveResultSets=true"
}
}
サンプルコードの説明
Controller
プロジェクト作成時に用意されるHomeControllerを次のように書き換えます。
※本来ロジックやデータ取得処理などはControllerに書くべきではありませんが、今回は簡略化のためにすべてControllerに処理を書いています。
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using MvcSqlCrudSample.Models;
using System.Data;
namespace MvcSqlCrudSample.Controllers
{
public class HomeController : Controller
{
private readonly string _connectionString;
public HomeController(IConfiguration configuration)
{
// appsettings.jsonファイルから接続文字列を取得
_connectionString = configuration.GetConnectionString("TodoConnection")
?? throw new ArgumentException("接続文字列が取得できませんでした");
}
/// <summary>
/// ToDoアイテムを全件検索する
/// </summary>
public async Task<IActionResult> Index()
{
using var connection = new SqlConnection(_connectionString);
using var command = connection.CreateCommand();
await connection.OpenAsync();
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アイテムを登録してIndexにリダイレクトする
/// </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();
await connection.OpenAsync();
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アイテムを削除し、Indexにリダイレクトする
/// </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();
await connection.OpenAsync();
command.CommandText = "DELETE FROM TodoItems WHERE Id = @Id";
command.Parameters.Add("@Id", SqlDbType.Int).Value = todoItem.Id;
// DELETE文の実行
await command.ExecuteNonQueryAsync();
}
return RedirectToAction(nameof(Index));
}
}
}
Todoアイテムの読み取り(Indexメソッド)・登録(Createメソッド)・削除(Deleteメソッド)が可能なシンプルな作りとなっています。
接続文字列は依存関係の挿入(DI)の仕組みを使ってappsettings.jsonファイルから取得しています。DIの詳細については以下を参照してください。
Model
次に、DBから取得したデータを格納し、Viewに渡すためのViewModelを用意します。
Modelフォルダ内に「TodoItemViewModel.cs」を作成してください。
using System.ComponentModel.DataAnnotations;
namespace MvcSqlCrudSample.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; }
}
}
取得したデータをリストに格納して渡すためにItemsというプロパティを保持しています。
View
次はViewです。Views > Home内の「Index.cshtml」を以下に書き換えてください。
@model MvcSqlCrudSample.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>
Todoタスクの登録を行うフォーム、タスクの一覧を表示する表、タスクを削除するボタンで構成されます。
デバッグ実行
それでは実行してみましょう。起動すると以下のような画面が表示されるはずです。
テキストボックスにタスク名を入力して「タスクを登録」ボタンをクリックすると、リストにデータが追加されることがわかります。
さらに「完了」ボタンをクリックすると、その行のタスクがリストから削除されます。
以上、ASP.NET Core MVCでEntity Frameworkを使わずに直接SQLを実行してデータを操作する方法を紹介しました。
今回はかなりシンプルなアプリでしたが、興味のある人はぜひ色々な機能を追加してみてください。