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