7. Database access
7.1. General
There are many databases available for Windows platforms. To access them, applications use programs called drivers.
 |
In the diagram above, the driver has two interfaces:
- the I1 interface presented to the application
- the I2 interface to the database
To prevent an application written for database B1 from having to be rewritten if migrating to a different database B2, standardization efforts have been made on interface I1. If databases using "standardized" drivers are employed, database B1 will be provided with driver P1, database B2 with driver P2, and the I1 interface of these two drivers will be identical. Thus, the application will not need to be rewritten. For example, you can migrate an ACCESS database to a MySQL database without changing the application.
There are two types of standardized drivers:
- ODBC (Open DataBase Connectivity) drivers
- OLE DB (Object Linking and Embedding DataBase) drivers
ODBC drivers provide access to databases. The data sources for OLE DB drivers are more varied: databases, email systems, directories, etc. There are no limits. Any data source can be the subject of an OLE DB driver if a vendor decides to do so. The benefit is obviously significant: you have uniform access to a wide variety of data.
The .NET platform comes with two types of data access classes:
- SQL Server.NET classes
- OLE DB.NET classes
The first classes allow direct access to Microsoft’s SQL Server DBMS without an intermediate driver. The second classes allow access to OLE DB data sources.
The .NET platform is provided (as of May 2002) with three OLE DB drivers for SQL Server, Oracle, and Microsoft Jet (Access), respectively. If you want to work with a database that has an ODBC driver but no OLE DB driver, you cannot. Thus, you cannot work with the MySQL DBMS, which (as of May 2002) does not provide an OLE DB driver. However, there is a set of classes that allow access to ODBC data sources: the odbc.net classes. These are not included by default with the SDK and must be downloaded from the Microsoft website. In the examples that follow, we will primarily use these ODBC classes because most databases on Windows come with such a driver. Here, for example, is a list of ODBC drivers installed on a Windows 2000 machine (Start Menu/Settings/Control Panel/Administrative Tools):
Select the ODBC Data Source icon:
7.2. The two ways to use a data source
The .NET platform allows you to use a data source in two different ways:
- connected mode
- disconnected mode
In connected mode, the application
- opens a connection to the data source
- works with the data source in read/write mode
- closes the connection
In offline mode, the application
- opens a connection to the data source
- retrieves a memory copy of all or part of the data from the source
- closes the connection
- works with the in-memory copy of the data in read/write mode
- when the work is finished, opens a connection, sends the modified data to the data source so it can be updated, and closes the connection
In both cases, it is the process of processing and updating the data that takes time. Imagine that these updates are performed by a user entering data; this process can take tens of minutes. During this entire time, in connected mode, the connection to the database is maintained and changes are reflected immediately. In offline mode, there is no connection to the database while the data is being updated. Changes are made only to the in-memory copy. They are reflected in the data source all at once when everything is finished.
What are the advantages and disadvantages of the two methods?
- A connection consumes system resources. If there are many simultaneous connections, offline mode helps minimize their duration. This is the case for web applications with thousands of users.
- The disadvantage of offline mode is the delicate handling of concurrent updates. User U1 retrieves data at time T1 and begins modifying it. At time T2, user U2 also accesses the data source and retrieves the same data. In the meantime, user U1 has modified some data but has not yet transmitted it to the data source. U2 is therefore working with data, some of which is incorrect. .NET classes offer solutions to manage this problem, but it is not easy to resolve.
- In connected mode, simultaneous data updates by multiple users do not normally pose a problem. Since the connection to the database is maintained, the database itself manages these simultaneous updates. Thus, Oracle locks a row in the database as soon as a user modifies it. It will remain locked—and therefore inaccessible to other users—until the user who modified it commits the change or rolls it back.
- If data needs to be shared over the network, you should choose offline mode. This mode provides a snapshot of the data in an object called a dataset, which functions as a standalone database. This object can be shared over the network between machines.
We will first examine connected mode.
7.3. Accessing data in connected mode
7.3.1. The databases in the example
We are considering an Access database named articles.mdb that contains only one table named ARTICLES with the following structure:
name | type |
code | 4-character item code |
name | its name (string) |
price | its price (actual) |
current_stock | current stock (integer) |
min_stock | the minimum stock (integer) below which the item must be restocked |
Its initial contents are as follows:
We will use this database via both an ODBC driver and an OLE DB driver to demonstrate the similarity between the two approaches and because we have both types of drivers available for ACCESS.
We will also use a MySQL database named DBARTICLES, which has the same single ARTICLES table, the same content, and is accessed via an ODBC driver, to demonstrate that the application written to use the Access database does not need to be modified to use the MySQL database. The DBARTICLES database is accessible to a user named admarticles with the password mdparticles. The following screenshot shows the contents of the MySQL database:
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
To define the ACCESS database as an ODBC data source, proceed as follows:
- Open the ODBC Data Source Administrator as shown above and select the User DSN tab (DSN = Data Source Name)
- Add a source using the Add button, specify that this source is accessible via an Access driver, and click Finish:
- Name the data source "articles-access," enter a description of your choice, and use the Select button to specify the database's .mdb file. Finish by clicking OK.
The new data source will then appear in the list of user DSNs:
To define the MySQL DBARTICLES database as an ODBC data source, proceed as follows:
- Open the ODBC Data Source Administrator as shown above and select the User DSN tab. Add a new data source using Add and select the MySQL ODBC driver.
- Click Finish. A MySQL source configuration page will then appear:
54321
- In (1), give a name to your ODBC data source
- In (2), specify the machine where the MySQL server is located. Here, we enter `localhost` to indicate that it is on the same machine as our application. If the MySQL server were on a remote machine `M`, we would enter its name here, and our application would then work with a remote database without any changes.
- In (3), enter the database name. Here, it is called DBARTICLES.
- In (4), enter the username admarticles, and in (5), the password mdparticles.
7.3.2. Using an ODBC driver
In an application using a database in connected mode, the following steps are generally involved:
- Connecting to the database
- Sending SQL queries to the database
- Receiving and processing the results of these queries
- Closing the connection
Steps 2 and 3 are performed repeatedly, with the connection closing only at the end of database operations. This is a relatively standard pattern that you may be familiar with if you have worked with a database interactively. These steps are the same whether the database is accessed via an ODBC driver or an OLE DB driver. Below is an example using the .NET classes for managing ODBC data sources. The program is called liste and takes as a parameter the DSN name of an ODBC data source containing a table named ARTICLES. It then displays the contents of this table:
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
From the results above, we can see that the program listed the contents of both the ACCESS database and the MySQL database. Let’s now examine the code for this program:
' options
Option Explicit On
Option Strict On
' namespaces
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports Microsoft.VisualBasic
Module db1
Sub main(ByVal args As String())
' application console
' displays the contents of a ARTICLES table in a DSN database
' whose name is passed in parameter
Const syntaxe As String = "syntaxe : pg dsnArticles"
Const tabArticles As String = "articles" ' table of articles
' parameter verification
' do we have 1 parameter
If args.Length <> 1 Then
' error msg
Console.Error.WriteLine(syntaxe)
' end
Environment.Exit(1)
End If
' parameter is retrieved
Dim dsnArticles As String = args(0) ' the DSN database
' preparing the connection to the comic
Dim articlesConn As OdbcConnection = Nothing ' the connection
Dim myReader As OdbcDataReader = Nothing ' the data reader
' attempt to access the database
Try
' base connection chain
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
' execution of a SQL command
Dim sqlText As String = "select * from " + tabArticles
Dim myOdbcCommand As New OdbcCommand(sqlText)
myOdbcCommand.Connection = articlesConn
myReader = myOdbcCommand.ExecuteReader()
' Using the recovered table
' column display
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))
' data display
While myReader.Read()
' current line operation
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
' drive lock
myReader.Close()
' locking connection
articlesConn.Close()
End Try
End Sub
End Module
The ODBC source management classes are located in the Microsoft.Data.Odbc namespace, which must therefore be imported. Additionally, a number of classes are located in the System.Data namespace.
Imports System.Data
Imports Microsoft.Data.Odbc
The namespaces used by the program are in different assemblies. Compile the program using the following command:
dos>vbc /r:microsoft.data.odbc.dll /r:microsoft.visualbasic.dll /r:system.dll /r:system.data.dll db1.vb
7.3.2.1. The connection phase
An ODBC connection uses the OdbcConnection class. The constructor of this class takes as a parameter what is called a connection string. This is a string of characters that defines all the parameters necessary for the connection to the database to be established. There can be many such parameters, making the string complex. The string has the form "param1=value1;param2=value2;...;paramj=valuej;". Here are some possible paramj parameters:
username of the user who will access the database | |
this user's password | |
the database's DSN name, if it has one | |
name of the database being accessed | |
If you define a data source as an ODBC data source using the ODBC Data Source Administrator, these parameters have already been specified and saved. In that case, you simply need to pass the DSN parameter, which provides the DSN name of the data source. This is what is done here:
' preparing the connection to the comic
Dim articlesConn As OdbcConnection = Nothing ' the connection
Dim myReader As OdbcDataReader = Nothing ' the data reader
Try
' attempt to access the database
' base connection chain
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
Once the OdbcConnection object is created, we open the connection using the Open method. This operation may fail, just like any other database operation. That is why the entire database access code is enclosed in a try-catch block. Once the connection is established, we can execute SQL queries on the database.
7.3.2.2. Executing SQL Queries
To execute SQL queries, we need a Command object—more specifically, an OdbcCommand object, since we are using an ODBC data source. The OdbcCommand class has several constructors:
- OdbcCommand(): creates an empty Command object. To use it, you’ll need to specify various properties later:
- CommandText: the text of the SQL query to be executed
- Connection: the OdbcConnection object representing the connection to the database on which the query will be executed
- CommandType: the type of the SQL query. There are three possible values
- CommandType.Text: the CommandText property contains the text of an SQL query (default value)
- CommandType.StoredProcedure: the CommandText property contains the name of a stored procedure in the database
- CommandType.TableDirect: the CommandText property contains the name of a table T. Equivalent to `SELECT * FROM T`. Exists only for OLE DB drivers.
- OdbcCommand(string sqlText): the sqlText parameter will be assigned to the CommandText property. This is the text of the SQL query to be executed. The connection must be specified in the Connection property.
- OdbcCommand(string sqlText, OdbcConnection connection): the sqlText parameter is assigned to the CommandText property, and the connection parameter to the Connection property.
To execute the SQL query, two methods are available:
- OdbcDataReader ExecuteReader(): sends the SELECT query from CommandText to the Connection and creates an OdbcDataReader object that provides access to all rows in the result table of the SELECT
- int ExecuteNonQuery(): sends the update query (INSERT, UPDATE, DELETE) from CommandText to the Connection and returns the number of rows affected by the update.
In our example, after opening the database connection, we issue an SQL SELECT query to retrieve the contents of the ARTICLES table:
' exécution d'une commande SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOdbcCommand As New OdbcCommand(sqlText)
myOdbcCommand.Connection = articlesConn
myReader = myOdbcCommand.ExecuteReader()
A query is typically of the following type:
Only the keywords in the first line are required; the others are optional. There are other keywords not shown here.
- A join is performed on all tables listed after the `FROM` keyword
- Only the columns following the `select` keyword are retained
- Only the rows that satisfy the condition of the `where` keyword are retained
- The resulting rows, sorted according to the expression in the `ORDER BY` keyword, form the result of the query.
The result of a SELECT is a table. If we consider the previous ARTICLES table and want the names of the items whose current stock is below the minimum threshold, we would write:
If we want them sorted alphabetically by name, we would write:
7.3.2.3. Working with the result of a SELECT query
The result of a SELECT query in disconnected mode is a DataReader object, in this case an OdbcDataReader object. This object allows you to retrieve all rows of the result sequentially and obtain information about the columns in those results. Let’s examine some properties and methods of this class:
the number of columns in the table | |
Item(i) represents column number i of the current row in the result | |
the value of column i of the current row, returned as type XXX (Int16, Int32, Int64, Double, String, Boolean, ...) | |
name of column number i | |
closes the OdbcdataReader object and releases the associated resources | |
moves forward one row in the result table. Returns false if this is not possible. The new row becomes the current row of the reader. |
Processing the result of a SELECT statement is typically a sequential operation similar to that of text files: you can only move forward in the table, not backward:
While myReader.Read()
' on a une ligne - on l'exploite
....
' ligne suivante
end while
These explanations are sufficient to understand the following code in our example:
' Exploitation de la table récupérée
' affichage des colonnes
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))
' affichage des données
While myReader.Read()
' exploitation ligne courante
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
The only difficulty lies in the statement where the values of the different columns in the current row are concatenated:
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
The notation line+=myReader(i).ToString is translated to line+=myReader.Item(i).ToString(), where Item(i) is the value of column i in the current row.
7.3.2.4. Resource Release
The OdbcReader and OdbcConnection classes both have a Close() method that releases the resources associated with the objects being closed.
7.3.3. Using an OLE DB Driver
We’ll use the same example, this time with a database accessed via an OLE DB driver. The .NET platform provides such a driver for Access databases. So we’ll use the same articles.mdb database as before. Our goal here is to show that while the classes may change, the concepts remain the same:
- the connection is represented by an OleDbConnection object
- an SQL query is issued using an OleDbCommand object
- if this query is a SELECT statement, an OleDbDataReader object is returned to access the rows of the result table
These classes are in the System.Data.OleDb namespace. The previous program can be easily adapted to work with an OLE DB database:
- Replace OdbcXX with OleDbXX everywhere
- modify the connection string. For an ACCESS database without a login/password, the connection string is Provider=Microsoft.JET.OLEDB.4.0;Data Source=[file.mdb]. The configurable part of this string is the name of the ACCESS file to use. We will modify our program so that it accepts the name of this file as a parameter.
- The namespace to import is now System.Data.OleDb.
Our program becomes the following:
' options
Option Explicit On
Option Strict On
' namespaces
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)
' application console
' displays the contents of a ARRTICLES table in a DSN database
' whose name is passed in parameter
Const syntaxe As String = "syntaxe : pg base_access_articles"
Const tabArticles As String = "articles" ' table of articles
' parameter verification
' do we have 1 parameter
If args.Length <> 1 Then
' error msg
Console.Error.WriteLine(syntaxe)
' end
Environment.Exit(1)
End If
' parameter is retrieved
Dim dbArticles As String = args(0) ' the database
' preparing the connection to the comic
Dim articlesConn As OleDbConnection = Nothing ' the connection
Dim myReader As OleDbDataReader = Nothing ' the data reader
' attempt to access the database
Try
' base connection chain
Dim connectString As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + dbArticles + ";"
articlesConn = New OleDbConnection(connectString)
articlesConn.Open()
' execution of a SQL command
Dim sqlText As String = "select * from " + tabArticles
Dim myOleDbCommand As New OleDbCommand(sqlText)
myOleDbCommand.Connection = articlesConn
myReader = myOleDbCommand.ExecuteReader()
' Using the recovered table
' column display
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))
' data display
While myReader.Read()
' current line operation
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
' drive lock
myReader.Close()
' locking connection
articlesConn.Close()
End Try
' end
Environment.Exit(0)
End Sub
End Module
The results obtained:
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. Updating a table
The previous examples simply listed the contents of a table. We will modify our product database management program so that it can modify the database. The program is called sql. We pass the DSN name of the product database to be managed as a parameter. The user types SQL commands directly on the keyboard, which the program executes, as shown by the following results obtained from the MySQL product database:
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
The program is as follows:
' options
Option Explicit On
Option Strict On
' namespaces
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)
' application console
' executes SQL requests typed on the keyboard on a
' table ARTICLES from a database DSN whose name is passed as a parameter
Const syntaxe As String = "syntaxe : pg dsnArticles"
' parameter verification
' do we have 2 parameters
If args.Length <> 1 Then
' error msg
Console.Error.WriteLine(syntaxe)
' end
Environment.Exit(1)
End If 'if
' parameter is retrieved
Dim dsnArticles As String = args(0)
' base connection chain
Dim connectString As String = "DSN=" + dsnArticles + ";"
' preparing the connection to the comic
Dim articlesConn As OdbcConnection = Nothing
Dim sqlCommand As OdbcCommand = Nothing
Try
' attempt to access the database
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
' create a command object
sqlCommand = New OdbcCommand("", articlesConn)
'try
Catch ex As Exception
' error msg
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
' freeing up resources
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(2)
End Try 'catch
' build a dictionary of accepted sql commands
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
' read-execute SQL commands typed on the keyboard
Dim requête As String = Nothing ' query text SQL
Dim champs() As String ' query fields
Dim modèle As New Regex("\s+")
' input-execution loop for SQL commands typed on keyboard
While True
' no error at start
Dim erreur As Boolean = False
' request for query
Console.Out.Write(ControlChars.Lf + "Requête SQL (fin pour arrêter) : ")
requête = Console.In.ReadLine().Trim().ToLower()
' finished?
If requête = "fin" Then
Exit While
End If
' the query is broken down into fields
champs = modèle.Split(requête)
' valid request?
If champs.Length = 0 Or Not dicoCommandes.ContainsKey(champs(0)) Then
' error msg
Console.Error.WriteLine("Requête invalide. Utilisez select, insert, update, delete")
' following request
erreur = True
End If
If Not erreur Then
' prepare the Command object to execute the request
sqlCommand.CommandText = requête
' query execution
Try
If champs(0) = "select" Then
executeSelect(sqlCommand)
Else
executeUpdate(sqlCommand)
End If
Catch ex As Exception
' error msg
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
End Try
End If
End While
' freeing up resources
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(0)
End Sub
' execute an update request
Sub executeUpdate(ByVal sqlCommand As OdbcCommand)
' executes sqlCommand, update request
Dim nbLignes As Integer = sqlCommand.ExecuteNonQuery()
' display
Console.Out.WriteLine(("Il y a eu " & nbLignes & " ligne(s) modifiée(s)"))
End Sub
' executing a Select query
Sub executeSelect(ByVal sqlCommand As OdbcCommand)
' executes sqlCommand, select query
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' Using the recovered table
' column display
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))
' data display
While myReader.Read()
' current line operation
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
' display
Console.WriteLine(ligne)
End While
' freeing up resources
myReader.Close()
End Sub
End Module
We will only comment here on what is new compared to the previous program:
- We build a dictionary of accepted SQL commands:
' on construit un dictionnaire des commandes sql acceptées
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
This then allows us to simply check if the first word (fields[0]) of the typed query is one of the four accepted commands:
' requête valide ?
If champs.Length = 0 Or Not dicoCommandes.ContainsKey(champs(0)) Then
' msg d'erreur
Console.Error.WriteLine("Requête invalide. Utilisez select, insert, update, delete")
' requête suivante
erreur = True
End If 'if
- Previously, the query had been split into fields using the Split method of the RegEx class:
Dim modèle As New Regex("\s+")
....
' the query is broken down into fields
champs = modèle.Split(requête)
The words in the query can be separated by any number of spaces.
- Executing a SELECT query does not use the same method as executing an update query (INSERT, UPDATE, DELETE). Therefore, we must perform a check and execute a different function for each of these two cases:
' préparation de l'objet Command pour exécuter la requête
sqlCommand.CommandText = requête
' exécution de la requête
Try
If champs(0) = "select" Then
executeSelect(sqlCommand)
Else
executeUpdate(sqlCommand)
End If 'try
Catch ex As Exception
' msg d'erreur
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
End Try
Executing an SQL query can generate an exception, which is handled here.
- The executeSelect function covers everything covered in the previous examples.
- The executeUpdate function uses the ExecuteNonQuery method of the OdbcCommand class, which returns the number of rows affected by the command.
7.3.5. Tax calculation
We reuse the tax object created in a previous chapter:
' options
Option Strict On
Option Explicit On
' namespaces
Imports System
Public Class impôt
' data required for tax calculation
' come from an external source
Private limites(), coeffR(), coeffN() As Decimal
' manufacturer
Public Sub New(ByVal LIMITES() As Decimal, ByVal COEFFR() As Decimal, ByVal COEFFN() As Decimal)
' we check that the 3 tablaeux are the same size
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
' it's good
Me.limites = LIMITES
Me.coeffR = COEFFR
Me.coeffN = COEFFN
End Sub
' tAX CALCULATION
Public Function calculer(ByVal marié As Boolean, ByVal nbEnfants As Integer, ByVal salaire As Integer) As Long
' calculating the number of shares
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
' calculation of taxable income & family quota
Dim revenu As Decimal = 0.72D * salaire
Dim QF As Decimal = revenu / nbParts
' tAX CALCULATION
limites((limites.Length - 1)) = QF + 1
Dim i As Integer = 0
While QF > limites(i)
i += 1
End While
' return result
Return CLng(revenu * coeffR(i) - nbParts * coeffN(i))
End Function
End Class
We add a new constructor to initialize the limit arrays, coeffR, and coeffN from an ODBC database:
Imports System.Data
Imports Microsoft.Data.Odbc
Imports System.Collections
...
' builder 2
Public Sub New(ByVal DSNimpots As String, ByVal Timpots As String, ByVal colLimites As String, ByVal colCoeffR As String, ByVal colCoeffN As String)
' initializes the three limit arrays, coeffR, coeffN from
' the contents of the Timpots table in the ODBC DSNimpots database
' colLimites, colCoeffR, colCoeffN are the three columns of this table
' can throw an exception
Dim connectString As String = "DSN=" + DSNimpots + ";" ' base connection chain
Dim impotsConn As OdbcConnection = Nothing ' the connection
Dim sqlCommand As OdbcCommand = Nothing ' the SQL command
' the SELECT query
Dim selectCommand As String = "select " + colLimites + "," + colCoeffR + "," + colCoeffN + " from " + Timpots
' tables to retrieve data
Dim tLimites As New ArrayList
Dim tCoeffR As New ArrayList
Dim tCoeffN As New ArrayList
' attempt to access the database
impotsConn = New OdbcConnection(connectString)
impotsConn.Open()
' create a command object
sqlCommand = New OdbcCommand(selectCommand, impotsConn)
' execute the query
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' Using the recovered table
While myReader.Read()
' the data of the current line are put in the tables
tLimites.Add(myReader(colLimites))
tCoeffR.Add(myReader(colCoeffR))
tCoeffN.Add(myReader(colCoeffN))
End While
' freeing up resources
myReader.Close()
impotsConn.Close()
' dynamic tables are placed in static tables
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
The test program is as follows: it receives as arguments the parameters to be passed to the tax class constructor. After constructing a tax object, it calculates the tax due:
Option Explicit On
Option Strict On
' namespaces
Imports System
Imports Microsoft.VisualBasic
' test pg
Module testimpots
Sub Main(ByVal arguments() As String)
' interactive tax calculator
' the user enters three data points on the keyboard: married nbEnfants salary
' the program then displays the tax payable
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"
' checking program parameters
If arguments.Length <> 5 Then
' error msg
Console.Error.WriteLine(syntaxe1)
' end
Environment.Exit(1)
End If 'if
' retrieve the arguments
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)
' tax object creation
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
' infinite loop
While True
' initially no errors
Dim erreur As Boolean = False
' tax calculation parameters are requested
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()
' anything to do?
If paramètres Is Nothing Or paramètres = "" Then
Exit While
End If
' check the number of arguments in the input line
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
' checking parameter validity
' married
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
' salary
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
' parameters are correct - tax is calculated
Console.Out.WriteLine(("impôt=" & objImpôt.calculer(marié = "o", nbEnfants, salaire).ToString + " F"))
End If
End While
End Sub
End Module
The database used is a MySQL database with the DSN name 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 |
+---------+--------+---------+
Running the test program yields the following results:
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 :