' ******************************************************
' 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