PLSQL クラス

  更新履歴



2008/06/12 初期 ( テスト中 )

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



  コード



  
Imports System.Data.OracleClient

Public Class PLSQL

	' ******************************************************
	' 内部変数( プロパティ )
	' ※ 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 PARAM As OracleParameter
	Public STATUS As OracleParameter
	Public EOD As Boolean

	' ******************************************************
	' コンストラクタ
	' ******************************************************
	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 ReadOnly Property myConnectString() As String
		Get
			Return _myConnectString
		End Get
	End Property

	Public ReadOnly Property myError() As String
		Get
			Return _myError
		End Get
	End Property

	Public ReadOnly Property SqlString() As String
		Get
			Return _SqlString
		End Get
	End Property

	' ******************************************************
	' 接続
	' ******************************************************
	Public Function Connect() As Boolean

		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

	' ******************************************************
	' PLSQL のビルド
	' ******************************************************
	Public Function Build(ByVal PLSQL As String) As Boolean

		Me._myError = ""
		Me._SqlString = PLSQL

		Dim ret As Boolean = True
		myCommand.CommandType = CommandType.Text

		myCommand.CommandText = PLSQL
		myCommand.Connection = Me.myCon
		Try
			myCommand.ExecuteNonQuery()
		Catch ex As Exception
			ret = False
			Me._myError = ex.Message
		End Try

		Return ret

	End Function

	' ******************************************************
	' DBMS_OUTPUT を使用可能にする
	' ******************************************************
	Public Function Enable_DBMS_OUTPUT() As Boolean

		Me._myError = ""
		Me._SqlString = "BEGIN DBMS_OUTPUT.ENABLE(); END;"

		Dim ret As Boolean = True
		myCommand.CommandType = CommandType.Text

		myCommand.CommandText = Me._SqlString
		myCommand.Connection = Me.myCon
		Try
			myCommand.ExecuteNonQuery()
		Catch ex As Exception
			ret = False
			Me._myError = ex.Message
		End Try

		Return ret

	End Function

	' ******************************************************
	' DBMS_OUTPUT 取得の準備を行う
	' ******************************************************
	Public Function Start_DBMS_OUTPUT(ByVal Size As Integer) As Boolean

		Me._myError = ""
		Me._SqlString = "DBMS_OUTPUT.GET_LINE"

		Dim ret As Boolean = True

		myCommand.Parameters.Clear()
		myCommand.CommandType = CommandType.StoredProcedure
		myCommand.CommandText = Me._SqlString

		PARAM = New OracleParameter("line", OracleType.VarChar)
		PARAM.Direction = ParameterDirection.Output
		PARAM.Size = Size
		myCommand.Parameters.Add(PARAM)
		STATUS = New OracleParameter("status", OracleType.Number)
		STATUS.Direction = ParameterDirection.Output
		myCommand.Parameters.Add(STATUS)

		Try
			myCommand.ExecuteNonQuery()
		Catch ex As Exception
			ret = False
			Me._myError = ex.Message
		End Try

		Return ret

	End Function

	' ******************************************************
	' DBMS_OUTPUT 取得
	' ******************************************************
	Public Function Get_DBMS_OUTPUT() As String

		Me._myError = ""
		Dim ret As String

		If STATUS.Value = 0 Then
			Me.EOD = False
			ret = PARAM.Value.ToString()
		Else
			Me.EOD = True
			ret = ""
		End If

		Try
			myCommand.ExecuteNonQuery()
		Catch ex As Exception
			Me.EOD = True
			Me._myError = ex.Message
		End Try

		Return ret

	End Function

	' ******************************************************
	' プロシージャ呼び出し準備
	' ******************************************************
	Public Sub PrepareProc(ByVal ProcName As String)

		Me._myError = ""
		Me._SqlString = ProcName

		myCommand.Parameters.Clear()
		myCommand.CommandType = CommandType.StoredProcedure
		myCommand.CommandText = Me._SqlString

	End Sub

	Public Sub PrepareText(ByVal CommandText As String)

		Me._myError = ""
		Me._SqlString = CommandText.Replace(ControlChars.CrLf, ControlChars.Lf)

		myCommand.Parameters.Clear()
		myCommand.CommandType = CommandType.Text
		myCommand.CommandText = Me._SqlString

	End Sub

	' ******************************************************
	' プロシージャ呼び出し
	' ******************************************************
	Public Function CallProc() As Boolean

		Dim ret As Boolean = True

		Try
			myCommand.ExecuteNonQuery()
		Catch ex As Exception
			ret = False
			Me._myError = ex.Message
		End Try

		Return ret

	End Function

	' ******************************************************
	' 接続解除
	' ******************************************************
	Public Sub 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()

		' 読み取りオブジェクトが閉じていない場合は、閉じる
		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

End Class
  










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





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

SQLの窓フリーソフト

素材

一般WEBツールリンク

SQLの窓

フリーソフト

JSライブラリ