Blazor Serverから PostgreSQLへSQL文を発行する

.Net 6 の Blazor Serverから、Dapper+Npgsql NuGetパッケージを使い、PostgreSQLデータベースへSQL文を発行するサンプルを作成しました。
ソースコードはGitHubで公開しています。

Blazor Serverに PostgreSQLを対象としたDB処理を実装する場合、Npgsql.EntityFrameworkCore.PostgreSQLを使ったコードファーストの実装は避けた方が良い。
Npgsql.EntityFrameworkCore.PostgreSQLは最新版でも、コードファーストの新しい書き方に対応していないことがあり、実装、試験していてもバグに気付かないことがある。

Containsを6つ使うコードファーストを実装したら、発行されたSQLにContains条件が発行されたのは4つしかないとか、SQLのIN条件に変換されていない、といったことがありました。
Containsが機能しないのは、Npgsql.EntityFrameworkCore.PostgreSQL 3.1 に含まれるバグらしい。

厄介なのは、実行されたSQLを常に確認しないと、Npgsql.EntityFrameworkCore.PostgreSQLのバグに気付けず、コードファーストで実装している人はコードファーストから発行されたSQLを確認しない点。

データ量増加に伴うSQLチューニングも、コードファーストでは対応できず、Dapper+Npgsql を使った、今回の実装方式でデータベース処理を実装し直すこともよく起こる。
「SQLを書けないからコードファーストで行く」と、PMが判断したプロジェクトで、上手く行っている現場は見たことがない。

ソースコード構成

今回使った Visual Studio プロジェクトテンプレートは、Visual Studio 2022 + .Net 6 + Blazor Server アプリの HTTPS無し。

テンプレートに対して、PostgreSQLへSQL文を発行する、下記の処理を加えました。

ソースコード変更内容を解説

/BlazorApp1.csproj

Dapper/Npgsql NuGetパッケージをインストール。


<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.123" />
    <PackageReference Include="Npgsql" Version="7.0.0" />
  </ItemGroup>

</Project>

<Nullable>enable</Nullable>

<ImplicitUsings>enable</ImplicitUsings>

</PropertyGroup>

</ItemGroup>

</Project>

/appsettings.Development.json

PostgreSQLへ接続するコネクションストリングを追加。


{
  "DetailedErrors": true,
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "PostgreSQLDB": "User ID=testuser;Password=testuser;Host=localhost;Port=5432;Database=testdb;Pooling=true;CommandTimeout=600;",
  }
}

{

"DetailedErrors": true,

"Logging": {

"LogLevel": {

"Default": "Information",

"Microsoft.AspNetCore": "Warning"

}

"ConnectionStrings": {

"PostgreSQLDB": "User ID=testuser;Password=testuser;Host=localhost;Port=5432;Database=testdb;Pooling=true;CommandTimeout=600;",

}

/Shared/SharedData.cs

・staticメンバ変数で実装した、アプリ全体で使えるコネクションストリング変数を追加。


namespace BlazorApp1.Shared
{
    public static class SharedData
    {
        /// <summary>
        /// PostgreSQL DB 接続文字列
        /// </summary>
        public static string ConnectionString;

    }
}

namespace BlazorApp1.Shared

{

public static class SharedData

{

/// <summary>

/// PostgreSQL DB 接続文字列

/// </summary>

public static string ConnectionString;

}

/Program.cs

・アプリ起動時に環境設定ファイルを読み込み、アプリ全体で使えるコネクションストリング変数にセットする処理を追加。
　環境設定ファイルの読み込み処理をアプリ起動時１回だけにし、アプリ起動後は staticメンバ変数を直接参照することで、処理コストを減らせる。


using BlazorApp1.Data;
using BlazorApp1.Shared;
using Microsoft.AspNetCore.Components;
using Microsoft.AspNetCore.Components.Web;

var builder = WebApplication.CreateBuilder(args);

var configuration = builder.Configuration;
SharedData.ConnectionString = configuration.GetSection("ConnectionStrings")["PostgreSQLDB"];

// Add services to the container.
builder.Services.AddRazorPages();
builder.Services.AddServerSideBlazor();
builder.Services.AddSingleton<WeatherForecastService>();

var app = builder.Build();

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


app.UseStaticFiles();

app.UseRouting();

app.MapBlazorHub();
app.MapFallbackToPage("/_Host");

app.Run();

using BlazorApp1.Data;

using BlazorApp1.Shared;

using Microsoft.AspNetCore.Components;

using Microsoft.AspNetCore.Components.Web;

var builder = WebApplication.CreateBuilder(args);

var configuration = builder.Configuration;

SharedData.ConnectionString = configuration.GetSection("ConnectionStrings")["PostgreSQLDB"];

// Add services to the container.

builder.Services.AddRazorPages();

builder.Services.AddServerSideBlazor();

builder.Services.AddSingleton<WeatherForecastService>();

var app = builder.Build();

// Configure the HTTP request pipeline.

if (!app.Environment.IsDevelopment())

{

app.UseExceptionHandler("/Error");

}

app.UseStaticFiles();

app.UseRouting();

app.MapBlazorHub();

app.MapFallbackToPage("/_Host");

app.Run();

/Model/ContractModel.cs

・PostgreSQLから取得したデータに対応する、データモデルクラスを追加。


using static System.Net.Mime.MediaTypeNames;

namespace BlazorApp1.Model
{
    public class ContractModel
    {
        public long id;
        public string code;
        public string? value_string;
        public DateTime? value_date;
    }
}

using static System.Net.Mime.MediaTypeNames;

namespace BlazorApp1.Model

{

public class ContractModel

{

public long id;

public string code;

public string? value_string;

public DateTime? value_date;

}

/Pages/Counter.razor

・PostgreSQLデータベースに対して、Select文を発行する「Select from PostgreSQL」ボタンを追加。


@page "/counter"

<PageTitle>Counter</PageTitle>

<h1>Counter</h1>

<p role="status">Current count: @currentCount</p>

<button class="btn btn-primary" @onclick="IncrementCount">Click me</button>

<button class="btn btn-primary" @onclick="SelectFromPostgreSQL">Select from PostgreSQL</button>

@code {
    private int currentCount = 0;

    private void IncrementCount()
    {
        currentCount++;
    }

    private void SelectFromPostgreSQL()
    {
        try
        {
            var codeList = new List<string>();
            codeList.Add("code1");
            codeList.Add("code2");

            var contractList = Contract.SelectContract(codeList);
        }
        catch (Exception ex)
        {
            throw;
        }
    }

}

@page "/counter"

<PageTitle>Counter</PageTitle>

<h1>Counter</h1>

<p role="status">Current count: @currentCount</p>

<button class="btn btn-primary" @onclick="IncrementCount">Click me</button>

<button class="btn btn-primary" @onclick="SelectFromPostgreSQL">Select from PostgreSQL</button>

@code {

private int currentCount = 0;

private void IncrementCount()

{

currentCount++;

}

private void SelectFromPostgreSQL()

{

try

{

var codeList = new List<string>();

codeList.Add("code1");

codeList.Add("code2");

var contractList = Contract.SelectContract(codeList);

}

catch (Exception ex)

{

throw;

}

/SQL/Contract.cs

・Select文の Where条件をメソッドパラメータから受け取り、パラメータを匿名クラスにし、DapperにSQLとパラメータを渡して、Select結果を受け取るメソッドを追加。
・データベースとC#側のデータ型が一致していれば、Any文の条件も簡単な記述で良く、あとは Dapperがバインドしてくれる。
・SQL文は定数化しておくことで処理コストを減らせる。


using BlazorApp1.Model;
using Dapper;
using Npgsql;

namespace BlazorApp1.SQL
{
    public class Contract
    {
        public static IEnumerable<ContractModel> SelectContract(string connectionString, IEnumerable<string> codeList)
        {
            const string sql =
                " SELECT " +
                "  id, " +
                "  code, " +
                "  value_string, " +
                "  value_date " +
                " FROM " +
                "  test_schema.m_table_a " +
                " WHERE " +
                "  code = ANY (@codeList) ";

            var sqlParam = new
            {
                codeList = codeList.ToArray(),
            };

            using (var conn = new NpgsqlConnection(connectionString))
            {
                return conn.Query<ContractModel>(sql, sqlParam);
            }
        }
    }

}

using BlazorApp1.Model;

using Dapper;

using Npgsql;

namespace BlazorApp1.SQL

{

public class Contract

{

public static IEnumerable<ContractModel> SelectContract(string connectionString, IEnumerable<string> codeList)

{

const string sql =

" SELECT " +

" id, " +

" code, " +

" value_string, " +

" value_date " +

" FROM " +

" test_schema.m_table_a " +

" WHERE " +

" code = ANY (@codeList) ";

var sqlParam = new

{

codeList = codeList.ToArray(),

};

using (var conn = new NpgsqlConnection(connectionString))

{

return conn.Query<ContractModel>(sql, sqlParam);

}

/_Imports.razor

・追加した classを、各Razorページで使用できるように Usingを追加。


@using System.Net.Http
@using Microsoft.AspNetCore.Authorization
@using Microsoft.AspNetCore.Components.Authorization
@using Microsoft.AspNetCore.Components.Forms
@using Microsoft.AspNetCore.Components.Routing
@using Microsoft.AspNetCore.Components.Web
@using Microsoft.AspNetCore.Components.Web.Virtualization
@using Microsoft.JSInterop
@using BlazorApp1
@using BlazorApp1.Shared
@using BlazorApp1.SQL

@using System.Net.Http

@using Microsoft.AspNetCore.Authorization

@using Microsoft.AspNetCore.Components.Authorization

@using Microsoft.AspNetCore.Components.Forms

@using Microsoft.AspNetCore.Components.Routing

@using Microsoft.AspNetCore.Components.Web

@using Microsoft.AspNetCore.Components.Web.Virtualization

@using Microsoft.JSInterop

@using BlazorApp1

@using BlazorApp1.Shared

@using BlazorApp1.SQL