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

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

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

• もくじ
  • INSERTのきほん
  • 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

INSERTのきほん

まずは一番オーソドックスで、かつ一番利用頻度が高い手法です。

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);
// DataTableにデータの追加
DataRow addRow = dt.NewRow();
addRow[dt.Columns[2].ColumnName] = "c";
addRow[dt.Columns[3].ColumnName] = 3;
dt.Rows.Add(addRow);
// データベースにINSERTするNpgsqlCommandを追加
using NpgsqlCommand insertCommand = new();
insertCommand.Connection = con;
insertCommand.CommandText = "INSERT INTO data(name, numeric) VALUES(@Name, @Numeric)";
// 「name」をINSERTするNpgsqlParameterを作成して追加
NpgsqlParameter insertName = new();
insertName.ParameterName = "@Name";
insertName.SourceColumn = "name";
_ = insertCommand.Parameters.Add(insertName);
// 「numeric」をINSERTするNpgsqlParameterを作成して追加
NpgsqlParameter insertNumeric = new();
insertNumeric.ParameterName = "@Numeric";
insertNumeric.SourceColumn = "numeric";
_ = insertCommand.Parameters.Add(insertNumeric);
// NpgsqlDataAdapterのInsertCommandに追加
nda.InsertCommand = insertCommand;
try
{
    result = nda.Update(dt);
}
catch (DBConcurrencyException)
{
    // INSERT、UPDATE、DELETE の各ステートメントを実行しようとしましたが、影響を受けたレコードがない
}

NpgsqlDataAdapterのコンストラクタ引数は4つありますが、うち3つはSELECTに関する変数や文字列が引数となります。

なにはともあれ運用が始まればSELECTでデータを取得しなきゃ始まらないですからね。

DataTableを用意しておきNpgsqlDataAdapterのインスタンスを生成したら「Fill」メソッドでデータを取得します。

FillメソッドはDataTableやDataSetを引数として渡せますので、その場で最適な引数を渡してください。

次に7～10行目でDataTableのdtに1つデータを追加してみます。

これで準備OKとはなりません。

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

文章にすると何となく面倒な感じがしますが、全て必要な事です。

手順1が上記サンプルコードの12～14行目が該当します。

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

手順,2,3,4はサンプルコード16～24行目が該当します。

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

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

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

例外「DBConcurrencyException」はINSERTだけじゃなく、データが変更されるような動作でエラーが出た時に発生します。

INSERTするまでの手順は今までより少し長いですが、大量なデータを処理する場合はDataTableを触るだけで良いのでミスが減る可能性があります。

NpgsqlCommandBuilderを使った時の自動挿入カラムの注意点

NpgsqlにはNpgsqlCommandBuilderという便利な機能があります。

SELECTしたデータを基にINSERTクエリ等を自動的に生成してくれる機能です。

これを利用すればINSERTやUPDATE、DELETEのクエリ文は自分で作らなくてもOKです。

しかし落とし穴もあるので問題点を解説します。

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);
// DataTableにデータの追加
DataRow addRow = dt.NewRow();
addRow[dt.Columns[2].ColumnName] = "c";
addRow[dt.Columns[3].ColumnName] = 3;
dt.Rows.Add(addRow);
using NpgsqlCommandBuilder cb = new(nda);
try
{
    result = nda.Update(dt);
}
catch (DBConcurrencyException)
{
    // INSERT、UPDATE、DELETE の各ステートメントを実行しようとしましたが、影響を受けたレコードがない
}

上記サンプルプログラムはSELECTで全てのカラムデータを取得しますがDataTableに追加するのはカラム名「name」と「numeric」です。

このプログラムを実行した後にpgAdmin4でテーブルのデータ一覧を見ると自動挿入のidはきちんと値がセットされていますが、同じく自動挿入を期待しているtimeカラムはnullになっています。

NpgsqlCommandBuilderの「GetInsertCommand().CommandText」プロパティを見ると実行されるクエリ文が分かるのですが、それを見ると

INSERT INTO "db_PostgreTest"."public"."data" ("time", "name", "numeric") VALUES (@p1, @p2, @p3)

となっており、timeにもデータをINSERTしようとしています。

当然DataTableのカラム名timeには何もデータをセットしていません。

同じくカラム名idにもデータは入れてませんが、NpgsqlCommandBuilderは自動挿入と判断しているらしくクエリ文にはidにINSERTする部分は見つかりません。

このように自分が意図する結果にならないケースがありますのでNpgsqlCommandBuilderを使うのは注意が必要です。

NpgsqlCommandBuilderを使った時の自動挿入カラムの解決

上記意図しないINSERTで自動的にセットする値が入らない場合の解決策は至って簡単です。

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();
// idは取得するがINSERTには含まれない
using NpgsqlDataAdapter nda = new("SELECT id, name, numeric FROM data;", con);
var result = nda.Fill(dt);
// DataTableにデータの追加
DataRow addRow = dt.NewRow();
addRow[dt.Columns[1].ColumnName] = "c";
addRow[dt.Columns[2].ColumnName] = 3;
dt.Rows.Add(addRow);
using NpgsqlCommandBuilder cb = new(nda);
try
{
    result = nda.Update(dt);
}
catch (DBConcurrencyException)
{
    // INSERT、UPDATE、DELETE の各ステートメントを実行しようとしましたが、影響を受けたレコードがない
}

SELECTの時に必要ではないデータを読み込まなければいいだけです。

上記サンプルプログラムは「id」、「name」、「numeric」だけをSELECTしていますが、この結果NpgsqlCommandBuilderで生成されたINSERT文は

INSERT INTO "db_PostgreTest"."public"."data" ("name", "numeric") VALUES (@p1, @p2)

となり、timeにINSERTされる事はありませんし、timeには自動的にINSERTされた時の時間がセットされます。

もしSELECTしたデータのtimeが必要であればNpgsqlDataAdapterのUpdateメソッドを実行する直前にDataTableのtimeにDateTime.Nowプロパティの値をセットすればほぼ正確なINSERTしたタイミングの時間がセットされます。