【VB.net】NpgsqlでPostgreSQLからSELECT結果を取得

はじめに

VB.netのコンソールアプリからNpgsqlでPostgreSQLに接続し、SELECT文の結果を取得しました。

DataTableにデータを格納してから標準出力しています。

概要

using句の中でSELECT文を実行しますが、using句の外で定義したDataTableに格納した後で標準出力します。

Npgsqlのインストール
PostgreSQLに接続
SQL結果をDataTableに格納
ソースコード全文

環境

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

1. Npgsqlのインストール

今回はオープンソースのADO.NETデータプロバイダであるNpgsqlを利用します。

ソリューションエクスプローラの[依存関係]を右クリックし、[NuGetパッケージの管理]を選択します。

表示されたNuGetパッケージマネージャでNpgsqlを検索し、[インストール]を選択することで使用可能になります。

2. PostgreSQLに接続

まずはIPアドレスやデータベース名などPostgreSQLの接続情報をNpgsqlConnectionStringBuilderに記述します。

Dim sb As New Npgsql.NpgsqlConnectionStringBuilder
With sb
    .Host = "localhost"     'ホスト名 or IPアドレス
    .Database = "testdb"    'データベース名
    .Username = "testuser"  'ユーザ名
    .Password = "password"  'パスワード
    .Port = 5432            'ポート番号
End With

上記の変数を引数にしたNpgsqlConnectionのOpen関数でPostgreSQLに接続できます。

Using conn As New NpgsqlConnection(sb.ConnectionString)
    conn.Open()
    'SQL実行処理
    '......
End Using

今回はID、NAMEで構成されたtesttableテーブルを使用します。

ID	NAME
1	testA
2	testB
3	testC

このテーブルからID=2以外のデータをSELECTするSQLを定義します。

後で値を設定するパラメータをSQL文字列に入れる際には:をパラメータの先頭に付けます。

SQL文字列を定義し、SQL内のパラメータを定義した後でSQLを実行できます。

Dim sql As String = "SELECT * FROM testtable WHERE id <> :parameter1"
Using cmd As New NpgsqlCommand(sql, conn)
    'SQL内のパラメータ設定
    cmd.Parameters.Add(New NpgsqlParameter("parameter1", NpgsqlDbType.Integer))
    cmd.Parameters("parameter1").Value = 2

    Using reader As NpgsqlDataReader = cmd.ExecuteReader()
        'SQL結果の処理
        '......
    End Using
End Using

3. SQL結果をDataTableに格納

Using句から出た場所でデータ参照を出来るようにするために、SQL結果をDataTableに格納します。

後続のSQL結果を取得する際にPostgreSQLでの名前を取得することも出来ますが、DataTableで定義したと分かるようにテスト的な名前にしています。

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

SQLの結果は1行ずつDataRowに格納したものをDataTableに取り込みます。

Using reader As NpgsqlDataReader = cmd.ExecuteReader()
    'SQL結果の処理
    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
End Using

これでDataTableにSQL結果を格納できたため、Usingの外でデータを参照できます。

'DataTableのテーブル名を標準出力
Console.WriteLine(dt.TableName.ToString)

'DataTableのヘッダ情報を標準出力
For i As Integer = 0 To dt.Columns.Count - 1
    Console.Write(dt.Columns(i).ToString & " ")
Next
Console.WriteLine()

'DataTableを標準出力
For i As Integer = 0 To dt.Rows.Count - 1
    Console.Write(dt.Rows(i).Item("vbID").ToString & " ")
    Console.Write(dt.Rows(i).Item("vbNAME").ToString)
    Console.WriteLine()
Next

実行結果は以下になります。

vbDataTable
vbID vbNAME
1 testA
3 testC

4. ソースコード全文

上記の処理に加え、エラーメッセージ出力用のTry-Catch処理やSQL結果の0件処理などを追加しています。

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

        'SQL(パラメータ付)
        Dim sql As String = "SELECT * FROM testtable WHERE id <> :parameter1"

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

        Using conn As New NpgsqlConnection(sb.ConnectionString)

            Try

                'PostgreSQLに接続
                conn.Open()

                Using cmd As New NpgsqlCommand(sql, conn)

                    'SQL内のパラメータ設定
                    cmd.Parameters.Add(New NpgsqlParameter("parameter1", NpgsqlDbType.Integer))
                    cmd.Parameters("parameter1").Value = 2

                    'SQL実行
                    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

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

        End Using

        'DataTableのテーブル名を標準出力
        Console.WriteLine(dt.TableName.ToString)

        'DataTableのヘッダ情報を標準出力
        For i As Integer = 0 To dt.Columns.Count - 1
            Console.Write(dt.Columns(i).ToString & " ")
        Next
        Console.WriteLine()

        'DataTableを標準出力
        For i As Integer = 0 To dt.Rows.Count - 1
            Console.Write(dt.Rows(i).Item("vbID").ToString & " ")
            Console.Write(dt.Rows(i).Item("vbNAME").ToString)
            Console.WriteLine()
        Next

    End Sub
End Module

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

kotememo