C#でNpgsqlを使ってPostgreSQLへアクセス【NpgsqlDataAdapterでUPDATE】

2021/10/12

C# Npgsql

アイキャッチ

今回はNpgsqlDataAdapterを使ったUPDATEの解説です。

注意点も解説しつつ幾つかの手法を紹介します。

Npgsqlの本家情報は

私の作成するサンプルソースファイルは

基本的なテーブルは下記構成となります。

テーブル名 概要
id serial 自動的にセットされる通し番号
time timestamp トランザクション開始時刻または入力された日付
name text 任意の文字列
numeric integer 任意の数値

時間はCREATE TABLEで「time timestamp DEFAULT clock_timestamp()」としており、意図的に時間をセットできますが、何もINSERTしなければ自動的に現在の時間がセットされます。

このテーブルに下記2つのデータが存在しているのを前提として説明します。

id time name numeric
1 INSERTした時間 a 1
2 INSERTした時間 b 2

UPDATEのきほん

オーソドックスな手法から

using DataTable dt = new();
using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public");
con.Open();
using NpgsqlDataAdapter nda = new("SELECT * FROM data;", con);
var result = nda.Fill(dt);
// データを更新
dt.Rows[0][3] = 100;
// データベースにUPDATEするNpgsqlCommandを追加
using NpgsqlCommand updateCommand = new();
updateCommand.Connection = con;
updateCommand.CommandText = "UPDATE data SET numeric = @numeric WHERE id = @id";
// 「id」を条件としてUPDATするNpgsqlParameterを作成して追加
NpgsqlParameter updateId = new();
updateId.ParameterName = "@id";
updateId.SourceColumn = "id";
_ = updateCommand.Parameters.Add(updateId);
// UPDATEする「numeric」のNpgsqlParameterを作成して追加
NpgsqlParameter updateNumeric = new();
updateNumeric.ParameterName = "@numeric";
updateNumeric.SourceColumn = "numeric";
_ = updateCommand.Parameters.Add(updateNumeric);
// NpgsqlDataAdapterのInsertCommandに追加
nda.UpdateCommand = updateCommand;
try
{
    result = nda.Update(dt);
}
catch (DBConcurrencyException)
{
    // INSERT、UPDATE、DELETE の各ステートメントを実行しようとしましたが、影響を受けたレコードがない
}

まずはFillメソッドで全てのデータを取得します。

全てのデータを取得した後、一番最初のデータのカラム名「numeric」の値を100にしてみます。

これで手持ちのデータは更新しましたが、この次から少し手間がかかります。

    UPDATEの手順としては
  1. UPDATEするデータ用NpgsqlCommandを作る。
  2. UPDATEするデータのクエリ文に渡すパワメータの数だけNpgsqlParameterを作る。
  3. NpgsqlParameteのParameterNameとSourceColumnを適切に設定する。
  4. NpgsqlCommandのParameters.Addメソッドを実行し作ったNpgsqlParameteを渡す。
  5. NpgsqlDataAdapterのUpdateCommandプロパティへNpgsqlCommandを渡す。
  6. NpgsqlDataAdapteのUpdateメソッドを実行する。

手順1が上記サンプルコードの9~11行目に該当します。

11行目で実際に実行するクエリ文と渡す値のパラメータをセットします。

手順,2,3,4はサンプルコード13~21行目が該当します。

NpgsqlParameterのParameterNameにはクエリ文で指定したパラメータ(@○○○)、SourceColumnにはデータベーステーブルの(またはDataTable)のカラム名を指定します。

そして手順5は23行目となります。

実際のUPDATE実行は26行目ですが、正常にUPDATEできなかった時のために例外「DBConcurrencyException」をキャッチできるようにすれば完璧です。

NpgsqlCommandBuilderを使った時の自動更新の注意点

INSERTの解説で紹介しましたが、同様にNpgsqlCommandBuilderの使い方と注意点を解説します。

まずは基本的な手法から

using DataTable dt = new();
using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public");
con.Open();
using NpgsqlDataAdapter nda = new("SELECT * FROM data;", con);
var result = nda.Fill(dt);
// データを更新
dt.Rows[0][3] = 100;
using NpgsqlCommandBuilder cb = new(nda);
try
{
    result = nda.Update(dt);
}
catch (DBConcurrencyException)
{
    // INSERT、UPDATE、DELETE の各ステートメントを実行しようとしましたが、影響を受けたレコードがない
}

やっている事は前回と同様で全てのデータを取得後、一番最初のデータにあるカラム名「numeric」の値を100にしています。

その後、NpgsqlDataAdapterのUpdateメソッドでデータを更新しており、結果としては最初のサンプルと同じになりました。

その時にNpgsqlCommandBuilderで自動的に生成されたクエリ文を見ると

UPDATE "db_PostgreTest"."public"."data" SET "time" = @p1, "name" = @p2, "numeric" = @p3 WHERE (("id" = @p4) AND ((@p5 = 1 AND "time" IS NULL) OR ("time" = @p6)) AND ((@p7 = 1 AND "name" IS NULL) OR ("name" = @p8)) AND ((@p9 = 1 AND "numeric" IS NULL) OR ("numeric" = @p10)))

やたらと長いクエリ文になってますが、簡単に言うと全ての値が一致したデータを更新する意味になります。

私が最初に示したサンプルでは更新する条件はidの一致みでしたが、今回の条件は全てが一致する事が条件となります。

データベースを使っている方ならすぐ理解できるかと思いますが、自分が更新しようと思ったデータが自分より先に誰かが更新し更に自分が更新した事によって起こる問題を抑制できます。

例えば更新しようと思っても誰かがnameを書き換えたら条件に完全一致するデータが存在しなくなるので更新できず、例外「DBConcurrencyException」が発生します。

NpgsqlCommandBuilderを使った時の自動更新の落とし穴

同様なケースで以下のような処理の時に問題が起こる可能性があります。

using DataTable dt = new();
using NpgsqlConnection con = new("Server=127.0.0.1; Port=5432; User Id=test_user; Password=pass; Database=db_PostgreTest; SearchPath=public");
con.Open();
using NpgsqlDataAdapter nda = new("SELECT id, name, numeric FROM data;", con);
var result = nda.Fill(dt);
// データを更新
dt.Rows[0][2] = 100;
using NpgsqlCommandBuilder cb = new(nda);
try
{
    result = nda.Update(dt);
}
catch (DBConcurrencyException)
{
    // INSERT、UPDATE、DELETE の各ステートメントを実行しようとしましたが、影響を受けたレコードがない
}

今回は取得したデータが「時間以外」です。

これで生成されるUPDATEクエリ文を見ると

UPDATE "db_PostgreTest"."public"."data" SET "name" = @p1, "numeric" = @p2 WHERE (("id" = @p3) AND ((@p4 = 1 AND "name" IS NULL) OR ("name" = @p5)) AND ((@p6 = 1 AND "numeric" IS NULL) OR ("numeric" = @p7)))

SELECTしたデータを基にUPDATEするクエリ文を生成するので条件にはカラム名「time」が含まれておらず、この条件でUPDATEした場合には問題の起こるケースもあれば何も問題にならないケースもありますので、これは状況次第としか言いようがありません。

NpgsqlCommandBuilderは簡潔で可読性の高い記述になりますが、生成されるクエリ文はNpgsqlCommandBuilderのGetUpdateCommand().CommandTextで確認した方がトラブルの元にならないと思います。

自己紹介

自分の写真



新潟県のとある企業で働いてます。
【できる事】
電子回路設計
基板パターン設計
マイコンプログラム
C#(WinForms WPF)を使ったWindowsアプリケーション作成
PLCラダー
自動化装置アドバイザー
にほんブログ村 IT技術ブログ ソフトウェアへ

カテゴリ

このブログを検索

QooQ