#はじめに
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接続クラスのソースコード全文です。使い方は後述します。
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で自動生成されるコード＝デストラクタは、このクラスをインスタンス化して使用する際に重要なメソッドです。簡単にまとまっている記事がありましたので、ご紹介します。
自動生成されるコードは初学者には意味がよくわからないかもしれません（筆者も完全には理解できていません）が、インスタンス化した際にUsingを利用するために必要なものです。確実に終了処理を行うためのコードと、重複して終了処理を行ってしまわないようにするフラグが生成されているという具合です。他のメソッド内にあるIf sql... Is Nothing Then...の部分も、重複して同じオブジェクトを生成しないようにするための保険です。
###接続文字列の設定
接続文字列の設定に関してはコードに直接入力してもいいですが、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>
このように設定した場合、共通クラスでの呼び出し方は下記のとおりです。
Private Property conStr As String
conStr = ConfigurationManager.ConnectionStrings("ConStr").ConnectionString
###postgreSQL以外のRDBMSへの対応
Npgsqlに依存している部分を置き換えれば、他のRDBMSに接続して同様の処理ができます。SQLServerであればSystem.Data.SqlClientをインポートすれば対応したメソッドが使えます。
#使い方
※SQLの生成方法に関しては、SQLインジェクション対策や実行速度などを考慮すると奥が深すぎるのでこの記事では取り扱わず、参考記事をご紹介します。
SQLを生成出来たら、以下のコードでDB操作を実行します。
'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にコンストラクタ・デストラクタがない場合を想定ください)。
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メソッドで例を示します。
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がバグを吐くので、パラメーターの型が異なると同じメソッドを使いまわせません。
''' <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
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の使い方を覚えることができました。これに限らず、共通処理をまとめたクラスを作るのは初学者にとってかなり勉強になる部分が大きいと思います。この記事が少しでも参考になれば幸いです。
改善点はあるはずですので、ご指摘をいただいたり自分で気づくことがあれば修正していきます。最後までご覧いただきありがとうございました。