[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

[OleDB] Prüfen, ob Datenbankverbindung offen

' Funktion zum prüfen der DB Verbindung
' DB_OleDBConnection ist dabei ein zuvor erzeugtes
' globales Objekt vom Typ System.Data.OleDb.OleDbConnection
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

[OleDB] Combobox mit Key/Value Paaren aus einer Datenbank füllen

Dim DB_OleDBCommand As New System.Data.OleDb.OleDbCommand
Dim DB_OleDBDataAdapter As New System.Data.OleDb.OleDbDataAdapter
Dim dsFuel As New DataSet

DB_OleDBCommand.Connection = frmMain.DB_OleDBConnection

dsFuel.Clear()
DB_OleDBCommand.CommandText = "SELECT ID, Name FROM Users ORDER BY Name"

DB_OleDBDataAdapter.SelectCommand = DB_OleDBCommand
DB_OleDBDataAdapter.Fill(dsFuel)

With cbFuel
    .Items.Clear()
    .DataSource = dsFuel.Tables(0)
    .DisplayMember = "Name"
    .ValueMember = "ID"
End With

' Key/Value Paare auslesen/setzen

Dim Key As String = cbFuel.SelectedValue.ToString() ' auslesen
cbFuel.SelectedValue = 10 ' setzen

[OleDB] DB-Daten in einem Treeview anzeigen

Public Sub RefreshTree()
    Dim DB_OleDBCommand As New System.Data.OleDb.OleDbCommand
    Dim DB_OleDBReader As System.Data.OleDb.OleDbDataReader

    ' wenn Datenverbindung offen Daten von DB holen
    If frmMain.DB_OleDBConnection.State = System.Data.ConnectionState.Open Then
        With DB_OleDBCommand
            .CommandText = "SELECT ID, Name FROM Users ORDER BY Name"
            .Connection = frmMain.DB_OleDBConnection
            DB_OleDBReader = .ExecuteReader()
        End With

        Dim tnRootNode As TreeNode
        Dim tnObjectNode As TreeNode

        ' TreeView leeren
        tvObjects.Nodes.Clear()

        ' Rootnode anlegen
        tnRootNode = tvObjects.Nodes.Add("Users")

        ' Daten aus DB in TreeView füllen
        Do While (DB_OleDBReader.Read())
            tnObjectNode = tnRootNode.Nodes.Add(DB_OleDBReader.Item("Name").ToString)
            ' ID in das Node-Tag schreiben, für spätere Referenzierung des Datensatzes
            tnObjectNode.Tag = DB_OleDBReader.Item("ID")
        Loop

        ' Verbindung schließen
        DB_OleDBReader.Close()

        ' Obj. löschen
        DB_OleDBCommand.Dispose()
        DB_OleDBCommand = Nothing

        ' Rootnode aufklappen
        tnRootNode.Expand()
    End If
End Sub

[ADODB] letzte Insert ID ermitteln

With DBRecordset
     .ActiveConnection = DBConnection
     .CursorLocation = ADODB.CursorLocationEnum.adUseClient
     .LockType = ADODB.LockTypeEnum.adLockOptimistic
     .Open("SELECT @@IDENTITY")
     
     oder
     
     .Open("SELECT MAX(ID_Field) FROM Table")
     
     If .RecordCount = 1 Then
         Dim iZahl As Interger = .Fields(0).Value
     End If
End With

[OleDB] Verwendung eines DataGridView

Dim sConnectionString
Dim sSQL As String

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydatabase.mdb"
sSQL = "SELECT ID, Name FROM Person"

Dim DB_Conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
Dim DB_DataAdapter As New System.Data.OleDb.OleDbDataAdapter(sSQL, DB_Conn)
Dim DataSet As New DataSet()

DB_Conn.Open()
DB_DataAdapter.Fill(DataSet, "Person")

dgvConsumptionData.DataSource = DataSet.Tables("Person")

DB_Conn.Close()

[OleDB] Daten in MS Access löschen (DELETE)

Dim DB_OleDBCommandDelete As New System.Data.OleDb.OleDbCommand
Dim sSQL As String

Try
    ' steht die Datenbankankbindung?
    If DB_OleDBConnection.State = System.Data.ConnectionState.Open Then
        DB_OleDBCommandDelete.Connection = DB_OleDBConnection
        sSQL = "DELETE FROM Users WHERE ID=1"

        DB_OleDBCommandDelete.CommandText = sSQL
        DB_OleDBCommandDelete.ExecuteNonQuery()
    End If

    DB_OleDBCommandDelete.Dispose()
    DB_OleDBCommandDelete = Nothing
Catch ex As Exception
    MessageBox.Show("Fehler: " & ControlChars.CrLf & ex.ToString())
End Try

[OleDB] Daten in MS Access ändern (UPDATE)

Dim DB_OleDBCommand As New System.Data.OleDb.OleDbCommand
Dim sSQL As String
Dim sDecimalSeparator As String = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator

Try
    DB_OleDBCommand.Connection = frmMain.DB_OleDBConnection

    sSQL = "UPDATE Users SET Name='" & tbName.Text & "', " & _
    "Weight=" & tbPower.Text.Replace(sDecimalSeparator, ".") & ", " & _
    "WHERE ID=" & Me.Tag.ToString

    DB_OleDBCommand.CommandText = sSQL
    DB_OleDBCommand.ExecuteNonQuery()
    DB_OleDBCommand.Dispose()
    DB_OleDBCommand = Nothing
Catch ex As Exception
    MessageBox.Show("Fehler: " & ControlChars.CrLf & ex.ToString())
End Try

[OleDB] Daten in MS Access einfügen (INSERT) und letzte Insert-ID ermitteln

Dim DB_OleDBCommandInsert As New System.Data.OleDb.OleDbCommand
Dim DB_OleDBCommandSelect As New System.Data.OleDb.OleDbCommand

With DB_OleDBCommandInsert
    .CommandText = "INSERT INTO Users (Name) VALUES ('Horst')"
    .Connection = frmMain.DB_OleDBConnection ' Connection wurde in frmMain erzeugt
    .ExecuteNonQuery()
    .Dispose()
End With

Dim iID As Integer = -1 ' Platzhalter für die Insert-ID

'letzte Insert ID zurückholen, ab Provider=Microsoft.Jet.OLEDB.4.0 möglich
With DB_OleDBCommandSelect
    .CommandText = "SELECT @@IDENTITY"
    .Connection = frmMain.DB_OleDBConnection ' Connection wurde in frmMain erzeugt
    iID = .ExecuteScalar()
    .Dispose()
End With

DB_OleDBCommandInsert = Nothing
DB_OleDBCommandSelect = Nothing

[OleDB] Datenzugriff (SELECT)

Dim DB_OleDBConnection As System.Data.OleDb.OleDbConnection
Dim DB_OleDBCommandSelect As New System.Data.OleDb.OleDbCommand
Dim DB_OleDBReader As System.Data.OleDb.OleDbDataReader

DB_OleDBConnection = New System.Data.OleDb.OleDbConnection

With DB_OleDBConnection
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyDatabase.mdb"
    .Open()
End With

With DB_OleDBCommandSelect
    .CommandText = "SELECT ID, Name FROM Users"
    .Connection = DB_OleDBConnection
    DB_OleDBReader = .ExecuteReader()
    .Dispose()
End With

With DB_OleDBReader
    If .HasRows Then
        Do While .Read()
            System.Console.WriteLine(.Item("Name"))
        Loop
    End If
    .Close()
End With

DB_OleDBCommandSelect = Nothing

DB_OleDBConnection.Close()
DB_OleDBConnection.Dispose()
DB_OleDBConnection = Nothing

oder

Dim sConnectionString
Dim sSQL As String

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydatabase.mdb"
sSQL = "SELECT ID, Name FROM Users"

Dim DB_Conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
Dim DB_Cmd As New System.Data.OleDb.OleDbCommand(sSQL, DB_Conn)
Dim DB_DataReader As System.Data.OleDb.OleDbDataReader

DB_Conn.Open()

DB_DataReader = DB_Cmd.ExecuteReader()

If DB_DataReader.HasRows Then
    Do While DB_DataReader.Read()
        System.Console.WriteLine(DB_DataReader.Item("Name"))
    Loop
End If

DB_DataReader.Close()
DB_Cmd.Dispose()
DB_Cmd = Nothing
DB_Conn.Close()
DB_Conn.Dispose()
DB_Conn = Nothing