LoginSignup
35
51

More than 5 years have passed since last update.

@mimitaro

C#(.NET系)からPostgreSQLへ接続(TransactionScopeや分散トランザクションについて)

Posted at

はじめに

今更ながらJavaに引き続き、色んな言語からPostgreSQLへ接続するシリーズです。
今回はC#(.NET系)です。C#はODBC接続などの方法もありますが、今回はNpgsqlによる接続を説明します。

Npgsqlとは

.NET環境からPostgreSQLへ接続するためのツールです。
今回は以下の環境でNpgsqlの導入と検証をしました。

環境 バージョン
OS Windows 10
.NET Framework 4.5
Visual Studio 2013
Npgsql 4.0.3
PostgreSQL 10.0

実際に使用する場合、.NET、Visual Studio、Npgsqlのバージョンを各々考慮する必要があります。
ただ結論を述べると注意すべき点は以下の通りです。

  • Visual Studio 2013(特に.NET 4.5.X)を使っている場合、サポートが切れている上に、古いNpgsqlだとバグがあるため注意する。
    (該当する.NET 4.5.Xに対応した最新のNpgsqlを必ず用いる)
  • 次期バージョンのNpgsql 4.1からは、.NET 4.5.2と.NET Standard 2.0がサポートされる最低バージョンとなる。
  • 最新版のVisual Studio上で、最新の.NET Frameworkを選択し、最新のNpgsqlを用いれば問題ない。

そもそも.NET Frameworkと.NET Standardって何? となった方(私はなった)や、詳細な各環境の対応を知りたい方は参考にしたサイトの.NET関連をご参照ください。

Npgsqlの導入

NpgsqlはNuGetを用いて導入可能です。以下のサイトから最新版を確認し、インストール用のコマンドをコピーします。

その後、Visual Studioを開き、メニューバーの[ツール]→[NuGetパッケージマネージャ]→[パッケージマネージャコンソール]を選択する。
するとVisual Studioの下部に、PM(パッケージマネージャ)コンソールが開くため、先程コピーしたコマンドを入力し、結果を確認する。

NuGetによるNpgsqlの導入(失敗)
PM> Install-Package Npgsql -Version 4.0.3
依存関係 'System.Runtime.CompilerServices.Unsafe (≥ 4.5.0)' の解決を試みています。
Install-Package : 'System.Runtime.CompilerServices.Unsafe 4.5.0' パッケージには NuGet クライアント バージョン '2.12' 以降が必要ですが、現在の NuGet バージョンは '2.8.60610.756' です。
発生場所 行:1 文字:1
+ Install-Package Npgsql -Version 4.0.3
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Install-Package], NuGetVersionNotSatisfiedException
    + FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PowerShell.Commands.InstallPackageCommand

今回はNuGetのバージョンが古いため失敗しました。
そのためNuGetをバージョンアップします。
メニューバーの[ツール]→[拡張機能と更新プログラム]→左メニューから[更新プログラム]の[Visutal Studioギャラリー]を選択します。
NuGetの[更新]をクリックし、インストール後、案内されるままVisutal Studioを再起動します。

バージョンアップ完了後、再びNpgsqlを入手するコマンドを実行すると以下の通り、Npgsqlの導入に成功します。

NuGetによるNpgsqlの導入(失敗)
PM> Install-Package Npgsql -Version 4.0.3
依存関係 'System.Runtime.CompilerServices.Unsafe (≥ 4.5.0)' の解決を試みています。
依存関係 'System.Threading.Tasks.Extensions (≥ 4.5.0)' の解決を試みています。
依存関係 'System.ValueTuple (≥ 4.5.0)' の解決を試みています。
'System.Runtime.CompilerServices.Unsafe 4.5.0' をインストールしています。
'System.Runtime.CompilerServices.Unsafe 4.5.0' が正常にインストールされました。
'System.Threading.Tasks.Extensions 4.5.0' をインストールしています。
'System.Threading.Tasks.Extensions 4.5.0' が正常にインストールされました。
'System.ValueTuple 4.5.0' をインストールしています。
'System.ValueTuple 4.5.0' が正常にインストールされました。
'Npgsql 4.0.3' をインストールしています。
'Npgsql 4.0.3' が正常にインストールされました。
'System.Runtime.CompilerServices.Unsafe 4.5.0' を npgsql_test に追加しています。
'System.Runtime.CompilerServices.Unsafe 4.5.0' が npgsql_test に正常に追加されました。
'System.Threading.Tasks.Extensions 4.5.0' を npgsql_test に追加しています。
'System.Threading.Tasks.Extensions 4.5.0' が npgsql_test に正常に追加されました。
'System.ValueTuple 4.5.0' を npgsql_test に追加しています。
'System.ValueTuple 4.5.0' が npgsql_test に正常に追加されました。
'Npgsql 4.0.3' を npgsql_test に追加しています。
'Npgsql 4.0.3' が npgsql_test に正常に追加されました。

Npgsqlによる単純な接続

まずは単純にPostgreSQLへ接続できるかを確認します。
Visual StudioからC#の空のプロジェクトを作り、コードを書き込んでいきます。

下準備

C#のプロジェクトを生成後、下準備として参照の追加とPostgreSQL側の設定変更をします。

参照の追加

デフォルトの参照設定ではNpgsqlの機能を使うことはできません。そのため参照を追加します。
まずVisual Studioのメニューバーの[プロジェクト]→[参照の追加]を選択します。 その後、左メニューの[アセンブリ]→[フレームワーク]を選択し、"System.Data"と"System.Transaction"にチェックし、参照へ追加します。
また単純な接続では必要ありませんが、SELECT結果取得の際に使用するDataTableのために"System.Xml"もチェックします。

PostgreSQL側の設定

C#アプリ側とPostgreSQLサーバ側が接続可能であるか、ping等で確かめます。
(pingが通らない場合、ファイアウォールやOSのネットワーク設定を見直してください)

サーバ同士の通信が可能であることが判明した後は、PostgreSQLのネットワーク設定を調整します。
PostgreSQLのデータディレクトリ(環境変数PGDATA配下)のpostgresql.confとpg_hba.confの2つの設定ファイルを次のように編集します。

postgresql.conf
listen_addresses = '*'

上記はPostgreSQL側の接続を受け付けるIPアドレスです。
デフォルトでは"localhost"のみなため、"localhost, "または全てのIPで受け付ける"*"(アスタリスク)を設定します。
詳細はマニュアルをご参照ください。

pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             <C#側のIP>/32           md5

接続を許可するクライアント側(今回の場合はC#プログラム側)の設定です。
接続先DB名や接続に用いるユーザ名も限定可能です。
また「METHOD」に"md5"を指定することでパスワード入力のみ受け付けます。
詳細はマニュアルをご参照ください。

上記設定後、PostgreSQLの設定変更を反映するためにPostgreSQLを再起動します。
※listen_addressesパラメータの反映には再起動が必要なためです。
※pg_hba.confの変更反映はreloadのみで可能です。

Npgsqlによる単純な接続の実行

準備が完了したため、早速接続を試行します。
今回作成するソースコードは次の通りです。

Class1.cs
using System;

using Npgsql;

namespace npgsql_test
{
    class Class1
    {
        static void Main(String[] args)
        {
            //接続文字列
            string conn_str = "Server=123.45.67.89;Port=5432;User ID=postgres;Database=postgres;Password=password;Enlist=true";

            using (NpgsqlConnection conn = new NpgsqlConnection(conn_str))
            {
                //PostgreSQLへ接続
                conn.Open();
                Console.WriteLine("Connection success!");
            }
        }
    }
}

※namespaceやclassは環境ごとに適切な名前にしてください。

接続文字列は以下の値を入力する必要があります。接続先の環境に合わせて設定下さい。

値 説明
Server 接続先サーバのIP or ホスト名
Port 接続先DBのポート番号
User ID DBへの接続ユーザ名
Database 接続先DB名
Password DB接続のパスワード
Enlist 既存のトランザクションへの自動参加の有効/無効を設定(Ttue/False)

上記を[ビルド]→[デバッグなしで開始]をすると、「Connection success!」の文字が標準出力されます。
エラーが発生する場合、そのエラー内容を確認し、PostgreSQLやOSの接続設定に誤りがないか確認してください。

成功時の標準出力
Connection success!

TransactionScopeの利用

C#(.NET系)にはTransactionScopeという、トランザクションを一括管理する頼もしい機能があります。
昔、Npgsql 3.0で検証した頃は、自身の環境のせいもありエラーが発生しましたが、最新版では問題なく使用できます。
C#でSQL処理をする際は頻繁に使うため、Npgsqlでも適切に利用できることを確認します。

TransactionScopeによるトランザクション管理

今回はINSERT、DELETE、SELECT処理を1つのトランザクション内で実施します。
検証に使用するテーブルは次の通りです。

検証用テーブル
postgres=# CREATE TABLE test (col1 INTEGER, col2 VARCHAR);
postgres=# \d test
                      テーブル "public.test"
  列  |        型         | 照合順序 | Null 値を許容 | デフォルト
------+-------------------+----------+---------------+------------
 col1 | integer           |          |               |
 col2 | character varying |          |               |

実際に上記のtestテーブルにSQLを実行したソースコードが以下です。

Class1.cs
using System;

using Npgsql;
using System.Transactions;
using System.Data;


namespace npgsql_test
{
    class Class1
    {
        static void Main(String[] args)
        {
            //SQL処理で用いる変数を予め宣言
            NpgsqlCommand cmd = null;
            string cmd_str = null;
            DataTable dt = null;
            NpgsqlDataAdapter da = null;

            //接続文字列
            string conn_str = "Server=123.45.67.89;Port=5432;User ID=postgres;Database=postgres;Password=password;Enlist=true";

            //TransactionScopeの利用
            using (TransactionScope ts = new TransactionScope()) 
            {
                //接続その1
                using (NpgsqlConnection conn = new NpgsqlConnection(conn_str))
                {
                    //PostgreSQLへ接続後、INSERT、DELETE処理を実施し、SELECT結果を取得
                    conn.Open();

                    //test対象のテーブルをリセット(全データDELETE)
                    cmd_str = "DELETE FROM test";
                    cmd = new NpgsqlCommand(cmd_str, conn);
                    cmd.ExecuteNonQuery();

                    //INSERT処理
                    cmd_str = "INSERT INTO test VALUES(1, 'AAA'), (2, 'BBB'), (3, 'CCC')";
                    cmd = new NpgsqlCommand(cmd_str, conn);
                    cmd.ExecuteNonQuery();

                    //DELETE処理
                    cmd_str = "DELETE FROM test WHERE col1 % 2 = 0";
                    cmd = new NpgsqlCommand(cmd_str, conn);
                    cmd.ExecuteNonQuery();

                    //SELECT処理
                    dt = new DataTable();
                    cmd_str = "SELECT * FROM test";
                    cmd = new NpgsqlCommand(cmd_str, conn);
                    da = new NpgsqlDataAdapter(cmd);
                    da.Fill(dt);

                    //SELECT結果表示
                    for (int i = 0; i < dt.Rows.Count; i++) 
                    {
                        Console.WriteLine("(col1, col2) = (" + dt.Rows[i][0] + ", " + dt.Rows[i][1] + ")");
                    }

                }
                //トランザクション完了
                ts.Complete();
            }
        }
    }
}

実行結果は以下の通りです。

成功時の標準出力
(col1, col2) = (1, AAA)
(col1, col2) = (3, CCC)

col1が1～3である行のINSERT後、偶数値の行だけ削除したため上記結果となります。
PostgreSQLのパラメータを「log_statement = 'all'」した状態で上記のC#プログラムの実行ログを見ると以下のようになります。

トランザクション処理の実行ログ
[3830] LOG:  statement: BEGIN
[3830] LOG:  statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
[3830] LOG:  execute <unnamed>: DELETE FROM test
[3830] LOG:  execute <unnamed>: INSERT INTO test VALUES(1, 'AAA'), (2, 'BBB'), (3, 'CCC')
[3830] LOG:  execute <unnamed>: DELETE FROM test WHERE col1 % 2 = 0
[3830] LOG:  execute <unnamed>: SELECT * FROM test
[3830] LOG:  statement: COMMIT
[3830] LOG:  statement: DISCARD ALL

TransactionScopeにより「BEGIN」～「COMMIT」が実行され、暗黙的にトランザクションが管理されていることが分かります。

TransactionScopeによる分散トランザクション

TransactionScopeを用いることで分散トランザクションが容易に実行可能です。
分散トランザクションは、複数のデータベースに対する処理を1つのトランザクションとして扱う技術です。

C#ではTransactionScopeを使うことで、意識することなく分散トランザクションを利用できます。
今回の検証では

  • test_db1データベースのtest1テーブルへのINSERT処理
  • test_db2データベースのtest2テーブルへのINSERT処理

を分散トランザクションとして実施します。

分散トランザクションのための下準備

下準備としてPostgreSQL側の「max_prepared_transactions」パラメータを0より大きくする必要があります。
(本パラメータは分散トランザクションにおける最大接続数を設定します。0の場合無効です)

postgresql.conf
max_prepared_transactions = 10

設定変更を反映するために、その後、PostgreSQLを再起動します。
本パラメータの詳細はマニュアルをご参照ください。

分散トランザクションの実施

準備が完了したため、以下のソースコードを記述し分散トランザクションを実行します。

Class1.cs
using System;

using Npgsql;
using System.Transactions;
using System.Data;


namespace npgsql_test
{
    class Class1
    {
        static void Main(String[] args)
        {
            //SQL処理で用いる変数を予め宣言
            NpgsqlCommand cmd = null;
            string cmd_str = null;
            DataTable dt = null;
            NpgsqlDataAdapter da = null;

            //接続文字列
            string conn_str1 = "Server=192.168.56.111;Port=5432;User ID=postgres;Database=tes_db1;Password=password;Enlist=true";
            string conn_str2 = "Server=192.168.56.111;Port=5432;User ID=postgres;Database=tes_db2;Password=password;Enlist=true";

            //TransactionScopeの利用(分散トランザクション)
            using (TransactionScope ts = new TransactionScope()) 
            {
                //接続その1(tes_db1へ接続)
                using (NpgsqlConnection conn1 = new NpgsqlConnection(conn_str1))
                {
                    //PostgreSQLへ接続後、INSERT処理を実施し、SELECT結果を取得
                    conn1.Open();

                    //test対象のテーブルをリセット(全データDELETE)
                    cmd_str = "DELETE FROM test1";
                    cmd = new NpgsqlCommand(cmd_str, conn1);
                    cmd.ExecuteNonQuery();

                    //INSERT処理
                    cmd_str = "INSERT INTO test1 VALUES(1, 'AAA'), (2, 'BBB')";
                    cmd = new NpgsqlCommand(cmd_str, conn1);
                    cmd.ExecuteNonQuery();

                    //SELECT処理
                    dt = new DataTable();
                    cmd_str = "SELECT * FROM test1";
                    cmd = new NpgsqlCommand(cmd_str, conn1);
                    da = new NpgsqlDataAdapter(cmd);
                    da.Fill(dt);

                    //SELECT結果表示
                    Console.WriteLine("tes_db1:");
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        Console.WriteLine("(col1, col2) = (" + dt.Rows[i][0] + ", " + dt.Rows[i][1] + ")");
                    }
                }

                //接続その2(tes_db2へ接続)
                using (NpgsqlConnection conn2 = new NpgsqlConnection(conn_str2))
                {
                    //PostgreSQLへ接続後、INSERT処理を実施し、SELECT結果を取得
                    conn2.Open();

                    //test対象のテーブルをリセット(全データDELETE)
                    cmd_str = "DELETE FROM test2";
                    cmd = new NpgsqlCommand(cmd_str, conn2);
                    cmd.ExecuteNonQuery();

                    //INSERT処理
                    cmd_str = "INSERT INTO test2 VALUES(3, 'ccc'), (4, 'ddd')";
                    cmd = new NpgsqlCommand(cmd_str, conn2);
                    cmd.ExecuteNonQuery();

                    //SELECT処理
                    dt = new DataTable();
                    cmd_str = "SELECT * FROM test2";
                    cmd = new NpgsqlCommand(cmd_str, conn2);
                    da = new NpgsqlDataAdapter(cmd);
                    da.Fill(dt);

                    //SELECT結果表示
                    Console.WriteLine("tes_db2:");
                    for (int i = 0; i < dt.Rows.Count; i++) 
                    {
                        Console.WriteLine("(col1, col2) = (" + dt.Rows[i][0] + ", " + dt.Rows[i][1] + ")");
                    }
                }

                //トランザクション完了
                ts.Complete();
            }
        }
    }
}

実行結果は以下の通りです。

成功時の標準出力
tes_db1:
(col1, col2) = (1, AAA)
(col1, col2) = (2, BBB)
tes_db2:
(col1, col2) = (3, ccc)
(col1, col2) = (4, ddd)

また実行ログは以下の通りです。

分散トランザクション処理の実行ログ
[5368] LOG:  statement: BEGIN
[5368] LOG:  statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
[5368] LOG:  execute <unnamed>: DELETE FROM test1
[5368] LOG:  execute <unnamed>: INSERT INTO test1 VALUES(1, 'AAA'), (2, 'BBB')
[5368] LOG:  execute <unnamed>: SELECT * FROM test1
…(中略)…
[5369] LOG:  statement: BEGIN
[5369] LOG:  statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
[5369] LOG:  execute <unnamed>: DELETE FROM test2
[5369] LOG:  execute <unnamed>: INSERT INTO test2 VALUES(3, 'ccc'), (4, 'ddd')
[5369] LOG:  execute <unnamed>: SELECT * FROM test2
[5368] LOG:  statement: PREPARE TRANSACTION '00000000-0000-0000-0000-000000000000/5368'
[5369] LOG:  statement: PREPARE TRANSACTION '00000000-0000-0000-0000-000000000000/5369'
[5368] LOG:  statement: COMMIT PREPARED '00000000-0000-0000-0000-000000000000/5368'
[5369] LOG:  statement: COMMIT PREPARED '00000000-0000-0000-0000-000000000000/5369'
[5368] LOG:  statement: DISCARD ALL
[5369] LOG:  statement: DISCARD ALL

接続その1(tes_db1)はプロセス[5368]で、接続その2(tes_db2)はプロセス[5369]で処理され、分散トランザクションが実施されています。

同一DBへ複数回接続する場合のTransactionScopeの動き

上記の通りTransacrionScopeは自動的に分散トランザクションを扱うことが可能です。
しかし、1つのトランザクション内で同一のデータベースに対して、複数回接続した場合どうなるでしょうか。

コネクタやNpgsqlのバージョンによっては、分散トランザクションに昇格してしまいますが、最近のNpgsqlではちゃんと同一接続として扱ってくれます。

実際のソースコードは次の通りです。

Class1.cs
using System;

using Npgsql;
using System.Transactions;
using System.Data;


namespace npgsql_test
{
    class Class1
    {
        static void Main(String[] args)
        {
            //SQL処理で用いる変数を予め宣言
            NpgsqlCommand cmd = null;
            string cmd_str = null;
            DataTable dt = null;
            NpgsqlDataAdapter da = null;

            //接続文字列
            string conn_str = "Server=123.45.67.89;Port=5432;User ID=postgres;Database=postgres;Password=password;Enlist=true";

            //TransactionScopeの利用
            using (TransactionScope ts = new TransactionScope()) 
            {
                //接続その1
                using (NpgsqlConnection conn1 = new NpgsqlConnection(conn_str))
                {
                    //PostgreSQLへ接続後、INSERT処理を実施
                    conn1.Open();

                    //test対象のテーブルをリセット(全データDELETE)
                    cmd_str = "DELETE FROM test";
                    cmd = new NpgsqlCommand(cmd_str, conn1);
                    cmd.ExecuteNonQuery();

                    //INSERT処理
                    cmd_str = "INSERT INTO test VALUES(1, 'AAA'), (2, 'BBB'), (3, 'CCC')";
                    cmd = new NpgsqlCommand(cmd_str, conn1);
                    cmd.ExecuteNonQuery();
                }

                //接続その2
                using (NpgsqlConnection conn2 = new NpgsqlConnection(conn_str))
                {
                    //PostgreSQLへ接続後、DELETE処理を実施し、SELECT結果を取得
                    conn2.Open();

                    //DELETE処理
                    cmd_str = "DELETE FROM test WHERE col1 % 2 = 0";
                    cmd = new NpgsqlCommand(cmd_str, conn2);
                    cmd.ExecuteNonQuery();

                    //SELECT処理
                    dt = new DataTable();
                    cmd_str = "SELECT * FROM test";
                    cmd = new NpgsqlCommand(cmd_str, conn2);
                    da = new NpgsqlDataAdapter(cmd);
                    da.Fill(dt);

                    //SELECT結果表示
                    for (int i = 0; i < dt.Rows.Count; i++) 
                    {
                        Console.WriteLine("(col1, col2) = (" + dt.Rows[i][0] + ", " + dt.Rows[i][1] + ")");
                    }
                }

                //トランザクション完了
                ts.Complete();
            }
        }
    }
}

上記のプログラムは、接続その1,2で同じDBへ接続しています(接続文字列が同一なため)。
Npgsqlは賢いため、ログを見れば分かる通り、ログインしたまま一貫したトランザクション処理をしています。
※古いNpgsql(3.2より古いバージョン?)では勝手に分散トランザクション扱いにし、エラーが発生する場合があります。

同一データベースへの複数回接続のログ
[5112] LOG:  statement: BEGIN
[5112] LOG:  statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
[5112] LOG:  execute <unnamed>: DELETE FROM test
[5112] LOG:  execute <unnamed>: INSERT INTO test VALUES(1, 'AAA'), (2, 'BBB'), (3, 'CCC')
[5112] LOG:  execute <unnamed>: DELETE FROM test WHERE col1 % 2 = 0
[5112] LOG:  execute <unnamed>: SELECT * FROM test
[5112] LOG:  statement: COMMIT
[5112] LOG:  statement: DISCARD ALL

接続1,2の処理においてプロセス番号が共に[5112]であることから分かる通り、1回の接続でそのまま処理を実行しています。
接続1,2でどちらもconnX.Open();を実行していますが、内部的には接続を使いまわしているようです。

参考にしたサイト

.NET関連

Npgsql関連

35
51
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
35
51