Npgsqlの基本的な使い方

新規作成日 2019-02-24

最終更新日 2019-02-24

Npgsqlの基本的な使い方

データ値をデータベースに送信するとき、あなたは、以下のように、SQLに値を含めるのではなく、パラメータを使用する必要があります。：

SQLの@pは、パラメータ・プレースホルダと呼ばれています。;Npgsqlは、コマンドのパラメーター・リストから、その名前で、パラメータを見つけることを期待します。そして、あなたのクエリと一緒に、それを送信します。これは、あなたのSQLに値を埋め込むことに勝る、次に示す利点があります。：

ユーザー提供の入力に対する、SQLインジェクションを避けます。：パラメータ・データは、SQLとは別にPostgreSQLに送信されます。そして、SQLとして、判断されることはありません。
あなたが、同じSQLを何度も実行した場合、劇的にパフォーマンスを向上させる準備されたステートメントを使用する必要があります。
パラメータデータは、SQLで文字列として表現されるのではなく、効率的なバイナリ形式で送信されます。

PostgreSQLが、任意の位置のパラメータをサポートしていないことに注意してください。－あなたは、データ値だけをパラメータ化することができます。例えば、テーブルや列の名前をパラメータ化しようとすると、失敗するでしょう。－パラメータは、あなたのSQLに、任意の文字列を貼り付けるための単純な方法ではありません。

パラメーターの型

Parameter types

PostgreSQLは、厳格に型指定された型システムを持っています。;列とパラメータは、型を持っています。そして、型は、通常、暗黙的に、他の型に変換されることはありません。これは、あなたが、送る型について考える必要があることを示しています。：整数列に文字列を挿入（またはその逆）をしようとすると、失敗します。

using (var cmd = new NpgsqlCommand("INSERT INTO table (col1) VALUES (@p)", conn))
{
    cmd.Parameters.AddWithValue("p", "some_value");
    cmd.ExecuteNonQuery();
}

上の例では：私たちは、Npgsqlに、.NET型からPostgreSQLデータ型を推論させます。：Npgsqlが、.NET文字列を見るとき、それは、自動的に、テキスト型のパラメータを送信します。（これが、varcharと同じではないことに注意してください）。多くの場合、これは、うまく動作し、あなたが、心配する必要はありません。いくつかの場合では、しかしながら、あなたは、明示的に、パラメーター型を設定する必要があります。例えば、Npgsqlは、.NET DateTimeをタイム・ゾーンなしのタイムスタンプとして送信します。しかし、あなたは、代わりに、.NETには、直接、対応しないPostgreSQLの日付を送信したいと思うかもしれません。サポートされている型とそのマッピングの詳細については、このページを確認して下さい。

NpgsqlParameterは、あなたが、パラメータのデータ型を指定できる、いくつかのプロパティを公開しています。：

DbType

DbTypeは、データベース型を指定するために、使用することができる、移動可能な列挙型です。この方法では、データベース間で、移植できるコードを書くことができますが、それは、あなたに言うまでもなく、PostgreSQLのための特別な型を指定できません。

NpgsqlDbType

NpgsqlDbTypeは、Npgsqlによってサポートされる、（ほとんど）すべてのPostgreSQL型が、含まれている、Npgsql固有の列挙型です。

DataTypeName

DataTypeNameは、パラメータで、PostgreSQL型の名前を直接設定できる、Npgsql固有の文字列プロパティです。これが、めったに、必要でありません。－NpgsqlDbTypeは、ほとんどの場合、適切である必要があります。しかしながら、あなたが、マッピングされていないユーザー定義型（列挙型や合成型）や（外部プラグインによってサポートされるため）NpgsqlDbTypeには含まれていない一部のPostgreSQL型を使用している場合、それは、役に立つ場合があります。

厳格に型指定されたパラメータ

Strongly-typed parameters

標準のADO.NETパラメータAPIは、残念なことに、弱く型付けされています。：パラメーター値は、intのような、ボックス値型のオブジェクトのNpgsqlParameter.Value上で設定されています。あなたが、たくさんの値型をデータベースに送信している場合、これは、大量の無駄なヒープ割り当てを作成し、ガベージ・コレクタに負担をかけます。

代わりに、あなたは、NpgsqlParameterを使用することができます。このジェネリック・クラスは、NpgsqlParameter.Valueと類似している、TypedValueメンバーを持っていますが、厳格に型指定されており、このように、ボクシングとヒープ割り付けを避けます。この厳密に型指定されたパラメータAPIは、完全にNpgsqlに固有のものであることに注意して下さい。そして、他のデータベースに移植できない、あなたのコードを作成するでしょう。これをADO.NETレベルで議論している問題については、#8955を参照してください。

ストアド関数と手順

Stored functions and procedures

PostgreSQLは、ストアド（あるいはサーバ・サイド）関数をサポートしています。そして、PostgreSQL 11以降は、ストアド・プロシージャです。これらは、（ビューと同じように）SQLで、あるいは、PL/pgSQL（PostgreSQLの手続き型言語）、PL/Pythonやいくつかの他のサーバー側の言語で、記述することができます。

一旦、関数や手順が定義された場合、それを呼び出すことは、通常のコマンドを実行する単純な内容です。：

// For functions
using (var cmd = new NpgsqlCommand("SELECT my_func(1, 2)", conn))
using (var reader = cmd.ExecuteReader()) { ... }

// For procedures
using (var cmd = new NpgsqlCommand("CALL my_proc(1, 2)", conn))
using (var reader = cmd.ExecuteReader()) { ... }

あなたは、上のパラメーター値を、通常のクエリと同じ、標準のプレースホルダ（例えば@p1）、に置き換えることができます。

いくつかの他のデータベースでは、ストアド・プロシージャを呼び出すことは、コマンドの動作を設定することを含んでいます。：

using (var cmd = new NpgsqlCommand("my_func", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("p1", "some_value");
    using (var reader = cmd.ExecuteReader()) { ... }
}

Npgsqlは、これを主に移植性のためにサポートしていますが、このスタイルの呼び出しは、上記の通常のコマンドを超える利点はありません。CommandType.StoredProcedureが、設定されるとき、Npgsqlは、単純に、あなたのための適切なSELECT my_func()だけを作成します。あなたが、特定の移植性要件を持っていない限り、それは、あなたに、単純に、CommandType.StoredProcedureを避け、そして、自分でSQLを構築することをお勧めします。

CommandType.StoredProcedureが、設定され、そして、あなたのインスタンスが名前を持っている場合、ことに注意して下さい。Npgsqlは、名前の付いた表記で、パラメータを生成します。：SELECT my_func(p1 => 'some_value').これは、あなたのNpgsqlParameter名が、あなたのPostgreSQL 関数パラメータと一致する必要がある、あるいは、関数呼び出しが、失敗することを示しています。あなたが、あなたのNpgsqlParametersの名前を省略する場合、代わりに、位置表記が使用されます。詳しくは、PostgreSQLのドキュメントを参照してください。

CommandType.StoredProcedureが、関数に適切な、そして、プロシージャに適したCALLコマンドではなく、SELECTコマンドを作成することに注意してください。Npgsqlは、ストアド・プロシージャが導入されるずっと前から、このように動作しました。そして、この動作を変更すると、多くのアプリケーションの下位互換性が損なわれます。CommandBehavior.StoredProcedureを設定することなく、ストアド・プロシージャを呼び出す唯一の方法は、あなたの自身のCALL my_proc(...)コマンドを書くことです。

In/outパラメータ

In/out parameters

SQL Server（とおそらく他のデータベース）では、関数は、出力パラメータ、入出力パラメータ、および戻り値を持つことができ、それは、スカラーやテーブル（TVF）のどちらかです。特別なパラメータ型を持つ関数を呼び出すために、Directionプロパティは、適切に、DbParameter上で設定されている必要があります。PostgreSQL関数は、一方では、常に、一つのテーブルを返します。－それらは、すべてTVFと見なすことができます。多少紛らわしいことに、PostgreSQLは、あなたの関数が、入力/出力パラメータで定義できます：

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

しかしながら、上記の構文は、関数の結果セットの定義に過ぎません。そして、以下と同じです。（PostgreSQLドキュメントを参照）：

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

言い換えると、PostgreSQL関数、出力パラメータを持っていません。それは、それらが返す結果セットとは異なっています。－出力パラメータは、その結果セットを記述するための単なる構文です。このため、Npgsql側で、出力（または入出力）パラメータについて考える必要は、ありません。：通常の結果セットと同じように、関数を呼び出してその結果セットを処理するだけです。

しかしながら、移植性を高めるために、Npgsqlは、以下のように、出力パラメータのためのサポートを提供します。

using (var cmd = new NpgsqlCommand("SELECT my_func()", conn))
{
    cmd.Parameters.Add(new NpgsqlParameter("p_out", DbType.String) { Direction = ParameterDirection.Output });
    cmd.ExecuteNonQuery();
    Console.WriteLine(c.Parameters[0].Value);
}

Npgsqlが、ParameterDirection.Output（またはInputOutput）でパラメータを見るとき、それは、単純に、名前がパラメータと一致する列のために、関数の結果セットを検索します。そして、出力パラメータに、1行目の値をコピーします。結果セットを自分で処理することには、全く価値がなく、推奨されていません。－あなたは、Npgsqlでは、出力パラメータのみを使うべきです。あなたが、他のデータベースとの移植性を維持する必要がある場合、それが必要です。

ページトップへ Npgsql4ドキュメント Back Next