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
  




yahoo  google  MSDN  MSDN(us)  WinFAQ  Win Howto  tohoho  ie_DHTML  vector  wdic  辞書  天気 


[dnettool]
claudebot
24/03/29 18:21:49
InfoBoard Version 1.00 : Language=Perl

1 BatchHelper COMprog CommonSpec Cprog CprogBase CprogSAMPLE CprogSTD CprogSTD2 CprogWinsock Cygwin GameScript HTML HTMLcss InstallShield InstallShieldFunc JScript JScriptSAMPLE Jsfuncs LLINK OldProg OracleGold OracleSilver PRO PRObrowser PROc PROconePOINT PROcontrol PROftpclient PROjscript PROmailer PROperl PROperlCHAT PROphp PROphpLesson PROphpLesson2 PROphpLesson3 PROphpfunction PROphpfunctionArray PROphpfunctionMisc PROphpfunctionString PROsql PROvb PROvbFunction PROvbString PROvbdbmtn PROvbonepoint PROwebapp PROwin1POINT PROwinSYSTEM PROwinYOROZU PROwindows ProjectBoard RealPHP ScriptAPP ScriptMaster VBRealtime Vsfuncs a1root access accreq adsi ajax amazon argus asp aspSample aspVarious aspdotnet aw2kinst cappvariety centura ckeyword classStyle cmaterial cmbin cmdbapp cmenum cmlang cmlistbox cmstd cmstdseed cmtxt cs daz3d db dbCommon dbaccess dnettool dos download flex2 flex3 flex4 framemtn framereq freeWorld freesoft gimp ginpro giodownload google hdml home hta htmlDom ie9svg install java javaSwing javascript jetsql jquery jsp jspTest jspVarious lightbox listasp listmsapi listmsie listmsiis listmsnt listmspatch listmsscript listmsvb listmsvc memo ms msde mysql netbeans oraPlsql oracle oracleWiper oraclehelper orafunc other panoramio pear perl personal pgdojo pgdojo_cal pgdojo_holiday pgdojo_idx pgdojo_ref pgdojo_req php phpVarious phpguide plsql postgres ps r205 realC realwebapp regex rgaki ruby rule sboard sc scprint scquest sdb sdbquest seesaa setup sh_Imagick sh_canvas sh_dotnet sh_google sh_tool sh_web shadowbox shgm shjquery shvbs shweb sjscript skadai skywalker smalltech sperl sqlq src systemdoc tcpip tegaki three toolbox twitter typeface usb useXML vb vbdb vbsfunc vbsguide vbsrc vpc wcsignup webanymind webappgen webclass webparts webtool webwsh win8 winofsql wmi work wp youtube