自社のみで開発を行って、すべてコミットするのであれば、データベースは1つに統一できますが、連係先が別ベンダーだったりし、複数のデータベースへ接続する場合が出てきます。
そんなとき、毎回ソースを修正し、コンパイルするのは大変です。
そこで「DbProviderFactories」を使い、データベース接続部分をデータプロバイダーにより切り替え、それ以降の処理を変更しなくて良いようにしました。
今まで接続を行ったデータベースは「PostgreSQL」「MySQL(ODBC)」「Oracle(ODBC/Instant Client)」「Symfoware(ODBC)」「MS SQLServer」「DB2(ODBC)」です。
記載されている内容は、自身の責任において行ってください。この記事により不具合が生じても一切責任は負いません。
必要なライブラリ等は予めインストール、参照の追加を行っておいてください。
ソース
Imports System.Data.Common Imports Npgsql Imports System.Data.SqlClient Imports System.Data.Odbc Imports System.Data.OleDb Public Class comSql Public DB_CON As DbConnection Public DB_DR As DbDataReader Public DB_TRN As DbTransaction Dim _NG As String = "999" Dim _EOF As String = "100" Dim _OK As String = "000" Public strErrMsg As String = "" Public strFieldType_(,) As String Dim provider As String = "" Dim host As String = "" Dim dbnm As String = "" Dim port As String = "" Dim user As String = "" Dim pass As String = "" ''' <summary> ''' コンストラクタ ''' </summary> ''' <param name="_provider">データプロバイダー</param> ''' <param name="_host">ホスト名、IP、ODBCデータソース名</param> ''' <param name="_port">ポート番号</param> ''' <param name="_dbNm">データベース名</param> ''' <param name="_user">ユーザー名</param> ''' <param name="_pass">パスワード</param> ''' <remarks></remarks> Public Sub New( _ ByVal _provider As String, ByVal _host As String, ByVal _port As String, ByVal _dbNm As String, ByVal _user As String, ByVal _pass As String _ ) provider = _provider host = _host port = _port dbnm = _dbNm user = _user pass = _pass End Sub ''' <summary> ''' データベースへの接続 ''' </summary> ''' <returns></returns> ''' <remarks></remarks> Public Function dbCon() As Short Dim connectionClassNm As String = "" ' コネクションクラス名 Dim connectionStr As String = "" ' 接続文字列 Dim factory As DbProviderFactory Select Case provider Case "pgsql" connectionClassNm = "Npgsql" connectionStr = "Host=" & host & ";Port=" & port & ";Database=" & dbnm & ";User Id= " & user & ";Password=" & pass & ";Preload Reader=true;CommandTimeout=60;Encoding=EUC_JP;" Exit Select Case "mssql" connectionClassNm = "System.Data.SqlClient" connectionStr = "server=" & host & "," & port & ";Initial Catalog=" & dbnm & ";User ID= " & user & ";Password=" & pass & ";MultipleActiveResultSets=True;" Exit Select Case "odbc" connectionClassNm = "System.Data.Odbc" connectionStr = "DSN=" & host & ";UID=" & user & ";PWD=" & pass & ";" Exit Select Case "oledb" connectionClassNm = "System.Data.OleDb" connectionStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbnm & ";User ID=" & user & ";Password=" & pass & ";" Exit Select Case "oracle" connectionClassNm = "Oracle.DataAccess.Client" connectionStr = "user id=" & user & ";password=" & pass & ";data source=" & host & ";" Exit Select End Select Try factory = DbProviderFactories.GetFactory(connectionClassNm) DB_CON = factory.CreateConnection() DB_CON.ConnectionString = connectionStr DB_CON.Open() Return _OK Catch ex As Exception strErrMsg = ex.ToString() Return _NG End Try End Function ''' <summary> ''' セレクトする ''' </summary> ''' <param name="_sql"></param> ''' <returns></returns> ''' <remarks></remarks> Public Function dbSelect(ByVal _sql) As Short Dim _cmd = DB_CON.CreateCommand _cmd.CommandText = _sql Try DB_DR = _cmd.ExecuteReader If (DB_DR.HasRows = False) Then Return _EOF Else Return _OK End If Catch ex As Exception strErrMsg = ex.ToString Return _NG End Try End Function ''' <summary> ''' 取得した結果から1行返す。 ''' </summary> ''' <returns></returns> ''' <remarks></remarks> Public Function getRowStr() As String() Dim strTmp As String = "" Dim strResults As String() = Nothing Dim i As Integer = 0 Dim j As Integer = 0 strErrMsg = "" Try If (DB_DR.Read) Then ReDim strResults(DB_DR.FieldCount - 1) 'カラム数分ループ For i = 0 To DB_DR.FieldCount - 1 If (DB_DR.IsDBNull(i)) Then strResults(i) = "" Else strResults(i) = DB_DR.GetString(i) End If Next i End If If (strResults Is Nothing) Then DB_DR.Close() If (strResults Is Nothing) Then DB_CON.Close() Return strResults Catch ex As Exception strErrMsg &= ex.Message.ToString() Return Nothing End Try End Function ''' <summary> ''' 行のフィールド名、型を取得 ''' </summary> ''' <param name="paramDR"></param> ''' <returns></returns> ''' <remarks></remarks> Public Function getFieldType(ByVal paramDR As DbDataReader) As Short Dim i As Integer Try If (paramDR.HasRows = False) Then Return _EOF End If ReDim strFieldType_(paramDR.FieldCount - 1, 1) For i = 0 To (paramDR.FieldCount - 1) strFieldType_(i, 0) = paramDR.GetName(i) strFieldType_(i, 1) = paramDR.GetDataTypeName(i) Next i Return _OK Catch ex As Exception strErrMsg = ex.ToString Return _NG End Try End Function ''' <summary> ''' SQLを実行する ''' </summary> ''' <param name="_sql"></param> ''' <returns></returns> ''' <remarks></remarks> Public Function sqlExec(ByVal _sql As String) As Short Dim _cmd = DB_CON.CreateCommand _cmd.CommandText = _sql Dim ResultCnt As Int32 Try ResultCnt = _cmd.ExecuteNonQuery If (ResultCnt = 0) Then Return _EOF Else Return _OK End If Catch ex As Exception strErrMsg = ex.ToString Return _NG End Try End Function ''' <summary> ''' クライアントの文字コードをセットする ''' </summary> ''' <param name="_enc"></param> ''' <returns></returns> ''' <remarks></remarks> Public Function setEnc(ByVal _enc As String) As Short Dim shtResult As Short Try shtResult = sqlExec("set client_encoding to '" & _enc & "'") Return shtResult Catch ex As Exception strErrMsg = ex.ToString Return _NG End Try End Function ''' <summary> ''' 接続を閉じる ''' </summary> ''' <returns></returns> ''' <remarks></remarks> Public Function dbClose() As Short DB_CON.Close() Return _OK End Function ''' <summary> ''' トランザクションの開始 ''' </summary> ''' <returns></returns> ''' <remarks></remarks> Public Function begin() As Short DB_TRN = DB_CON.BeginTransaction() Return _OK End Function ''' <summary> ''' トランザクションのコミット ''' </summary> ''' <returns></returns> ''' <remarks></remarks> Public Function commit() As Short DB_TRN.Commit() Return _OK End Function ''' <summary> ''' トランザクションのロールバック ''' </summary> ''' <returns></returns> ''' <remarks></remarks> Public Function rollback() As Short DB_TRN.Rollback() Return _OK End Function End Class
使い方
' インスタンス作成 Dim comdb As New comSql("pgsql", "localhost", "5432", "testdb", "user", "password") ' 接続 comdb.dbCon() 'クライアント文字列セット comdb.setEnc("UTF-8") ' セレクト実行 comdb.dbSelect("select id , username from usertable;") ' 処理 Do strRow = comdb.getRowStr If (strRow Is Nothing) Then ' 処理終了 Exit Do End If ' 処理 Loop ' 接続を閉じる comsql.dbClose()
特記事項・雑感
バイナリ型、ラージオブジェクト型、XMLなんかが含まれている場合は、注意が必要です。
必要そうなライブラリとか
- Npgsql.dllダウンロード
- Oracle Instant ClientOracle社トップページ
コメント