kotememo

【VB.net】NpgsqlでPostgreSQLにINSERTする方法

作成日：2022/10/10

はじめに

VB.netのコンソールアプリからNpgsqlでPostgreSQLに接続し、トランザクション中にデータをINSERTしました。

DataTableに抽出したテーブルのデータを加工したものをINSERTしています。

概要

SELECT文でデータを抽出し、トランザクション中に加工データをINSERTしています。

  1. トランザクション処理
  2. SQL結果をDataTableに格納
  3. 加工データをINSERT
  4. ソースコード全文

環境

  • Windows 10
  • Visual Studio 2019
  • Visual Basicコンソールアプリ(.NET Core 3.1)

1. トランザクション処理

コネクションをconnとした時、トランザクションはconn.BeginTransactionで開始できます。

トランザクション中にINSERTしたデータはCommit()するまで実際には書き込みが保存されません。

INSERT処理が全て成功した場合はCommit()で処理を確定します。

途中で失敗した場合はRollback()することでそれまでの書き込み内容を破棄します。

Dim tran As NpgsqlTransaction = Nothing

Using conn As New NpgsqlConnection(sb.ConnectionString)
    Try
        'PostgreSQLに接続
        conn.Open()

        'トランザクション開始
        tran = conn.BeginTransaction

        'SQL処理
        '......

        'コミット
        tran.Commit()
    Catch e As Exception
        'ロールバック
        If tran IsNot Nothing Then
            tran.Rollback()
        End If
    End Try
End Using

2. SQL結果をDataTableに格納

SELECT文の結果をDataTableに抽出することは以前に行っているため省略します。

今回は以下のtesttableテーブルのデータをDataTableに格納したデータを加工して同テーブルにINSERTします。

※ソースコード全文は最後に記載します。

IDNAME
1testA
2testB
3testC

3. 加工データをINSERT

まずINSERT文の中にパラメータを記述している場合は型情報をcmd.Paramters.Add()で定義します。

次にDataTableのデータを加工し、パラメータに設定します。

  • id → 値を+10
  • name → 末尾に"_ADD"を追加

cmd.ExecuteNonQuery()で1行ずつINSERTします。

最後に、tran.Commit()でデータ挿入を確定させます。

'SQL(INSERT文)
Dim sqlInsert As String = "INSERT INTO testtable (id, name) VALUES (:parameter1, :parameter2)"

Using cmd As New NpgsqlCommand(sqlInsert, conn)
    'SQL内のパラメータ型設定
    cmd.Parameters.Add(New NpgsqlParameter("parameter1", NpgsqlDbType.Integer))
    cmd.Parameters.Add(New NpgsqlParameter("parameter2", NpgsqlDbType.Varchar))

    'DataTableをINSERT
    For i As Integer = 0 To dt.Rows.Count - 1
        'SQL内のパラメータ設定
        cmd.Parameters("parameter1").Value = dt.Rows(i).Item("vbID") + 10
        cmd.Parameters("parameter2").Value = dt.Rows(i).Item("vbNAME") & "_ADD"

        'SQL実行(INSERT)
        cmd.ExecuteNonQuery()
    Next

End Using

'コミット
tran.Commit()

挿入後、改めてSELECTした結果が以下になります。

vbDataTable
vbID vbNAME
1 testA
2 testB
3 testC
11 testA_ADD
12 testB_ADD
13 testC_ADD

4. ソースコード全文

Imports System.Data
Imports Npgsql
Imports NpgsqlTypes

Module Program
    Sub Main(args As String())

        'PostgreSQL接続情報の設定
        Dim sb As New Npgsql.NpgsqlConnectionStringBuilder
        With sb
            .Host = "localhost"     'ホスト名orIPアドレス
            .Database = "testdb"    'データベース名
            .Username = "testuser"  'ユーザ名
            .Password = "password"  'パスワード
            .Port = 5432            'ポート番号
            .Timeout = 20           'タイムアウト接続時間
            .CommandTimeout = 20    'タイムアウト実行時間
        End With

        'DataTableのテーブル名・ヘッダ設定
        Dim dt As New DataTable("vbDataTable")
        dt.Columns.Add("vbID", GetType(Integer))
        dt.Columns.Add("vbNAME", GetType(String))

        'トランザクション
        Dim tran As NpgsqlTransaction = Nothing

        Using conn As New NpgsqlConnection(sb.ConnectionString)

            Try

                'PostgreSQLに接続
                conn.Open()

                'トランザクション開始
                tran = conn.BeginTransaction

                'SQL(SELECT文)
                Dim sqlSelect As String = "SELECT * FROM testtable"

                Using cmd As New NpgsqlCommand(sqlSelect, conn)

                    'SQL実行(SELECT)
                    Using reader As NpgsqlDataReader = cmd.ExecuteReader()

                        If reader.HasRows Then
                            'SELECT結果：1件以上
                            'SELECT内容抽出
                            While (reader.Read())
                                'DataRowをDataTableに格納
                                Dim dr As DataRow = dt.NewRow()
                                dr.Item("vbID") = reader.Item("id")
                                dr.Item("vbNAME") = reader.Item("name")
                                dt.Rows.Add(dr)
                            End While
                        Else
                            'SELECT結果：0件
                            Console.WriteLine("SELECT 0件")
                        End If
                    End Using

                End Using

                'SQL(INSERT文)
                Dim sqlInsert As String = "INSERT INTO testtable (id, name) VALUES (:parameter1, :parameter2)"

                Using cmd As New NpgsqlCommand(sqlInsert, conn)
                    'SQL内のパラメータ型設定
                    cmd.Parameters.Add(New NpgsqlParameter("parameter1", NpgsqlDbType.Integer))
                    cmd.Parameters.Add(New NpgsqlParameter("parameter2", NpgsqlDbType.Varchar))

                    'DataTableをINSERT
                    For i As Integer = 0 To dt.Rows.Count - 1

                        'SQL内のパラメータ設定
                        cmd.Parameters("parameter1").Value = dt.Rows(i).Item("vbID") + 10
                        cmd.Parameters("parameter2").Value = dt.Rows(i).Item("vbNAME") & "_ADD"

                        'SQL実行(INSERT)
                        cmd.ExecuteNonQuery()
                    Next

                End Using

                'コミット
                tran.Commit()

            Catch e As Exception
                'エラーメッセージ
                Console.WriteLine(e.ToString)

                'ロールバック
                If tran IsNot Nothing Then
                    tran.Rollback()
                End If
            End Try

        End Using

    End Sub
End Module