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

はじめに

まずはデータベーステーブルにデータを入れなければ話は始まりません。

と言う事で３つのINSERT手法を解説したいと思います。

尚、以下のサンプルコードを基に記述したのにビルドエラーが出る場合は「using System.Data;」が抜けている可能性がありますので確認してください。

Npgsqlの本家情報は

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

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

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

クエリ文にINSERTするデータを含める

SQLクエリ文そのままで直感的にも一番理解できる手法です。

メソッドの引数に文字列と数値があって、それをそのままINSERTします。

C#では文字列の先頭に$を付け{変数}を直接文字列に記述する事でint型でも文字列にしてくれます。

static void insert1(string insertName, int insertNumeric)
{
    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 var cmd = new NpgsqlCommand($"INSERT INTO data(name, numeric) VALUES ({insertName}, {insertNumeric})", con);
    int result = cmd.ExecuteNonQuery();
}

クエリ文の{insertName}と{insertNumeric}が直接文字列内に埋め込まれた変数で、これができるようになってから文字列に変数を加えるのが楽になりました。

プログラムの可読性も上がりバグが起きにくいと思います。

SQLインジェクションを考慮した手法その1

どちらかと言えば単発なINSERTに向いてます。

無論、連続した値も可能ですが、その場合は次の手法が向いているかと思います。

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 var cmd = new NpgsqlCommand("INSERT INTO data(name, numeric) VALUES (@insert_name, @insert_numeric)", con);
cmd.Parameters.AddWithValue("insert_name", "a");
cmd.Parameters.AddWithValue("insert_numeric", 1);
int result1 = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("insert_name", "b");
cmd.Parameters.AddWithValue("insert_numeric", 2);
int result2 = cmd.ExecuteNonQuery();

3行目のクエリ文にある「@insert_name」と「@insert_numeric」が後から値をセットする部分で「@」を除いた部分が後のAddWithValueメソットで指定するパラメータ名称です。

実際に値を追加するAddWithValueメソッドの1つ目の引数が上記の「@」を除いた名称、2つ目の引数がINSERTする値となります。

7行目のClearメソッドを実行した後に再度値をセットすれば複数のデータをINSERTする事は可能です。

SQLインジェクションを考慮した手法その2

次に紹介するのは複数のINSERTを行う時、ちょっとだけ分かりやすい（かもしれない）方法です。

最初にINSERTするパラメータを作り、そこに値をセットしてクエリ文を実行させます。

先程の例と比べると事前にパラメータを作る手間が余計なので単発なINSERTだと記述が増えますが、わかりにくい記述ではないです。

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 var cmd = new NpgsqlCommand("INSERT INTO data(name, numeric) VALUES (@insert_name, @insert_numeric)", con);
// NpgsqlCommandのParametersに新しいNpgsqlParameterを作成
cmd.Parameters.Add(new NpgsqlParameter("insert_name", DbType.String));
cmd.Parameters.Add(new NpgsqlParameter("insert_numeric", DbType.Int32));
// INSERTする値をセット
cmd.Parameters["insert_name"].Value = "b";
cmd.Parameters["insert_numeric"].Value = 2;
int result1 = cmd.ExecuteNonQuery();
cmd.Parameters["insert_name"].Value = "c";
cmd.Parameters["insert_numeric"].Value = 3;
int result2 = cmd.ExecuteNonQuery();

クエリ文は先程と同じでデータが入るパラメータ名に「@」を付けたクエリ文となります。

次にParametersのAddメソッドで新しいNpgsqlParameterを加えますが、NpgsqlParameterの1つ目の引数は上記「@」を除いた名称とするのは前に紹介した手法と同じです。

NpgsqlParameterの2つ目の引数はINSERTするデータの型を示します。

ここまでできたら最後に値をセットします。

cmd.Parameters["insert_name"].Value = "b";ですね。[]の中に「@」を除き""で囲ったパラメータ名を示し、そのValueにINSERTする値をセットします。

個人的には最後のINSERT例が一番可読性が高いと感じました。

配列へのINSERT

うっかり忘れてたので追記します。

他のデータベースは分かりませんが、PostgreSQLには配列があります。

意外と便利で使用する頻度は高いので紹介したいと思います。

// テーブル作成
using NpgsqlCommand cmd = new("CREATE TABLE data(result boolean[])", con);
// INSERTする配列
List<bool> input = new() { true, false, true };
// 標準的なINSERT
cmd.CommandText = $"INSERT INTO data(result) VALUES (ARRAY[{input[0]}, {input[1]}, {input[2]}]);";
cmd.ExecuteNonQuery();

配列のINSERTはARRAY[]の中にカンマ区切りでデータをセットします。

但し、これだと要素数が動的に変化した時クエリ文を生成するのが煩わしいです。

そこでパラメータに配列を指定してあげれば要素数を気にする必要がなくなります。

// INSERTする配列
List<bool> input = new() { true, false, true };
// 配列変数をまとめてINSERT
cmd.CommandText = $"INSERT INTO data(result) VALUES (@array)";
cmd.Parameters.Add("array", NpgsqlDbType.Array | NpgsqlDbType.Boolean).Value = input;
cmd.ExecuteNonQuery();

パラメータに加えるデータの形式として「NpgsqlDbType.Array | NpgsqlDbType.Boolean」とする事でBoolean型の配列を示します。

これで要素数を気にしないでINSERTできます。

ついでに配列を読み出してListに入れるのは

cmd.CommandText = ("SELECT result FROM data;");
using var rd = cmd.ExecuteReader();
while (rd.Read())
{
    List<bool> resut = new((bool[])rd["result"]);
}

1カラムあたりのデータ取得は1行で可能です。