7. Toegang tot de databases
7.1. Algemeen
Er bestaan talrijke databases voor Windows-platforms. Om hier toegang toe te krijgen, maken applicaties gebruik van programma's die drivers worden genoemd.
 |
In het bovenstaande schema heeft de driver twee interfaces:
- de interface I1 die aan de applicatie wordt aangeboden
- de interface I2 naar de database
Om te voorkomen dat een applicatie die is geschreven voor een database met de naam B1 opnieuw moet worden geschreven bij een migratie naar een andere database met de naam B2, is er gewerkt aan de standaardisatie van de interface I1. Als er databases worden gebruikt die gebruikmaken van „genormaliseerde” stuurprogramma’s, wordt de database B1 geleverd met een stuurprogramma P1, de database B2 met een driver P2, en de interface I1 van deze twee drivers zal identiek zijn. Daardoor hoeft de applicatie niet opnieuw te worden geschreven. Zo kan bijvoorbeeld een database ACCESS worden gemigreerd naar een database MySQL zonder de applicatie te wijzigen.
Er zijn twee soorten gestandaardiseerde stuurprogramma's:
- de stuurprogramma’s ODBC (Open DataBase Connectivity)
- de OLE- en DB-drivers (Object Linking and Embedding DataBase)
De stuurprogramma's ODBC bieden toegang tot databases. De gegevensbronnen voor de stuurprogramma's OLE en DB zijn veelzijdiger: databases, e-mailsystemen, adresboeken, ... Er zijn geen beperkingen. Elke gegevensbron kan het onderwerp zijn van een OLE- of DB-driver, indien een ontwikkelaar daartoe besluit. Het voordeel is uiteraard groot: men heeft uniforme toegang tot een grote verscheidenheid aan gegevens.
Het .NET-platform wordt geleverd met twee soorten klassen voor gegevenstoegang:
- de klassen SQL en Server.NET
- de klassen OLE en Db.NET
De eerste klassen bieden directe toegang tot de Microsoft-server zonder tussenliggende driver. De tweede klassen bieden toegang tot de gegevensbronnen.
Het .NET-platform wordt (mei 2002) geleverd met drie stuurprogramma's: OLE, DB en SQL, respectievelijk voor: SQL Server, Oracle en Microsoft Jet (Access). Als men wil werken met een database die wel een stuurprogramma ODBC heeft, maar geen stuurprogramma’s OLE en DB, dan is dat niet mogelijk. Zo is het bijvoorbeeld niet mogelijk om te werken met de SGBD en MySQL, die (mei 2002) geen stuurprogramma's OLE en DB bieden. Er bestaat echter een reeks klassen die toegang bieden tot de gegevensbronnen ODBC, namelijk de klassen odbc.net. Deze worden niet standaard meegeleverd met SDK en moeten worden gedownload van de website van Microsoft. In de volgende voorbeelden zullen we vooral deze klassen gebruiken, omdat de meeste databases onder Windows met een dergelijke driver worden geleverd. Hier volgt bijvoorbeeld een lijst met ODBC-stuurprogramma’s die op een Windows 2000-computer zijn geïnstalleerd (Startmenu/Instellingen/Configuratiescherm/Systeembeheer):
We selecteren het pictogram Gegevensbron ODBC:
7.2. De twee manieren om een gegevensbron te gebruiken
Het platform .NET biedt twee verschillende manieren om een gegevensbron te gebruiken:
- verbonden modus
- offline-modus
In de modus met verbinding maakt de applicatie
- een verbinding met de gegevensbron
- werkt met de gegevensbron in lees-/schrijfmodus
- sluit de verbinding af
In de offline modus doet de app het volgende:
- opent de app een verbinding met de gegevensbron
- haalt een kopie in het geheugen op van alle of een deel van de gegevens uit de bron
- sluit de verbinding
- werkt met de kopie in het geheugen van de gegevens (lezen/schrijven)
- zodra het werk klaar is, opent de app een verbinding, stuurt de gewijzigde gegevens naar de gegevensbron zodat deze ze verwerkt, en sluit de verbinding
In beide gevallen kost het verwerken en bijwerken van de gegevens tijd. Stel dat deze updates worden uitgevoerd door een gebruiker die gegevens invoert, dan kan dit tientallen minuten duren. Gedurende deze hele tijd blijft in de online modus de verbinding met de database in stand en worden de wijzigingen onmiddellijk doorgevoerd. In de offline modus is er geen verbinding met de database tijdens het bijwerken van de gegevens. De wijzigingen worden uitsluitend in de kopie in het geheugen aangebracht. Ze worden in één keer doorgevoerd in de gegevensbron wanneer alles voltooid is.
Wat zijn de voor- en nadelen van beide methoden?
- Een verbinding kost veel systeembronnen. Als er veel gelijktijdige verbindingen zijn, kan de offline-modus de duur ervan tot een minimum beperken. Dit geldt bijvoorbeeld voor webapplicaties met duizenden gebruikers.
- Het nadeel van de offline modus is het lastige beheer van gelijktijdige updates. Gebruiker U1 haalt gegevens op op tijdstip T1 en begint deze te wijzigen. Op tijdstip T2 krijgt gebruiker U2 ook toegang tot de gegevensbron en haalt dezelfde gegevens op. Ondertussen heeft gebruiker U1 bepaalde gegevens gewijzigd, maar deze nog niet teruggestuurd naar de gegevensbron. U2 werkt dus met gegevens waarvan sommige onjuist zijn. De klassen .NET bieden oplossingen om dit probleem aan te pakken, maar het is niet eenvoudig op te lossen.
- In de verbonden modus levert het gelijktijdig bijwerken van gegevens door meerdere gebruikers normaal gesproken geen problemen op. Aangezien de verbinding met de database in stand wordt gehouden, is het de database zelf die deze gelijktijdige updates beheert. Zo vergrendelt Oracle een rij in de database zodra een gebruiker deze wijzigt. Deze blijft vergrendeld en dus ontoegankelijk voor andere gebruikers totdat degene die de wijziging heeft aangebracht, deze bevestigt (commit) of ongedaan maakt (rollback).
- Als de gegevens over het netwerk moeten worden verzonden, moet de offline-modus worden gekozen. Deze modus biedt een momentopname van de gegevens in een object dat ‘dataset’ wordt genoemd en dat op zichzelf een database vormt. Dit object kan via het netwerk tussen computers worden uitgewisseld.
We bekijken eerst de modus ‘verbonden’.
7.3. Toegang tot gegevens in de verbonden modus
7.3.1. De databases uit het voorbeeld
We nemen een database ACCESS, ook wel articles.mdb genoemd, die slechts één tabel bevat, genaamd ARTICLES, met de volgende structuur:
naam | type |
code | 4-cijferige artikelcode |
naam | de naam (tekenreeks) |
prijs | de prijs (werkelijke) |
stock_actuel | zijn huidige voorraad (geheel getal) |
stock_minimum | de minimumvoorraad (geheel getal) waaronder het artikel moet worden bijgevuld |
De begininhoud is als volgt:
We zullen deze basis zowel gebruiken via een pilot ODBC alseen driver OLE DB om de gelijkenis tussen beide benaderingen te laten zien en omdat we over deze twee soorten drivers voor ACCESS beschikken.
We zullen ook een database MySQL DBARTICLES gebruiken met dezelfde unieke tabel ARTICLES, dezelfde inhoud heeft en toegankelijk is via een driver ODBC, om aan te tonen dat de applicatie die is geschreven om de database ACCESS te benutten, niet hoeft te worden aangepast om de database MySQL te gebruiken. De database DBARTICLES is toegankelijk voor een gebruiker met de naam admarticles en het wachtwoord mdparticles. De volgende schermafbeelding toont de inhoud van de database MySQL:
C:\mysql\bin>mysql --database=dbarticles --user=admarticles --password=mdparticles
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.49-max-debug
Type 'help' for help.
mysql> show tables;
+----------------------+
| Tables_in_dbarticles |
+----------------------+
| articles |
+----------------------+
1 row in set (0.01 sec)
mysql> select * from articles;
+------+--------------------------------+------+--------------+---------------+
| code | nom | prix | stock_actuel | stock_minimum |
+------+--------------------------------+------+--------------+---------------+
| a300 | vÚlo | 2500 | 10 | 5 |
| b300 | pompe | 56 | 62 | 45 |
| c300 | arc | 3500 | 10 | 20 |
| d300 | flÞches - lot de 6 | 780 | 12 | 20 |
| e300 | combinaison de plongÚe | 2800 | 34 | 7 |
| f300 | bouteilles d'oxygÞne | 800 | 10 | 5 |
+------+--------------------------------+------+--------------+---------------+
6 rows in set (0.02 sec)
mysql> describe articles;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| code | text | YES | | NULL | |
| nom | text | YES | | NULL | |
| prix | double | YES | | NULL | |
| stock_actuel | smallint(6) | YES | | NULL | |
| stock_minimum | smallint(6) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> exit
Bye
Ga als volgt te werk om de database ACCESS in te stellen als gegevensbron voor ODBC:
- activeer de gegevensbronbeheerder ODBC zoals hierboven weergegeven en selecteer het tabblad User DSN (DSN = Data Source Name)
- voeg een bron toe met de knop Add, geef aan dat deze bron toegankelijk is via een stuurprogramma Access en voer het volgende uit: Terminer:
- Geef de gegevensbron de naam articles-access, voer een vrije beschrijving in en gebruik de knop Sélectionner om het bestand .mdb uit de database aan te wijzen. Sluit af met OK.
De nieuwe gegevensbron verschijnt vervolgens in de lijst met gebruikersbronnen DSN:
Om de database MySQL DBARTICLES als gegevensbron ODBC in te stellen, gaat u als volgt te werk:
- open de gegevensbronbeheerder ODBC zoals hierboven weergegeven en selecteer het tabblad ‘Gebruiker’ DSN. Voeg een nieuwe gegevensbron toe met Add en selecteer de driver ODBC van MySQL.
- Voer Terminer in. Er verschijnt dan een configuratiepagina voor de bron MySQL:
54321
- in (1) geven we een naam aan onze gegevensbron ODBC
- in (2) geven we de machine aan waarop de server MySQL zich bevindt. Hier vullen we localhost in om aan te geven dat deze zich op dezelfde machine bevindt als onze applicatie. Als de server MySQL zich op een externe machine M zou bevinden, zouden we daar de naam ervan invullen en zou onze applicatie dan zonder aanpassingen met een externe database werken.
- In (3) vullen we de naam van de database in. Hier heet deze DBARTICLES.
- In (4) vullen we de gebruikersnaam admarticles in en in (5) het wachtwoord mdparticles.
7.3.2. Gebruik van een driver ODBC
In een toepassing die een database in de ‘connected’ modus gebruikt, zijn doorgaans de volgende stappen te volgen:
- Verbinding maken met de database
- Verzenden van query’s SQL naar de database
- Ontvangen en verwerken van de resultaten van deze query's
- De verbinding afsluiten
Stappen 2 en 3 worden herhaaldelijk uitgevoerd, terwijl de verbinding pas wordt verbroken aan het einde van het gebruik van de database. Dit is een relatief klassiek patroon waarmee u wellicht vertrouwd bent als u al eens interactief met een database hebt gewerkt. Deze stappen zijn hetzelfde, ongeacht of de database wordt gebruikt via een driver ODBC of een driver OLE DB. Hieronder geven we een voorbeeld met de klassen .NET voor het beheer van gegevensbronnen ODBC. Het programma heet liste en accepteert als parameter de naam DSN van een gegevensbron ODBC met een tabel ARTICLES. Vervolgens geeft het de inhoud van deze tabel weer:
dos>liste
syntaxe : pg dsnArticles
dos>liste articles-access
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
dos>liste mysql-artices
Erreur d'exploitation de la base de données (ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified)
dos>liste mysql-articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
Uit bovenstaande resultaten blijkt dat het programma zowel de inhoud van de database ACCESS als die van de database MySQL heeft weergegeven. Laten we nu de code van dit programma eens bekijken:
' opties
Option Explicit On
Option Strict On
' naamruimten
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports Microsoft.VisualBasic
Module db1
Sub main(ByVal args As String())
' console-applicatie
' toont de inhoud van een tabel ARTICLES uit een database DSN
' waarvan de naam als parameter is doorgegeven
Const syntaxe As String = "syntaxe : pg dsnArticles"
Const tabArticles As String = "articles" ' la table des articles
' controle van de parameters
' is er 1 parameter
If args.Length <> 1 Then
' foutmelding
Console.Error.WriteLine(syntaxe)
' einde
Environment.Exit(1)
End If
' de parameter wordt opgehaald
Dim dsnArticles As String = args(0) ' la base DSN
' voorbereiding van de verbinding met de database
Dim articlesConn As OdbcConnection = Nothing ' la connexion
Dim myReader As OdbcDataReader = Nothing ' le lecteur de données
' poging om toegang te krijgen tot de database
Try
' verbindingsstring naar de database
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
' uitvoering van een commando SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOdbcCommand As New OdbcCommand(sqlText)
myOdbcCommand.Connection = articlesConn
myReader = myOdbcCommand.ExecuteReader()
' Verwerking van de opgehaalde tabel
' kolommen weergeven
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' Weergave van de gegevens
While myReader.Read()
' verwerking van de huidige rij
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
Catch ex As Exception
Console.Error.WriteLine(("Erreur d'exploitation de la base de données " + ex.Message + ")"))
Environment.Exit(2)
Finally
' lezer sluiten
myReader.Close()
' Verbinding afsluiten
articlesConn.Close()
End Try
End Sub
End Module
De klassen voor het beheer van de bronnen ODBC bevinden zich in de naamruimte Microsoft.Data.Odbc, die we dus moeten importeren. Daarnaast bevinden zich een aantal klassen in de naamruimte System.Data.
Imports System.Data
Imports Microsoft.Data.Odbc
De door het programma gebruikte naamruimten bevinden zich in verschillende assemblages. Het programma wordt gecompileerd met de volgende opdracht:
dos>vbc /r:microsoft.data.odbc.dll /r:microsoft.visualbasic.dll /r:system.dll /r:system.data.dll db1.vb
7.3.2.1. De verbindingsfase
Een verbinding met de naam ODBC maakt gebruik van de klasse OdbcConnection. De constructor van deze klasse accepteert als parameter een zogenaamde verbindingsstring. Dit is een tekenreeks die alle parameters definieert die nodig zijn om verbinding te maken met de database. Er kunnen heel veel van deze parameters zijn, waardoor de tekenreeks complex kan zijn. De tekenreeks heeft de vorm "param1=valeur1;param2=valeur2;...;paramj=valeurj;". Hier volgen enkele mogelijke parameters voor paramj:
naam van een gebruiker die toegang krijgt tot de database | |
het wachtwoord van deze gebruiker | |
naam DSN van de database, indien deze een naam heeft | |
naam van de database waartoe toegang is verkregen | |
Als men een gegevensbron definieert als gegevensbron ODBC met behulp van de gegevensbronbeheerder ODBC, zijn deze parameters al opgegeven en opgeslagen. Het volstaat dan om de parameter DSN door te geven, die de naam DSN van de gegevensbron oplevert. Dat is wat hier gebeurt:
' voorbereiding van de verbinding met de database
Dim articlesConn As OdbcConnection = Nothing ' la connexion
Dim myReader As OdbcDataReader = Nothing ' le lecteur de données
Try
' er wordt geprobeerd toegang te krijgen tot de database
' verbindingsreeks naar de database
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
Zodra het object OdbcConnection is aangemaakt, wordt de verbinding geopend met de methode Open. Het openen van de verbinding kan mislukken, net als elke andere bewerking op de database. Daarom is de volledige code voor toegang tot de database ondergebracht in een try-catch. Zodra de verbinding tot stand is gebracht, kunnen er SQL-query’s naar de database worden verzonden.
7.3.2.2. SQL-query’s uitvoeren
Om SQL-query's uit te voeren, hebben we een Command-object nodig, in dit geval meer bepaald een OdbcCommand-object, aangezien we een ODBC-gegevensbron gebruiken. De klasse OdbcCommand heeft verschillende constructors:
- OdbcCommand(): maakt een leeg object Command aan. Om dit te kunnen gebruiken, moeten later diverse eigenschappen worden gespecificeerd:
- CommandText: de tekst van de uit te voeren query SQL
- Connection: het object OdbcConnection dat de verbinding vertegenwoordigt met de database waarop de query zal worden uitgevoerd
- CommandType: het type van de query SQL. Er zijn drie mogelijke waarden
- CommandType.Text: de eigenschap CommandText bevat de tekst van een query SQL (standaardwaarde)
- CommandType.StoredProcedure: de eigenschap CommandText bevat de naam van een opgeslagen procedure in de database
- CommandType.TableDirect: de eigenschap CommandText bevat de naam van een tabel T. Komt overeen met `select * from T`. Bestaat alleen voor de stuurprogramma's OLE en DB.
- OdbcCommand(string sqlText): de parameter sqlText wordt toegewezen aan de eigenschap CommandText. Dit is de tekst van de uit te voeren query SQL. De verbinding moet worden opgegeven in de eigenschap Connection.
- OdbcCommand(string sqlText, OdbcConnection verbinding): de parameter sqlText wordt toegewezen aan de eigenschap CommandText en de parameter connexion aan de eigenschap Connection.
Om de query SQL uit te voeren, zijn er twee methoden beschikbaar:
- OdbcdataReader ExecuteReader(): verzendt de query SELECT van CommandText naar de verbinding Connection en maakt een object OdbcDataReader aan waarmeetoegang tot alle rijen van de resultatentabel van select
- int ExecuteNOnQuery(): verstuurt het updateverzoek (INSERT, UPDATE, DELETE) van CommandText naar de verbinding Connection en geeft het aantal rijen weer dat door deze update wordt beïnvloed.
In ons voorbeeld sturen we, nadat we de verbinding met de database hebben geopend, een query SQL SELECT om de inhoud van de tabel ARTICLES op te halen:
' een commando wordt uitgevoerd SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOdbcCommand As New OdbcCommand(sqlText)
myOdbcCommand.Connection = articlesConn
myReader = myOdbcCommand.ExecuteReader()
Een zoekopdracht is doorgaans een verzoek van het type:
select col1, col2,... from table1, table2,...
where condition
order by expression
...
Alleen de trefwoorden in de eerste regel zijn verplicht, de overige zijn optioneel. Er zijn nog andere trefwoorden die hier niet worden weergegeven.
- Er wordt een join uitgevoerd met alle tabellen die achter het trefwoord from staan
- Alleen de kolommen die achter het trefwoord `select` staan, worden behouden
- Alleen de rijen die voldoen aan de voorwaarde van het trefwoord `where` worden behouden
- De resulterende rijen, gesorteerd volgens de uitdrukking van het trefwoord `order by`, vormen het resultaat van de query.
Het resultaat van een select is een tabel. Als we de vorige tabel ARTICLES als uitgangspunt nemen en de namen willen weten van de artikelen waarvan de huidige voorraad onder de minimumdrempel ligt, schrijven we:
select nom from articles where stock_actuel<stock_minimum
Als we deze op alfabetische volgorde van de namen willen, schrijven we:
select nom from articles where stock_actuel<stock_minimum order by nom
7.3.2.3. Het resultaat van een query SELECT gebruiken
Het resultaat van een query SELECT in de offline modus is een object DataReader, hier een object OdbcDataReader. Met dit object kunt u alle rijen van het resultaat achtereenvolgens ophalen en informatie verkrijgen over de kolommen van deze resultaten. Laten we enkele eigenschappen en methoden van deze klasse bekijken:
het aantal kolommen in de tabel | |
Item(i) vertegenwoordigt kolom nr. i van de huidige regel van het resultaat | |
de waarde van kolom nr. i van de huidige rij, weergegeven als type XXX (Int16, Int32, Int64, Double, String, Boolean, ...) | |
naam van kolom nr. i | |
sluit het object OdbcdataReader en geeft de bijbehorende bronnen vrij | |
gaat één regel verder in de resultatentabel. Geeft 'false' terug als dit niet mogelijk is. De nieuwe regel wordt de huidige regel van de lezer. |
Het doorlopen van het resultaat van een select gebeurt doorgaans sequentieel, net als bij tekstbestanden: men kan alleen vooruitgaan in de tabel, niet achteruit:
While myReader.Read()
' er is een rij - deze wordt verwerkt
....
' volgende rij
end while
Deze uitleg volstaat om de volgende code uit ons voorbeeld te begrijpen:
' Verwerking van de opgehaalde tabel
' weergave van de kolommen
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' weergave van gegevens
While myReader.Read()
' huidige regel verwerken
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
De enige moeilijkheid zit in de instructie waarin de waarden van de verschillende kolommen van de huidige regel worden samengevoegd:
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
De notatie regel+=myReader(i).ToString wordt vertaald naar regel+=myReader.Item(i).ToString() waarbij Item(i) de waarde is van kolom i van de huidige rij.
7.3.2.4. Vrijgeven van bronnen
De klassen OdbcReader en OdbcConnection beschikken beide over een methode Close() die de bronnen vrijgeeft die aan de aldus gesloten objecten zijn gekoppeld.
7.3.3. Gebruik van een driver OLE DB
We nemen hetzelfde voorbeeld, ditmaal met een database die wordt benaderd via een stuurprogramma OLE DB. Het platform .NET biedt een dergelijke driver voor de databases ACCESS. We gaan dus dezelfde database articles.mdb gebruiken als eerder. We willen hier laten zien dat, hoewel de klassen veranderen, de concepten hetzelfde blijven:
- de verbinding wordt weergegeven door een object OleDbConnection
- er wordt een verzoek SQL verzonden via een object OleDbCommand
- als deze query een clausule SELECT is, wordt er een object OleDbDataReader teruggestuurd om toegang te krijgen tot de rijen van de resultatentabel
Deze klassen bevinden zich in de naamruimte System.Data.OleDb. Het voorgaande programma kan eenvoudig worden aangepast om een database OLE DB te verwerken:
- vervang overal OdbcXX door OleDbXX
- de verbindingsstring wordt aangepast. Voor een database met de naam ACCESS zonder login/wachtwoord is de verbindingsstring Provider=Microsoft.JET.OLEDB.4.0;Data Source=[fichier.mdb]. Het instelbare deel van deze tekenreeks is de naam van het te gebruiken bestand ACCESS. We passen ons programma aan zodat het de naam van dit bestand als parameter accepteert.
- De te importeren naamruimte is nu System.Data.OleDb.
Ons programma ziet er nu als volgt uit:
' opties
Option Explicit On
Option Strict On
' naamruimten
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Module db2
Public Sub Main(ByVal args() As String)
' console-applicatie
' toont de inhoud van een tabel ARRTICLES uit een database DSN
' waarvan de naam als parameter is doorgegeven
Const syntaxe As String = "syntaxe : pg base_access_articles"
Const tabArticles As String = "articles" ' la table des articles
' controle van de parameters
' is er 1 parameter
If args.Length <> 1 Then
' foutmelding
Console.Error.WriteLine(syntaxe)
' einde
Environment.Exit(1)
End If
' de parameter wordt opgehaald
Dim dbArticles As String = args(0) ' la base de données
' voorbereiding van de verbinding met de database
Dim articlesConn As OleDbConnection = Nothing ' la connexion
Dim myReader As OleDbDataReader = Nothing ' le lecteur de données
' poging om toegang te krijgen tot de database
Try
' verbindingsstring naar de database
Dim connectString As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + dbArticles + ";"
articlesConn = New OleDbConnection(connectString)
articlesConn.Open()
' uitvoering van een commando SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOleDbCommand As New OleDbCommand(sqlText)
myOleDbCommand.Connection = articlesConn
myReader = myOleDbCommand.ExecuteReader()
' Verwerking van de opgehaalde tabel
' kolommen weergeven
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' Weergave van de gegevens
While myReader.Read()
' verwerking van de huidige rij
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
Catch ex As Exception
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
Environment.Exit(2)
Finally
' lezer sluiten
myReader.Close()
' Verbinding afsluiten
articlesConn.Close()
End Try
' einde
Environment.Exit(0)
End Sub
End Module
De verkregen resultaten:
dos>vbc liste.vb
E:\data\serge\MSNET\vb.net\adonet\6>dir
07/05/2002 15:09 2 325 liste.CS
07/05/2002 15:09 4 608 liste.exe
20/08/2001 11:54 86 016 ARTICLES.MDB
dos>liste articles.mdb
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
7.3.4. Een tabel bijwerken
In de voorgaande voorbeelden werd alleen de inhoud van een tabel weergegeven. We passen ons programma voor het beheer van de artikeldatabase aan, zodat het deze ook kan wijzigen. Het programma heet sql. Als parameter wordt de naam DSN van de te beheren artikeldatabase doorgegeven. De gebruiker voert de commando’s SQL rechtstreeks via het toetsenbord in, waarna het programma deze uitvoert, zoals blijkt uit de volgende resultaten die zijn verkregen op de artikeldatabase MySQL:
dos>vbc /r:microsoft.data.odbc.dll sql.vb
dos>sql mysql-articles
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
Requête SQL (fin pour arrêter) : select * from articles where stock_actuel<stock_minimum
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
Requête SQL (fin pour arrêter) : insert into articles values ("1","1",1,1,1)
Il y a eu 1 ligne(s) modifiée(s)
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
1 1 1 1 1
Requête SQL (fin pour arrêter) : update articles set nom="2" where nom="1"
Il y a eu 1 ligne(s) modifiée(s)
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
1 2 1 1 1
Requête SQL (fin pour arrêter) : delete from articles where code="1"
Il y a eu 1 ligne(s) modifiée(s)
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
Requête SQL (fin pour arrêter) : select * from articles order by nom asc
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
c300 arc 3500 10 20
f300 bouteilles d'oxygène 800 10 5
e300 combinaison de plongée 2800 34 7
d300 flèches - lot de 6 780 12 20
b300 pompe 56 62 45
a300 vélo 2500 10 5
Requête SQL (fin pour arrêter) : fin
Het programma is als volgt:
' opties
Option Explicit On
Option Strict On
' naamruimten
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports System.Data.OleDb
Imports System.Text.RegularExpressions
Imports System.Collections
Imports Microsoft.VisualBasic
Module db3
Public Sub Main(ByVal args() As String)
' console-applicatie
' voert via het toetsenbord ingevoerde SQL-verzoeken uit op een
' ARTICLES-tabel van een DSN-database waarvan de naam als parameter is doorgegeven
Const syntaxe As String = "syntaxe : pg dsnArticles"
' controle van de parameters
' zijn er 2 parameters
If args.Length <> 1 Then
' foutmelding
Console.Error.WriteLine(syntaxe)
' einde
Environment.Exit(1)
End If 'if
' de parameter wordt opgehaald
Dim dsnArticles As String = args(0)
' verbindingsstring naar de database
Dim connectString As String = "DSN=" + dsnArticles + ";"
' voorbereiding van de verbinding met de database
Dim articlesConn As OdbcConnection = Nothing
Dim sqlCommand As OdbcCommand = Nothing
Try
' er wordt geprobeerd toegang te krijgen tot de database
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
' er wordt een command-object aangemaakt
sqlCommand = New OdbcCommand("", articlesConn)
'try
Catch ex As Exception
' foutmelding
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
' vrijgeven van bronnen
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(2)
End Try 'catch
' er wordt een woordenboek samengesteld van de geaccepteerde SQL-opdrachten
Dim commandesSQL() As String = {"select", "insert", "update", "delete"}
Dim dicoCommandes As New Hashtable
Dim i As Integer
For i = 0 To commandesSQL.Length - 1
dicoCommandes.Add(commandesSQL(i), True)
Next i
' lezen en uitvoeren van de via het toetsenbord ingevoerde commando's SQL
Dim requête As String = Nothing ' texte de la requête SQL
Dim champs() As String ' les champs de la requête
Dim modèle As New Regex("\s+")
' lus voor invoer en uitvoering van SQL-opdrachten die via het toetsenbord zijn ingevoerd
While True
' geen fout bij de start
Dim erreur As Boolean = False
' verzoek om de query
Console.Out.Write(ControlChars.Lf + "Requête SQL (fin pour arrêter) : ")
requête = Console.In.ReadLine().Trim().ToLower()
' klaar?
If requête = "fin" Then
Exit While
End If
' het verzoek wordt opgesplitst in velden
champs = modèle.Split(requête)
' geldige aanvraag?
If champs.Length = 0 Or Not dicoCommandes.ContainsKey(champs(0)) Then
' foutmelding
Console.Error.WriteLine("Requête invalide. Utilisez select, insert, update, delete")
' volgende verzoek
erreur = True
End If
If Not erreur Then
' het Command-object voorbereiden om de aanvraag uit te voeren
sqlCommand.CommandText = requête
' het verzoek wordt uitgevoerd
Try
If champs(0) = "select" Then
executeSelect(sqlCommand)
Else
executeUpdate(sqlCommand)
End If
Catch ex As Exception
' foutmelding
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
End Try
End If
End While
' vrijgeven van bronnen
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(0)
End Sub
' uitvoering van een updateverzoek
Sub executeUpdate(ByVal sqlCommand As OdbcCommand)
' voert sqlCommand uit, een updateverzoek
Dim nbLignes As Integer = sqlCommand.ExecuteNonQuery()
' weergave
Console.Out.WriteLine(("Il y a eu " & nbLignes & " ligne(s) modifiée(s)"))
End Sub
' uitvoering van een SELECT-query
Sub executeSelect(ByVal sqlCommand As OdbcCommand)
' voert sqlCommand uit, select-query
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' verwerking van de opgehaalde tabel
' kolommen weergeven
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' Weergave van de gegevens
While myReader.Read()
' verwerking van de huidige rij
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
' Weergave
Console.WriteLine(ligne)
End While
' vrijgeven van bronnen
myReader.Close()
End Sub
End Module
We bespreken hier alleen wat er nieuw is ten opzichte van het vorige programma:
- We stellen een woordenboek samen van de geaccepteerde SQL-opdrachten:
' er wordt een woordenboek samengesteld van de geaccepteerde SQL-opdrachten
Dim commandesSQL() As String = {"select", "insert", "update", "delete"}
Dim dicoCommandes As New Hashtable
Dim i As Integer
For i = 0 To commandesSQL.Length - 1
dicoCommandes.Add(commandesSQL(i), True)
Next i
waardoor we vervolgens eenvoudig kunnen controleren of het eerste woord (veld [0]) van de ingevoerde zoekopdracht een van de vier geaccepteerde commando's is:
' geldige query?
If champs.Length = 0 Or Not dicoCommandes.ContainsKey(champs(0)) Then
' foutmelding
Console.Error.WriteLine("Requête invalide. Utilisez select, insert, update, delete")
' volgende query
erreur = True
End If 'if
- Eerder was de query opgesplitst in velden met behulp van de methode Split van de klasse RegEx:
Dim modèle As New Regex("\s+")
....
' de query wordt opgesplitst in velden
champs = modèle.Split(requête)
De woorden waaruit de query bestaat, mogen door een willekeurig aantal spaties van elkaar worden gescheiden.
- Bij het uitvoeren van een query select wordt niet dezelfde methode gebruikt als bij een updatequery (insert, update, delete). Daarom moet er een test worden uitgevoerd en moet voor elk van deze twee gevallen een andere functie worden uitgevoerd:
' het Command-object wordt voorbereid om de query uit te voeren
sqlCommand.CommandText = requête
' het verzoek wordt uitgevoerd
Try
If champs(0) = "select" Then
executeSelect(sqlCommand)
Else
executeUpdate(sqlCommand)
End If 'try
Catch ex As Exception
' foutmelding
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
End Try
Het uitvoeren van een query SQL kan een uitzondering genereren die hier wordt afgehandeld.
- De functie executeSelect bevat alles wat in de voorgaande voorbeelden is behandeld.
- De functie executeUpdate maakt gebruik van de methode ExecuteNonQuery van de klasse OdbcCommand, die het aantal regels retourneert waarop de opdracht van invloed is.
7.3.5. IMPOTS
We gebruiken het object impôt dat in een vorig hoofdstuk is aangemaakt:
' opties
Option Strict On
Option Explicit On
' naamruimten
Imports System
Public Class impôt
' de gegevens die nodig zijn voor de belastingberekening
' zijn afkomstig van een externe bron
Private limites(), coeffR(), coeffN() As Decimal
' fabrikant
Public Sub New(ByVal LIMITES() As Decimal, ByVal COEFFR() As Decimal, ByVal COEFFN() As Decimal)
' er wordt gecontroleerd of de drie tabellen dezelfde grootte hebben
Dim OK As Boolean = LIMITES.Length = COEFFR.Length And LIMITES.Length = COEFFN.Length
If Not OK Then
Throw New Exception("Les 3 tableaux fournis n'ont pas la même taille(" & LIMITES.Length & "," & COEFFR.Length & "," & COEFFN.Length & ")")
End If
' het klopt
Me.limites = LIMITES
Me.coeffR = COEFFR
Me.coeffN = COEFFN
End Sub
' berekening van de belasting
Public Function calculer(ByVal marié As Boolean, ByVal nbEnfants As Integer, ByVal salaire As Integer) As Long
' berekening van het aantal aandelen
Dim nbParts As Decimal
If marié Then
nbParts = CDec(nbEnfants) / 2 + 2
Else
nbParts = CDec(nbEnfants) / 2 + 1
End If
If nbEnfants >= 3 Then
nbParts += 0.5D
End If
' berekening van het belastbaar inkomen en het gezinsquotiënt
Dim revenu As Decimal = 0.72D * salaire
Dim QF As Decimal = revenu / nbParts
' berekening van de belasting
limites((limites.Length - 1)) = QF + 1
Dim i As Integer = 0
While QF > limites(i)
i += 1
End While
' weergave van het resultaat
Return CLng(revenu * coeffR(i) - nbParts * coeffN(i))
End Function
End Class
We voegen er een nieuwe constructor aan toe waarmee de arrays limites, coeffR en coeffN kunnen worden geïnitialiseerd op basis van een database ODBC:
Imports System.Data
Imports Microsoft.Data.Odbc
Imports System.Collections
...
' constructor 2
Public Sub New(ByVal DSNimpots As String, ByVal Timpots As String, ByVal colLimites As String, ByVal colCoeffR As String, ByVal colCoeffN As String)
' initialiseert de drie limiettabellen, coeffR, coeffN, op basis van
' de inhoud van de tabel Timpots uit de database ODBC DSNimpots
' colLimites, colCoeffR, colCoeffN zijn de drie kolommen van deze tabel
' kan een uitzondering veroorzaken
Dim connectString As String = "DSN=" + DSNimpots + ";" ' chaîne de connexion à la base
Dim impotsConn As OdbcConnection = Nothing ' la connexion
Dim sqlCommand As OdbcCommand = Nothing ' la commande SQL
' de query SELECT
Dim selectCommand As String = "select " + colLimites + "," + colCoeffR + "," + colCoeffN + " from " + Timpots
' tabellen om de gegevens op te halen
Dim tLimites As New ArrayList
Dim tCoeffR As New ArrayList
Dim tCoeffN As New ArrayList
' er wordt geprobeerd toegang te krijgen tot de database
impotsConn = New OdbcConnection(connectString)
impotsConn.Open()
' er wordt een command-object aangemaakt
sqlCommand = New OdbcCommand(selectCommand, impotsConn)
' de query wordt uitgevoerd
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' De opgehaalde tabel wordt verwerkt
While myReader.Read()
' de gegevens van de huidige rij worden in de tabellen geplaatst
tLimites.Add(myReader(colLimites))
tCoeffR.Add(myReader(colCoeffR))
tCoeffN.Add(myReader(colCoeffN))
End While
' vrijgeven van de bronnen
myReader.Close()
impotsConn.Close()
' de dynamische tabellen worden in statische tabellen geplaatst
Me.limites = New Decimal(tLimites.Count) {}
Me.coeffR = New Decimal(tLimites.Count) {}
Me.coeffN = New Decimal(tLimites.Count) {}
Dim i As Integer
For i = 0 To tLimites.Count - 1
limites(i) = Decimal.Parse(tLimites(i).ToString())
coeffR(i) = Decimal.Parse(tCoeffR(i).ToString())
coeffN(i) = Decimal.Parse(tCoeffN(i).ToString())
Next i
End Sub
Het testprogramma werkt als volgt: het ontvangt als argumenten de parameters die moeten worden doorgegeven aan de constructor van de klasse impôt. Nadat het een object impôt heeft aangemaakt, berekent het de te betalen belasting:
Option Explicit On
Option Strict On
' naamruimten
Imports System
Imports Microsoft.VisualBasic
' testpagina
Module testimpots
Sub Main(ByVal arguments() As String)
' interactief programma voor belastingberekening
' de gebruiker voert drie gegevens in via het toetsenbord: gehuwd nbEnfants salaris
' het programma geeft vervolgens de te betalen belasting weer
Const syntaxe1 As String = "pg DSNimpots tabImpots colLimites colCoeffR colCoeffN"
Const syntaxe2 As String = "syntaxe : marié nbEnfants salaire" + ControlChars.Lf + "marié : o pour marié, n pour non marié" + ControlChars.Lf + "nbEnfants : nombre d'enfants" + ControlChars.Lf + "salaire : salaire annuel en F"
' controle van de programma-instellingen
If arguments.Length <> 5 Then
' foutmelding
Console.Error.WriteLine(syntaxe1)
' einde
Environment.Exit(1)
End If 'if
' de argumenten worden opgehaald
Dim DSNimpots As String = arguments(0)
Dim tabImpots As String = arguments(1)
Dim colLimites As String = arguments(2)
Dim colCoeffR As String = arguments(3)
Dim colCoeffN As String = arguments(4)
' een belastingobject aanmaken
Dim objImpôt As impôt = Nothing
Try
objImpôt = New impôt(DSNimpots, tabImpots, colLimites, colCoeffR, colCoeffN)
Catch ex As Exception
Console.Error.WriteLine(("L'erreur suivante s'est produite : " + ex.Message))
Environment.Exit(2)
End Try
' oneindige lus
While True
' in het begin geen fouten
Dim erreur As Boolean = False
' de parameters voor de belastingberekening worden opgevraagd
Console.Out.Write("Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :")
Dim paramètres As String = Console.In.ReadLine().Trim()
' moet er iets gebeuren?
If paramètres Is Nothing Or paramètres = "" Then
Exit While
End If
' controle van het aantal argumenten in de ingevoerde regel
Dim args As String() = paramètres.Split(Nothing)
Dim nbParamètres As Integer = args.Length
If nbParamètres <> 3 Then
Console.Error.WriteLine(syntaxe2)
erreur = True
End If
Dim marié As String
Dim nbEnfants As Integer
Dim salaire As Integer
If Not erreur Then
' controle van de geldigheid van de parameters
' getrouwd
marié = args(0).ToLower()
If marié <> "o" And marié <> "n" Then
Console.Error.WriteLine((syntaxe2 + ControlChars.Lf + "Argument marié incorrect : tapez o ou n"))
erreur = True
End If
' nbEnfants
nbEnfants = 0
Try
nbEnfants = Integer.Parse(args(1))
If nbEnfants < 0 Then
Throw New Exception
End If
Catch
Console.Error.WriteLine(syntaxe2 + "\nArgument nbEnfants incorrect : tapez un entier positif ou nul")
erreur = True
End Try
' salaris
salaire = 0
Try
salaire = Integer.Parse(args(2))
If salaire < 0 Then
Throw New Exception
End If
Catch
Console.Error.WriteLine(syntaxe2 + "\nArgument salaire incorrect : tapez un entier positif ou nul")
erreur = True
End Try
End If
If Not erreur Then
' de parameters zijn correct – de belasting wordt berekend
Console.Out.WriteLine(("impôt=" & objImpôt.calculer(marié = "o", nbEnfants, salaire).ToString + " F"))
End If
End While
End Sub
End Module
De gebruikte database is een MySQL-database met de naam DSN mysql-impots:
C:\mysql\bin>mysql --database=impots --user=admimpots --password=mdpimpots
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.49-max-debug
Type 'help' for help.
mysql> show tables;
+------------------+
| Tables_in_impots |
+------------------+
| timpots |
+------------------+
mysql> select * from timpots;
+---------+--------+---------+
| limites | coeffR | coeffN |
+---------+--------+---------+
| 12620 | 0 | 0 |
| 13190 | 0.05 | 631 |
| 15640 | 0.1 | 1290.5 |
| 24740 | 0.15 | 2072.5 |
| 31810 | 0.2 | 3309.5 |
| 39970 | 0.25 | 4900 |
| 48360 | 0.3 | 6898 |
| 55790 | 0.35 | 9316.5 |
| 92970 | 0.4 | 12106 |
| 127860 | 0.45 | 16754 |
| 151250 | 0.5 | 23147.5 |
| 172040 | 0.55 | 30710 |
| 195000 | 0.6 | 39312 |
| 0 | 0.65 | 49062 |
+---------+--------+---------+
Het uitvoeren van het testprogramma levert de volgende resultaten op:
dos>D:\data\devel\vbnet\poly\chap6\impots>vbc /r:system.data.dll /r:microsoft.data.odbc.dll /r:system.dll /t:library impots.vb
dos>vbc /r:impots.dll testimpots.vb
dos>test mysql-impots timpots limites coeffr coeffn
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :o 2 200000
impôt=22506 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :n 2 200000
impôt=33388 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :o 3 200000
impôt=16400 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :n 3 300000
impôt=50082 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :n 3 200000
impôt=22506 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :