' ******************************************************
' Oracle
' ******************************************************
Public Class DbOracle
Implements DbInterface
' ******************************************************
' 内部変数( プロパティ )
' ※ Public は、開発中なので都合がいいように
' ******************************************************
Public _myConnectString As String = Nothing
Public myCon As OracleConnection = Nothing
Public myCommand As OracleCommand = New OracleCommand()
Public myReader As OracleDataReader = Nothing
Public _myError As String = ""
Public _SqlString As String = ""
Public _Rdbms As String = "Oracle"
Public Shared _SQLTables As String = "select * from USER_TABLES"
Public Shared _SQLViews As String = "select * from USER_VIEWS"
Public Shared _SQLUsers As String = "select * from DBA_USERS"
Public Shared _SQL_ORA_TABLE_COMMENT As String = "select * from USER_TAB_COMMENTS"
Public Shared _SQL_ORA_DIR As String = "select * from ALL_DIRECTORIES"
Public Shared _SQL_ORA_SYNONYM As String = "select * from USER_SYNONYMS"
Public Shared _SQL_ORA_SEQ As String = "select * from USER_SEQUENCES"
Public Shared _SQL_ORA_OBJECT As String = "select * from USER_OBJECTS"
Public Shared _SQL_ORA_PROC As String = "select * from USER_OBJECTS " + _
" where OBJECT_TYPE in ('FUNCTION','PROCEDURE')"
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 sv As String, _
ByVal user As String, _
ByVal pass As String)
Me._myConnectString = _
"Server=" + sv + ";" + _
"User ID=" + user + ";" + _
"Password=" + pass + ";"
End Sub
' ******************************************************
' 接続
' ******************************************************
Public Function Connect() As Boolean _
Implements DbInterface.Connect
Dim ret As Boolean
Me.myCon = New OracleConnection()
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 QueryTableInfo(ByVal table As String) As Boolean
Me._SqlString = String.Format( _
"select * from USER_TAB_COLUMNS where TABLE_NAME = '{0}'" + _
" order by COLUMN_ID ", table.ToUpper())
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 = Me._SqlString
Try
Me.myReader = myCommand.ExecuteReader()
ret = True
Catch ex As Exception
Me._myError = ex.Message
ret = False
End Try
Return ret
End Function
' ******************************************************
' SQL を実行して プロシージャのテキストを取得
' ******************************************************
Public Function QueryProcText(ByVal proc As String) As String
Me._SqlString = String.Format( _
"select * from USER_SOURCE where NAME = '{0}'" + _
" order by LINE ", proc.ToUpper())
' 読み取りオブジェクトが閉じていない場合は、閉じる
If Not Me.myReader Is Nothing Then
If Not Me.myReader.IsClosed Then
Me.myReader.Close()
End If
End If
Me.myCommand.CommandText = Me._SqlString
Try
Me.myReader = myCommand.ExecuteReader()
Catch ex As Exception
Me._myError = ex.Message
Return "ERROR"
End Try
Dim ret As String
ret = ""
Do While Me.Read()
ret &= Me.GetValue("TEXT")
Loop
Me.Close()
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 OracleCommand = New OracleCommand()
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 ExecuteTransaction(ByVal SQL As String, ByVal tr As OracleTransaction) As Integer
Me._SqlString = SQL
Dim ret As Integer
Dim execCommand As OracleCommand = New OracleCommand()
execCommand.CommandText = SQL
execCommand.Connection = Me.myCon
execCommand.Transaction = tr
Try
ret = execCommand.ExecuteNonQuery()
Catch ex As Exception
ret = -2
Me._myError = ex.Message
End Try
execCommand.Dispose()
Return ret
End Function
' ******************************************************
' フィールドの数を得る
' ******************************************************
Public Function GetFieldCount() As Integer
Return Me.myReader.FieldCount
End Function
' ******************************************************
' 接続が閉じているかどうか
' ******************************************************
Public Function IsConnectClosed() As Boolean
If Me.myCon.State = ConnectionState.Closed Then
Return True
Else
Return False
End If
End Function
' ******************************************************
' reader が閉じているるかどうか
' ******************************************************
Public Function IsReaderClosed() As Boolean
Return Me.myReader.IsClosed
End Function
' ******************************************************
' 行を持つかどうか
' ******************************************************
Public Function IsRow() As Boolean
Return Me.myReader.HasRows
End Function
' ******************************************************
' 指定した列が NULL かどうか
' ******************************************************
Public Function IsNull(ByVal ColumnName As String) As Boolean
Dim ret As String = ""
Dim fld As Integer = 0
fld = Me.myReader.GetOrdinal(ColumnName)
Return Me.myReader.IsDBNull(fld)
End Function
Public Function IsNull(ByVal ColumnIndex As Integer) As Boolean
Return Me.myReader.IsDBNull(ColumnIndex)
End Function
' ******************************************************
' 指定した位置より列名を文字列として値を得る
' ******************************************************
Public Function GetName(ByVal ColumnIndex As Integer) As String
Dim ret As String = ""
ret = Me.myReader.GetName(ColumnIndex)
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 "DbOracle"
End Function
End Class