' ******************************************************
' MDB
' ******************************************************
Public Class DbAccess
Implements DbInterface
' ******************************************************
' 内部変数( プロパティ )
' ※ Public は、開発中なので都合がいいように
' ******************************************************
Public _myConnectString As String = Nothing
Public myCon As OleDbConnection = Nothing
Public myCommand As OleDbCommand = New OleDbCommand()
Public myReader As OleDbDataReader = Nothing
Public _myError As String = ""
Public _SqlString As String = ""
Public _Rdbms As String = "Mdb"
Public Property Rdbms() As String _
Implements DbInterface.Rdbms
Get
Return _Rdbms
End Get
Set(ByVal value As String)
_Rdbms = value
End Set
End Property
Public ReadOnly Property myConnectString() As String _
Implements DbInterface.myConnectString
Get
Return _myConnectString
End Get
End Property
Public ReadOnly Property myError() As String _
Implements DbInterface.myError
Get
Return _myError
End Get
End Property
Public ReadOnly Property SqlString() As String _
Implements DbInterface.SqlString
Get
Return _SqlString
End Get
End Property
' ******************************************************
' コンストラクタ
' ******************************************************
Public Sub New(ByVal dbpath As String)
Me._myConnectString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=" + dbpath + ";"
End Sub
' ******************************************************
' 接続
' ******************************************************
Public Function Connect() As Boolean _
Implements DbInterface.Connect
Dim ret As Boolean
Me.myCon = New OleDbConnection()
Me.myCon.ConnectionString = Me.myConnectString
Try
Me.myCon.Open()
Me.myCommand.Connection = Me.myCon
ret = True
Catch ex As Exception
Me.myCon = Nothing
Me._myError = "接続エラーです" + ControlChars.CrLf
Me._myError += "接続文字列=" + Me.myConnectString + ControlChars.CrLf
Me._myError += "システムのメッセージ=" + ex.Message + ControlChars.CrLf
ret = False
End Try
Return ret
End Function
' ******************************************************
' SQL を実行して読み込みの準備
' ※ OleDbDataReader を作成
' ******************************************************
Public Function Query(ByVal SQL As String) As Boolean _
Implements DbInterface.Query
Me._SqlString = SQL
Dim ret As Boolean
' 読み取りオブジェクトが閉じていない場合は、閉じる
If Not Me.myReader Is Nothing Then
If Not Me.myReader.IsClosed Then
Me.myReader.Close()
End If
End If
Me.myCommand.CommandText = SQL
Try
Me.myReader = myCommand.ExecuteReader()
ret = True
Catch ex As Exception
Me._myError = ex.Message
ret = False
End Try
Return ret
End Function
' ******************************************************
' 1行読み込む
' ※ データが無くなると False を返す
' ******************************************************
Public Function Read() As Boolean _
Implements DbInterface.Read
If Not Me.myReader Is Nothing Then
If Not Me.myReader.IsClosed Then
Return Me.myReader.Read()
Else
Me._myError = "Reader は閉じられています"
Return False
End If
Else
Me._myError = "Query が一度も実行されていません"
Return False
End If
End Function
' ******************************************************
' SQL を実行して1行読み込む
' ******************************************************
Public Function QueryAndRead(ByVal SQL As String) As Boolean _
Implements DbInterface.QueryAndRead
Dim ret As Boolean
ret = Me.Query(SQL)
If ret Then
ret = Me.Read()
End If
Return ret
End Function
' ******************************************************
' UPDATE、INSERT、および DELETE を実行する
' 戻り値は影響を受けた行数
' -1 は他の SQL を実行した場合
' -2 はエラー
' ******************************************************
Public Function Execute(ByVal SQL As String) As Integer _
Implements DbInterface.Execute
Me._SqlString = SQL
Dim ret As Integer
Dim execCommand As OleDbCommand = New OleDbCommand()
execCommand.CommandText = SQL
execCommand.Connection = Me.myCon
Try
ret = execCommand.ExecuteNonQuery()
Catch ex As Exception
ret = -2
Me._myError = ex.Message
End Try
execCommand.Dispose()
Return ret
End Function
' ******************************************************
' 指定した列名より文字列として値を得る
' ******************************************************
Public Function GetValue(ByVal ColumnName As String) As String _
Implements DbInterface.GetValue
Dim ret As String = ""
Dim fld As Integer = 0
fld = Me.myReader.GetOrdinal(ColumnName)
If Me.myReader.IsDBNull(fld) Then
ret = ""
Else
ret = Me.myReader.GetValue(fld).ToString()
End If
Return ret
End Function
Public Function GetValue(ByVal ColumnIndex As Integer) As String _
Implements DbInterface.GetValue
Dim ret As String = ""
If Me.myReader.IsDBNull(ColumnIndex) Then
ret = ""
Else
ret = Me.myReader.GetValue(ColumnIndex).ToString()
End If
Return ret
End Function
' ******************************************************
' 日付時間型として、日付部分を文字列として値を得る
' ******************************************************
Public Function GetDate(ByVal ColumnName As String) As String _
Implements DbInterface.GetDate
Dim ret As String = ""
Dim fld As Integer = 0
fld = Me.myReader.GetOrdinal(ColumnName)
If Me.myReader.IsDBNull(fld) Then
ret = ""
Else
Try
ret = Me.myReader.GetDateTime(fld).ToString("d")
Catch ex As Exception
ret = ""
Me._myError = ex.Message
End Try
End If
Return ret
End Function
Public Function GetDate(ByVal ColumnIndex As Integer) As String _
Implements DbInterface.GetDate
Dim ret As String = ""
If Me.myReader.IsDBNull(ColumnIndex) Then
ret = ""
Else
Try
ret = Me.myReader.GetDateTime(ColumnIndex).ToString("d")
Catch ex As Exception
ret = ""
Me._myError = ex.Message
End Try
End If
Return ret
End Function
' ******************************************************
' 接続解除
' ******************************************************
Public Sub Close() _
Implements DbInterface.Close
' 読み取りオブジェクトが閉じていない場合は、閉じる
If Not Me.myReader Is Nothing Then
If Not Me.myReader.IsClosed Then
Me.myReader.Close()
End If
End If
If Not Me.myCon Is Nothing Then
' 接続を閉じる
If Me.myCon.State = ConnectionState.Open Then
Me.myCon.Close()
End If
End If
End Sub
' ******************************************************
' 接続解除
' ******************************************************
Public Sub CloseAndDispose() _
Implements DbInterface.CloseAndDispose
' 読み取りオブジェクトが閉じていない場合は、閉じる
If Not Me.myReader Is Nothing Then
If Not Me.myReader.IsClosed Then
Me.myReader.Close()
End If
End If
If Not Me.myCon Is Nothing Then
' 接続を閉じる
If Me.myCon.State = ConnectionState.Open Then
Me.myCon.Close()
End If
' リソースの開放
Me.myCon.Dispose()
Me.myCommand.Dispose()
' 次に備えて初期化
Me.myCon = Nothing
End If
End Sub
' ******************************************************
' クラス名を戻す
' ******************************************************
Public Overrides Function ToString() As String _
Implements DbInterface.ToString
Return "DbAccess"
End Function
End Class
|