[OleDB] Beispiel für ein Datenbank-Modul

  1. Modulbegriff
    • stellt eine Art statische Klasse ohne Konstruktor/Destruktor, parallel zum Programm im
      gesamten Namespace zur Verfügung
  2. 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

Schreibe einen Kommentar