Rechnungsverwaltung: Beispieldaten

Nachdem wir im Beitrag „Rechnungsverwaltung: Datenmodell“ das Datenmodell für die Rechnungsverwaltung definiert haben, könnten wir eigentlich zur Programmierung der für die Dateneingabe benötigten Formulare übergehen. Allerdings macht die Programmierung von Formularen deutlich mehr Spaß, wenn bereits einige Beispieldaten vorliegen und man direkt damit ausprobieren kann, ob die Formulare funktionieren. Als Hilfsmittel zum Erstellen der Beispieldaten verwenden wir das im Beitrag „Beispieldaten generieren mit .NET und Bogus“ vorgestellte Werkzeug.

Vorbereitungen

Im Beitrag Datenzugriff mit .NET, LINQPad und LINQ to DB (www.access-im-unternehmen.de/****) finden Sie alles, was Sie benötigen, um die Datenbank, die Sie mit Beispieldaten füllen wollen, von LINQPad aus zu referenzieren.

Im Beitrag Beispieldaten generieren mit .NET und Bogus (www.access-im-unternehmen.de/****) finden Sie zusätzlich ausführliche Hinweise darauf, wie Sie die für die Verwendung des Tools Bogus notwendigen Schritte durchführen.

Deshalb hier nur noch einmal in aller Kürze die notwendigen Schritte:

• Installieren Sie das Tool LINQPad, mit dem Sie die vielen Bibliotheken nutzen können, die nur über .NET, aber nicht über VBA verfügbar sind.
• Installieren Sie LINQ to DB in LINQPad.
• Fügen Sie eine Verbindung zu der Datenbank hinzu, die Sie mit Beispieldaten füllen wollen. Sie sollten die Verbindungseigenschaften beispielsweise wie in Bild 1 ausfüllen.



Bild 1: Verbindungseigenschaften für die Datenbank

• Danach sollte LINQPad die Tabellen wie in Bild 2 anzeigen.



Bild 2: Die verfügbaren Tabellen

• Damit können Sie eine neue Query zu LINQPad hinzufügen, was Sie über den Registerreiter mit dem +-Zeichen erledigen. Speichern Sie die Query unter dem Namen Rechnungsbericht_Beispieldaten.
• Wählen Sie unter Connection die soeben hinzugefügte Verbindung aus.
• Legen Sie unter Language den Wert VB Program fest.
• Fügen Sie wie im Beitrag Beispieldaten generieren mit .NET und Bogus (www.access-im-unternehmen.de/****) beschrieben die Elemente hinzu, die für den Einsatz von Bogus notwendig sind.

Vorhandene Daten löschen

Wir wollen immer, wenn wir die Anwendung ausprobieren, einen frischen Satz von Daten bereitstellen. Eventuell bei einem vorherigen Test geänderte, hinzugefügte oder gelöschte Daten wollen wir wieder in den Urzustand versetzen – damit ermöglichen wir auch das automatisierte Testen von Teilen der Anwendung. Zum Löschen der Daten verwenden wir die Prozedur aus Listing 1. Diese rufen wir von der automatisch beim Hinzufügen der Query bereitgestellten Methode Main aus auf:

Public Sub DatenLoeschen
     Dim objUserQuery As UserQuery
     objUserQuery = tblAnreden.DataContext
     objUserQuery.Execute("DELETE FROM tblBestellpositionen")
     objUserQuery.Execute("DELETE FROM tblBestellungen")
     objUserQuery.Execute("DELETE FROM tblKunden")
     objUserQuery.Execute("DELETE FROM tblProdukte")
     objUserQuery.Execute("DELETE FROM tblAnreden")
     objUserQuery.Execute("DELETE FROM tblEinheiten")
     objUserQuery.Execute("DELETE FROM tblMehrwertsteuersaetze")
End Sub

Listing 1: Löschen der Daten

Sub Main
     DatenLoeschen
End Sub

Die Prozedur DatenLoeschen definiert das UserQuery-Objekt, über das wir auf die einzelnen Tabellen der Datenbank zugreifen können. Hier rufen wir für jede Tabelle einmal die Execute-Methode auf und übergeben jeweils eine DELETE-Abfrage, mit der wir die Daten der jeweiligen Tabelle löschen. Dies erledigen wir gleich für alle Tabellen der Datenbank, und zwar in einer Reihenfolge, in der zuerst die Daten aus den Tabellen gelöscht werden, die über Fremdschlüsselfelder mit anderen Tabellen verknüpft sind. Die Tabelle tblBestellpositionen ist gleich über zwei Fremdschlüsselfelder mit den Tabellen tblBestellungen und tblProdukte verknüpft, sodass wir diese zuerst leeren.

Danach folgt die Tabelle tblBestellungen, die mit der Tabelle tblKunden verknüpft ist. Die Tabelle tblKunden ist wiederum mit den Datensätzen der Tabelle tblAnreden verknüpft und wird als nächstes gelöscht. Die Tabelle tblProdukte greift auf Daten aus den Tabellen tblEinheiten und tblMehrwertsteuersaetze zu und muss folglich vor diesen Tabellen geleert werden. Wenn wir für die Beziehungen die Löschweitergabe definiert hätten, könnten wir die Löschvorgänge andersherum steuern und würden auch weniger DELETE-Anweisungen brauchen, weil ja die Inhalte verknüpfter Tabellen direkt mit gelöscht werden.

Anschließend beginnen wir damit, einige der Tabellen erneut zu füllen. Am einfachsten geht das mit den Tabellen, für die wir keine Zufallsdaten generieren müssen, sondern die wir direkt mit den gewünschten Daten füllen.

Da wäre als Erstes die Tabelle tblAnreden, die wir mit den bekannten Anreden füllen. Die dazu notwendige Prozedur finden Sie in Listing 2.

Public Sub AnredenAnlegen
     Dim objUserQuery As UserQuery
     objUserQuery = tblAnreden.DataContext
     objUserQuery.Insert(New tblAnreden With {.ID = 1, .Anredebezeichnung = "Herr"})
     objUserQuery.Insert(New tblAnreden With {.ID = 2, .Anredebezeichnung = "Frau"})
End Sub

Listing 2: Anlegen der Anreden

Auf ähnliche Weise füllen wir die Tabelle tblEinheiten, und zwar mit der Prozedur EinheitenAnlegen (siehe Listing 3).

Public Sub EinheitenAnlegen
     Dim objUserQuery As UserQuery
     objUserQuery = tblEinheiten.DataContext
     objUserQuery.Insert(New tblEinheiten With {.ID = 1, .Einheitbezeichnung = "Stück"})
     objUserQuery.Insert(New tblEinheiten With {.ID = 2, .Einheitbezeichnung = "Stunde"})
     objUserQuery.Insert(New tblEinheiten With {.ID = 3, .Einheitbezeichnung = "Pauschal"})
     objUserQuery.Insert(New tblEinheiten With {.ID = 4, .Einheitbezeichnung = "Kilo"})
     objUserQuery.Insert(New tblEinheiten With {.ID = 5, .Einheitbezeichnung = "Liter"})
     objUserQuery.Insert(New tblEinheiten With {.ID = 6, .Einheitbezeichnung = "Meter"})
     objUserQuery.Insert(New tblEinheiten With {.ID = 7, .Einheitbezeichnung = "Abonnement"})
End Sub

Listing 3: Anlegen der Einheiten

Und auch die Tabelle tblMehrwertsteuersaetze füllen wir nach diesem Schema (siehe Listing 4).

Public Sub MehrwertsteuersaetzeAnlegen
     Dim objUserQuery As UserQuery
     objUserQuery = tblMehrwertsteuersaetze.DataContext
     objUserQuery.Insert(New tblMehrwertsteuersaetze With {.ID = 1, .Mehrwertsteuersatzbezeichnung = _
         "Ermäßigter Mehrwertsteuersatz", .Mehrwertsteuersatzwert = 0,07})
     objUserQuery.Insert(New tblMehrwertsteuersaetze With {.ID = 2, .Mehrwertsteuersatzbezeichnung = _
         "Regelsteuersatz", .Mehrwertsteuersatzwert = 0,19})
End Sub

Listing 4: Anlegen der Mehrwertsteuersätze

Damit geht es nun an die wirklich interessanten Daten – nämlich die, welche wir mit dem Zufallsgenerator erstellen lassen. In welcher Reihenfolge gehen wir mit den übrigen Tabellen nun vor? Wir beginnen mit den Tabellen, die nur mit den bisher gefüllten Tabellen verknüpft sind. Hier bieten sich zunächst die Tabellen tblProdukte und tblKunden an.

Produkte per Zufallsgenerator füllen

Als Nächstes wollen wir also die Tabelle tblProdukte mit einigen Datensätzen füllen. Wir hätten gern 100 verschiedene Produkte, also fügen wir schon einmal den Aufruf der noch zu definierenden Prozedur ProdukteAnlegen mit dem Wert 100 als Parameter zur Methode Main hinzu:

Sub Main
     DatenLoeschen
     AnredenAnlegen
     MehrwertsteuersaetzeAnlegen
     EinheitenAnlegen
     ProdukteAnlegen(100)
End Sub

Die dadurch aufgerufene Prozedur finden Sie in Listing 5. Hier finden wir als Erstes den Parameter intMenge vor, dem wir die Anzahl der zu erzeugenden Produkte übergeben.

Public Sub ProdukteAnlegen(intMenge As Int32)
     Dim Beispielprodukt As New tblProdukte
     Dim objUserQuery As UserQuery
     Dim i As Int32
     objUserQuery = tblProdukte.DataContext
     Dim objFaker As New Bogus.Faker(Of tblProdukte)("de")
     objFaker.Rules(Function(f, p)
         p.Produktbezeichnung = f.Commerce.ProductName
         p.Einzelpreis = f.Commerce.Price(10, 100, 2)
         p.EinheitID = f.PickRandom(Of tblEinheiten)(tblEinheiten).ID
         p.MehrwertsteuersatzID = f.PickRandom(Of tblMehrwertsteuersaetze)(tblMehrwertsteuersaetze).ID
     End Function)
     For i = 1 To intMenge
         Beispielprodukt = objFaker.Generate
         Beispielprodukt.ID = i
         Debug.Print("ID: " & i & " " & Beispielprodukt.ID.ToString & " " & Beispielprodukt.Produktbezeichnung)
         objUserQuery.Insert(Beispielprodukt)
     Next
End Sub

Listing 5: Zufallsgesteuertes Anlegen von Produkten

Die Prozedur greift über die Tabelle tblProdukte auf den DataContext der Datenbankverbindung zu. Mit einer Lambda-Funktion (eine Erläuterung würde den Rahmen sprengen) definieren wir die Regeln für das Anlegen der Beispieldaten. Als Produktbezeichnung wählen wir die Eigenschaft ProduktName der Klasse Commerce. Für den Preis nutzen wir die Eigenschaft Price der gleichen Klasse und geben hier den kleinsten und den größten möglichen Preis an sowie die Anzahl der Nachkommastellen. Wir erhalten hiermit Preise zwischen 10 und 100 EUR mit zwei Nachkommastellen.

Dann folgen die interessanten Elemente, nämlich die zufällige Auswahl von Einträgen der Tabelle tblEinheiten. Dies erreichen wir mit der Funktion PickRandom, der wir den Typ der Klasse mit den gewünschten Daten übergeben und für die wir anschließend einen Verweis auf die zu verwendende Auflistung tblEinheiten übergeben. Von dem jeweils gewählten Element möchten wir dem Feld Einheit des neuen Produkts die Eigenschaft ID der Tabelle tblEinheiten zuweisen.

Anschließend durchläuft die Prozedur eine For…Next-Schleife über die mit dem Parameter intMenge angegebene Anzahl von Elementen. Darin erstellen wir mit der Generate-Methode des Faker-Objekts ein neues Element und weisen seiner Eigenschaft ID den Wert der Laufvariablen zu. Anschließend geben wir zur Prüfung noch ein paar Eigenschaften aus, bevor wir das Element mit der Insert-Methode zur Tabelle tblProdukte hinzufügen.

Fehlermeldung beim Hinzufügen von Produkten

Nun wollen wir den Code ausprobieren, um die Produkte hinzuzufügen. Dazu ist es wie immer wichtig, dass die Datenbank geschlossen ist. Anderenfalls kann LINQPad nicht schreibend auf die enthaltenen Tabellen zugreifen.

Beim Aufrufen der Methode Main beispielsweise mit der Taste F5 erhalten wir jedoch den Fehler aus Bild 3. Allerdings tritt dieser Fehler nicht immer auf, und er passiert meist nach einer scheinbar willkürlich angelegten Anzahl von Produktdatensätzen.

Bild 3: Fehlermeldung beim Hinzufügen von Produkten

Die Fehlermeldung weist darauf hin, dass ein eindeutiger Schlüssel verletzt wurde, also dass wir versuchen, einen bereits vorhandenen Wert nochmals in ein Feld mit einem eindeutigen Index einzufügen. Leider erhalten wir keinen Hinweis, um welches Feld es sich handelt. Also können wir nur selbst nachsehen und müssten dazu eigentlich die Datenbank öffnen – was nach sich ziehen würde, dass wir auch den Zugriff auf die Datenbank via LINQPad unterbrechen müssen, was am zuverlässigsten geschieht, indem wir LINQPad schließen.

Allerdings haben wir bereits eine Debug.Print-Anweisung hinterlegt, welche die ID und den Produktnamen des anzulegenden Datensatzes ausgibt. Hier finden wir schnell heraus, dass es nicht an der ID liegt, sondern am Produktnamen – Bogus liefert schlicht keine eindeutigen Produktnamen und so kommt es früher oder später (oder auch mal gar nicht) dazu, dass ein Produktname doppelt angelegt wird.

Fehlerbehandlung mit Try…Catch

Die performanteste Möglichkeit, dies zu umgehen, ist das Ignorieren des Fehlers und das erneute Schreiben eines Produktdatensatzes im Fehlerfall. Dazu fügen wir eine Try…Catch-Anweisung zur Schleife hinzu, die wie in Listing 6 aussieht. Wir fügen die Insert-Methode in den Try-Block ein. Tritt hier ein Fehler auf, wird der Catch-Block ausgeführt.

For i = 1 To intMenge
     Beispielprodukt = objFaker.Generate
     Beispielprodukt.ID = i
     Debug.Print("ID: " & i & " " & Beispielprodukt.ID.ToString & " " & Beispielprodukt.Produktbezeichnung)
     Try
         objUserQuery.Insert(Beispielprodukt)
     Catch e As Exception
         Debug.Print("Fehler: " & e.Message)    
         i = i - 1
     End Try
Next

Listing 6: Erweiterung um eine Fehlerbehandlung

In diesem Block geben wir, nur zur Information, die Fehlermeldung im Direktbereich von LINQPad aus. Außerdem setzen wir i um 1 zurück, damit die Prozedur einen neuen Anlauf starten kann, einen eindeutigen Produktnamen zu finden.

Wir könnten an dieser Stelle auch jeweils prüfen, ob der neu ermittelte Produktname bereits vorhanden ist, aber dies würde mit wachsender Anzahl vorhandener Produkte immer mehr Zeit in Anspruch nehmen. Die Methode mit Try…Catch scheint die performantere Möglichkeit zu sein.

Ergebnis in der Tabelle tblProdukte prüfen

Schließen wir nun LINQPad und öffnen wir die Tabelle tblProdukte der Datenbank, finden wir die gewünschten 100 Datensätze in der Tabelle tblProdukte vor (siehe Bild 4).

Bild 4: Die Tabelle tblProdukte mit Beispieldaten

Kundendaten anlegen

Als Nächstes folgen die Kundendaten. Auch für diese legen wir eine neue Prozedur an, die wir diesmal KundenAnlegen nennen (siehe Listing 7). Auch diese Prozedur erhält einen Parameter, der wieder intMenge heißt und die Anzahl der zu erstellenden Kundendatensätze entgegennimmt.

Public Sub KundenAnlegen(intMenge As Int32)
     Dim Beispielkunde As New tblKunden
     Dim objUserQuery As UserQuery
     Dim i As Int32
     Dim intGender As int32
     objUserQuery = tblKunden.DataContext
     Dim objFaker As New Bogus.Faker(Of tblKunden)("de")
     objFaker.Rules(Function(f, k)
         intGender = f.PickRandom(Of Gender)
         k.kundennummer = 99000000 + i
         k.Firma = f.Company.CompanyName
         k.AnredeID = intGender + 1
         k.Vorname = f.Name.FirstName(intGender)
         k.Nachname = f.Name.LastName()
         k.Strasse = f.Address.StreetAddress
         k.PLZ = f.Address.ZipCode
         k.Ort = f.Address.City
         k.LandID = 1
         k.UstIDNr = "DE" & f.Random.Number(100000000, 999999999)
         k.EMail = f.Internet.Email
     End Function)
     For i = 1 To intMenge
         Beispielkunde = objFaker.Generate
         Beispielkunde.ID = i
         objUserQuery.Insert(Beispielkunde)
     Next
End Sub

Listing 7: Anlegen von Kundendaten

Diesmal haben wir keine Probleme wegen der Verletzung eindeutiger Indizes zu erwarten, da in der Tabelle tblKunden neben dem Primärschlüsselfeld ID keine Felder mit eindeutigem Schlüssel versehen sind. Man könnte sich überlegen, ob man eines für die Firma vorsieht, aber es kann durchaus sein, dass es mehrere Kunden mit der gleichen Firmenadresse gibt. Und da es selbst mehrere Michael Müller in der gleichen Firma geben kann (die Firma muss nur groß genug sein), lassen wir den eindeutigen Index einfach weg.

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo