7. 数据库访问
7.1. 概述
Windows 平台上有许多可用的数据库。应用程序通过称为驱动程序的程序来访问这些数据库。
 |
在上图中，驱动程序有两个接口：
- 面向应用程序的 I1 接口
- 面向数据库的 I2 接口
为了避免为数据库 B1 编写的应用程序在迁移到另一个数据库 B2 时需要重写，已对接口 I1 进行了标准化工作。如果使用的是采用“标准化”驱动程序的数据库，数据库 B1 将配备驱动程序 P1，数据库 B2 将配备驱动程序 P2，且这两个驱动程序的 I1 接口将完全一致。因此，应用程序无需重写。 例如，您可以将一个 ACCESS 数据库迁移到 MySQL 数据库，而无需更改应用程序。
标准化驱动程序有两种类型：
- ODBC（开放数据库连接）驱动程序
- OLE DB（对象链接与嵌入数据库）驱动程序
ODBC 驱动程序提供对数据库的访问。OLE DB 驱动程序的数据源则更为多样：数据库、电子邮件系统、目录等。其范围不受限制。只要供应商决定支持，任何数据源都可以成为 OLE DB 驱动程序的对象。其优势显而易见：您可以统一访问各种各样的数据。
.NET 平台提供了两种类型的数据访问类：
- SQL Server.NET 类
- OLE DB.NET 类
第一类类允许在不使用中间驱动程序的情况下直接访问 Microsoft 的 SQL Server 数据库管理系统。第二类类允许访问 OLE DB 数据源。
截至 2002 年 5 月，.NET 平台提供了三个 OLE DB 驱动程序，分别对应 SQL Server、Oracle 和 Microsoft Jet（Access）。 如果您想操作一个拥有 ODBC 驱动程序但没有 OLE DB 驱动程序的数据库，则无法实现。因此，您无法操作 MySQL 数据库管理系统，因为（截至 2002 年 5 月）它不提供 OLE DB 驱动程序。不过，有一组类允许访问 ODBC 数据源：即 odbc.net 类。这些类默认不包含在 SDK 中，必须从微软网站下载。 在接下来的示例中，我们将主要使用这些 ODBC 类，因为 Windows 上的大多数数据库都附带此类驱动程序。例如，以下是安装在 Windows 2000 计算机上的 ODBC 驱动程序列表（开始菜单/设置/控制面板/管理工具）：
选择“ODBC 数据源”图标：
7.2. 使用数据源的两种方式
.NET 平台允许您通过两种不同的方式使用数据源：
- 连接模式
- 脱机模式
在连接模式下，应用程序
- 会与数据源建立连接
- 以读写模式处理数据源
- 关闭连接
在脱机模式下，应用程序
- 打开与数据源的连接
- 从源中检索全部或部分数据的内存副本
- 关闭连接
- 以读写模式处理内存中的数据副本
- 工作完成后，建立连接，将修改后的数据发送至数据源以便更新，然后关闭连接
在这两种情况下，耗时的主要环节都是数据的处理和更新过程。 试想这些更新是由用户手动输入数据完成的；这一过程可能需要数十分钟。在连接模式下，整个过程中与数据库的连接始终保持，更改会立即反映出来。而在离线模式下，数据更新期间不与数据库建立连接，更改仅对内存中的副本进行。待所有操作完成后，更改才会一次性反映到数据源中。
这两种方法各有何优缺点？
- 连接会消耗系统资源。如果存在大量并发连接，离线模式有助于最大限度地缩短连接时长。对于拥有数千名用户的 Web 应用程序，情况正是如此。
- 离线模式的缺点在于需要谨慎处理并发更新。 用户 U1 在时间 T1 检索数据并开始修改。在时间 T2，用户 U2 也访问数据源并检索了相同数据。此时，用户 U1 已修改部分数据但尚未将其传输至数据源。因此，U2 操作的数据中包含部分错误信息。.NET 类提供了管理此问题的解决方案，但解决起来并不容易。
- 在联机模式下，多用户同时更新数据通常不会造成问题。由于与数据库的连接始终保持，数据库本身会管理这些并发更新。因此，当用户修改数据库中的一行数据时，Oracle 会立即对其进行锁定。该行将保持锁定状态——其他用户无法访问——直到修改该行的用户提交更改或回滚更改为止。
- 如果需要通过网络共享数据，应选择脱机模式。该模式会将数据快照封装在一个称为数据集（dataset）的对象中，该对象可作为独立数据库运行。该对象可在不同机器之间通过网络共享。
我们将首先探讨联机模式。
7.3. 在连接模式下访问数据
7.3.1. 示例中的数据库
我们正在考虑一个名为 articles.mdb 的 Access 数据库，其中仅包含一个名为 ARTICLES 的表，其结构如下：
name | 类型 |
代码 | 4位项目代码 |
名称 | 其名称（字符串） |
价格 | 其价格（实际） |
当前库存 | 当前库存（整数） |
min_stock | 最低库存量（整数），低于该数值时必须补货 |
其初始内容如下：
我们将通过 ODBC 驱动程序和 OLE DB 驱动程序两种方式使用该数据库，以展示这两种方法的相似之处，同时也因为 ACCESS 同时支持这两种类型的驱动程序。
我们还将使用一个名为 DBARTICLES 的 MySQL 数据库，该数据库包含相同的单个 ARTICLES 表、相同的内容，并通过 ODBC 驱动程序进行访问，以此演示：针对 Access 数据库编写的应用程序无需修改即可用于 MySQL 数据库。用户 admarticles（密码 mdparticles）可访问 DBARTICLES 数据库。下图展示了 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
要将 ACCESS 数据库定义为 ODBC 数据源，请按以下步骤操作：
- 如上图所示，打开 ODBC 数据源管理器，并选择“用户 DSN”选项卡（DSN = 数据源名称）
- 使用“添加”按钮添加数据源，指定该数据源可通过 Access 驱动程序访问，然后单击“完成”：
- 将数据源命名为“articles-access”，输入您选择的描述，并使用“选择”按钮指定数据库的 .mdb 文件。最后单击“确定”。
新数据源随后将显示在用户 DSN 列表中：
要将 MySQL 的 DBARTICLES 数据库定义为 ODBC 数据源，请按以下步骤操作：
- 如上图所示，打开 ODBC 数据源管理器，并选择“用户 DSN”选项卡。使用“添加”按钮添加一个新的数据源，并选择 MySQL ODBC 驱动程序。
- 点击“完成”。随后将显示 MySQL 数据源配置页面：
54321
- 在 (1) 中，为您的 ODBC 数据源命名
- 在 (2) 中，指定 MySQL 服务器所在的机器。此处我们输入 `localhost`，表示它与我们的应用程序位于同一台机器上。如果 MySQL 服务器位于远程机器 `M` 上，我们应在此处输入其名称，这样我们的应用程序无需任何更改即可与远程数据库进行交互。
- 在 (3) 中，输入数据库名称。此处命名为 DBARTICLES。
- 在 (4) 中，输入用户名 admarticles；在 (5) 中，输入密码 mdparticles。
7.3.2. 使用 ODBC 驱动程序
在以连接模式使用数据库的应用程序中，通常涉及以下步骤：
- 连接到数据库
- 向数据库发送 SQL 查询
- 接收并处理这些查询的结果
- 关闭连接
步骤 2 和 3 会反复执行，只有在数据库操作结束时才会关闭连接。这是一种相对标准的模式，如果您曾进行过交互式数据库操作，可能对此已经很熟悉。无论通过 ODBC 驱动程序还是 OLE DB 驱动程序访问数据库，这些步骤都是相同的。 下面是一个使用 .NET 类管理 ODBC 数据源的示例。该程序名为 liste，其参数为包含名为 ARTICLES 的表的 ODBC 数据源的 DSN 名称。随后，它将显示该表的内容：
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
从上述结果可以看出，该程序列出了 ACCESS 数据库和 MySQL 数据库中的内容。现在让我们来查看该程序的代码：
' 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
ODBC 源管理类位于 Microsoft.Data.Odbc 命名空间中，因此必须导入该命名空间。此外，还有一些类位于 System.Data 命名空间中。
Imports System.Data
Imports Microsoft.Data.Odbc
程序使用的命名空间位于不同的程序集之中。请使用以下命令编译程序：
dos>vbc /r:microsoft.data.odbc.dll /r:microsoft.visualbasic.dll /r:system.dll /r:system.data.dll db1.vb
7.3.2.1. 连接阶段
ODBC 连接使用 OdbcConnection 类。该类的构造函数接受一个称为“连接字符串”的参数。这是一个字符串，用于定义建立数据库连接所需的所有参数。此类参数可能有很多，因此字符串会比较复杂。该字符串的格式为“param1=value1;param2=value2;...;paramj=valuej;”。以下是一些可能的 paramj 参数：
将访问数据库的用户名 | |
该用户的密码 | |
数据库的 DSN 名称（如有） | |
要访问的数据库的名称 | |
如果您使用 ODBC 数据源管理器将数据源定义为 ODBC 数据源，这些参数已经指定并保存。在这种情况下，您只需传递 DSN 参数，该参数提供数据源的 DSN 名称。此处就是这样操作的：
' 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()
创建 OdbcConnection 对象后，我们使用 Open 方法打开连接。此操作可能会失败，就像任何其他数据库操作一样。这就是为什么整个数据库访问代码都被包含在 try-catch 代码块中。一旦建立连接，我们就可以在数据库上执行 SQL 查询。
7.3.2.2. 执行 SQL 查询
要执行 SQL 查询，我们需要一个 Command 对象——更确切地说，是一个 OdbcCommand 对象，因为我们正在使用 ODBC 数据源。OdbcCommand 类有几个构造函数：
- OdbcCommand()：创建一个空的 Command 对象。要使用它，您需要在后续步骤中指定各种属性：
- CommandText：待执行的 SQL 查询文本
- Connection：表示将执行查询的数据库连接的 OdbcConnection 对象
- CommandType：SQL 查询的类型。可能有三个值
- CommandType.Text：CommandText 属性包含 SQL 查询的文本（默认值）
- CommandType.StoredProcedure：CommandText 属性包含数据库中存储过程的名称
- CommandType.TableDirect：CommandText 属性包含表 T 的名称。等同于 `SELECT * FROM T`。仅适用于 OLE DB 驱动程序。
- OdbcCommand(string sqlText)：sqlText 参数将被赋值给 CommandText 属性。这是待执行的 SQL 查询文本。必须在 Connection 属性中指定连接。
- OdbcCommand(string sqlText, OdbcConnection connection)：sqlText 参数将赋值给 CommandText 属性，connection 参数将赋值给 Connection 属性。
要执行 SQL 查询，有两种方法可用：
- OdbcDataReader ExecuteReader()：将 CommandText 中的 SELECT 查询发送至 Connection，并创建一个 OdbcDataReader 对象，该对象提供对 SELECT 结果表中所有行的访问权限
- int ExecuteNonQuery()：将 CommandText 中的更新查询（INSERT、UPDATE、DELETE）发送给 Connection，并返回受更新操作影响的行数。
在本示例中，建立数据库连接后，我们执行一个 SQL SELECT 查询以检索 ARTICLES 表的内容：
' 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()
查询通常采用以下形式：
仅第一行的关键字是必需的；其余的均为可选。还有其他未在此处显示的关键字。
- 将对 `FROM` 关键字后列出的所有表执行连接
- 仅保留 `select` 关键字后面的列
- 仅保留满足 `where` 关键字条件的行
- 根据 `ORDER BY` 关键字中的表达式对所得行进行排序后，即构成查询结果。
SELECT 的结果是一个表。如果我们考虑前面的 ARTICLES 表，并希望获取当前库存低于最低阈值的商品名称，我们会这样写：
如果要按名称按字母顺序排序，则应编写如下语句：
7.3.2.3. 处理 SELECT 查询的结果
在脱机模式下，SELECT 查询的结果是一个 DataReader 对象，在本例中是 OdbcDataReader 对象。该对象允许您依次检索结果集中的所有行，并获取这些结果中各列的相关信息。下面我们来看看该类的某些属性和方法：
表中的列数 | |
Item(i) 表示结果中当前行的第 i 列 | |
当前行第 i 列的值，返回类型为 XXX（Int16、Int32、Int64、Double、String、Boolean 等） | |
第 i 列的名称 | |
关闭 OdbcdataReader 对象并释放相关资源 | |
在结果表中向前移动一行。如果无法执行此操作，则返回 false。新行将成为读取器的当前行。 |
处理 SELECT 语句的结果通常是一项类似于文本文件的顺序操作：你只能在表中向前移动，而不能向后移动：
While myReader.Read()
' on a une ligne - on l'exploite
....
' ligne suivante
end while
这些解释足以理解我们示例中的以下代码：
' 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
唯一的难点在于将当前行中不同列的值进行拼接的语句：
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
语法 line+=myReader(i).ToString 被转换为 line+=myReader.Item(i).ToString()，其中 Item(i) 是当前行第 i 列的值。
7.3.2.4. 资源释放
OdbcReader 和 OdbcConnection 类都提供了一个 Close() 方法，该方法会释放与被关闭对象相关的资源。
7.3.3. 使用 OLE DB 驱动程序
我们将使用相同的示例，但这次通过 OLE DB 驱动程序访问数据库。 .NET 平台为 Access 数据库提供了此类驱动程序。因此，我们将使用与之前相同的 articles.mdb 数据库。我们的目的是展示：虽然类可能有所不同，但核心概念始终如一：
- 连接由 OleDbConnection 对象表示
- 使用 OleDbCommand 对象发出 SQL 查询
- 如果该查询是 SELECT 语句，则会返回一个 OleDbDataReader 对象以访问结果表的行
这些类位于 System.Data.OleDb 命名空间中。前面的程序可以轻松改编为与 OLE DB 数据库配合使用：
- 将所有 OdbcXX 替换为 OleDbXX
- 修改连接字符串。对于无需登录名/密码的 ACCESS 数据库，连接字符串为 Provider=Microsoft.JET.OLEDB.4.0;Data Source=[file.mdb]。该字符串中可配置的部分是待使用的 ACCESS 文件名。我们将修改程序，使其能够接受该文件名作为参数。
- 现在需要导入的命名空间是 System.Data.OleDb。
我们的程序代码如下：
' 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
所得结果：
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. 更新表
前面的示例只是简单地列出了表中的内容。我们将修改产品数据库管理程序，使其能够修改数据库。该程序名为 sql。我们将待管理的产品数据库的 DSN 名称作为参数传递给程序。用户直接在键盘上输入 SQL 命令，程序会执行这些命令，如下所示，这是从 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
活动安排如下：
' 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
这里我们仅就与前一个程序相比的新内容进行说明：
- 我们构建了一个已接受的SQL命令字典：
' 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
这样，我们就可以简单地检查输入的查询的第一个单词（fields[0]）是否属于四个被接受的命令之一：
' 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
- 此前，该查询曾使用 RegEx 类的 Split 方法拆分为各个字段：
Dim modèle As New Regex("\s+")
....
' the query is broken down into fields
champs = modèle.Split(requête)
查询中的单词可以用任意数量的空格分隔。
- 执行 SELECT 查询与执行更新查询（INSERT、UPDATE、DELETE）所用的方法不同。因此，我们必须进行检查，并针对这两种情况分别执行不同的函数：
' 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
执行 SQL 查询可能会引发异常，此处将对此进行处理。
- executeSelect 函数涵盖了前几个示例中的所有内容。
- executeUpdate 函数使用 OdbcCommand 类的 ExecuteNonQuery 方法，该方法返回受该命令影响的行数。
7.3.5. 税费计算
我们重用上一章创建的 tax 对象：
' 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
我们添加了一个新的构造函数，用于从 ODBC 数据库初始化 limit 数组、coeffR 和 coeffN：
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
测试程序如下：它接收作为参数的、将传递给税类构造函数的参数。在构造一个税对象后，它计算应缴税款：
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
所使用的数据库是一个 MySQL 数据库，其 DSN 名称为 mysql-imports：
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 |
+---------+--------+---------+
运行测试程序得到以下结果：
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 :