WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

PostgreSQL で Movie チュートリアル (CORE)

by WebSurfer 18. July 2022 18:48

データベースに PostgreSQL を利用して、Micorsoft の ASP.NET Core MVC のチュートリアル「ASP.NET Core MVC の概要」および「パート 4、ASP.NET Core MVC アプリにモデルを追加する」に従ってアプリを作る方法を書きます。

Movie アプリ

先の記事「MySQL で Movie チュートリアル (CORE)」、「SQLite で Movie チュートリアル (CORE)」でデータベースにそれぞれ MySQL, SQLite を使った例を書きましたが、この記事はその PosrgreSQL 版です。

PostgreSQL 本体は先の記事「PostgreSQL をインストールしました」でインストールした v14.4 を使います。

(1) プロジェクトの作成

チュートリアル「ASP.NET Core MVC の概要」の通り、Visual Studio 2022 のテンプレートを利用して対象のフレームワークは .NET 6.0認証「なし」の ASP.NET Core MVC アプリを作成します。

対象のフレームワークを .NET Core 3.1 とか .NET 5.0 にした場合は NuGet パッケージのバージョンの選び方に注意してください。違うとスキャフォールディングでエラーになるかもしれません。

また、認証を「個別のアカウント」にすると SQL Server を利用した Entity Framework 関係のパッケージがインストールされ話がややこしくなりますので、まずは認証は「なし」でやってみることをお勧めします。

(2) モデルの定義とスキャフォールディングの実行

チュートリアル「パート 4、ASP.NET Core MVC アプリにモデルを追加する」に従って、モデル(エンティティ)クラスの定義をプロジェクトに既存の Models フォルダに追加します。

using System.ComponentModel.DataAnnotations;

namespace PosrgreSqlMovie.Models
{
    public class Movie
    {
        public int Id { get; set; }
        public string? Title { get; set; }

        [DataType(DataType.Date)]
        public DateTime ReleaseDate { get; set; }
        public string? Genre { get; set; }
        public decimal Price { get; set; }
    }
}

次に、Microsoft.EntityFrameworkCore.Design を NuGet からインストールします。この記事では、この記事を書いた時点での最新版 6.0.7 を使いました。対象のフレームワークが .NET Core 3.1 とか .NET 5.0 の場合は、Microsoft.EntityFrameworkCore.Design のバージョンもそれに合わせるのが良さそうです。バージョンが違うとスキャフォールディングでエラーになるかもしれません。

チュートリアルの通り Visual Studio でスキャフォールディングを実行すると以下の操作が自動的に行われます。(コードジェネレータ関係のエラーが出る場合がありますが、その際は下の追加 NuGet パッケージがインストールされていることを確認してから再度スキャフォールディングを行うと成功すると思います)

  1. 他に必要な NuGet パッケージ(Microsoft.EntityFrameworkCore.SqlServer, Microsoft.EntityFrameworkCore.Tools, Microsoft.VisualStudio.Web.CodeGeneration.Design)の追加
  2. Data フォルダにコンテキストクラスの作成
  3. Program.cs ファイル (.NET 5.0 以前の場合は Startup.cs ファイル) でコンテキストクラスの登録
  4. appsettings.json ファイルへの接続文字列の追加
  5. CRUD 操作に必要な Controller / View 一式の生成

SQL Server の場合は上記でプロジェクトは完成ですが、PostgreSQL を利用する場合は上の 1, 3, 4 に以下の変更を行う必要があります。

(3) NuGet パッケージの追加

NuGet パッケージ Npgsql.EntityFrameworkCore.PostgreSQL をインストールします。その結果が以下の画像です。

NuGet パッケージ

スキャフォールディングで自動的に追加される Microsoft.EntiryFrameworkCore.Tools のバージョンがランタイムのバージョンより古い場合は Migration 操作の際警告が出るのと思いますので更新してください。

注意: Microsoft.EntityFrameworkCore.SqlServer はモデルクラスの定義によってはスキャフォールディングで必要になるケースがあるようです。具体的にどのようなケースで必要になるかは調べ切れてませんが、データアノテーション属性が関係しているような感じです。なので、この先モデルの定義を変更して Migration ⇒ スキャフォールディング操作を繰り返すなら、Microsoft.EntityFrameworkCore.SqlServer は残しておいた方が良さそうです。

(4) Program.cs ファイルの修正

スキャフォールディング操作で Program.cs ファイル (.NET 5.0 以前の場合は Startup.cs ファイル) でサービスに自動的にコンテキストクラスが登録されますが、それは SQL Server 用なので、以下のように PostgreSQL 用に変更します。

UseSqlServer を UseNpgsql に変更

コンテキストクラスの登録はコントローラーへの DI に必要です。登録してあれば、フレームワークがクライアントからの要求を受けてコントローラーを初期化する際、コンテキストクラスを初期化してコンストラクタ経由で渡してくれます。

(5) 接続文字列を PostgreSQL 用に変更

スキャフォールディング操作で appsettings.json ファイルに接続文字列が自動生成されますが、それは SQL Server (LocalDB) 用なので PostgreSQL 用に変更します。

接続文字列の変更

データベース名は任意です。上の画像のように Database=Movie というようにデータベース名を指定すると、Entity Framework Code First の機能を使って Movie という名前のデータベースを新たに生成し、そこに必要なテーブルを生成してくれます。

(6) Add-Migration の実行

ソリューションをリビルドしてから、Visual Studio のパッケージマネージャーコンソールで Add-Migration InitialCreate コマンドを実行します。結果、以下の画像の通り InitialCreate クラスが Migrations フォルダに自動生成されます。

Add-Migration の実行結果

Movie クラスの ReleaseDate プロパティ、Price プロパティの型はそれぞれ DateTime、decimal ですが、PostgreSQL にはそれらに該当する型がないらしく、それぞれ timestamp with time zone, numeric となっています。(後述しますが、それにより問題が出ます)

Add-Migration InitialCreate の InitialCreate という名前は任意に指定できます。指定した名前で xxxxx_InitialCreate.cs (xxxxx は作成日時) という名前のファイルが作成され、それに InitialCreate という名前のクラスが定義されます。

(7) Update-Database の実行

Visual Studio のパッケージマネージャーコンソールで Update-Database コマンドを実行します。これにより以下の画像の通り PostgreSQL データベースが生成されます。

Update-Database の実行結果

接続文字列で Database=Movie とした通り Movie という名前でデータベースが生成され、InitialCreate クラスの name: "Movie" で指定された名のテーブルが生成されています。

Movie クラスの ReleaseDate プロパティ、Price プロパティに該当する Movie テーブルのフィールドの型は、上のコードの InitialCreate クラスの指定通りそれぞれ timestamp with time zone, numeric となっています。

(8) プロジェクトの実行

これで完成と思って、プロジェクトを実行し Create 画面を表示してレコードを追加しようとしたら・・・

InvalidCastException: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior."

・・・という例外がスローされて失敗します。(汗)

エラーメッセージでググって調べてヒットした記事「Date and Time Handling」によると "Npgsql 6.0 introduced some important changes to how timestamps are mapped" とのことで変更があったようです。Npgsql 6.0 より前の動作に戻すには以下のコードを追加すると書いてあります。

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

ステップ (2) で Data フォルダに自動生成されたコンテキストクラスに上のコードを追加することで、臨時処置的な解決策かもしれませんが、例外はスローされなくなります。

レコードの Create 結果

上の修正を加えた後でプロジェクトを実行して、Create 画面でレコードを 3 件追加し Index 画面でその一覧を表示したのがこの記事の一番上の画像です。

上の例外に対する恒久処置ですが、Movie クラスの ReleaseDateプロパティの DateTime 型を DateOnly 型に変えて、対応する PostgreSQL の型を date 型にするという記事を目にしました。それをやってみた結果を別の記事「ASP.NET Core で DateOnly 型を使用」に書きましたので、興味があれば見てください。

Tags: , , , ,

CORE

RemoteAttribute による検証 (MVC5)

by WebSurfer 11. February 2022 13:23

ASP.NET MVC 5.2 で導入された RemoteAttribute クラスを使ってみました。

RemoteAttribute による検証

サーバー側でなければユーザー入力の検証ができないケースがあります。例えばデータベースに存在する名前との重複を確認するなど。RemoteAttribute 属性を使えばそのようなケースでのクライアント側での検証が可能になるそうです。

どのような仕組みかを簡単に書くと、JavaScript で Ajax を使って検証用のアクションメソッドを呼び出し、返ってきた検証結果をクライアント側での検証に反映するというものです。詳しくは Microsoft のドキュメント「ASP.NET Core MVC および Razor Pages でのモデルの検証」の [Remote] 属性のセクションを見てください。

その記事に書かれているのは Microsoft.AspNetCore.Mvc 名前空間の RemoteAttribute クラスで ASP.NET Core 用ですが、.NET Framework でも ASP.NET MVC 5.2 以降であれば System.Web.Mvc 名前空間に同様な検証属性が用意されています。

実は最近までその存在を知りませんでした。(汗) 試しに使ってみましたので備忘録としてこの記事を書いた次第です。

上の画像を表示したサンプルコードを下に載せておきます (View は省略)。品名テキストボックスへのユーザー入力と Northwind サンプルデータベースの Products テーブルの ProductName フィールドにある品名との重複をチェックし、重複していたら検証結果を NG にしています。

Model

using System.ComponentModel.DataAnnotations;
using System.Web.Mvc;

namespace Mvc5App.Models
{
    // RemoteAttribute を使用
    public class ProductInfo3
    {
        [Display(Name = "品名")]
        [Required]
        [Remote(action: "VerifyName", controller: "Validation")]
        public string Name { get; set; }

        [Display(Name = "単価")]
        [Required]
        public decimal UnitPrice { get; set; }
    }
}

Controller / Action Method

using System.Web.Mvc;
using System.Threading.Tasks;
using Mvc5App.Models;
using System.Data.Entity;

namespace Mvc5App.Controllers
{
    public class ValidationController : Controller
    {
        private NORTHWINDEntities db = new NORTHWINDEntities();

        public ActionResult Index()
        {
            return View();
        }

        // RemoteAttribute を使ってみる

        [AcceptVerbs("GET", "POST")]
        public async Task<ActionResult> VerifyName(string name)
        {
            // 応答に時間がかかる時どうなるかの検証用
            //await Task.Delay(5000);
            
            // サーバーエラーが起こるとどうなるかの検証用
            //throw new System.Exception();

            Products product = await db.Products
                .FirstOrDefaultAsync(m => m.ProductName == name);            

            if (product != null)
            {
                return Json($"品名 {name} は重複しています", 
                    JsonRequestBehavior.AllowGet);
            }

            return Json(true, JsonRequestBehavior.AllowGet);
        }

        public ActionResult Create4()
        {
            return View();
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> Create4(ProductInfo3 model)
        {
            Products product = await db.Products
                .FirstOrDefaultAsync(m => m.ProductName == model.Name);

            if (product != null)
            {
                ModelState.AddModelError("Name",
                    $"品名 {model.Name} は重複しています");
            }

            if (ModelState.IsValid)
            {
                // 検証 OK なら Create 処理して Index にリダイレクト
                db.Add(model);
                await db.SaveChangesAsync();
                return RedirectToAction("Index");
            }
            return View(model);
        }


        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }
}

RemoteAttribute が上の VerifyName アクションメソッドを呼び出して検証を行うのですが、その際クエリ文字列を使って品名テキストボックスへのユーザー入力を送信しています。サーバーへの要求は Ajax を使って行っています。下の画像はその時の要求ヘッダを Fiddler で見たものです。

要求ヘッダ

上のサンプルコードのコメントに書いたように await Task.Delay(5000) を使って応答に時間がかかる時はどうなるかを調べてみました。

応答が返ってくる前に[Create]ボタンをクリックしても submit されることはなく (クリックしても無視される)、RemoteAttribute により検証結果 OK という応答が戻ってきてからクリックすると submit されます。

先に「CustomValidator で jQuery.ajax 利用」で jQuery Ajax を使って async オプションを false に設定して検証を行う記事を書きましたが、その際問題になった応答が返ってくるまでユーザー入力・操作ができなくなるということもありませんでした。どのようにしているのか不明ですが、そのあたりはうまく考えられているようです。

ただし、VerifyName アクションメソッドでサーバーエラーが発生した場合 (HTTP 500 応答とエラーメッセージは返ってくる)、何らかの問題で応答が返ってこなかった場合は、検証中とみなされるらしく何のメッセージも表示されないままそこで止まってしまいます。

サーバーエラーに対しては以下のように try - catch を使って例外がスローされたら catch 句で JSON 文字列を返してやることで対応できそうです。

[AcceptVerbs("GET", "POST")]
public async Task<ActionResult> VerifyName(string name)
{
    try
    {
        Products product = await db.Products
            .FirstOrDefaultAsync(m => m.ProductName == name);

        if (product != null)
        {
            return Json($"品名 {name} は重複しています",
                JsonRequestBehavior.AllowGet);
        }
    }
    catch(Exception)
    {
        return Json("サーバーエラーで検証失敗", 
            JsonRequestBehavior.AllowGet);
    }          

    return Json(true, JsonRequestBehavior.AllowGet);
}

応答が返ってこないことに対しては、タイムアウトの設定などで検証中で止まってしまう問題を回避できないか調べてみましたが、自分が調べた限りでは RemoteAttribute にはそのようなオプションは見つからなかったです。

なので、応答に時間がかかるとか返ってこないということがよく起こる環境では、RemoteAttribute は使わないでサーバー側だけでの検証にとどめておいた方が良いかもしれません。

ただし、検証を行うのが CancellationToken を引数に渡せる非同期メソッド主体の場合は、CancellationTokenSource.CancelAfter メソッドを使ったコードを書いて対応できるかもしれません。具体例は以下のようになります。

[AcceptVerbs("GET", "POST")]
public async Task<ActionResult> VerifyName(string name)
{
    try
    {
        using (var cts = new CancellationTokenSource())
        {
            // 5 秒でタイムアウトに設定
            cts.CancelAfter(5000);
            CancellationToken token = cts.Token;

            Products product = await db.Products
                .FirstOrDefaultAsync(m => m.ProductName == name,
                                     token);

            // 応答に時間がかかる時どうなるかの検証用。
            await Task.Delay(10000, token);

            // 以下は無くても上の Task.Delay でタイムアウトして
            // OperationCanceledException がスローされる
            token.ThrowIfCancellationRequested();

            if (product != null)
            {
                return Json($"品名 {name} は重複しています",
                    JsonRequestBehavior.AllowGet);
            }
        }
    }
    catch (OperationCanceledException) 
    {
        return Json("タイムアウトで検証失敗",
            JsonRequestBehavior.AllowGet);
    }
    catch(Exception)
    {
        return Json("サーバーエラーで検証失敗", 
            JsonRequestBehavior.AllowGet);
    }          

    return Json(true, JsonRequestBehavior.AllowGet);
}

Tags: , ,

Validation

SignalR と SqlDependency

by WebSurfer 26. December 2021 13:38

.NET Framework 4.8 の ASP.NET Web アプリで、SqlDependency クラスを使って SQL Server のデータが更新されたときのクエリ通知を受け取れるように設定し、通知を受け取ったら更新後のデータを SQL Server から取得して、接続されている全クライアントに ASP.NET SignalR を使ってリアルタイムに配信する方法を書きます。

SignalR と SqlDependency

参考にしたのは Microsoft の「チュートリアル: SignalR 2 を使用したサーバーブロードキャスト」です。SqlDependecy 関係については Code Project の ASP.NET MVC 5 SignalR, SqlDependency and EntityFramework 6 も参考にしました。

Code Project からダウンロードできるサンプルコードは、サンプルデータベースとテーブルを作成後、接続文字列を自分の環境に合わせて変更すれば動きます。しかし、(1) 全クライアントが Entity Framework を使って直接 SQL Server にデータを取得に行く、(2) クエリ通知のサブスクリプションを設定するのに必要な情報を DbContext から取得している・・・という点が冗長だと思いました。

なので、それらを変更して (1) クエリ通知のサブスクリプションを設定するのと同時に SQL Server からデータを取得できるので (SELECT クエリを投げるのはその一回で済みます)、それをサーバーに保持しておいて Hub からクライアントに配信する、(2) サブスクリプション設定に必要な情報は接続文字列と SELECT クエリだけなので DbContext から取得という面倒なことは止めて直接コードに記述する・・・というように変更しました。

以下にアプリの作成手順を書きます。

(1) サンプルデータベースとテーブルの作成

サンプルデータベースとテーブルを SQL Server に作成します。この記事で使用した SQL Server は開発マシンの Windows 10 Pro 64-bit にインストールした SQL Server 2012 Express です。

クエリ通知はサービスブローカを使用するため、データベースに対して以下の要件がありますので注意してください。(詳しくは Microsoft のドキュメント「ADO.NET 2.0 のクエリ通知」参照)

  1. 通知クエリが実行されるデータベースでサービスブローカが有効になっている必要があります。
  2. クエリを送信するユーザーには、クエリ通知にサブスクライブするための権限が必要です。

まず SQL Server Management Studio を使って SqlDependency という名前 (名前は任意) でデータベースを作成します。上に書いた要件 1 に従ってオプションの[Broker が有効]を True に設定しましたが、それ以外はデフォルトのままです。

新しいデータベースの作成

次に Products という名前 (名前は任意) でテーブルを作成します。これも下の画像の通り SQL Server Management Studio で行いました。

Products テーブルの作成

作成したテーブルをスクリプト化すると以下の通りとなります。

USE [SqlDependency]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Products](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[UnitPrice] [decimal](18, 2) NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

(2) ASP.NET プロジェクトの作成

Visual Studio 2022 のテンプレートを使ってフレームワーク .NET Framework 4.8 で ASP.NET プロジェクトを作成します。この記事では アプリは MVC を選んで認証は「なし」としておきました (MVC である必要はなく SignalR v2 が動けば OK です)。

ASP.NET プロジェクトの作成

(3) Product クラスの作成

Models フォルダに Product.cs という名前のクラスファイルを作成し、自動生成されたコードを以下のように書き換えます。

namespace SqlDependencySignalR2.Models
{
    public class Product
    {
        public int ProductID { get; set; }
        public string Name { get; set; }
        public decimal UnitPrice { get; set; }
        public int Quantity { get; set; }
    }
}

Product クラスは Model-View-Controller (MVC) の Model とは異なり、サーバーで SQL Server のデータを保持するのと Hub からクライアントへデータを渡すために使います。

具体的には、クエリ通知のサブスクリプションを設定するのと同時に SQL Server からデータを取得し List<Product> オブジェクトとしてサーバー側に保持しておきます。それを Hub からクライアントに送信します。その際、サーバー側での List<Product> オブジェクトの JSON 文字列へのシリアライズ、クライアント側で受け取った JSON 文字列の JavaScript オブジェクトへのデシリアライズは SignalR のフレームワークがやってくれます。

(4) 接続文字列の設定

web.config に接続文字列の設定を追加します。ADO.NET の SqlConnection, SqlCommand, SqlDataReader を使ってデータを取得しますので、そのために有効な接続文字列としてください。また、上に書いた要件 2 の「クエリを送信するユーザーには、クエリ通知にサブスクライブするための権限が必要です」に注意してください。

<connectionStrings>
  <add name="ProductConnection" 
     connectionString="data source=(local)\sqlexpress;initial catalog=SqlDependency;integrated security=True" 
     providerName="System.Data.SqlClient" />
</connectionStrings>

この記事では Visual Studio 2022 を管理者権限で立ち上げて IIS Express 上で実行して検証しています。その管理者は SQL Server のログインに設定してありサーバーロールは sysadmin を持っています。上の接続文字列の例では Windows 認証を設定していますので SQL Server には sysadmin サーバーロールでログインしますので権限の問題が避け られていますが、実環境ではそうはできない点に注意してください。

(5) SignalR Hub を追加

ソリューションエクスプローラーでプロジェクトルートを右クリックして[追加(D)]⇒[新しい項目(W)...]で「SignalR Hub クラス (v2)」を選んで ProductHub.cs という名前 で追加します。

SignalR Hub クラスの作成

自動生成されたコードの内容を以下のように書き換えます。下のコードで参照している Notifier クラスは下のステップ (7) で定義します。

using Microsoft.AspNet.SignalR;
using System.Collections.Generic;
using SqlDependencySignalR2.SqlDependencyNotifier;
using SqlDependencySignalR2.Models;

namespace SqlDependencySignalR2
{
    public class ProductHub : Hub
    {
        // Notifier インスタンスへの参照を保持する
        private readonly Notifier _notifier;

        public ProductHub() : this(Notifier.Instance) { }

        public ProductHub(Notifier notifier)
        {
            _notifier = notifier;
        }

        // 初期画面のデータをクライアントが取得する時に
        // JavaScript でこのメソッドを呼び出す
        public IEnumerable<Product> GetAllProducts()
        {
            return _notifier.GetAllProducts();
        }
    }
}

(6) Startup クラスの追加

ソリューションエクスプローラーでプロジェクトルートを右クリックして[追加(D)]⇒[新しい項目(W)...]で Startup.cs という名前のクラスファイルを追加します。 内容は以下のようにします。

using Owin;
using Microsoft.Owin;

// アプリの起動時にハブにマップする。SignalR 2 では、OWIN startup
// クラスを追加するとマッピングが作成される
[assembly: OwinStartup(typeof(SqlDependencySignalR2.Startup))]
namespace SqlDependencySignalR2
{
    public class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            // OWIN startup クラスは、アプリが Configuration メソッド
            // を実行するときに MapSignalR を呼び出す。OwinStartup
            // assembly 属性を使用して OWIN のスタートアッププロセス
            // にクラスを追加する
            app.MapSignalR();
        }
    }
}

(7) Notifier クラスの実装

プロジェクトルート直下に SqlDependencyNotifier という名前のフォルダを設けて、その中に上のステップ (5) の Hub が使用する Notifier クラスを作成します。

クエリ通知のサブスクリプションを設定しているのが RegisterForNotifications メソッドです。ADO.NET の SqlConnection, SqlCommand, SqlDataReader を使って SELECT クエリを発行しデータを取得してキャッシュするのと同時に、通知のサブスクリプションの設定と、通知によって発生する SqlDependency.OnChange イベントで必要な処理を行うためイベントハンドラを設定しています。

Microsoft のドキュメント「クエリ通知を使用するときの特別な注意事項 (ADO.NET)」に書いてありますようにいろいろ制約があるので注意してください。

特に自分がハマったのが SELECT クエリのテーブル名です。ドキュメントには "テーブル名は 2 つの部分から構成される名前で修飾する必要があります" と書いてありますが、それは dbo.Products のようにする必要があると言うことです。SqlDependency.dbo.Products でも Products でもダメで、通知のサブスクリプションに失敗します。

ちなみに、失敗すると即 SqlDependency.OnChange イベントが発生し、RegisterForNotifications メソッドが実行されるので無限ループに陥ってしまいます。それを避けるためイベントハンドラの引数 SqlNotificationEventArgs をチェックして期待する結果と違っていたら何もしないようにしました。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.AspNet.SignalR;
using System.Web.Configuration;
using SqlDependencySignalR2.Models;

namespace SqlDependencySignalR2.SqlDependencyNotifier
{
    public class Notifier
    {
        // シングルトンとなるよう Lazy<T> クラスを使用
        private static readonly Lazy<Notifier> _instance
            = new Lazy<Notifier>(() => new Notifier());

        private readonly IHubContext _hubContext;
        private readonly string _connString;
        private readonly string _sqlQuery;
        private List<Product> _products;

        // コンストラクタ
        private Notifier()
        {
            // SignalR コンテキストを保持
            _hubContext = GlobalHost
                          .ConnectionManager
                          .GetHubContext<ProductHub>();

            // 接続文字列
            _connString = WebConfigurationManager
                          .ConnectionStrings["ProductConnection"]
                          .ConnectionString;
            
            // SELECT クエリ。テーブル名は dbo.Products とすること。
            // SqlDependency.dbo.Products でも Products でもダメで、
            // 通知のサブスクリプションに失敗する
            _sqlQuery = "SELECT ProductID,Name,UnitPrice,Quantity" +
                        " FROM dbo.Products";

            // クエリ通知のサブスクリプションを設定するのと同時に SQL
            // Server からデータを取得し List<Product> オブジェクトと
            // してサーバーに保持しておく
            _products = RegisterForNotifications();
        }

        public static Notifier Instance
        {
            get { return _instance.Value; }
        }

        // Hub の GetAllProducts メソッドから呼ばれる。保持している
        // List<Product> オブジェクトを返す
        public IEnumerable<Product> GetAllProducts()
        {
            return _products;
        }

        // クエリ通知のサブスクリプションを設定するのと同時に SQL
        // Server からデータを取得し List<Product> として返す
        private List<Product> RegisterForNotifications()
        {
            var products = new List<Product>();
            using (var connection = new SqlConnection(_connString))
            using (var command = new SqlCommand(_sqlQuery, connection))
            {                
                var sqlDependency = new SqlDependency(command);

                // イベントハンドラの設定
                sqlDependency.OnChange += OnSqlDependencyChange;

                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                // ExecuteReader でクエリ通知のサブスクリプションが設定
                // される。同時に SqlDataReader でデータを取得できる
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var product = new Product
                        {
                            ProductID = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            UnitPrice = reader.GetDecimal(2),
                            Quantity = reader.GetInt32(3)
                        };
                        products.Add(product);
                    }
                }
            }
            return products;
        }

        // Products テーブルが更新されるとこのイベントハンドラに制御が
        // 飛んでくる
        private void OnSqlDependencyChange(object sender, 
                                           SqlNotificationEventArgs e)
        {
            // 引数 e が期待する結果と違っていたら何もしない
            if ((e.Info == SqlNotificationInfo.Insert ||
                e.Info == SqlNotificationInfo.Update ||
                e.Info == SqlNotificationInfo.Delete) &&
                e.Source == SqlNotificationSource.Data &&
                e.Type == SqlNotificationType.Change)
            {
                // 一度通知が行われるとサブスクリプションが解除されてしま
                // うので、以下のメソッドで再度設定するとともに更新後の
                // データを _products に取得する
                _products = RegisterForNotifications();

                // 更新後のデータを接続されている全クライアントに送信
                _hubContext.Clients.All.broadcastMessage(_products);
            }
        }
    }
}

(8) SqlDependency.Start / Stop の設定

Global.asax.cs に、接続文字列で指定される SQL Server のインスタンスから依存関係の変更通知を受け取るリスナの開始 / 停止を設定します。

using System;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;
using System.Web.Configuration;
using System.Data.SqlClient;

namespace SqlDependencySignalR2
{
    public class MvcApplication : System.Web.HttpApplication
    {
        protected String SqlConnectionString { get; set; }

        protected void Application_Start()
        {
            // この下の 4 行は自動生成された既存のコード
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            SqlConnectionString = WebConfigurationManager
                                  .ConnectionStrings["ProductConnection"]
                                  .ConnectionString;

            if (!String.IsNullOrEmpty(SqlConnectionString))
            {
                SqlDependency.Start(SqlConnectionString);
            }
        }

        protected void Application_End()
        {
            if (!String.IsNullOrEmpty(SqlConnectionString))
            {
                SqlDependency.Stop(SqlConnectionString);
            }
        }
    }

(9) 表示画面の作成

表示画面は、この記事では Controller / View を使いましたが、静的な html ページで作っても良いです。ただし、ブラウザにキャッシュされるので内容を変更するたびキャッシュを削除するのが面倒ですが。

自動生成されている HomeController に Product という名前のアクションメソッドを追加します。

public ActionResult Product()
{
    return View();
}

上の Product アクションメソッドを右クリックして表示されるダイアログで以下のように設定し、アクションメソッドに対応するView を自動生成させます。

View の作成

自動生成されたコードを以下のように書き換えます。

@{
    ViewBag.Title = "Product";
}

<h2>SignalR and SqlDependency Sample</h2>

<div id="pruductTable">
    <table border="1">
        <thead>
            <tr><th>ProductID</th><th>Name</th><th>UnitPrice</th><th>Quantity</th></tr>
        </thead>
        <tbody id="productbody">
            <tr class="loading"><td colspan="4">loading...</td></tr>
        </tbody>
    </table>
</div>

@section scripts {
    <!--jQuery.js は _Layout.cshtml で参照済み -->

    <!--SignalR ライブラリの参照 -->
    <script src="~/Scripts/jquery.signalR-2.2.2.min.js"></script>

    <!--サーバー側で自動生成されるプロキシの JavaScript コードを取得する -->
    <script src="~/signalr/hubs"></script>

    <script type="text/javascript">
        var signalRHubInitialized = false;

        $(function () {
            InitializeSignalRHubStore();
        });

        function InitializeSignalRHubStore() {

            if (signalRHubInitialized) {
                return;
            }

            try {
                // ハブ用に自動生成されたプロキシへの参照を作成
                var productHub = $.connection.productHub;

                // SqlDependency.OnChange イベントが発生すると呼び出される
                productHub.client.broadcastMessage = function (products) {
                    $('#productbody').empty();
                    $.each(products, function (index, product) {
                        $('#productbody').append(
                            '<tr><td>' + product.ProductID +
                            '</td><td>' + product.Name +
                            '</td><td>' + product.UnitPrice +
                            '</td ><td>' + product.Quantity +
                            '</td></tr >');
                    });
                };

                // start() で Hub への接続を開始。.done でクライアントから Hub の
                // パブリックメソッド GetAllProducts を呼び出す
                $.connection.hub.start().done(function () {
                    productHub.server.getAllProducts().done(function (products) {
                        $('#productbody').empty();
                        $.each(products, function (index, product) {
                            $('#productbody').append(
                                '<tr><td>' + product.ProductID +
                                '</td><td>' + product.Name +
                                '</td><td>' + product.UnitPrice +
                                '</td ><td>' + product.Quantity +
                                '</td></tr >');
                        });
                    });
                    signalRHubInitialized = true;
                });

            } catch (err) {
                signalRHubInitialized = false;
            }
        };
    </script>
}

上の html コードの table 要素内の tbody 要素をサーバーから送られてきたデータで書き換えるようにしています。

まず、初期画面が表示されると Hub への接続が開始され、接続が完了すると Hub の GetAllProducts が呼び出されサーバ側で保持されている List<Product> がクライアントに送信され、それが上の function (products) の引数に JavaScript オブジェクトとして渡されます。その products を使って tbody 要素を書き換えて初期データを表示します。

その後、SQL Server の Products テーブルが更新されるとサーバー側で SqlDependency.OnChange イベントが発生し、上の productHub.client.broadcastMessage に設定された function (products) が呼び出されます。その際、引数 products には更新後のデータを含 む JavaScript オブジェクトが渡され、それを使って tbody 要素を書き換えて更新後のデータを表示します。

アプリを実行して複数のブラウザでアクセスし、SQL Sever Management Studio などを使って Products テーブルを更新すると、更新結果がリアルタイムで接 続されているすべてのブラウザに反映されます。それを表示したのがこの記事の一番上の画像です。

Tags: , , ,

ASP.NET

About this blog

2010年5月にこのブログを立ち上げました。主に ASP.NET Web アプリ関係の記事です。

Calendar

<<  March 2024  >>
MoTuWeThFrSaSu
26272829123
45678910
11121314151617
18192021222324
25262728293031
1234567

View posts in large calendar