- Modulbegriff
- stellt eine Art statische Klasse ohne Konstruktor/Destruktor, parallel zum Programm im
gesamten Namespace zur Verfügung
- Beispiel
- Code für ein Modul, dass grundlegende Funktionen für den Zugriff auf eine Access-Datenbank realisiert
' statisches DB Modul für die Anbindung an eine Access-Datenbank
Module DBAccess
Private DB_OleDBConnection As System.Data.OleDb.OleDbConnection
Private sDBError As String
' letzte Fehlermeldung
Public ReadOnly Property ErrorString() As String
Get
Return sDBError
End Get
End Property
' Status DB Verbindung
Public Function DBConnectionIsOpen() As Boolean
Dim bRetVal As Boolean = False
If Not IsNothing(DB_OleDBConnection) Then
bRetVal = IIf(DB_OleDBConnection.State = System.Data.ConnectionState.Open, True, False)
End If
Return bRetVal
End Function
' DB Verbindung öffnen
Public Function Open(ByVal sDBString As String) As Boolean
Dim bRetVal As Boolean = False
Try
If IsNothing(DB_OleDBConnection) Then DB_OleDBConnection = New System.Data.OleDb.OleDbConnection
With DB_OleDBConnection
If (DBConnectionIsOpen() = True) Then .Close()
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBString
.Open()
bRetVal = DBConnectionIsOpen()
End With
Catch ex As Exception
sDBError = ex.ToString
bRetVal = False
End Try
Return bRetVal
End Function
' DB Verbindung schließen
Public Function Close() As Boolean
Dim bRetVal As Boolean = False
Try
If DBConnectionIsOpen() = True Then
DB_OleDBConnection.Close()
DB_OleDBConnection.Dispose()
DB_OleDBConnection = Nothing
End If
bRetVal = True
Catch ex As Exception
sDBError = ex.ToString
bRetVal = False
End Try
Return bRetVal
End Function
' Select-Funktion
' gibt SQLReader zur weiteren Verarbeitung zurück
Public Function SQLSelect(ByVal sSQL As String, ByRef DB_OleDBReader As System.Data.OleDb.OleDbDataReader) As Boolean
Dim bRetVal As Boolean = False
Dim DB_OleDBCommandSelect As New System.Data.OleDb.OleDbCommand
Try
If DBConnectionIsOpen() = True Then
With DB_OleDBCommandSelect
.CommandText = sSQL
.Connection = DB_OleDBConnection
DB_OleDBReader = .ExecuteReader()
.Dispose()
End With
End If
DB_OleDBCommandSelect = Nothing
bRetVal = True
Catch ex As Exception
sDBError = ex.ToString
bRetVal = False
End Try
Return bRetVal
End Function
' Select-Funktion
' gibt DataSet zur weiteren Verarbeitung zurück
Public Function SQLSelect(ByVal sSQL As String, ByRef dsGrid As DataSet) As Boolean
Dim bRetVal As Boolean = False
Dim DB_OleDBCommandSelect As New System.Data.OleDb.OleDbCommand
Dim DB_OleDBDataAdapter As New System.Data.OleDb.OleDbDataAdapter
If Not IsNothing(dsGrid) Then
dsGrid.Clear()
Try
If DBConnectionIsOpen() = True Then
DB_OleDBCommandSelect.CommandText = sSQL
DB_OleDBCommandSelect.Connection = DB_OleDBConnection
DB_OleDBDataAdapter.SelectCommand = DB_OleDBCommandSelect
DB_OleDBDataAdapter.Fill(dsGrid)
End If
DB_OleDBCommandSelect.Dispose()
DB_OleDBCommandSelect = Nothing
DB_OleDBDataAdapter.Dispose()
DB_OleDBDataAdapter = Nothing
bRetVal = True
Catch ex As Exception
sDBError = ex.ToString
bRetVal = False
End Try
End If
Return bRetVal
End Function
' Select-Funktion, für Ergebnismengen mit nur einen einzigen Rückgabewert
' sItem ist dabei der Name der Spalte, deren Wert in den Rückgabestring übergeben werden soll
Public Function SQLSelect(ByVal sSQL As String, ByVal sItem As String) As String
Dim sRetVal As String = ""
Dim DB_OleDBCommandSelect As New System.Data.OleDb.OleDbCommand
Try
If DBConnectionIsOpen() = True Then
With DB_OleDBCommandSelect
.CommandText = sSQL
.Connection = DB_OleDBConnection
sRetVal = .ExecuteScalar().ToString() ' Item aus der Rückgabemenge auslesen
.Dispose()
End With
DB_OleDBCommandSelect = Nothing
End If
Catch ex As Exception
sDBError = ex.ToString
sRetVal = ""
End Try
Return sRetVal
End Function
' Insert-Funktion
' gibt letzte Insert-ID zurück
Public Function SQLInsert(ByVal sSQL As String) As Integer
Dim iRetVal As Integer = -1
Dim DB_OleDBCommandInsert As New System.Data.OleDb.OleDbCommand
Dim DB_OleDBCommandSelect As New System.Data.OleDb.OleDbCommand
Try
If DBConnectionIsOpen() = True Then
With DB_OleDBCommandInsert
.CommandText = sSQL
.Connection = DB_OleDBConnection
.ExecuteNonQuery()
.Dispose()
End With
With DB_OleDBCommandSelect
.CommandText = "SELECT @@IDENTITY"
.Connection = DB_OleDBConnection
iRetVal = .ExecuteScalar() ' Insert-ID holen
.Dispose()
End With
DB_OleDBCommandInsert = Nothing
DB_OleDBCommandSelect = Nothing
End If
Catch ex As Exception
sDBError = ex.ToString
iRetVal = -1
End Try
Return iRetVal
End Function
' Aktualisiert Datensatz
Public Function SQLUpdate(ByVal sSQL As String) As Boolean
Dim bRetVal As Boolean = False
Dim DB_OleDBCommandUpdate As New System.Data.OleDb.OleDbCommand
Try
If DBConnectionIsOpen() = True Then
With DB_OleDBCommandUpdate
.CommandText = sSQL
.Connection = DB_OleDBConnection
.ExecuteNonQuery()
.Dispose()
End With
DB_OleDBCommandUpdate = Nothing
bRetVal = True
End If
Catch ex As Exception
sDBError = ex.ToString
bRetVal = False
End Try
Return bRetVal
End Function
' Löscht Datensatz aus Tabelle anhand einer ID
Public Function SQLDelete(ByVal sTable As String, ByVal iID As Integer) As Boolean
Dim bRetVal As Boolean = False
Dim DB_OleDBCommandDelete As New System.Data.OleDb.OleDbCommand
Try
If DBConnectionIsOpen() = True Then
With DB_OleDBCommandDelete
.CommandText = "DELETE FROM " & sTable & " WHERE ID=" & iID.ToString
.Connection = DB_OleDBConnection
.ExecuteNonQuery()
.Dispose()
End With
DB_OleDBCommandDelete = Nothing
bRetVal = True
End If
Catch ex As Exception
sDBError = ex.ToString
bRetVal = False
End Try
Return bRetVal
End Function
End Module