Visual StudioでSQL Serverを使う

はじめに

Visual StudioでSQL Serverを使用するプログラムについて解説する。ASP.NET Bindで使用したモデルで、Entity Frameworkを介してデータベース接続を行う。

データベースコンテキストの作成

プロジェクトに「Data」フォルダを作成し、その中に「TestContexts.cs」ファイルを作成する。内容は以下のようにする。

using Microsoft.EntityFrameworkCore;

using Test.Models;

namespace Test.Data

{

  public class TestContext : DbContext

  {

    public MvcMovieContext(DbContextOptions<TestContext> options)

        : base(options)

    {

    }

    public DbSet<AInt> Movie { get; set; }

  }

}

パッケージのインストール

「using Microsoft.EntityFrameworkCore;」のところは参照がないためにエラーになっている場合は参照を追加する。個別に参照を追加する方法もあるが、関連する参照をまとめて参照を追加する方法がある。それにはパッケージマネージャーのコマンドを利用する。[ツール]メニューの[NuGet パッケージ マネージャー]>[パッケージ マネージャー コンソール]をクリックする。

すると、[パッケージ マネージャー コンソール]ペインが表示され、カーソルがそこへ移動する。[パッケージ マネージャー コンソール]ペインで以下のコマンドを実行する。

Install-Package Microsoft.EntityFrameworkCore.SqlServer

これでエラーはなくなる。プロジェクトの[依存関係]の[パッケージ]に[Microsoft.EntityFrameworkCore.SqlServer]が追加されれば成功である。バージョンが2020/03/16時点で3.1.2が最新である。現時点で最新版でなければ以下のコマンドで最新のパッケージをインストールできる。

Update-Package Microsoft.EntityFrameworkCore.SqlServer

ついでに、実行時にエラーが出ないよう、以下のコマンドでツールをインストールする。２行目はアップデートである。

Install-Package Microsoft.EntityFrameworkCore.Tools

Update-Package Microsoft.EntityFrameworkCore.Tools

プロジェクトの[依存関係]の[パッケージ]に[Microsoft.EntityFrameworkCore.Tools]が追加されれば成功である。こちらもバージョンが2020/03/16時点で3.1.2が最新である。

データベース構成の追加

Startup.csのコードを変更する。using に以下の２つを追加する。コメントも含めている。

using Test.Data;  // for TestContext

using Microsoft.EntityFrameworkCore;  // for UseSqlServer

ConfigureServicesメソッドは以下のように変更する。

    public void ConfigureServices(IServiceCollection services)

    {

      services.AddControllersWithViews();

      services.AddDbContext<TestContext>(options =>

            options.UseSqlServer(Configuration.GetConnectionString("TestContext")));

    }

「UseSqlServer」のところは参照がないためにエラーになっている場合は[Microsoft.EntityFrameworkCore.SqlServer]パッケージがあるか確認する。

ここで一度実行してみよう。接続部分を追加しただけなので、正常に動いたときの動作は見た目変わらない。SQL Server はまだ動作していなくても問題ない。

パッケージが足りないときは実行時に「System.IO.FileNotFoundException: 'Could not load file or assembly 'Microsoft.Bcl.AsyncInterfaces, Version=1.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'. 指定されたファイルが見つかりません。'」などの例外が発生する。パッケージは「Microsoft.EntityFrameworkCore.Tools」に含まれるので、インストールしよう。

次に、コネクション失敗による「Internal Server Error」が出ることがある。

「options.UseSqlServer(Configuration.GetConnectionString("TestContext")));」と赤で表示されるが、間違っているのはStartup.csの「services.AddDbContext<TestContext>(options =>options.UseSqlServer(Configuration.GetConnectionString("TestContext")));」でGetConnectionString()の引数と、[appsettings.json]ファイルの「ConnectionStrings」キーの値が「{"TestContext":"・・・"]」のところと一致しているか確認しよう。「・・・」のところは現状なんらかの文字があればエラーにはならない。文字列長が0であればエラーになる。

コントローラーの変更１

HomeController.csに移動する。usingには以下の１つを追加する。コメントを含む。

using Test.Data; // for context

次に、フィールドに以下の１行を追加する。

    private readonly TestContext _context;

コンストラクタは以下のように変更する。

    public HomeController(ILogger<HomeController> logger, TestContext context)

    {

      _logger = logger;

      _context = context;

    }

PostのIndexメソッドは以下のように変更する。

    [HttpPost]

    public IActionResult Index(string btn)

    {

      if (btn == "+")

      {

        AInt.s_v++;

      }

      else if (btn == "-")

      {

        AInt.s_v--;

      }

      _context.AInt.Update(new AInt());

      _context.SaveChanges();

      _logger.LogInformation("Info: [Post]Index() v=" + AInt.s_v + ", btn=" + btn);

      return View(new AInt());

    }

モデルの変更

AInt.csを以下のように変更する。各プロパティはデータベースと連動する。

using System;

using System.ComponentModel.DataAnnotations;

namespace Test.Models

{

  public class AInt

  {

    public static int s_v = 0;

    public AInt()

    {

      Id = 1;

      v = s_v;

    }

    public int Id { get; set; }

    [Range(int.MinValue, int.MaxValue)]

    public int v { get; set; }

  }

}

Idはテーブルの主キーにあたるフィールドである。コンストラクタではIdを1にしているが、0にしてしまうとNULLと判定されDbUpdateException例外が発生する。また、データベースに連動するプロパティはgetアクセサとsetアクセサの両方を実装しなければならない。setアクセサがないとInvalidOperationException例外が発生する。

データベースとテーブルの準備

データベースの準備を行う。アーキテクチャとしてはSQL ServerにN個のデータベースがあり、データベースにテーブルがN個あるというようになる。

SQL Serverはすでにあるものとする。無ければSQL Serverのページなどを参考に用意してほしい。[SQL Server オブジェクト エクスプローラー]ペインの[SQL Serve]>(サーバー)>[データベース]を右クリックし、[新しいデータベースの追加]をクリックする。

データベース名は「Test」とする。データベースを展開し、[テーブル]を右クリックし、[新しいテーブルの追加]をクリックする。

テーブルデザインで各設定を行う。「v」項目を追加し、データ型を「int」にして、[Null を許容]のチェックを外す。また、T-SQLのテーブル名は「[dbo].[AInt]」にする。そして[更新]をクリックする。

[データベース更新のプレビュー]ウィンドウが表示されたら、[データベースの更新]ボタンをクリックする。

「bdo.Aint」と表示されるAIntテーブルが作成されるので、右クリックし、[データの表示]をクリックする。

データにIdを1、vを0とするデータを１件追加する。コミットは下の行のNULLのところをクリックだけする。左上の[最新の情報に更新]をクリックすると表示を更新できるので、更新してコミットできているか確認しよう。これでデータベースの準備はできた。

JSONファイルの編集

プロジェクトの[appsettings.json]を以下のように変更する。

{

  "Logging": {

    "LogLevel": {

      "Default": "Information",

      "Microsoft": "Warning",

      "Microsoft.Hosting.Lifetime": "Information"

    }

  },

  "AllowedHosts": "*",

  "ConnectionStrings": {

    "TestContext": "Server=(コンピューター名)\\SQLSERVER2019;Database=Test;Trusted_Connection=True;MultipleActiveResultSets=true"

  }

}

「(コンピューター名)」のところは、コンピューター名に置き換える。

実行

ようやく実行できる。[IIS Express]をクリックするなど実行してみよう。[+]と[-]ボタンを押すたびにデータベースに反映される。

テーブルデータを表示して、データを更新するとブラウザの値になっていれば成功である。

いろいろなエラー

実行してみると、いろいろなエラーが出てくることがある。

「SqlException: A network-related or instance-specific error～」というエラーは[appsettings.json]の"TestContext"の"Server="ところで、コンピュータ名またはサーバー名を間違っている。

「SqlException: このログインで要求されたデータベース "※※※" を開けません。」というエラーはデータベース名を間違っているときに発生する。[appsettings.json]の"TestContext"の"Database="のところと、[SQL Server オブジェクト エクスプローラー]のデータベース名が一致しているか確認しよう。

「InvalidOperationException」はモデルAInt.csのAIntクラスのプロパティにsetアクセサが実装されていないときに出るエラーである。例えばプロパティを「public int Id { get { return 1; } }」としている場合などであるが、「public int Id { get { return 1; } set { } }」などと修正することができる。

「SqlException:列名'v'が無効です。」エラーはテーブルの列名とモデルとなるクラスAIntのプロパティの名前が一致しているか確認しよう。

「DbUpdateConcurrencyException」はテーブルのデータを更新する際、更新元がないときに発生するエラーである。

このページの解説ではAIntのIdプロパティを主キーとしてデータは1で固定している扱いだが、データベースのAIntテーブルでIdが1のレコード（行）が無ければ、HomeController.csの「_context.AInt.Update(new AInt());」は更新元がないために更新することができない。対処法としては、データベースのテーブルに更新元となるレコードを追加しよう。

「SqlException: テーブル 'Test.dbo.AInt' の列 'Id' に値 NULL を挿入できません。」エラーはレコードキーに0を指定すると、Entity Frameworkは0をNULLとして扱われ、主キーにNULLは使えないためエラーが表示されるのである。

FileNotFoundExceptionエラーは、実行時にアセンブリがないときに発生する。必要なパッケージが揃えるか、パッケージのバージョンを新しくしよう。

コントローラーの変更２

Textページでもデータベースへ反映されるようコードを変更する。また、ページを表示時にはデータベースからデータを取得するように変更する。コードを示す。比較のため、[HttpPost]属性のIndexメソッドを含めているが変わっていない。

    [HttpGet]

    public IActionResult Index()

    {

      AInt aint =  _context.AInt.Find(AInt.id);

      _logger.LogInformation("Info: [Get]Index() aint.v=" + aint.v);

      return View(aint);

    }

    [HttpPost]

    public IActionResult Index(string btn)

    {

      if (btn == "+")

      {

        AInt.s_v++;

      }

      else if (btn == "-")

      {

        AInt.s_v--;

      }

      _context.AInt.Update(new AInt());

      _context.SaveChanges();

      _logger.LogInformation(

        "Info: [Post]Index() v=" + AInt.s_v + ", btn=" + btn);

      return View(new AInt());

    }

    [HttpGet]

    public IActionResult Text()

    {

      AInt aint = _context.AInt.Find(AInt.id);

      _logger.LogInformation("Info: [Get]Text() aint.v=" + aint.v);

      return View(aint);

    }

    [HttpPost]

    public IActionResult Text(string v)

    {

      if (!string.IsNullOrEmpty(v))

      {

        AInt.s_v = int.Parse(v);

      }

      _context.AInt.Update(new AInt());

      _context.SaveChanges();

      _logger.LogInformation("Info: [Post]Text() AInt.v=" + AInt.s_v);

      return View(new AInt());

    }

「AInt aint = _context.AInt.Find(AInt.id);」のところでデータベースからデータを取得している。FindはSQL文のSELECT句のような役割を果たす。Findメソッド引数の「AInt.id」はこの場合常に1が返ることを期待している。Viewの引数にaintを指定することで、ページにデータを渡している。

[HttpPost]属性のTestメソッドは、[HttpPost]属性のIndexメソッドと同様の変更である。