DbOracle クラス

  更新履歴



2008/06/12 初期

↓ダウンロードページ
http://winofsql.jp/VA003334/dnettool080612191802.htm



  コード



インターフェイス定義

Public db As DbOracle

' ******************************************************
' 初期処理
' ******************************************************
Private Sub Form1_Load(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load

	' Oracle 用インスタンス作成
	db = New DbOracle( _
	 "night/xe", _
	 "lightbox", _
	 "lightbox" _
	)

End Sub

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











  infoboard   管理者用   
このエントリーをはてなブックマークに追加




SQLの窓  天気  IT用語辞典
Yahoo!ニュース  マルチ辞書
PHP マニュアル  Google URL短縮 


フリーフォントWEBサービス
SQLの窓WEBサービス

SQLの窓フリーソフト

素材

一般WEBツールリンク

SQLの窓

フリーソフト

JSライブラリ