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

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

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

• もくじ
  • UPDATEのきほん
  • NpgsqlCommandBuilderを使った時の自動更新の注意点
  • NpgsqlCommandBuilderを使った時の自動更新の落とし穴

Npgsqlの本家情報は

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

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

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

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

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

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で確認した方がトラブルの元にならないと思います。