lsNawDB

clsNawDB:
Hier staat of valt je database-toepassing mee. Dit is, wat database betreft, een redelijk eenvoudige Class. Maar als je de werking ervan door hebt dan toets je zo een gecompliceerde module in elkaar, want het is in principe allemaal ongeveer het zelfde. Voorwaarde is dan wel dat je goede SQL queries kunt bouwen. Deze Class bevat de belangrijkste elementen voor de communicatie tussen DB-bestand en GUI. Er staan lokaal werkende acties in en directe, in de DB werkende, SQL acties in. Beide hebben het nadeel dat ze maar in één richting de gegevens bewerken; of alleen in de DB - of alleen in de GUI. In beide gevallen is het dus zaak om om de andere zijde te Updaten. Lokale acties zijn wat sneller maar ook minder zeker omdat, bij storing of verkeerd afsluiten, wijzigingen niet direct in de DB worden doorgevoerd en dan verloren gaan. Met SQL-acties is praktisch elke datamanipulatie mogelijk. gewoon een kwestie van de juiste query invoegen. Hier staan alleen wat simpele queries, maar met deze als voorbeeld kun je denk ik prima vooruit. Als je het begrijpt zijn ook relationele-databewerkingen eenvoudig toe te passen. Daar clsNawDB een Class is kun je het op diverse plaatsen declareren en met eigen DB-data gebruiken. Anders is dat bij de Module modPasswordDB. Deze heeft nagenoeg dezelfde opbouw als clsNawDB maar is, ongedeclareerd, overal met dezelfde DB-data beschikbaar. Eerder zagen we al dat Database, Connection, DataSet, Adapter, DataTable, BindingSource met Event noodzakelijk zijn. Extra een DataRow per tabel (zie ook modPasswordDB) welke steeds automatisch wordt gevuld vanuit het geselecteerde record en zo eenvoudig is uit te lezen. Gebruikt ze ook voor het kopiëren van ongewijzigde waarden in queries. Enkele van deze componenten zijn als Public en anderen als Private gedeclareerd. Je ziet dus gelijk welke je ook buiten deze module kunt benutten. Om het overzichtelijk te houden is de code in logische blokken opgedeeld:

#Region "<naam>"
'' ' code blok
#End Region ' <naam>

Code welke niet door deze demo NAW GUI wordt gebruikt hoeft niet gecompileerd te worden, dat staat dan in een conditieblok:

#Const Compile = False
'#Const Compile = true

#If Compile Then
'' ' code
#End If ' compile 

Namen zijn zo gekozen dat ze, naar mijn mening, bijna voor zichzelf spreken.

Project NAW - clsNawDB.vb

Imports System.Data.SQLite

Public Class NawDB
' '' NAW-record(s) moet(en) per Form individueel beschikbaar zijn
' ''  dus staat deze in een Class

#Region "NAW"

Private _RowGetTblNAW As DataRow ' BindingSourceTblNAW.Current-Record voor uitlezen
Public ReadOnly Property RowTblNAW() As DataRow
Get
Return _RowGetTblNAW
End Get
End Property

Private Connection As New SQLiteConnection ' de connectie met het DB-bestand
Private DataSetDbNAW As DataSet = New DataSet() ' de set DB-tabellen 
Private AdapterTblNAW As SQLiteDataAdapter ' de adapter werkt direct met DB
Private DataTableTblNAW As DataTable ' de lokale tabel
Public WithEvents BindingSourceTblNAW As New BindingSource() ' binding vanuit DataTable
Public Event BindingSourceTblNAWChanged(ByVal Records As Integer) ' doorgeven van binding-Event

Public Function InitDB() As Boolean
Try
Dim i As Integer
Connection.ConnectionString = ConnectionString
      Connection.Open()
      AdapterTblNAW = New SQLiteDataAdapter("SELECT * FROM tblNAW", Connection)
      i = AdapterTblNAW.Fill(DataSetDbNAW, "tblNAW")
      DataTableTblNAW = DataSetDbNAW.Tables("tblNAW")
      BindingSourceTblNAW.DataSource = DataTableTblNAW
Catch ex As Exception
Return False
End Try
Return True
End Function

Public Sub CloseDB()
Try
Dim i As Integer
i = AdapterTblNAWUpdate()
      Connection.Close()
      BindingSourceTblNAW.Dispose()
      DataTableTblNAW.Dispose()
      AdapterTblNAW.Dispose()
      DataSetDbNAW.Dispose()
      Connection.Dispose()
Catch ex As Exception
End Try
End Sub

Public Function AdapterTblNAWUpdate() As Integer
Dim cmdBuilder As SQLiteCommandBuilder
    cmdBuilder = New SQLiteCommandBuilder(AdapterTblNAW)
Return AdapterTblNAW.Update(DataSetDbNAW, "tblNAW")
End Function

Private Sub DBNullTest()
' '' Vervangt een eventueel DBNull.Value door een lege Sting
If DBNull.Value.Equals(_RowGetTblNAW("Naam")) Then _RowGetTblNAW("Naam") = ""
If DBNull.Value.Equals(_RowGetTblNAW("Adres")) Then _RowGetTblNAW("Adres") = ""
If DBNull.Value.Equals(_RowGetTblNAW("Woonplaats")) Then _RowGetTblNAW("Woonplaats") = ""
If DBNull.Value.Equals(_RowGetTblNAW("Geheim")) Then _RowGetTblNAW("Geheim") = ""
If DBNull.Value.Equals(_RowGetTblNAW("Homepage")) Then _RowGetTblNAW("Homepage") = ""
If DBNull.Value.Equals(_RowGetTblNAW("Relatiesoort")) Then _RowGetTblNAW("Relatiesoort") = 0
End Sub

Private Sub BindingSourceTblNAW_CurrentChanged(ByVal sender As Object, ByVal e As EventArgs) _
Handles BindingSourceTblNAW.CurrentChanged
' '' Vult, na wijzigen van SelectedIndexChange, _RowGetTblNAW met het huidige record
' '' Roept BindingSourceTblPassword_CurrentChanged-Event aan bij gebruikers-Class
If BindingSourceTblNAW.Count > 0 Then
' '' CType() tegen Late Binding Error in Mobile
_RowGetTblNAW = CType(BindingSourceTblNAW.Current, DataRowView).Row
      DBNullTest()
RaiseEvent BindingSourceTblNAWChanged(BindingSourceTblNAW.Count)
End If
End Sub

#End Region ' NAW
' '' ______________________________________________________________________________
' '' Acties via een DataSet:
' '' Deze zijn lokaal, dus sneller maar ook minder veilig 
' '' Vergeet je een DataAdapter.Update of ongewone beëindigen van het programma 
' ''  dan gaan de wijzigen verloren
' '' DataBinding van Controls is ook lokaal, dus moet je wijzigingen middels prog.code
' ''  in de database opslaan
' '' 
' '' Acties via een SQL statement:
' '' Deze werken direct in de database, dat is veilig maar relatief traag
' '' Zonder DataAdapter.Fill komt het niet in je DataTable terecht
' ''
' '' Het Form frmNAW gebruikt slecht 1 van onderstaande acties daar BindingSource ook
' ''  diverse mogelijkheden heeft
' '' Gebruik ze als model voor uitbreiding of andere ontwikkelingen

#Const Compile = False
'#Const Compile = true

#Region "Acties via DataSet"

#If Compile Then

Private Function UpdateInsert(ByVal ID As Integer, ByVal Naam As String, _
ByVal Adres As String, ByVal Woonplaats As String, ByVal Geheim As String, _
ByVal Homepage As String, ByVal Relatiesoort As Integer) As Boolean
' '' ID: -1 Insert, >0 Update
Dim i As Boolean = True
Try
Dim row As DataRow
If ID = -1 Then
row = DataTableTblNAW.NewRow()
Else
' '' Dit werkt zo natuurlijk alleen met een uniek veld als ID
' '' In andere gevallen gebruik je een Row(Array)
' '' Select("<naam> = '<parameter>'") is een wat anders uitziende query 
row = DataTableTblNAW.Select("ID = '" & ID & "'")(0)
End If
row("Naam") = Naam
      row("Adres") = Adres
      row("Woonplaats") = Woonplaats
      row("Geheim") = Geheim
      row("Homepage") = Homepage
      row("Relatiesoort") = Relatiesoort
Catch ex As Exception
      i = False
End Try
Return i
End Function

Public Function NAWUpdate(ByVal ID As Integer, ByVal Naam As String, ByVal Adres As String, _
ByVal Woonplaats As String, ByVal Geheim As String, ByVal Homepage As String, _
ByVal Relatiesoort As Integer) As Boolean
Return UpdateInsert(ID, Naam, Adres, Woonplaats, Geheim, Homepage, Relatiesoort)
End Function

Public Function NAWInsert(ByVal Naam As String, ByVal Adres As String, _
ByVal Woonplaats As String, ByVal Geheim As String, ByVal Homepage As String, _
ByVal Relatiesoort As Integer) As Boolean
Return UpdateInsert(-1, Naam, Adres, Woonplaats, Geheim, Homepage, Relatiesoort)
End Function

Public Function NAWDelete(ByVal ID As Integer) As Boolean
Dim i As Boolean = True
Try
' '' Dit werkt zo natuurlijk alleen met een uniek veld als ID
' '' In andere gevallen gebruik je een Row(Array)
' '' Select("<naam> = '<parameter>'") is een wat anders uitziende query 
Dim row As DataRow = DataTableTblNAW.Select("ID = '" & ID & "'")(0)
      row.Delete()
Catch ex As Exception
      i = False
End Try
Return i
End Function

#End If ' compile

#End Region ' Acties via DataSet

#Region "Acties via SQL statements"

Public Function NAWSelectSQL(ByVal Relatiesoort As Integer) As Integer
' '' Relatiesoort: -1 of 0 = alles, >0 selectief
Dim Query0 As String = "SELECT * FROM tblNAW"
Dim Query1 As String = " WHERE (Relatiesoort = @Relatiesoort)"
Dim i As Integer = 0
Try
If Relatiesoort = 0 OrElse Relatiesoort = -1 Then
AdapterTblNAW.SelectCommand.CommandText = Query0
Else
AdapterTblNAW.SelectCommand.Parameters.Add("@Relatiesoort", _
                                                   SqlDbType.Int).Value = Relatiesoort
        AdapterTblNAW.SelectCommand.CommandText = Query0 & Query1
End If
DataTableTblNAW.Clear()
      i = AdapterTblNAW.Fill(DataTableTblNAW)
Catch ex As Exception
Return -1
End Try
Return i
End Function

#If Compile Then

Private Function NAWUpdateInsertSQL(ByVal ID As Integer, ByVal Naam As String, _
ByVal Adres As String, ByVal Woonplaats As String, ByVal Geheim As String, _
ByVal Homepage As String, ByVal Relatiesoort As Integer) As Boolean
' '' ID: -1 Insert, >0 Update
Dim i As Integer = 0
Try
Dim cmd As SQLiteCommand = Connection.CreateCommand()
      cmd.Connection.Open()
Dim p As SQLiteParameter = Nothing
cmd = Connection.CreateCommand()
If ID = -1 Then
cmd.CommandText = _
"INSERT " & _
"INTO NAW (ID, Naam, Adres, Woonplaats, Geheim, Homepage, Relatiesoort) " & _
"VALUES (@Naam, @Adres, @Woonplaats, @Geheim, @Homepage, @Relatiesoort)"
Else
cmd.CommandText = _
"UPDATE NAW " & _
"SET Naam = @Naam, Adres = @Adres, Woonplaats = @Woonplaats, Geheim = @Geheim, " & _
"Homepage = @Homepage, Relatiesoort = @Relatiesoort " & _
"WHERE (ID = @ID)"
p = cmd.Parameters.Add("@ID", SqlDbType.Int)
        p.Value = ID
        p.SourceVersion = DataRowVersion.Original
End If
p = cmd.Parameters.Add("@Naam", SqlDbType.VarChar, 32)
      p.Value = Naam
      p.SourceVersion = DataRowVersion.Current
      p = cmd.Parameters.Add("@Adres", SqlDbType.VarChar, 32)
      p.Value = Adres
      p.SourceVersion = DataRowVersion.Current
      p = cmd.Parameters.Add("@Woonplaats", SqlDbType.VarChar, 32)
      p.Value = Woonplaats
      p.SourceVersion = DataRowVersion.Current
      p = cmd.Parameters.Add("@Geheim", SqlDbType.VarChar, 100)
      p.Value = Geheim
      p.SourceVersion = DataRowVersion.Current
      p = cmd.Parameters.Add("@Homepage", SqlDbType.VarChar, 260)
      p.Value = Homepage
      p.SourceVersion = DataRowVersion.Current
      p = cmd.Parameters.Add("@Relatiesoort", SqlDbType.Int)
      p.Value = Relatiesoort
      p.SourceVersion = DataRowVersion.Current
      i = cmd.ExecuteNonQuery()
      AdapterTblNAW.UpdateCommand = cmd
      cmd.Connection.Close()
      cmd.Dispose()
If i = 0 Then
Throw New ApplicationException()
End If
i = AdapterTblNAW.Fill(DataSetDbNAW, "tblNAW")
Catch ex As Exception
Return False
End Try
Return True
End Function

Public Function NAWUpdateSQL(ByVal ID As Integer, ByVal Naam As String, _
ByVal Adres As String, ByVal Woonplaats As String, ByVal Geheim As String, _
ByVal Homepage As String, ByVal Relatiesoort As Integer) As Boolean
Return NAWUpdateInsertSQL(ID, Naam, Adres, Woonplaats, Geheim, Homepage, Relatiesoort)
End Function

Public Function NAWInsertSQL(ByVal Naam As String, ByVal Adres As String, _
ByVal Woonplaats As String, ByVal Geheim As String, ByVal Homepage As String, _
ByVal Relatiesoort As Integer) As Boolean
Return NAWUpdateInsertSQL(-1, Naam, Adres, Woonplaats, Geheim, Homepage, Relatiesoort)
End Function

Public Function NAWDeleteSQL(ByVal ID As Integer) As Boolean
Dim i As Integer = 0
Try
Dim cmd As SQLiteCommand = Connection.CreateCommand()
      cmd.Connection.Open()
Dim p As SQLiteParameter = Nothing
cmd.CommandText = _
"DELETE " & _
"FROM NAW " & _
"WHERE (ID = @ID)"
p = cmd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
      p.Value = ID
      p.SourceVersion = DataRowVersion.Original
      i = cmd.ExecuteNonQuery()
      AdapterTblNAW.DeleteCommand = cmd
      cmd.Connection.Close()
      cmd.Dispose()
If i = 0 Then
Throw New ApplicationException()
End If
i = AdapterTblNAW.Fill(DataSetDbNAW, "tblNAW")
Catch ex As Exception
      i = -1
End Try
Return i
End Function

Public Function NAWDeleteAllSQL() As Integer
' '' Opgepast!!! alles weg en niet ongedaan te maken
' '' Return: -1 niet uitgevoerd, >=0 aantal
Dim i As Integer = 0
Try
' ''Dit doen we met een query
Dim cmd As SQLiteCommand = Connection.CreateCommand()
      cmd.Connection.Open()
Dim p As SQLiteParameter = Nothing
cmd.CommandText = _
"DELETE " & _
"FROM tblNAW"
i = cmd.ExecuteNonQuery()
      AdapterTblNAW.DeleteCommand = cmd
      cmd.Connection.Close()
      cmd.Dispose()
If i = 0 Then
Throw New ApplicationException()
End If
i = AdapterTblNAW.Fill(DataSetDbNAW, "tblNAW")
Catch ex As Exception
      i = -1
End Try
Return i
End Function

#End If ' compile

#End Region ' Acties via SQL statements

End Class