[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

Klasse mit Konstruktor und Destruktor erzeugen

Public Class DBAccess
    Implements IDisposable 'erweitert Disposable

    ' allg Variable
    Private DB_OleDBConnection As System.Data.OleDb.OleDbConnection

    ' Konstruktor
    Public Sub New()
        DB_OleDBConnection = New System.Data.OleDb.OleDbConnection
    End Sub

    ' Destruktor
    Public Sub Dispose() Implements IDisposable.Dispose
        DB_OleDBConnection.Dispose()
        DB_OleDBConnection = Nothing
    End Sub
    
    ...
End Class

bestehende Klasse erweitern und Funktion überschreiben

' neue Klasse
Public Class ValueChangedEvent
    ' erweitert die Basisklasse
    Inherits SourceGrid.Cells.Controllers.ControllerBase

    ' überschreibt die Funktion
    Public Overrides Sub OnValueChanged(ByVal sender As SourceGrid.CellContext, ByVal e As System.EventArgs)
        ' ruft die Funktion der Basisklasse auf
        MyBase.OnValueChanged(sender, e)

        ' zus. Code
        Dim val As String = "Value of cell {0} is '{1}'"

        MessageBox.Show(sender.Grid, String.Format(val, sender.Position, sender.Value))
    End Sub
End Class

Cast-Funktionen

TryCast(QuellObjekt, ZielTyp)

  • Try … Catch ist implizit
  • löst keine Exception aus
  • gibt ‚Nothing‘ zurück, falls der Cast nicht möglich war
FMonthData = TryCast(frmMain.MdiChildren(i), frmMonth)

If Not FMonthData Is Nothing Then
  ...
End If

DirectCast(QuellObjekt, ZielTyp)

  • muß evtl. in Try … Catch
  • löst bei Fehler eine Exception aus
Private Sub tbQ_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles tbQ.KeyPress, tbV.KeyPress
   Try
      Dim tbSender As System.Windows.Forms.TextBox = DirectCast(sender, System.Windows.Forms.TextBox)
      ...
   Catch ex As Exception
      MessageBox.Show("Fehler: " & ControlChars.CrLf & ex.ToString())
   End Try
End Sub

Typ-Konvertierungen

CType

  • wandelt einen Typ in einen angegebenen anderen Typ um
  • ' wandelt die Zahl 123.45 in einen String um
    Dim n As Decimal = 123.45
    Dim s As String
    s = CType(n, String)
    

spezielle Typumwandlungen, welche immer einen übergebenes Objekt in den festgelegten Typ wandeln

  • CBool – Bool
  • Dim bBoolean As Boolean
    bBoolean = CBool(0) ' False
    bBoolean = CBool(1) ' True
    
  • CByte – Byte
  • CChar – Char
  • CDate – Datumsangaben
  • CDbl – Double
  • Dim i1 As Integer = 56
    Dim d As Double = CDbl(i1) / 3.14
    
  • CDec – Decimal
  • Dim i1 As Integer = 5000
    Dim d As Decimal = CDec(i1) / 34)
    
  • CInt – Integer
  • CLng – Long
  • Dim i1 As Integer = 1005
    Dim i2 As Integer = 2100
    Dim l As Long = CLng(i1) * CLng(i2)
    
  • CObj – Object
  • CSByte – SByte
  • CShort – Short
  • CSng – Single
  • Dim i1 As Integer = 56
    Dim z As Single = CSng(i1) / 3.4
    
  • CStr – String
  • CUInt – unsigned Integer
  • CULng – unsigned Long
  • CUShort – unsigned Short

Eine neue Klasse mit Propertys definieren

Public Class USState
   ' Eigenschaften
   Private myShortName As String
   Private myLongName As String
   
   ' Konstruktor
   Public Sub New(strLongName As String, strShortName As String)
      Me.myShortName = strShortName
      Me.myLongName = strLongName
   End Sub 'New

   ' ReadOnly Property zum auslesen des Kurznamens   
   Public ReadOnly Property ShortName() As String
      Get
         Return myShortName
      End Get
   End Property
   
   ' ReadOnly Property zum auslesen des Langnamens   
   Public ReadOnly Property LongName() As String
      Get
         Return myLongName
      End Get
   End Property
   
   ' überschreiben der bestehenden Funktion ToString()
   Public Overrides Function ToString() As String
      Return Me.ShortName + " - " + Me.LongName
   End Function 'ToString
End Class 'USState