vb.net select文実行後のinsert文で失敗する

Question

win7(64bit)、vb2017(community版)、postgresql6.3、npgsqlを使っています。
select文を実行し、データがなければinsert文を実行するというコードを書いています。

        Dim cn As New NpgsqlConnection
        Dim scmd As New NpgsqlCommand
        Dim icmd As New NpgsqlCommand
        Dim dr As NpgsqlDataReader
        Dim trn As NpgsqlTransaction
        Dim intCount As Int32
        Dim strSSQL As String
        Dim strISQL As String
        Dim strDateTime As String
        Try
            cn.ConnectionString = 接続文字列
            cn.Open()
            trn = cn.BeginTransaction()
            strSSQL = セレクト文
            scmd.Connection = cn
            scmd.CommandText = strSSQL
            dr = scmd.ExecuteReader()
            If (dr.HasRows = False) Then
                strISQL = インサート文
                icmd.CommandText = strISQL
                intCount = icmd.ExecuteNonQuery
                trn.Commit()
            Else
                trn.Rollback()
            End If
        Catch ex As Exception
            trn.Rollback()
            strDateTime = Format(Now, "yyyy-MM-dd hh:mm:ss")
            Call sWriteErrorLog(strDateTime, "DB選択", ex.ToString)
        Finally
            cn.Close()
            dr = Nothing
            scmd = Nothing
            icmd = Nothing
            cn = Nothing
        End Try

このコードではintCount = icmd.ExecuteNonQueryでエラーになりex.tostringには沢山書かれていますが、先頭に「System.InvalidOperationException: Connection property has not been initialized.」と書かれています。
cnは初期化したつもりはないのですが。。。

それと何故かcatchに行った後trn.Rollback()でエラーになります。
「A command is already in progress」
例外処理でrollbackはできないのでしょうか？

上記2つのことをお聞きしたいです。

ちなみにselect文とinsert文はツールのA5:SQL Mk-2で実行して問題なく実行できます。

よろしくお願いします。

教えていただいた情報を読みコードを書いてみました。

        Using cn As NpgsqlConnection = New NpgsqlConnection()
            cn.ConnectionString = 接続文字列
            cn.Open()
            Dim strSelectSQL As String = セレクト文
            Dim nSelectCmd As NpgsqlCommand = New NpgsqlCommand(strSelectSQL, cn)
            Dim dr As NpgsqlDataReader = nSelectCmd.ExecuteReader
            If (dr.HasRows = False) Then
                'Dim trn As NpgsqlTransaction = cn.BeginTransaction()
                Try
                    Dim strInsertSQL As String = インサート文
                    Dim nInsertCmd As NpgsqlCommand = New NpgsqlCommand(strInsertSQL, cn)
                    Dim intCount As Int32 = nInsertCmd.ExecuteNonQuery()
                    'trn.Commit()
                    nInsertCmd.Dispose()
                Catch ex As Exception
                    'trn.Rollback()
                    Dim strDateTime As String = Format(Now, "yyyy-MM-dd hh:mm:ss")
                    Call sWriteErrorLog(strDateTime, "DB選択", ex.ToString)
                Finally
                    dr.Dispose()
                    nSelectCmd.Dispose()
                End Try
            End If
        End Using

コメントアウトしていますが、Dim trn As NpgsqlTransaction = cn.BeginTransaction()でエラーになります。

またDim intCount As Int32 = nInsertCmd.ExecuteNonQuery()でcatchに行き以下のようなエラーが出ます。

「"A command is already in progress: セレクトのSQL文"」

vb.netが初心者に近いので解らないのですが。

何が良くないのでしょうか？
なぜトランザクションがエラーになるのでしょうか？

Answer 1

Tak1016さんの回答が正しそうなので、あえて違う観点からコメントしてみます。

Npgsqlライブラリがこの構文をサポートしているか分かりませんが、まずは1つのSQLで実現できないかを考えた方がいいと思います。PostgreSQLであれば以下のようなSQLで実現できます。

INSERT INTO example_table
    (id, name)
SELECT 1, 'John'
WHERE
    NOT EXISTS (
        SELECT id FROM example_table WHERE id = 1
    );

Postgres: INSERT if does not exist already

また、「selectしてinsertする関数」はもちろん作れますが、テストの観点からは「①sqlを実行する関数」「②select()でレコード件数を返却する関数」、「③insertする関数」、「④select()とinsertの2つの関数を呼び出す関数」等に分けた方がスッキリするかもしれません。①に対して②、③はクエリ(オブジェクト)を引数として渡すイメージです。これはあくまで一例ですが、この他にも色々なところにSQLを実行するコードが散財してそうかな...?と思いまして。

Comments

[chintao1224]

たしかにSQL文の書き方でできますね。勉強になりました。ありがとうございました。

Answer 2

色々問題あるように思えます。

まず、connectionをOpenしたら必ずCloseしないといけないです。
ということはOpenしていなければCloseしてはいけません。

提示されたコードを見ると、Openに失敗したらどうなりますか？
Catchでロールバックをしますが、そもそもBeginTransactionもされていないのでRollBackできませんね。
FinallyでCloseしますが、OpenしてないのでCloseもできませんね。

1 Usingを使ってConnectionをOpenすればCloseを明示しなくても勝手にCloseします。
2 BeginTransactionが成功した場合、TryCatchでRollBackしましょう。

あとは
SQlCommandもReaderもConnectionもIDisposableを実装しているクラスは必ずDispose()しないとだめです。
Usingを使ってDisposeを保障してください。
Classにカーソルを当ててF12を押して定義をさかのぼってIDisposableを宣言しているかどうか確認しながら実装してください。

Comments

[chintao1224]

ソースを変更して実行したのですが、だめでした。よろしくお願いします。

[chintao1224]

vb.netではselectしてinsertするなんて関数作れないのでしょうか？

[Tak1016]

存在チェックのためのSQLであれば

select count(*) from table where 条件

ですよね。

ExecuteReaderではなくてExecuteScalarでカウントを取得すればいいのでは？
勘だけどReaderをDisposeしてないのにInsertしてるから怒られてるんじゃないの？
あとFinallyでDisposeするのはかなり古いです。Connection同様全部Usingで囲ってください。

[chintao1224]

遅くなりまして申し訳ございません。usingを使って何とかできました。ありがとうございました。