ASP.NET Core で SqlDependency

by WebSurfer 2022年1月1日 17:50

.NET 6.0 アプリでも NuGet で System.Data.SqlClient をインストールすれば SqlDependency は使えるようです (コンソールアプリで確認しました)。

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

SignalR と SqlDependency

先の記事「SignalR と SqlDependency」とほぼ同じことをしていますが、そちらはターゲットフレームワークが .NET Framework 4.8 だったものを、.NET 6.0 に変えて書き直したものです。

以下にその作成手順を書きます。

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

先の記事「SignalR と SqlDependency」と同じデータベースとテーブルを使います。作成手順は先の記事を見てください。

参考に作成したテーブルを CREATE TABLE スクリプト化したものを下に再掲しておきます。

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 6.0 として ASP.NET Core Web アプリのプロジェクトを作成します。この記事ではアプリは MVC を選んで認証は「なし」としておきました (MVC である必要はなく Razor Pages でも OK です)。

(3) Product クラスの作成

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

#nullable disable

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

内容は先の記事「SignalR と SqlDependency」と同じですが、フレームワーク .NET 6.0 でプロジェクトを作成するとデフォルトで NULL 許容参照型が有効になるので、警告を消すため #nullable disable を追加しています (警告を無視しないでちゃんと対応した方が良いかも)。

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

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

(4) 接続文字列の設定

appsettings.json に接続文字列の設定を追加します。下のコードの "AllowedHosts": "*" までは自動生成されたもので、その後カンマ , に続けて接続文字列の設定を追加します。JSON 文字列なので \ はエスケープして \\ にする必要があることに注意してください。

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "ProductConnection": 
      "data source=lpc:(local)\\sqlexpress;initial catalog=SqlDependency;integrated security=True"
  }
}

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

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

(5) クライアントライブラリの取得

ASP.NET Core SignalR 用のクライアントライブラリ signalr.js, signalr.min.js は npm から取得してプロジェクトにインストールする必要があります。LibMan でインストールする方法が Microsoft のドキュメント「SignalR クライアントライブラリを追加する」に書いてあります。

サーバーライブラリは ASP.NET Core フレームワークに含まれていますので、それを取得するための作業は不要です。

(6) SignalR Hub を追加

ソリューションエクスプローラーでプロジェクトルート直下に Hubs というフォルダを作成し、その中に ProductHub.cs という名前でクラスファイルを追加します。

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

using Microsoft.AspNetCore.SignalR;
using SqlDependency.Broadcasters;
using SqlDependency.Models;

namespace SqlDependency.Hubs
{
    public class ProductHub : Hub
    {
        private readonly Notifier _notifier;

        // Notifier インスタンスを DI により取得して設定。
        // Program.cs で AddSingleton メソッドを使ってシング
        // ルトンになるようにしている
        public ProductHub(Notifier notifier)
        {
            _notifier = notifier;
        }

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

(7) NuGet パッケージのインストール

クエリ通知のサブスクリプションの設定に必要な SqlConnection, SqlCommand 等は .NET 6.0 には含まれておらず NuGet で System.Data.SqlClient をインストールする必要があります。（そこは .NET Core 3.1、.NET 5.0 でも同じです）

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

上の画像の System.Data.Common には System.DBNull とか System.Data.DbType 等が含まれているとのことなので念のため追加しておきました。

(8) Notifier クラスの実装

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

Notifier ク��スは、SQL Server からデータの取得してキャッシュするのと同時にクエリ通知のサブスクリプションの設定し、キャッシュしたデータをクライアントからの要求に応じて配信します。

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

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

SQL Server のインスタンスから依存関係の変更通知を受け取るリスナの開始 / 停止を設定する SqlDependency.Start / Stop は、.NET Framework 版 ASP.NET Web アプリでは Global.asax に実装しましたが、ASP.NET Core Web アプリでは Global.asax は存在しないので Notifier クラスで行うようにしました。

SqlDependency.Start は Nortifier クラスのコンストラクタで、SqlDependency.Stop は Nortifier クラスに Dispose パターンを実装し Dispose(bool disposing) メソッドに含めるようにしました。Stop の方が期待通り動いているか不安ではありますが。

using System.Data;
using System.Data.SqlClient;
using SqlDependency.Models;
using SqlDependency.Hubs;
using Microsoft.AspNetCore.SignalR;

namespace SqlDependency.Broadcasters
{
    public class Notifier : IDisposable
    {
        private readonly IHubContext<ProductHub> _hubContext;
        private readonly IConfiguration _configuration;
        private readonly string _connString;
        private readonly string _sqlQuery;
        private List<Product> _products;

        // コンストラクタ
        public Notifier(IHubContext<ProductHub> hubContext,
                         IConfiguration configuration)
        {
            // SignalR コンテキストを DI により取得して設定
            _hubContext = hubContext;

            // IConfiguration を DI により取得して設定            
            _configuration = configuration;

            // appsettings.json の接続文字列を取得
            _connString = _configuration
                          .GetConnectionString("ProductConnection");

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

            System.Data.SqlClient.SqlDependency.Start(_connString);

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

        // 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 System.Data.SqlClient.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
                           .SendAsync("UpdateProductInfo", _products);
            }
        }

        private bool disposedValue;

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue)
            {
                if (disposing)
                {
                    System.Data.SqlClient.SqlDependency.Stop(_connString);
                }

                disposedValue = true;
            }
        }

        public void Dispose()
        {
            Dispose(disposing: true);
            GC.SuppressFinalize(this);
        }
    }
}

Notifier クラスは ASP.NET Core アプリのフレームワークに備わっている DI 機能を使ってシングルトンインスタンスを作成し、それへの参照を Hub のコンストラクタ経由で DI します。

Program.cs で AddSingleton<T> メソッドを使ってサービスに登録すると、一番最初に DI される時点でインスタンスが生成され、アプリケーションが終了するまで、以降の DI ではそのインスタンスを使いまわす、即ちシングルトンインスタンスになります。下のステップ (9) のコード例を見てください。

(9) Program.cs の修正

要求が SignalR に渡されるように SignalR サーバーを構成します。Program.cs（.NET Core 3.1, .NET 5.0 の場合は Startup.cs）で、以下のコードに「*** 追加 ***」「*** 書き換え ***」とコメントしたように設定します。

上のステップ (8) で述べた AddSingleton<T> メソッドを使っての Notifier クラスのサービスへの登録も同時に行います。

// *** 追加 ***
using SqlDependency.Hubs;
using SqlDependency.Broadcasters;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllersWithViews();

// *** 追加 ***
builder.Services.AddSignalR();

// ProductHub がコンストラクタ経由 DI により Notifier クラスの
// シングルトンインスタンスを取得できるよう以下の設定を行う
builder.Services.AddSingleton<Notifier>();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

// *** 書き換え ***
//app.MapControllerRoute(
//    name: "default",
//    pattern: "{controller=Home}/{action=Index}/{id?}");
app.UseEndpoints(endpoints =>
{
    endpoints.MapControllerRoute(
        name: "default",
        pattern: "{controller=Home}/{action=Index}/{id?}");

    endpoints.MapHub<ProductHub>("/productHub");
});

app.Run();

(10) 表示画面の作成

表示画面は、この記事では Controller / View を使いましたが、静的な html ページで作っても良いです。

自動生成されている HomeController に Product という名前のアクションメソッドを追加します。Product アクションメソッドを右クリックしてスキャフォールディング機能により View を生成します。自動生成された View のコードを以下のように書き換えます。

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>ASP.NET Core SqlDependency</title>
    <script src="~/lib/jquery/dist/jquery.min.js"></script>
    <script src="~/js/signalr/dist/browser/signalr.js"></script>
</head>
<body>
    <h1>ASP.NET Core SqlDependency</h1>
    <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>

<script type="text/javascript">
        var signalRHubInitialized = false;
 
        $(function () {
            InitializeSignalRHubStore();
        });
 
        function InitializeSignalRHubStore() {
 
            if (signalRHubInitialized) {
                return;
            }
 
            try {
                // 接続を作成。"/productHub" は Program.cs で
                // endpoints.MapHub("/productHub");
                // としてマップしたエンドポイントらしい
                var connection = new signalR.HubConnectionBuilder()
                                    .withUrl("/productHub")
                                    .build();

                // Hub への接続を開始、接続されるとinit メソッドを呼び出す
                connection.start().then(init);

                // Hub の GetAllProducts メソッドを呼び出す。戻り値は products 
                // に JavaScript の連想配列として渡される（キー名が camel 
                // case になるので注意）。そのデータで初期画面を描画
                function init() { 
                    connection.invoke("GetAllProducts").then(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;
                };

                // Notifier クラスの OnSqlDependencyChange イベントハンドラの 
                // SendAsync("UpdateProductInfo", _products); で SignalR コ
                // ンテキストを通じて下の function (products) { ... } が起
                // 動される。引数の products に含まれる情報により table の
                // 表示を更新する。
                connection.on("UpdateProductInfo", 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 >');
                    });
                });                
            } catch (err) {
                signalRHubInitialized = false;
            }
        };
    </script>
</body>
</html>

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

まず、初期画面が表示されると connection.start メソッドで Hub への接続が開始され、接続が完了すると init メソッドが呼び出されます。

init メソッド内の connection.invoke メソッドにより Hub の GetAllProducts が呼び出され、サーバ側で保持されている List<Product> が JSON 文字列にシリアライザされてクライアントに送信されます。クライアントでは受け取った JSON 文字列を JavaScript オブジェクト（連想配列）にデシリアライズして function (products) の引数 products に渡します。その products を使って tbody 要素を書き換えて初期データを表示します。

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

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

bef5c21e-1c6d-4c4e-8f3f-97a311aec7b2|1|5.0

Tags: Core, SQL Server, SqlDependency, SignalR

CORE

日	月	火	水	木	金	土
26	27	28	29	30	31	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31	1	2	3	4	5

WebSurfer's Home

ASP.NET Core で SqlDependency

About this blog

Search

Recent Posts

Calendar

日	月	火	水	木	金	土
26	27	28	29	30	31	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31	1	2	3	4	5

日	月	火	水	木	金	土
26	27	28	29	30	31	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31	1	2	3	4	5

WebSurfer's Home

ASP.NET Core で SqlDependency

About this blog

Search

Tag cloud

Recent Posts

Calendar

日	月	火	水	木	金	土
26	27	28	29	30	31	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31	1	2	3	4	5