はじめに

　VB.NETでpostgreSQLに接続する共通クラスを作りました。ググれば一発と思っていたのですが、使い方まで含めてまとまっている情報が見当たらなかったので、自分の理解を深めるためにも記事を書きました。ご指摘があればぜひお願いいたします。
　前提として、接続するRDBMSに応じたプロバイダをNugetなどでインストールする必要があります。今回はpostgreSQLに接続するのでNpgsqlをインストールしています。（SQLServerを使えば下準備がいらなくて便利です。）

環境	バージョン
OS	Windows 10
.NET Framework	4.7.2
Visual Studio	2019
PostgreSQL	13.1
Npgsql	5.0.2

DB接続共通クラス

　DB接続クラスのソースコード全文です。使い方は後述します。

dbConnection.vb

Imports Npgsql

Public Class dbConnection

    '確実に接続を閉じるための呪文
    'これを記述するだけでデストラクタが自動生成される
    Implements IDisposable

    'デストラクト済みか否かのフラグ
    Private disposedValue As Boolean '自動生成コード

    Private Property conStr As String = (接続文字列)
    Private Property sqlCon As NpgsqlConnection
    Private Property sqlTrn As NpgsqlTransaction
    Private Property sqlCmd As NpgsqlCommand
    Private Property sqlAdp As NpgsqlDataAdapter

    'コンストラクタ
    'クラスをインスタンス化した時、DB接続を開始する
    Public Sub New()
        Me.open()
    End Sub

    'DB接続を開始
    Public Sub open()
        If sqlCon Is Nothing Then
            sqlCon = New NpgsqlConnection(conStr)
            sqlCon.Open()
        End If
    End Sub

    '全てのオブジェクトを破棄し、DB接続を終了
    Public Sub close()
        If Not sqlAdp Is Nothing Then
            sqladp.Dispose()
            sqladp = Nothing
        End If
        If Not sqlCmd Is Nothing Then
            sqlCmd.Dispose()
            sqlCmd = Nothing
        End If
        If Not sqlTrn Is Nothing Then
            sqlTrn.Dispose()
            sqlTrn = Nothing
        End If
        If Not sqlCon Is Nothing Then
            sqlCon.Close()
            sqlCon.Dispose()
            sqlCon = Nothing
        End If
    End Sub

    'トランザクション開始
    Public Sub trnStart()
        If sqlTrn Is Nothing Then
            sqlTrn = sqlCon.BeginTransaction
        End If
    End Sub

    'トランザクションコミット
    Public Sub commit()
        If Not sqlTrn Is Nothing Then
            sqlTrn.Commit()
        End If
    End Sub

    'トランザクションロールバック
    Public Sub rollback()
        If Not sqlTrn Is Nothing Then
            sqlTrn.Rollback()
        End If
    End Sub

    ''' <summary>
    ''' トランザクションを伴わないSQLを実行(主にSELECT文)
    ''' </summary>
    ''' <param name="sql"></param>
    ''' <returns>Datatable</returns>
    Public Function getDtSql(sql As String) As DataTable
        '結果を格納するDataTableを宣言
        Dim returnDt As New DataTable

        Try
            sqlCmd = New NpgsqlCommand(sql, sqlCon)
            sqlAdp = New NpgsqlDataAdapter(sqlCmd)
            sqlAdp.Fill(returnDt)
        Catch ex As Exception
            Throw
        End Try

        Return returnDt

    End Function

    ''' <summary>
    ''' トランザクションを伴うSQLを実行(主にINSERT,UPDATE,DELETE文)
    ''' </summary>
    ''' <param name="sql"></param>
    Public Sub executeSql(sql As String)

        Try
            sqlCmd = New NpgsqlCommand(sql, sqlCon, sqlTrn)
            sqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw
        End Try

    End Sub

    '以下ほぼ自動生成コード
    Protected Overridable Sub Dispose(disposing As Boolean)
        '重複してデストラクタを実行しないためのIfステートメント
        'この中身の処理だけ自分で書く
        If Not disposedValue Then
            Me.close() 'クラスのインスタンスを破棄するとき、DB接続を終了する
            disposedValue = True
        End If
    End Sub

    Protected Overrides Sub Finalize()
        Dispose(disposing:=False)
        MyBase.Finalize()
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(disposing:=True)
        GC.SuppressFinalize(Me)
    End Sub
End Class

補足

コンストラクタとデストラクタ

　コンストラクタとImplements IDisposableで自動生成されるコード＝デストラクタは、このクラスをインスタンス化して使用する際に重要なメソッドです。簡単にまとまっている記事がありましたので、ご紹介します。

VB基本まとめ~クラス(インスタンス・コンストラクタ・デストラクタ)編~

　自動生成されるコードは初学者には意味がよくわからないかもしれません（筆者も完全には理解できていません）が、インスタンス化した際にUsingを利用するために必要なものです。確実に終了処理を行うためのコードと、重複して終了処理を行ってしまわないようにするフラグが生成されているという具合です。他のメソッド内にあるIf sql... Is Nothing Then...の部分も、重複して同じオブジェクトを生成しないようにするための保険です。

接続文字列の設定

接続文字列の設定に関してはコードに直接入力してもいいですが、Web.configに設定する方法もあります。同じ名前で異なる環境の接続文字列を複数用意すれば、コメントアウトで簡単に切り替えられて便利です。

web.config

<connectionStrings>
<!--メイン環境接続文字列-->
<!--<add name="ConStr" connectionString="Persist Security Info=True;Password=********;Username=********;Database=MainDB;Host=localhost" providerName="Npgsql"/>-->
<!--サブ環境接続文字列-->
    <add name="ConStr" connectionString="Persist Security Info=True;Password=********;Username=********;Database=SubDB;Host=localhost" providerName="Npgsql"/>
</connectionStrings>

このように設定した場合、共通クラスでの呼び出し方は下記のとおりです。

dbConnection.vb


Private Property conStr As String 
conStr = ConfigurationManager.ConnectionStrings("ConStr").ConnectionString

postgreSQL以外のRDBMSへの対応

　Npgsqlに依存している部分を置き換えれば、他のRDBMSに接続して同様の処理ができます。SQLServerであればSystem.Data.SqlClientをインポートすれば対応したメソッドが使えます。

使い方

　※SQLの生成方法に関しては、SQLインジェクション対策や実行速度などを考慮すると奥が深すぎるのでこの記事では取り扱わず、参考記事をご紹介します。

　SQLを生成出来たら、以下のコードでDB操作を実行します。

dbSample.vb


'SQLを格納する変数
Dim sql As String
'SELECT結果を格納したDataTableを受取る変数
Dim dt As DataTable

'getDtSqlの場合
Using db As New dbConnection()

    dt = db.getDtSql(sql)

End Using

'executeSqlの場合
Using db As New dbConnection()

    Try
        db.trnStart()
        db.executeSql(sql)
        db.commit()
    Catch ex As Exception
        db.rollback()
        Throw
    End Try

End Using

　共通クラスを作っておいたおかげでかなりスッキリ書けました。Using...End Usingを利用することで、DB接続の開始と終了を忘れたり重複させてしまう心配がなく簡潔に書くことができます。デバッグモードで実行してステップインしていくとわかりやすいですが、Using...New...でコンストラクタが、End Usingでデストラクタが自動的に起動します。

非推奨な使い方

コンストラクタ・デストラクタを使わない場合

　書けることは書けますが、行数が多くなりますし接続の開始や終了を忘れるリスクがあります。一応例をあげておきます(dbConnection.vbにコンストラクタ・デストラクタがない場合を想定ください)。

dbSample2.vb


Dim sql As String
Dim dt As DataTable
Dim db As New dbConnection()

'getDtSqlの場合
Try
    db.open()
    dt = db.getDtSql(sql)
Catch ex As Exception
    Throw
Finally
    db.close()
End Try

'executeSqlの場合
Try
    db.open()
    db.trnStart()
    db.executeSql(sql)
    db.commit()
Catch ex As Exception
    db.rollback()
    Throw
Finally
    db.close()
End Try

　はい。記述量が多くて共通クラスを作った意味が薄いですね。
　ここで、“DB接続やトランザクション関連の処理をSQL実行メソッドに組み込めば、記述量を減らせるのでは”と思った方、以前の私と同じ考えです。ただ、それでは色々と不都合があります。executeSqlメソッドで例を示します。

dbConnection2.vb


Public Sub executeSql(sql As String)

    Try
        Me.open()
        Me.trnStart()
        sqlCmd = New NpgsqlCommand(sql, sqlCon, sqlTrn)
        sqlCmd.ExecuteNonQuery()
    Catch ex As Exception
        Me.rollback()
        Throw
    Finally
        Me.close()
    End Try

End Sub

　このメソッドは、複数のSQLを一度のトランザクションで実行したい場合には使えません。メソッドを実行するたびにコネクションを生成しては破棄することを繰り返してしまうのでオーバーヘッドも大きいですし、任意のタイミングでのコミットやロールバックができないので、例えばUPDATEとINSERTの両方が成功して初めてコミットしたいような場合に使えません。このようなコードは共通クラスとして不適切なので、避けましょう。

SQLにパラメータを含める場合

　動的なSQLを生成する場合、セキュリティの観点から変数を直接使ったりはせずにパラメータを埋め込む方法が安全です。その場合、SQL実行メソッドを調整する必要があります。
　※このコードはおそらく洗練されていないので、改善点などご指摘いただければ嬉しいです。型の変換を明示的に行わないとDBがバグを吐くので、パラメーターの型が異なると同じメソッドを使いまわせません。

dbConnection3.vb


''' <summary>
''' SQL実行_SELECT
''' String型のParameter付き
''' </summary>
''' <param name="sql"></param>
''' <param name="prmName1"></param>
''' <param name="prmName2"></param>
''' <param name="prmVal1"></param>
''' <param name="prmVal2"></param>
''' <returns>Datatable</returns>
Public Function getDtSqlWithStrPrm(
    sql As String,prmName1 As String,prmVal1 As String,
    Optional prmName2 As String = Nothing,
    Optional prmVal2 As String = Nothing) As DataTable

    Dim returnDt As New DataTable
    sqlCmd = New NpgsqlCommand(sql.ToString, sqlCon)
    sqlAdp = New NpgsqlDataAdapter(sqlCmd)

    Dim param1 As NpgsqlParameter
    param1 = New NpgsqlParameter(prmName1, SqlDbType.VarChar)       
    param1.Value = prmVal1
    sqlCmd.Parameters.Add(param1)

    If prmName2 IsNot Nothing And prmVal2 IsNot Nothing Then
        Dim param2 As NpgsqlParameter
        param2 = New NpgsqlParameter(prmName2, SqlDbType.VarChar)
        param2.Value = prmVal2
        sqlCmd.Parameters.Add(param2)
    End If

    sqlAdp.Fill(returnDt)

    Return returnDt

End Function

dbSample.3vb


Dim PrmVal1 As String
Dim PrmVal2 As String
Dim sql As String
Dim dt As Datatable

Using db As New dbConnectionClass()

    dt = db.getDtSqlWithStrPrm(sql, 
              "PrmName1", PrmVal1,
              "PrmName2", PrmVal2)

End Using

　これでパラメータつきSQLも同じように実行できます。Int型のパラメータを使う場合はprmValの型をInt型にして、SqlDbType.Integerにすれば対応できます。

おわりに

　個人的には、このDB接続共通クラスを作る過程でコンストラクタ・デストラクタとUsingの使い方を覚えることができました。これに限らず、共通処理をまとめたクラスを作るのは初学者にとってかなり勉強になる部分が大きいと思います。この記事が少しでも参考になれば幸いです。
　改善点はあるはずですので、ご指摘をいただいたり自分で気づくことがあれば修正していきます。最後までご覧いただきありがとうございました。

【VB.NET】DB接続共通クラスについて