|
Imports System.Data.OleDb
Imports System.Data.Odbc
Imports System.Data.OracleClient
Imports System.Data.SqlClient
' ******************************************************
' DB アクセス用インターフェイス
' ******************************************************
Interface DbInterface
ReadOnly Property myError() As String
ReadOnly Property myConnectString() As String
ReadOnly Property SqlString() As String
Function Connect() As Boolean
Function Query(ByVal SQL As String) As Boolean
Function Read() As Boolean
Function QueryAndRead(ByVal SQL As String) As Boolean
Function Execute(ByVal SQL As String) As Integer
Function GetValue(ByVal ColumnName As String) As String
Function GetDate(ByVal ColumnName As String) As String
Sub Close()
End Interface
' ******************************************************
' 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 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 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 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 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 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
' 接続を閉じる
Me.myCon.Close()
' リソースの開放
Me.myCon.Dispose()
Me.myCommand.Dispose()
' 次に備えて初期化
Me.myCon = Nothing
End If
End Sub
End Class
' ******************************************************
' ODBC
' ******************************************************
Public Class DbOdbc
Implements DbInterface
' ******************************************************
' 内部変数( プロパティ )
' ※ Public は、開発中なので都合がいいように
' ******************************************************
Public _myConnectString As String = Nothing
Public myCon As OdbcConnection = Nothing
Public myCommand As OdbcCommand = New OdbcCommand()
Public myReader As OdbcDataReader = Nothing
Public _myError As String = ""
Public _SqlString As String = ""
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 driver As String, _
ByVal sv As String, _
ByVal db As String, _
ByVal user As String, _
ByVal pass As String)
Me._myConnectString = _
"Driver={" + driver + "};" + _
"SERVER=" + sv + ";" + _
"DATABASE=" + db + ";" + _
"UID=" + user + ";" + _
"PWD=" + pass + ";"
End Sub
' ******************************************************
' 接続
' ******************************************************
Public Function Connect() As Boolean _
Implements DbInterface.Connect
Dim ret As Boolean
Me.myCon = New OdbcConnection()
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 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 OdbcCommand = New OdbcCommand()
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 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 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 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
' 接続を閉じる
Me.myCon.Close()
' リソースの開放
Me.myCon.Dispose()
Me.myCommand.Dispose()
' 次に備えて初期化
Me.myCon = Nothing
End If
End Sub
End Class
' ******************************************************
' MDB
' ******************************************************
Public Class DbAccess
Implements DbInterface
' ******************************************************
' 内部変数( プロパティ )
' ※ Public は、開発中なので都合がいいように
' ******************************************************
Public _myConnectString As String = Nothing
Public myCon As OleDbConnection = Nothing
Public myCommand As OleDbCommand = New OleDbCommand()
Public myReader As OleDbDataReader = Nothing
Public _myError As String = ""
Public _SqlString As String = ""
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 dbpath As String)
Me._myConnectString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=" + dbpath + ";"
End Sub
' ******************************************************
' 接続
' ******************************************************
Public Function Connect() As Boolean _
Implements DbInterface.Connect
Dim ret As Boolean
Me.myCon = New OleDbConnection()
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 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 OleDbCommand = New OleDbCommand()
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 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 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 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
' 接続を閉じる
Me.myCon.Close()
' リソースの開放
Me.myCon.Dispose()
Me.myCommand.Dispose()
' 次に備えて初期化
Me.myCon = Nothing
End If
End Sub
End Class
' ******************************************************
' SQLServer
' ******************************************************
Public Class DbSql
Implements DbInterface
' ******************************************************
' 内部変数( プロパティ )
' ※ Public は、開発中なので都合がいいように
' ******************************************************
Public _myConnectString As String = Nothing
Public myCon As SqlConnection = Nothing
Public myCommand As SqlCommand = New SqlCommand()
Public myReader As SqlDataReader = Nothing
Public _myError As String = ""
Public _SqlString As String = ""
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 db As String, _
ByVal user As String, _
ByVal pass As String)
Me._myConnectString = _
"Data Source=" + sv + ";" + _
"Initial Catalog=" + db + ";" + _
"User ID=" + user + ";" + _
"Password=" + pass + ";"
End Sub
' ******************************************************
' 接続
' ******************************************************
Public Function Connect() As Boolean _
Implements DbInterface.Connect
Dim ret As Boolean
Me.myCon = New SqlConnection()
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 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 SqlCommand = New SqlCommand()
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 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 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 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
' 接続を閉じる
Me.myCon.Close()
' リソースの開放
Me.myCon.Dispose()
Me.myCommand.Dispose()
' 次に備えて初期化
Me.myCon = Nothing
End If
End Sub
End Class
| |