C#.NETでPostgreSQLのストアドOUTパラメータを受け取りたい

###前提・実現したいこと
WindowsフォームアプリケーションよりNpgsql経由でDBへアクセスする際、あらかじめ作ったストアドのOUTパラメータを受け取ることができません（コマンドプロンプトで実行するとうまく結果を受け取れます）。

フォーム側でSQL直打ちすれば結果は受け取れるのですが、使えるのであればストアドにしたいと思っています。C#.NET、PostgreSQLどちらも日が浅く、根本的な勘違い（無知）かもしれませんが、ご教示ください。

###発生している問題・エラーメッセージ

Field not found in row:username

###該当のソースコード

作成したストアド
create or replace function db_login(in varchar, in varchar, out varchar, out varchar) as
'select username, userstatus from 表名 where userid = $1 and password = $2' language 'sql';

上記を実行
select db_login('hoge','fuga');
db_login
-----------
("ほげさん", active)
(1行)

フォーム側
Using (NpgsqlConnection con = new NpgsqlConnection(接続文字列))
{
 using (NpgsqlCommand cmd = new NpgsqlCommand("",con))
 {
   useridとpasswordをNpgsqlParameterへ格納

  // これだとうまくいきます
  cmd.commandText="select username, userstatus from 表名 where userid = :userid and password = :password";

  // これがダメ
  cmd.commandText="select db_login(:userid, :password)";

  以下、NpgsqlDataReaderで値の受け取り部分で「Field not found in row:username」

 }
}

###試したこと
NpgsqlParameterにOUTパラメーターを作ろうとした
→ParameterDirectionがNpgsqlではなくSystem.Dataにしか表示されない

CommandTypeをStoredProcedureにした
→cmd.ExecuteNonQuery()で{42601:syntax error at or near "select""}

###補足情報(言語/FW/ツール等のバージョンなど)
PostgreSQL(Windows 32bit 9.6.3)

hihijiji

2017/09/02 10:22

Npgsqlは使ってないのでヒントだけです。 useridとpasswordは接続文字列に、NpgsqlParameterはストアドに渡すパラメータ、CommandTypeをStoredProcedureにした→正解、new NpgsqlCommand("",con)→new NpgsqlCommand("[ストアド名]",con)

dotnet

2017/09/02 10:51

hihijiji さま　お世話になります。useridとpasswordはDBユーザーではなく、ユーザー表を作って管理しているuseridとpasswordで、ストアドのパラメーター(input)にあたります。 CommandType=StoredProcedureにしてCommandText="db_login()";　ですと 42601: syntax error at or near "("　となり、CommandText="db_login(:p_userid,:p_password)";　ですと 42601: syntaxerror at or near ":"　となりました。

行動規範の内容に同意します

回答4件

自分がfunctionの仕様を理解しきれていなかったことによる自爆で（language→sql、plpgsql）、Oracleと同じように書いたらすんなり期待通りの結果が得られました。長くなり恐縮ですが、省略せずに試したことを記載します。

以下、自己検証です。全言語において独習者ですので「おいおい」といった箇所があれば是非教えてください。

--テスト用テーブル
create table test_table(
	userid varchar(20),
	password varchar(20),
	usertext varchar(20),
	status varchar(20),
	constraint pk_test_table primary key(userid));

--ダミーデータ
insert into test_table(userid,password,usertext,status)
values('hoge','fuga','hogeさん','active');

--ストアド
create or replace function testFunction(in p_userid varchar,in p_password varchar,out v_usertext varchar,out v_status varchar) as $$
begin
	select usertext,status into v_usertext,v_status from test_table where userid=p_userid and password=p_password;
end; $$ language plpgsql;

//C#側 ユーザー名:textBox1.text パスワード:textBox2.text 処理開始:button1
private void button1_Click(object sender, EventArgs e)
{
	using (NpgsqlConnection con = new NpgsqlConnection("接続文字列"))
        {
            using (NpgsqlCommand cmd = new NpgsqlCommand("testFunction",con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                NpgsqlParameter p_userid = 
				cmd.Parameters.Add("p_userid", NpgsqlDbType.Varchar);
                p_userid.Direction = ParameterDirection.Input;
                p_userid.Value = this.textBox1.Text;

                NpgsqlParameter p_passwod = 
				cmd.Parameters.Add("p_password", NpgsqlDbType.Varchar);
                p_passwod.Direction = ParameterDirection.Input;
                p_passwod.Value = this.textBox2.Text;

                NpgsqlParameter v_usertext = 
				cmd.Parameters.Add("v_usertext", NpgsqlDbType.Varchar);
                v_usertext.Direction = ParameterDirection.Output;

                NpgsqlParameter v_status = 
				cmd.Parameters.Add("v_status", NpgsqlDbType.Varchar);
                v_status.Direction = ParameterDirection.Output;

                try
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
                catch(NpgsqlException ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                    return;
                }
                MessageBox.Show(
			"ユーザー名：" + v_usertext.Value.ToString() + "\r\n" +
			"ステータス：" + v_status.Value.ToString());
        }
    }
}

引数 textBox1.text hoge, textBox2.text fuga

実行結果
ユーザー名：hogeさん
ステータス：active

ベストアンサーについては
（１）ベストアンサー選択＝解決済みであることを知らずにクリックしてしまった
（２）当初の疑問が解決されたという認識はありませんでしたが、language 'sql' の場合はこう使えばよかったのか！への謝意

このようにご理解いただければ幸いです。ご回答・コメントくださった皆々さま、本当にありがとうございました。

投稿2017/09/05 04:20

編集2017/09/05 05:54

dotnet

総合スコア25

PostgreSQL / Npgsql には触ったこともないのでハズレかもしれませんが、SQL Server / SqlClient と似たようなものだと想像してレスします。

あまり適当な例ではないかもしれませんが、以下の記事の「ストアドプロシージャ」のコードと「aspx ファイル」の CreateDataSource メソッドを見てください。

カスタムページャー
http://surferonwww.info/BlogEngine/post/2010/08/25/Custom-pager.aspx

ストアドは引数で pageNum と pageSize を入力とし、pageCount を OUTPUT に設定しています。

ストアドの中では、入力の pageNum と pageSize を条件に使って、SELECT * ... FROM [Products] ... というクエリで Products テーブルからデータを抽出しています。

さらに、SET @pageCount = CEILING(@rowCount / @pageSize) でページ番号を計算して OUTPUT として渡しています。質問者さんのコードにはこれと同等なコードがないです。

ADO.NET の C# のコードでは pageNum, pageSize, pageCount はパラメータとして設定します。質問者さんのコードにはパラメータの設定がないです。

sqlCom.Parameters.AddWithValue("@pageNum", pageNum);
sqlCom.Parameters.AddWithValue("@pageSize", pageSize);
SqlParameter param = new SqlParameter("@pageCount", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
sqlCom.Parameters.Add(param);

ストアドで OUTPUT として設定した pageCount は、ストアド実行後、以下のようにして取得できます。

pn1.Count = (int)param.Value;
pn2.Count = (int)param.Value;

そのあたりの方法は、たぶん PostgreSQL / Npgsql でも同じだと思います。

＃質問者さんは全くの初学者だとお見受けしますが、であれば、サンプルがはるかに多い SQL Server / SqlClient で ADO.NET の勉強を先にして、ある程度基本的な知識をつけるべきと思うのですが。質問にアップされていたストアドの書き方、ADO.NET + Npgsql を使った C# のコードの書き方がメチャクチャなような気がします。（SQL Server であれば問題外です）

投稿2017/09/03 04:30

退会済みユーザー

総合スコア0

dotnet

2017/09/03 15:37 編集

SurferOnWww さま後出しになってしまい本当に申し訳ありません。「useridとpasswordをNpgsqlParameterへ格納」の個所で変数を宣言しています。 NpgsqlParameter p_userid = cmd.Parameters.Add("p_userid",NpgsqlDbType.Varchar); p_userid.Direction = ParameterDirection.Input; p_userid.Value = "hoge"; （以下略）変数の宣言に問題があれば変数の数が違うとか名前が違うとか怒られるはずと思い込んでいたので、原因は変数ではないだろうと省略してしまいました。。。おっしゃるとおりの初学者ですので、これから勉強していこうと思います。ありがとうございました！

退会済みユーザー

2017/09/03 17:01 編集

それで結局解決したのですか？　ベストアンサーを付けているということは解決したのかもと思ってましたが、よく読むとそうではないように思えるのですが？　根本的に解決しないまま終わりにするというのはいかがなものかと思うのですが。

sazi

2017/09/04 09:01

解決済みになったので、まだ質問があるなら新規の質問でという誘導をしたのですが、この場合はこのままで継続の方がよいのでしょうか？質問したことがないので分かりませんけども、解決済みやベストアンサーが外せるなら、PostgreSQL / Npgsqlの組み合わせでの回答が付くかもしれないので、そっちの方が良いのでしょうか？後学の為、お手数でなければ、コメント下されば嬉しいです。

退会済みユーザー

2017/09/04 09:37

表題の「.NETでPostgreSQLのストアドOUTパラメータを受け取りたい」は解決してないと思いますので、自分としてはそこが解決できるまで継続すべきと思うのですが、継続するか否かは質問者さん次第で、質問者さんが続ける気がない（と言うより、できれば何でも OK ということで終わりにしたように見えますが）のであれば自分としては何ともなりません。

sazi

2017/09/04 15:17

コメントありがとうございます。質問者様がコメントで質問されているから、続ける気が無いわけではないと思いますが、そのコメントは解決済みにされた後でした。私が回答できればいいのですが、何分力不足で。記事としての価値を高めるなら、別途質問してこの質問に結果をフィードバックしてもらう位しか思いつかないですけど。

dotnet

2017/09/05 04:19

SurferOnWww　さま、sazi　さまお世話になります。先ほどまで端末を触れず亀レスで恐縮です。それぞれご回答いただいただけでなく、論議を巻き起こしてしまったようで申し訳ありません。その後の顛末を回答に記載しました。今後ともご指導よろしくお願いします。

退会済みユーザー

2017/09/06 11:06

あなたの言う「論議を巻き起こし」というのはどういう意味ですか？

行動規範の内容に同意します

ベストアンサー

 cmd.commandText="select db_login(:userid, :password)";

上記だと戻りは'(xxx,xxx)'のように値の列挙で返却されるだけです。
db_login()のOUTパラメータには名称が無いので、名称で参照されるなら名称を付ける必要があります。
※Npgsqlで$1,$2のように順序で取得する方法があるかは分かりません。
ストアドを変更しない場合は以下のようになります。

 cmd.commandText="select * from db_login(:userid, :password) as w(username, userstatus)";

※*で取得できないようであれば、カラム名でselectしてみて下さい。

ただ、今後の拡張があれば別ですが、それが無いならこんなに単純な処理をわざわざストアドにしなくても良い気がしますが。

投稿2017/09/02 13:05

編集2017/09/03 00:18

sazi

総合スコア25173

dotnet

2017/09/03 15:26 編集

sazi さまお世話になります。休日で実機が手元になく、自宅の適当なPCで環境を作っていたので返信が遅くなり申し訳ありませんでした。 cmd.commandText="select * from db_login(:userid, :password) as w(username, userstatus)"; 有難うございます。これで解決しました！ psqlで実行するときは select db_login(:p_userid,:p_password); で欲しいものが返ってきていたので、そこで思考停止してしまっていました。少しだけOracleを触っていたので、fromの無いselectとか、今回のストアドとか、色々と衝撃を受けているところです。なんとかC#からストアドを呼ぶ方法を習得したいのですが、元々のコードでsyntax errorになったのはなぜかご教示頂けると幸いです。宜しくお願い致します。