10. 使用 ADO.NET 访问数据库
10.1. ADO.NET 连接器
让我们回顾一下自第 6 章以来反复使用的分层架构。到目前为止,[Tax] 应用程序的 [dao] 层已经使用了三种类型的数据源:
- 硬编码在代码中的数据([HardwiredTax]);
- 来自文本文件的数据([FileTax]);
- 来自 JSON 文件的数据([JsonTax])。
在本章中,我们来研究数据来自数据库的情形。架构变为如下:

在上图中,[dao] 层 [1] 通过一个特定于所用 DBMS 的类库 [2] 与 DBMS(数据库管理系统)[3]通信,DBMS 负责管理数据库 [4]。该类库实现了统称为 ADO.NET 的标准功能(ADO 是“ActiveX Data Objects”的缩写,是 Windows 上先于它出现的数据访问技术的名称)。它被称为“数据提供程序”或“连接器”。所有常见的 DBMS 都有 ADO.NET 连接器。
连接器的类以 DBMS 命名:[MySqlConnection] 用于 MySQL,[SqlConnection] 用于 SQL Server,[NpgsqlConnection] 用于 PostgreSQL……如果 [dao] 层使用这些类,其代码中就包含连接器的名称,更换 DBMS 就需要修改它。这种架构既高效,因为针对特定 DBMS 编写的连接器可以充分利用该 DBMS;又僵化,因为更换 DBMS 需要修改 [dao] 层。第二点应当客观看待:企业并不会经常更换其 DBMS。此外,我们将看到,从 .NET 2.0 开始,ADO.NET 提供了一种“通用连接器”,在不牺牲性能的前提下提供了灵活性。
2008 年的课程依次使用了 SQL Server Compact(已被 Microsoft 停止支持)、SQL Server 2005 Express、MySQL 5,然后是 ODBC 和 OLE DB 驱动程序,数据库是用 Visual Studio 向导创建的。这里我们使用单一的 DBMS,即 MySQL 8(附录中使用 Laragon 安装它),以及当今使用最广泛的 ADO.NET 连接器之一 MySqlConnector。数据库由 SQL 脚本创建,这些脚本通过 [mysql] 命令行客户端运行;附录还展示了如何使用 Laragon 附带的 HeidiSQL 图形客户端创建它们。
10.2. 使用数据源的两种方式
ADO.NET 允许以两种不同的方式使用数据源:
在连接模式下,应用程序:
- 打开到数据源的连接;
- 以读/写模式使用数据源;
- 关闭连接。
在离线模式下,应用程序:
- 打开到数据源的连接;
- 将来自数据源的全部或部分数据的副本取回到内存中;
- 关闭连接;
- 使用内存中的数据副本,对其进行读取和写入;
- 工作完成后,打开连接,将修改后的数据发送给数据源以便更新,然后关闭连接。
离线模式依赖于 [DataSet] (一个小型内存数据库,包含其表 [DataTable]、行 [DataRow] 和关系)和 [DataAdapter]([DataSet] 与数据库之间的桥梁)这些类。这些类在 .NET 10 中仍然存在,但在新应用程序中很少使用:对内存中的数据副本进行操作、随后保存更改的工作,现在由 ORM (“对象关系映射器”)来处理,它操作的是普通的 C# 对象,而不是表中的行。.NET 的 ORM Entity Framework Core是第 11 章的主题。与 2008 年的课程一样,我们在这里只关注连接模式,Entity Framework Core 正是基于该模式。
10.3. 数据库操作的基本概念
10.3.1. 示例数据库
我们将使用一个名为 [dbarticles] 的 MySQL 数据库来讲解使用数据库的主要概念。它只包含一张表 [articles],其中每一行描述商店中的一件商品:
- [ID]:商品的唯一标识符,是该表的主键;
- [NAME]:商品的名称,同样是唯一的;
- [PRICE]:商品的价格;
- [CURRENTSTOCK]:商品的当前库存;
- [MINIMUMSTOCK]:最低库存量,低于该水平时必须补货。
该数据库由以下 SQL 脚本 [sql/dbarticles.sql] 创建:
| -- creation of the database [dbarticles] and of its table [articles]
-- execution: mysql -uroot < dbarticles.sql
-- the script is written in UTF-8
SET NAMES utf8mb4;
-- the database is recreated on each execution of the script
DROP DATABASE IF EXISTS dbarticles;
CREATE DATABASE dbarticles CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- choice of the current database
USE dbarticles;
-- creation of the articles table
CREATE TABLE articles (
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30) NOT NULL,
PRICE DECIMAL(10,2) NOT NULL,
CURRENTSTOCK INT NOT NULL,
MINIMUMSTOCK INT NOT NULL,
-- integrity constraints
CONSTRAINT UNQ_NAME UNIQUE (NAME),
CONSTRAINT CHK_NAME CHECK (NAME <> ''),
CONSTRAINT CHK_PRICE CHECK (PRICE > 0),
CONSTRAINT CHK_CURRENTSTOCK CHECK (CURRENTSTOCK >= 0),
CONSTRAINT CHK_MINIMUMSTOCK CHECK (MINIMUMSTOCK >= 0)
);
-- insertion of data into the table
INSERT INTO articles (NAME, PRICE, CURRENTSTOCK, MINIMUMSTOCK) VALUES
('bike', 500, 10, 5),
('pump', 10, 10, 2),
('bow', 600, 4, 1),
('arrows - set of 6', 100, 12, 20),
('diving suit', 300, 8, 2),
('diver''s oxygen tanks', 120, 10, 5);
|
- 第 5 行:该脚本是一个 UTF-8 编码的文本文件。[SET NAMES utf8mb4] 将这一点告知服务器;否则,带重音符号的字符可能会被客户端 [mysql] 误解;
- 第 8–9 行:如果数据库存在则将其删除,然后重新创建。因此该脚本可以多次运行,以将数据库恢复到初始状态。[utf8mb4] 是 MySQL 的完整 Unicode 编码(MySQL 历史上的 [utf8] 编码只能编码最多占三个字节的字符);
- 第 12 行:[dbarticles] 成为当前数据库:后续的 SQL 命令将在其上执行;
- 第 15–26 行:表 [articles] 的定义:
- 第 16 行:列 [ID] 是该表的主键([PRIMARY KEY])。MySQL 特有的 [AUTO_INCREMENT] 属性表示,其值在插入行时由 DBMS 自己生成:1、2、3……其他 DBMS 也有等价的概念,但语法不同(SQL Server 使用 [IDENTITY] ,PostgreSQL 使用 [GENERATED … AS IDENTITY] );
- 第 18 行:价格是一个精确的 10 位十进制数,小数点后有 2 位。2008 年的脚本使用的是 [DOUBLE PRECISION] 类型,即浮点数,不适合用于金额(参见第 3 章中 [double] 和 [decimal] 的区别)。SQL 类型 [DECIMAL] 对应于 C# 类型 [decimal];
- 第 22–26 行:完整性约束。[UNQ_NAME] 防止两件商品同名;[CHECK] 约束防止名称为空、价格为负数或零、以及库存为负数。2008 年的脚本已经添加了这些约束,但 MySQL 会悄悄地忽略它们;从 8.0.16 版本开始,MySQL 才会强制执行这些约束;
- 第 30–36 行:插入六行数据。[ID] 列没有被初始化:由 DBMS 为其赋值。
使用命令行客户端 [mysql] 在 [sql] 文件夹中运行该脚本(在附录的安装中,MySQL 的用户 [root] 没有密码;否则请添加 [-p] 选项,客户端会提示输入密码):
| > mysql -uroot < dbarticles.sql
|
如果一切顺利,该命令不会显示任何内容。你可以使用同一个客户端检查表的结构和内容:
| > mysql -uroot --default-character-set=utf8mb4 -t dbarticles -e "describe articles; select * from articles"
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL | auto_increment |
| NAME | varchar(30) | NO | UNI | NULL | |
| PRICE | decimal(10,2) | NO | | NULL | |
| CURRENTSTOCK | int | NO | | NULL | |
| MINIMUMSTOCK | int | NO | | NULL | |
+--------------+---------------+------+-----+---------+----------------+
+----+----------------------+--------+--------------+--------------+
| ID | NAME | PRICE | CURRENTSTOCK | MINIMUMSTOCK |
+----+----------------------+--------+--------------+--------------+
| 1 | bike | 500.00 | 10 | 5 |
| 2 | pump | 10.00 | 10 | 2 |
| 3 | bow | 600.00 | 4 | 1 |
| 4 | arrows - set of 6 | 100.00 | 12 | 20 |
| 5 | diving suit | 300.00 | 8 | 2 |
| 6 | diver's oxygen tanks | 120.00 | 10 | 5 |
+----+----------------------+--------+--------------+--------------+
|
- 第 1 行:选项 [-t] 要求以表格形式显示;选项 [-e] 指定要执行的 SQL 命令,选项 [--default-character-set=utf8mb4] 确保带重音符号的字符在 UTF-8 终端中正确显示;
- 第 3–9 行:表的结构,由 MySQL 命令 [describe] 获得;
- 第 12–19 行:表的内容。[ID] 列中的值由 DBMS 生成。
关于大小写敏感性的说明:列名始终不区分大小写([name] 和 [NAME] 指的是同一列),但表名是服务器上的文件名。它们在 Windows 上不区分大小写,而在 Linux 上区分大小写。为确保示例在任何地方都能运行,该表被命名为 [articles],使用小写,查询中也以这种方式引用它。
现在我们有了数据库,就可以使用它了。在此之前,让我们回顾一下 SQL 的几个概念。
10.3.2. SQL 语言的四条基本命令
SQL(“Structured Query Language”,结构化查询语言)是一种部分标准化的、用于查询和更新数据库的语言。所有 DBMS 的方言都遵循 SQL 的标准化部分,但会添加专有的语言扩展,以利用其各自的特性。我们已经遇到过其中的两个例子:主键的自动生成,以及表的列所允许的数据类型,它们往往取决于 DBMS。
我们在这里介绍的 SQL 语言的四条基本命令是标准化的,所有 DBMS 都支持:
命令 | 作用 |
[select col1, col2, … from table1, table2, … where condition order by expression] | 用于从数据库中检索数据的查询。只有关键字 [select] 和 [from] 是必需的。对关键字 [from] 之后的所有表执行连接;只保留关键字 [select] 之后的列;只保留满足关键字 [where] 条件的行;所得的行按关键字 [order by] 中的表达式排序,构成查询的结果。这个结果是一张表。 |
[insert into table(col1, col2, …) values (val1, val2, …)] | 向 [table] 中插入一行。[(col1, col2, …)] 指定该行中要用 [(val1, val2, …)] 中的值填充的列。 |
[update table set col1=val1, col2=val2, … where condition] | 根据 [condition] 更新 [table] 中的行(如果没有 [where] 则更新所有行)。对于这些行,[coli] 列被设置为 [vali]。 |
[delete from table where condition] | 删除 [table] 中与 [condition] 匹配的所有行(如果没有 [where] 则删除所有行)。 |
我们将编写一个控制台应用程序,它基于 [dbarticles] 发出 SQL 命令。下面是一个输出示例。建议读者理解所发出的 SQL 命令及其结果:
| > dotnet run adonet-01.cs
Database connection string: [Server=localhost;Port=3306;Database=dbarticles;User=root;Password=]
SQL query (nothing to stop): select id,name,price,currentstock,minimumstock from articles
---------------------------------------
id,name,price,currentstock,minimumstock
---------------------------------------
1 bike 500.00 10 5
2 pump 10.00 10 2
3 bow 600.00 4 1
4 arrows - set of 6 100.00 12 20
5 diving suit 300.00 8 2
6 diver's oxygen tanks 120.00 10 5
SQL query (nothing to stop): insert into articles(name,price,currentstock,minimumstock) values('x',100,10,1)
1 row(s) modified
SQL query (nothing to stop): select * from articles
---------------------------------------
ID,NAME,PRICE,CURRENTSTOCK,MINIMUMSTOCK
---------------------------------------
1 bike 500.00 10 5
2 pump 10.00 10 2
3 bow 600.00 4 1
4 arrows - set of 6 100.00 12 20
5 diving suit 300.00 8 2
6 diver's oxygen tanks 120.00 10 5
7 x 100.00 10 1
SQL query (nothing to stop): update articles set price=price*1.1 where name='x'
1 row(s) modified
SQL query (nothing to stop): select id,name,price from articles where name='x'
-------------
id,name,price
-------------
7 x 110.00
SQL query (nothing to stop): delete from articles where name='x'
1 row(s) modified
SQL query (nothing to stop): select count(*) from articles
--------
count(*)
--------
6
SQL query (nothing to stop): selectt * from articles
Invalid query. Use select, insert, update, delete or nothing to stop
SQL query (nothing to stop): select * from article
Database access error (Table 'dbarticles.article' doesn't exist)
SQL query (nothing to stop): insert into articles(name,price,currentstock,minimumstock) values('bike',100,10,1)
Database access error (Duplicate entry 'bike' for key 'articles.UNQ_NAME')
SQL query (nothing to stop):
|
- 第 2 行:连接字符串:它包含连接数据库所需的全部参数;
- 第 4 行:检索表 [articles] 的内容。价格以小数点显示;
- 第 17 行:插入一个新行。请注意,在此操作中 [id] 列没有被初始化,因为其值将由 DBMS 生成;
- 第 20 行:验证。第 32 行:该行确实已被添加,其 [id] 为 7。使用 [select *] 时,列名就是表中的列名,为大写;
- 第 34 行:我们将刚刚添加的商品的价格提高 10%;
- 第 43 行:涨价已经生效;
- 第 45 行:删除之前添加的商品;
- 第 48 行:我们验证只剩下六件商品;
- 第 56 行:程序只接受 [select]、[insert]、[update]、和 [delete] 这四条命令;
- 第 60 行:DBMS 报告的一个错误:表 [article] 不存在;
- 第 63 行:唯一性约束 [UNQ_NAME] 阻止添加第二件 [bike] 商品。MySQL 的错误消息与 .NET 的一样,是英文的。
10.3.3. 连接模式下 ADO.NET 的基类
让我们回到通过 ADO.NET 连接器访问数据库的应用程序示意图:

在连接模式下,应用程序:
- 打开到数据源的连接;
- 以读/写模式使用数据源;
- 关闭连接。
这些操作主要涉及三类对象:
- 连接,它封装了数据库连接的属性和方法;
- 命令,它封装了所执行 SQL 命令的属性和方法;
- 数据读取器,它封装了 SQL [select] 命令的结果的属性和方法。
每个连接器都定义了自己的类(MySqlConnector 的 [MySqlConnection]、[MySqlCommand]、[MySqlDataReader] ),这些类派生自所有连接器共有的抽象类,这些抽象类定义在 [System.Data.Common] 命名空间中:[DbConnection]、[DbCommand]、[DbDataReader]。2008 年的课程介绍的是 [IDbConnection]、[IDbCommand] 和 [IDataReader] 这几个接口,位于命名空间 [System.Data] 中,这些类实现了它们。如今更倾向于使用抽象类:它们比接口提供更多功能,而接口在不破坏现有连接器的前提下已无法再添加方法(当时还没有默认接口方法)。例如,方法的异步版本([OpenAsync]、[ExecuteReaderAsync]、[ReadAsync] 等)只存在于抽象类中。
10.3.3.1. DbConnection 类
它用于管理与数据库的连接。我们将使用的该类的方法 M 和属性 P 如下:
名称 | 类型 | 作用 |
[ConnectionString] | P | 数据库连接字符串。它指定了与特定数据库建立连接所需的全部参数。 |
[Open] | M | 打开到由 [ConnectionString] 定义的数据库的连接。 |
[Close] | M | 关闭连接。[Dispose] 同样会关闭它。 |
[BeginTransaction] | M | 启动一个事务。 |
[CreateCommand] | M | 创建一个与此连接关联的命令。 |
[State] | P | 连接状态:[ConnectionState.Closed]、[ConnectionState.Open]、[ConnectionState.Connecting]、[ConnectionState.Executing]、[ConnectionState.Fetching]、[ConnectionState.Broken]。 |
使用 MySqlConnector,可以按如下方式建立连接:
| DbConnection connection = new MySqlConnection();
connection.ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
connection.Open();
|
或者更简单地,将连接字符串传递给构造函数:
| var connection = new MySqlConnection("Server=localhost;Port=3306;Database=dbarticles;User=root;Password=");
connection.Open();
|
10.3.3.2. DbCommand 类
它用于执行 SQL 命令或存储过程。我们将使用的该类的方法 M 和属性 P 如下:
名称 | 类型 | 作用 |
[CommandType] | P | 指定要执行的内容。其取值来自枚举 [CommandType]:[CommandType.Text] 执行属性 [CommandText] 中定义的 SQL 命令(这是默认值);[CommandType.StoredProcedure] 执行数据库中的存储过程。 |
[CommandText] | P | 如果 [CommandType] 等于 [CommandType.Text],则为要执行的 SQL 命令的文本;如果 [CommandType] 等于 [CommandType.StoredProcedure],则为要执行的存储过程的名称。 |
[Connection] | P | 用于执行该 SQL 命令的连接 [DbConnection] 。 |
[Transaction] | P | 执行该 SQL 命令所在的事务 [DbTransaction] 。 |
[Parameters] | P | 参数化 SQL 命令的参数列表。命令 [update articles set price=price*1.1 where id=@id] 带有 [@id] 参数。 |
[ExecuteReader] | M | 执行 SQL [select] 命令。它返回一个 [DbDataReader] 对象,表示 [select] 的结果。 |
[ExecuteNonQuery] | M | 执行 SQL [update]、[insert] 或 [delete] 命令。它返回受该操作影响的行数(更新、插入、删除的行数)。 |
[ExecuteScalar] | M | 执行只返回单个结果的 SQL [select] 命令,例如 [select count(*) from articles]。 |
[CreateParameter] | M | 根据参数化 SQL 命令创建一个参数 [DbParameter] 。 |
[Prepare] | M | 当参数化查询要以不同参数多次执行时,可用它来优化其执行。 |
不使用事务时,SQL 命令的执行采用以下形式:
| // 打开连接
var connection = new MySqlConnection(connectionString);
connection.Open();
// 准备命令
var command = new MySqlCommand();
command.Connection = connection;
// 执行 select 语句
command.CommandText = "select ...";
MySqlDataReader reader = command.ExecuteReader();
...
// 执行 update、insert、delete 语句
command.CommandText = "insert ...";
int nbInsertedRows = command.ExecuteNonQuery();
...
// 关闭连接
connection.Close();
|
10.3.3.3. DbDataReader 类
它封装了 SQL [select] 命令的结果。[DbDataReader] 对象表示一张包含行和列的表,其行被顺序处理:先处理第一行,再处理第二行,依此类推,无法回退。并非所有行都会被加载到内存中:读取器在读取时才从服务器接收这些行。我们将使用的该类的方法 M 和属性 P 如下:
名称 | 类型 | 作用 |
[FieldCount] | P | 结果表中的列数。 |
[GetName] | M | [GetName(i)] 返回结果表中第 i 列的名称(列从 0 开始编号)。 |
[GetOrdinal] | M | [GetOrdinal(name)] 返回名为 [name] 的列的列号。 |
[Item] | P | 索引器 [reader[i]] 表示当前行的第 i 列,而 [reader["name"]] 表示名为 [name] 的列,二者均以 [object] 的形式给出。 |
[Read] | M | 移动到结果表的下一行。如果成功读取了该行,则返回 [true] ;如果没有更多的行,则返回 [false] 。 |
[Close] | M | 关闭读取器。[Dispose] 同样会关闭它。 |
[GetInt32] | M | [GetInt32(i)] 返回当前行第 i 列的整数值。其他类似的方法有 [GetBoolean]、[GetDateTime]、[GetDecimal]、[GetDouble]、[GetFloat]、[GetInt16]、[GetInt64]、[GetString] 等。 |
[GetValue] | M | [GetValue(i)] 以 [object] 类型返回当前行第 i 列的值。 |
[IsDBNull] | M | [IsDBNull(i)] 如果当前行的第 i 列没有值(用 SQL 值 [NULL] 表示),则返回 [true] 。C# 中对应的值是 [DBNull.Value]。 |
对 [DbDataReader] 对象的处理通常如下所示:
| // 打开连接
var connection = new MySqlConnection(connectionString);
connection.Open();
// 准备命令
var command = new MySqlCommand("select ...", connection);
// 执行 select 语句
MySqlDataReader reader = command.ExecuteReader();
// 处理结果
while (reader.Read())
{
// 处理当前行
...
}
// 关闭读取器
reader.Close();
// 关闭连接
connection.Close();
|
10.3.4. 错误处理
[dao] 层在操作数据库时可能会遇到许多错误:服务器宕机、数据库不存在、密码错误、SQL 语法错误、违反完整性约束等。这些错误以 ADO.NET 连接器抛出的异常的形式报告。[dao] 层中的代码必须处理这些异常,并且无论结果如何,都要释放它所获取的资源:必须关闭连接和读取器。上面处理 [select] 命令结果的代码变为如下形式:
| MySqlConnection? connection = null;
try
{
// 打开连接
connection = new MySqlConnection(connectionString);
connection.Open();
// 执行 select 语句
var command = new MySqlCommand("select ...", connection);
MySqlDataReader reader = command.ExecuteReader();
try
{
// 处理结果
while (reader.Read())
{
...
}
}
finally
{
// 关闭读取器
reader.Close();
}
}
catch (DbException ex)
{
// 处理异常
...
}
finally
{
// 关闭连接
connection?.Close();
}
|
可以使用 [using] 语句自动关闭连接和读取器:连接、命令和读取器都实现了 [IDisposable] 接口,它们的 [Dispose] 方法会关闭它们。从 C# 8 开始,[using] 声明(不带代码块)会在包含该声明的代码块结束时释放资源:
| try
{
// 无论以何种方式离开 try 块,连接都会在离开时关闭
using var connection = new MySqlConnection(connectionString);
connection.Open();
using var command = new MySqlCommand("select ...", connection);
using MySqlDataReader reader = command.ExecuteReader();
// 处理结果
while (reader.Read())
{
...
}
}
catch (DbException ex)
{
// 处理异常
...
}
|
- 第 4 行:[using] 声明确保在退出 [try] 块时关闭连接,无论是正常退出还是因异常退出。这省去了一个 [finally] 子句,但好处并不在于这点微小的节省:[using] 声明使开发人员不必手动关闭连接。而忘记关闭连接可能不会被察觉,并导致应用程序在 DBMS 达到其所能支持的最大打开连接数时以看似随机的方式“崩溃”;
- 第 7 行:读取器采用同样的处理方式。资源按声明的相反顺序释放:先是读取器,然后是命令,最后是连接。
连接器抛出的异常都继承自抽象类 [DbException] (命名空间 [System.Data.Common])。每个连接器都有自己的派生类,MySqlConnector 的派生类是 [MySqlException] ,它提供有关错误的附加信息:其 MySQL 编号([Number])、枚举形式的同一编号([ErrorCode],类型为 [MySqlErrorCode]),以及标准化的 SQL 错误代码([SqlState])。程序 [adonet-02.cs] 生成各种错误并显示它们:
| #:package MySqlConnector@2.4.0
// 处理数据库访问错误
using System.Data.Common;
using MySqlConnector;
// 连接字符串,位于程序顶部
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
// 会失败的操作,每个失败的原因各不相同
Execute("nonexistent database", "Server=localhost;Port=3306;Database=dbunknown;User=root;Password=",
"select * from articles");
Execute("unreachable server", "Server=localhost;Port=3399;Database=dbarticles;User=root;Password=;Connection Timeout=5",
"select * from articles");
Execute("syntax error", ConnectionString, "select * fromm articles");
Execute("nonexistent table", ConnectionString, "select * from article");
Execute("duplicate name", ConnectionString,
"insert into articles(name, price, currentstock, minimumstock) values('bike', 100, 10, 1)");
Execute("negative price", ConnectionString,
"insert into articles(name, price, currentstock, minimumstock) values('canoe', -100, 10, 1)");
// 执行一条 SQL 语句并显示可能的错误
static void Execute(string title, string connectionString, string query)
{
Console.WriteLine($"--- {title}");
try
{
using var connection = new MySqlConnection(connectionString);
connection.Open();
using var command = new MySqlCommand(query, connection);
command.ExecuteNonQuery();
Console.WriteLine("no error");
}
catch (MySqlException ex) when (ex.ErrorCode == MySqlErrorCode.DuplicateKeyEntry)
{
// 特定错误,通过其代码识别
Console.WriteLine($"this article already exists ({ex.Message})");
}
catch (MySqlException ex)
{
// 其他 MySQL 错误
Console.WriteLine($"{ex.GetType().Name} : Number={ex.Number}, ErrorCode={ex.ErrorCode}, SqlState={ex.SqlState}");
Console.WriteLine($" {ex.Message}");
}
catch (DbException ex)
{
// 来自其他 ADO.NET 连接器的错误(这里不会发生)
Console.WriteLine($"{ex.GetType().Name} : {ex.Message}");
}
}
|
结果:
| > dotnet run adonet-02.cs
--- nonexistent database
MySqlException : Number=1049, ErrorCode=UnknownDatabase, SqlState=42000
Unknown database 'dbunknown'
--- unreachable server
MySqlException : Number=1042, ErrorCode=UnableToConnectToHost, SqlState=
Unable to connect to any of the specified MySQL hosts.
--- syntax error
MySqlException : Number=1064, ErrorCode=ParseError, SqlState=42000
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fromm articles' at line 1
--- nonexistent table
MySqlException : Number=1146, ErrorCode=NoSuchTable, SqlState=42S02
Table 'dbarticles.article' doesn't exist
--- duplicate name
this article already exists (Duplicate entry 'bike' for key 'articles.UNQ_NAME')
--- negative price
MySqlException : Number=3819, ErrorCode=3819, SqlState=HY000
Check constraint 'CHK_PRICE' is violated.
|
- 第 1 行:单文件应用程序(第 2 章)中的 [#:package] 指令将 NuGet 包 [MySqlConnector](版本 2.4.0)添加到程序中。我们将在下一段中回到这一点;
- 第 8 行:数据库连接字符串,写在程序开头;
- 第 11–20 行:六个失败的操作:数据库不存在;服务器无法访问(端口 3399 上没有 MySQL 服务器在监听,这产生的错误与服务器已停止时相同);语法错误;表不存在;商品名称重复;价格为负数;
- 第 23 行:方法 [Execute] 是主程序的一个局部函数 (C# 7)。关键字 [static] (C# 8)使它无法使用主程序的局部变量:它只使用其参数(和常量);
- 第 34 行:异常筛选器(C# 6)允许单独处理某个特定错误——在此处是违反唯一性约束(MySQL 错误编号 1062)。在这种情况下,应用程序会显示一条用户能够理解的消息;
- 第 39 行:其他 MySQL 错误。结果显示了它们的编号和代码:[UnknownDatabase] (1049)、[UnableToConnectToHost] (1042)、[ParseError] (1064)、[NoSuchTable](1146)。违反 [CHECK] 约束(错误 3819,第 17 行)在 MySqlConnector 2.4.0 的 [MySqlErrorCode] 枚举中没有名称:因此显示的是编号;
- 第 45 行:可以捕获来自任何连接器的异常的子句。它在这里没有用处,但独立于连接器编写的 [dao] 层会使用它。请注意 [catch] 子句的顺序:从最具体的([MySqlException])到最通用的([DbException])。
10.3.5. 示例项目配置
MySqlConnector 连接器是一个 NuGet 包。在单文件应用程序中,使用以下指令添加它:
| #:package MySqlConnector@2.4.0
|
在项目中,使用命令 [dotnet add package] 添加它,该命令在项目文件夹中输入:
| > dotnet add package MySqlConnector --version 2.4.0
|
该命令会将对该包的引用写入文件 [.csproj] 中:
| <ItemGroup>
<PackageReference Include="MySqlConnector" Version="2.4.0" />
</ItemGroup>
|
MySQL 有两个 ADO.NET 连接器:
- [MySql.Data] (“Connector/NET”),由 MySQL 的所有者 Oracle 发布。这是 2008 年的课程中使用的连接器,版本为 5.2;
- [MySqlConnector],一个独立于 Oracle 开发的开源连接器(采用 MIT 许可)。它是完全异步的(其 [XxxAsync] 方法是真正的异步方法,而 [MySql.Data] 的这些方法长期以来都是伪装成异步的同步方法),速度更快,并且同时适用于 MySQL 和 MariaDB。我们将在第 11 章使用的 Entity Framework Core MySQL 提供程序 [Pomelo.EntityFrameworkCore.MySql] 使用的就是它。
两个连接器的类名相同([MySqlConnection]、[MySqlCommand] 等),但位于不同的命名空间中:第一个位于 [MySqlConnector] ,第二个位于 [MySql.Data.MySqlClient] 。
在实际应用程序中,不应将连接字符串硬编码:它取决于安装应用程序的机器。2008 年的课程把它放在 XML 文件 [connectionStrings] 的 [App.config] 部分中,并使用 [ConfigurationManager] 类读取。.NET 当前的配置文件是 JSON 文件 [appsettings.json],已在第 6 章中使用过,它也有一个专门用于连接字符串的节 [ConnectionStrings]。本章的第一个程序使用以下位于 [appsettings.json] 文件夹 [10-adonet] 中的文件:
| {
"ConnectionStrings": {
"dbarticles": "Server=localhost;Port=3306;Database=dbarticles;User=root;Password="
}
}
|
- 第 2 行:连接字符串节。它可以包含多个字符串,每个字符串由一个名称标识;
- 第 3 行:名为 [dbarticles] 的连接字符串。连接字符串的形式为“parameter1=value1;parameter2=value2;…”。其参数取决于连接器;MySqlConnector 的参数如下:
- [Server]:MySQL DBMS 所在机器的名称——此处为 [localhost]——即运行程序的那台机器;
- [Port]:MySQL 服务器监听连接的端口;默认值为 3306;
- [Database]:数据库名称,此处为 [dbarticles];
- [User] (或 [Uid]):MySQL 用户,此处为 [root],即 DBMS 管理员;
- [Password] (或 [Pwd]):其密码,此处留空。
connectionstrings.com 网站提供了主要 DBMS 的连接字符串格式,MySqlConnector 文档则提供了其全部设置的完整列表:MySqlConnector Connection Options。
本章中的其他程序较短,它们将连接字符串存放在程序开头的常量中,例如 [adonet-02.cs]:这对示例来说更简单,但在实际应用程序中不应这样做。
10.3.6. 示例程序
程序 [adonet-01.cs](我们已经看过它的运行示例)如下(主程序):
| #:package MySqlConnector@2.4.0
#:package Microsoft.Extensions.Configuration.Json@10.0.12
// 控制台应用程序:执行键盘键入的 SQL 语句
// 针对一个数据库,其连接字符串从配置文件读取
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.Extensions.Configuration;
using MySqlConnector;
// 从 [appsettings.json](当前文件夹)读取连接字符串
string? connectionString;
try
{
IConfiguration config = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: false)
.Build();
connectionString = config.GetConnectionString("dbarticles");
}
catch (Exception e)
{
Console.WriteLine($"Configuration error: {e.Message}");
return;
}
if (connectionString is null)
{
Console.WriteLine("Configuration error: the connection string [dbarticles] is missing");
return;
}
// 显示连接字符串
Console.WriteLine($"Database connection string: [{connectionString}]");
// 接受的 SQL 语句
HashSet<string> sqlCommands = ["select", "insert", "update", "delete"];
// 键盘键入 SQL 语句的输入-执行循环
while (true)
{
// 请求输入查询
Console.Write("\nSQL query (nothing to stop): ");
string query = Console.ReadLine()?.Trim() ?? "";
// 完成了吗?
if (query == "")
{
break;
}
// 查询的第一个单词必须是四种被接受的语句之一
string firstWord = Regex.Match(query, @"^\S+").Value.ToLower();
if (!sqlCommands.Contains(firstWord))
{
Console.WriteLine("Invalid query. Use select, insert, update, delete or nothing to stop");
continue;
}
// 执行查询
if (firstWord == "select")
{
ExecuteSelect(connectionString, query);
}
else
{
ExecuteUpdate(connectionString, query);
}
}
|
- 第 1–2 行:所使用的两个包:MySQL 连接器和 JSON 配置文件;
- 第 10 行:连接器的类的命名空间。这意味着依赖于特定连接器的命名空间:如果更换 DBMS 连接器,就需要修改该程序;
- 第 16–19 行:读取 [appsettings.json] 文件,如第 6 章所述。该文件在当前目录([Directory.GetCurrentDirectory])中查找,也就是启动 [dotnet run adonet-01.cs] 的目录。单文件应用程序实际上是在与源文件分开的临时文件夹中编译的:[AppContext.BaseDirectory] 不会指向文件夹 [10-adonet];
- 第 20 行:[GetConnectionString(name)] 是 [config["ConnectionStrings:name"]] 的快捷方式。如果该字符串不存在,它返回 [null] ,这种情况在第 27–29 行处理;
- 第 37 行:四条被接受的 SQL 命令,存放在集合 [HashSet] (第 5 章)中,由集合表达式(C# 12)初始化。2008 年的课程使用了一个其值毫无用处的字典;
- 第 40 行:输入 SQL 命令的循环,这些命令由键盘键入并在数据库上执行;
- 第 51 行:查询的第一个单词,通过正则表达式获得(第 5 章):[\S+] 表示由非空白字符组成的序列。它被转换为小写以便比较,但查询本身不会被修改:2008 年的程序将整个查询转换为小写,这使得无法插入名为“Bike”的商品;
- 第 52–55 行:如果查询无效,则显示错误消息,程序转到下一个查询;
- 第 58–64 行:执行输入的 SQL 命令。执行的形式取决于它是 [select] 命令还是 [insert]、[update] 或 [delete] 命令:前一种情况下,命令从数据库检索数据而不修改它;后一种情况下,命令更新数据库而不检索任何数据。在这两种情况下,执行都委托给一个方法,该方法接收连接字符串和要执行的 SQL 命令。
10.3.7. 执行 SELECT 查询
执行 SQL 命令包括以下步骤:
- 连接到数据库;
- 向数据库发送 SQL 命令;
- 处理 SQL 命令的结果;
- 关闭连接。
第 2 步和第 3 步可以重复;只有在数据库处理结束时才关闭连接。打开的连接在 DBMS 中是有限的资源:必须节约使用,我们总是力求缩短打开连接的存续时间。在我们的程序中,每条 SQL 命令之后都会关闭连接,并为下一条命令打开一个新连接。
然而,打开连接的代价很高:它需要与服务器建立网络连接,可能还要协商加密并进行身份验证。为了降低这一代价,连接器管理着一个连接池:当应用程序关闭连接时,连接器实际上并不关闭它,而是将其归还到池中;当应用程序使用相同的连接字符串打开新连接时,它会得到池中已经建立好的某个连接。这种机制的优点是对开发人员透明:无需修改程序即可使用它。在 MySqlConnector 中(与其他当前的连接器一样),连接池默认启用,并通过连接字符串参数([Pooling]、[MaximumPoolSize]、[MinimumPoolSize] 等)进行配置。因此,最佳实践是在每个操作开始时打开连接,并在结束时使用 [using] 将其关闭。
首先,我们关注 SQL [select] 命令的执行。我们示例程序中的 [ExecuteSelect] 方法及其所用的 [DisplayReader] 方法如下:
| // 执行 select 语句
static void ExecuteSelect(string connectionString, string query)
{
try
{
using var connection = new MySqlConnection(connectionString);
// 打开连接
connection.Open();
// 执行 SQL 语句
using var command = new MySqlCommand(query, connection);
using MySqlDataReader reader = command.ExecuteReader();
// 显示结果
DisplayReader(reader);
}
catch (DbException ex)
{
Console.WriteLine($"Database access error ({ex.Message})");
}
}
// 显示 select 结果的行
static void DisplayReader(DbDataReader reader)
{
// 列名
var names = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
names[i] = reader.GetName(i);
}
string header = string.Join(",", names);
string dashes = new('-', header.Length);
Console.WriteLine($"\n{dashes}\n{header}\n{dashes}\n");
// 各行,逐行显示
while (reader.Read())
{
var row = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
row.Append(reader[i]).Append(' ');
}
Console.WriteLine(row);
}
}
|
- 第 89 行:该方法接收两个参数:
- 连接字符串 [connectionString],用于连接到数据库;
- 要在该连接上执行的 SQL [select] 命令 [query];
- 第 91 行:任何数据库操作都可能抛出异常。这里发生异常的可能性更大,因为用户提供的 SQL 命令可能包含语法错误。我们需要能够告知用户这一点:因此,所有代码都必须包含在 [try] / [catch] 中;
- 第 93 行:这里有几点需要说明:
- 使用连接字符串 [connectionString] 初始化数据库连接。它此时尚未打开;将在第 95 行打开;
- [using] 声明确保在退出 [try] 块时关闭连接(归还到池中);
- 该连接的类型特定于连接器:[MySqlConnection];
- 第 95 行:打开连接。此时会用到连接字符串的参数,连接错误也在此时发生;
- 第 97 行:将 SQL 命令赋给一个 [MySqlCommand] 对象,该对象用两项信息初始化:要发出的 SQL 命令和要使用的连接。同一类型的对象既用于执行 [select] 命令,也用于执行 [update]、[insert] 和 [delete] 命令;
- 第 98 行:[select] 命令由命令的 [ExecuteReader] 方法执行,该方法返回一个派生自 [MySqlDataReader] 的 [DbDataReader] 对象;
- 第 100 行:结果的显示由 [DisplayReader] 方法处理;
- 第 102 行:任何异常都以其基类型 [DbException] 捕获;
- 第 109 行:该方法接收一个 [DbDataReader] 对象。请注意,它使用的是抽象类,而不是连接器的 [MySqlDataReader] 类:因此它适用于任何连接器;
- 第 112–119 行:显示 [select] 结果表的列名。它们就是查询 [coli] 中 [select col1, col2, … from table …] 的各列。[string.Join] 用逗号分隔并将它们连接起来,而 [new string('-', n)] 构造函数生成由短横线组成的字符串;
- 第 121–128 行:遍历结果表,并显示每一行的值;
- 第 126 行:结果中第 i 列的类型是未知的,因为用户输入的查询是未知的。因此,无法使用语法 [reader.GetXxx(i)]——其中 [Xxx] 是列的类型。于是我们使用索引器 [reader[i]],它以 [object] 的形式返回值;[StringBuilder.Append(object)] 通过其方法 [ToString] 添加它。对于 SQL 类型为 [DECIMAL] 的列 [price],对象是一个 [decimal],以两位小数和小数点显示。
10.3.8. 执行更新命令:INSERT、UPDATE、DELETE
方法 [ExecuteUpdate] 的代码如下:
| // 执行 update 语句(insert、update、delete)
static void ExecuteUpdate(string connectionString, string query)
{
try
{
using var connection = new MySqlConnection(connectionString);
// 打开连接
connection.Open();
// 执行 SQL 语句
using var command = new MySqlCommand(query, connection);
int nbRows = command.ExecuteNonQuery();
// 显示结果
Console.WriteLine($"{nbRows} row(s) modified");
}
catch (DbException ex)
{
Console.WriteLine($"Database access error ({ex.Message})");
}
}
|
执行更新命令与执行 [select] 命令的区别仅在于所使用的命令方法:[ExecuteReader] 用于 [select],[ExecuteNonQuery] 用于 [update]、[insert]、[delete]:
- 第 78 行:[update]、[insert] 或 [delete] 命令 由命令的 [ExecuteNonQuery] 方法执行。如果成功,该方法返回被更新([update])、插入([insert])或删除([delete])的行数;
- 第 80 行:显示这个行数。
建议读者回顾“SQL 语言的四条基本命令”一节中给出的执行示例。
10.4. 其他 ADO.NET 连接器
10.4.1. 当前的连接器
我们研究的代码是特定于某个连接器的:它使用了 [MySqlConnector] 命名空间中的类。2008 年的课程随后分别使用 SQL Server Compact 连接器、SQL Server 2005 连接器、MySQL 5 连接器,以及 ODBC 和 OLE DB 驱动程序构建了同一个程序,以说明只有类名发生了变化。这一结论依然成立,我们不再重复这一练习,因为那需要安装额外的 DBMS。以下是当今主要的 ADO.NET 连接器,全部以 NuGet 包的形式分发:
DBMS | NuGet 包 | 连接 | 说明 |
MySQL、MariaDB | [MySqlConnector] | [MySqlConnection] | 独立的连接器,本课程使用它 |
MySQL | [MySql.Data] | [MySqlConnection] | Oracle 的连接器;命名空间 [MySql.Data.MySqlClient] |
SQL Server、Azure SQL | [Microsoft.Data.SqlClient] | [SqlConnection] | 取代 2008 年使用的 .NET Framework 中旧的 [System.Data.SqlClient] |
PostgreSQL | [Npgsql] | [NpgsqlConnection] | |
SQLite | [Microsoft.Data.Sqlite] | [SqliteConnection] | 基于文件、无需服务器的数据库:适用于小型应用程序,是 SQL Server Compact 的继任者 |
Oracle | [Oracle.ManagedDataAccess.Core] | [OracleConnection] | 命名空间 [Oracle.ManagedDataAccess.Client] |
除非另有说明,类的命名空间以包名命名。
要从一个 DBMS 切换到另一个 DBMS,需要更改包、命名空间([using])、类前缀([MySqlCommand] 变为 [SqlCommand]、[NpgsqlCommand] 等)以及连接字符串;其余代码保持不变。
ODBC(“Open DataBase Connectivity”)和 OLE DB(“Object Linking and Embedding DataBase”)在 2008 年是一种用同一套代码访问任何配有此类驱动程序的 DBMS 的方式,尽管代价是性能有所下降。它们是早于 .NET 的技术,其中 OLE DB 仅限于 Windows。它们仍然可用([System.Data.Odbc] 和 [System.Data.OleDb] 包),用于访问没有 ADO.NET 连接器的数据源,但新应用程序已不再选用它们:所有常见的 DBMS 都有自己的 ADO.NET 连接器,而实现 DBMS 无关性的是下面介绍的通用连接器。
10.4.2. 通用连接器
所使用的架构如下:

“通用连接器”为使用它的应用程序提供了一个标准接口,同时不牺牲性能,因为它依赖于特定于 DBMS 的连接器。应用程序只使用 [System.Data.Common] 的抽象类([DbConnection]、[DbCommand]、[DbDataReader]、[DbParameter] 等)。具体对象由连接器提供的工厂创建:这是一个派生自 [DbProviderFactory] 的类,其方法 [CreateConnection]、[CreateCommand]、[CreateParameter] 等以抽象类的形式返回连接器的对象。每个连接器都有自己的工厂,可通过其静态属性 [Instance] 访问:MySqlConnector 的工厂是 [MySqlConnectorFactory.Instance] ,SQL Server 的是 [SqlClientFactory.Instance] ,PostgreSQL 的是 [NpgsqlFactory.Instance] 。
静态类 [DbProviderFactories] 维护着一个工厂注册表,每个工厂由一个所谓的不变名称标识。在 .NET Framework 中,这个注册表由连接器的安装程序写入机器的配置文件来填充,2008 年的课程显示了作者机器上安装的六个连接器。在 .NET 中,注册表在启动时为空:应用程序自己使用 [DbProviderFactories.RegisterFactory] 方法添加它所支持的连接器。之后就可以通过配置,使用连接器的不变名称来选择连接器。这正是程序 [adonet-03.cs] 所做的;它以要使用的连接器的不变名称作为参数:
| #:package MySqlConnector@2.4.0
// "通用连接器":代码只使用 System.Data.Common 的抽象类
// 实际使用的连接器由其固定名称指定(第 1 个参数)
using System.Data;
using System.Data.Common;
using MySqlConnector;
// 连接器的固定名称和连接字符串
string connectorName = args.Length > 0 ? args[0] : "MySqlConnector";
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
// 以固定名称注册应用程序已知的连接器
DbProviderFactories.RegisterFactory("MySqlConnector", MySqlConnectorFactory.Instance);
// 已注册连接器的列表
Console.WriteLine("Registered connectors:");
DataTable connectors = DbProviderFactories.GetFactoryClasses();
foreach (DataRow row in connectors.Rows)
{
Console.WriteLine($" {row["InvariantName"]} : {row["AssemblyQualifiedName"]}");
}
// 我们获取所请求连接器的工厂
DbProviderFactory factory;
try
{
factory = DbProviderFactories.GetFactory(connectorName);
}
catch (ArgumentException ex)
{
Console.WriteLine($"Configuration error: {ex.Message}");
return;
}
Console.WriteLine($"Connector [{connectorName}]: factory of type {factory.GetType().Name}");
// 其余代码只知道抽象类 DbConnection、DbCommand、DbDataReader
try
{
using DbConnection connection = factory.CreateConnection()!;
connection.ConnectionString = ConnectionString;
connection.Open();
using DbCommand command = factory.CreateCommand()!;
command.Connection = connection;
command.CommandText = "select id, name, price from articles where price > @price";
// 由工厂创建的参数
DbParameter parameter = factory.CreateParameter()!;
parameter.ParameterName = "@price";
parameter.Value = 200;
command.Parameters.Add(parameter);
using DbDataReader reader = command.ExecuteReader();
Console.WriteLine($"Objects created: {connection.GetType().Name}, {command.GetType().Name}, {reader.GetType().Name}");
while (reader.Read())
{
Console.WriteLine($" {reader.GetInt32(0)} {reader.GetString(1)} {reader.GetDecimal(2)}");
}
}
catch (DbException ex)
{
Console.WriteLine($"Database access error ({ex.Message})");
}
|
使用 [MySqlConnector] 连接器(默认值)的结果:
| > dotnet run adonet-03.cs
Registered connectors:
MySqlConnector : MySqlConnector.MySqlConnectorFactory, MySqlConnector, Version=2.0.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92
Connector [MySqlConnector]: factory of type MySqlConnectorFactory
Objects created: MySqlConnection, MySqlCommand, MySqlDataReader
1 bike 500.00
3 bow 600.00
5 diving suit 300.00
|
使用未注册连接器的结果:
| > dotnet run adonet-03.cs -- Npgsql
Registered connectors:
MySqlConnector : MySqlConnector.MySqlConnectorFactory, MySqlConnector, Version=2.0.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92
Configuration error: The specified invariant name 'Npgsql' wasn't found in the list of registered .NET Data Providers.
|
- 第 10 行:连接器的不变名称,作为参数传入;默认值为 [MySqlConnector];
- 第 14 行:在注册表中以不变名称 [MySqlConnector] 注册 MySqlConnector 工厂。这是程序中唯一提到连接器类名的一行。如果应用程序还要支持 SQL Server,则需要添加这一行 [DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance)] (以及对包 [Microsoft.Data.SqlClient] 的引用);
- 第 18–21 行:[GetFactoryClasses] 以内存表的形式返回已注册连接器的列表,这是离线模式下的一个 [DataTable] 对象:其 [DataRow] 行按列名索引。列 [AssemblyQualifiedName] 包含工厂类的完整名称;请注意,程序集 MySqlConnector 的版本是 2.0.0.0:只有包版本(2.4.0)会随着每次小版本更新而变化;
- 第 28 行:通过不变名称获取连接器工厂。如果该名称尚未注册,[GetFactory] 会抛出 [ArgumentException] (第二组结果的第 4 行);
- 第 40–42 行:连接由工厂创建,用连接字符串配置,然后打开。[CreateXxx] 方法返回可为 null 的类型([DbConnection?]):工厂可能不知道如何创建某些对象。[!] 运算符告诉编译器,我们知道结果不是 [null];
- 第 43–45 行:命令以同样的方式创建,然后接收其连接和文本。我们也可以写成 [connection.CreateCommand()];
- 第 47–50 行:查询包含一个参数 [@price]。参数化查询将在下一节讨论;目前只需注意,参数同样由工厂创建;
- 第 52 行:结果表明,通过抽象类操作的对象是 MySqlConnector 连接器的对象([MySqlConnection]、[MySqlCommand]、[MySqlDataReader]);
- 第 55 行:由于列的类型已知,因此使用方法 [GetInt32]、[GetString] 和 [GetDecimal] ,它们直接返回预期类型的值。
从 .NET 7 开始,ADO.NET 还提供了抽象类 [DbDataSource],它将工厂和连接字符串结合在一起:[factory.CreateDataSource(connectionString)] 返回一个数据源,其方法 [OpenConnection] 返回一个已打开的连接。我们将在异步方法中使用它。
10.4.3. 应该选择哪个连接器?
我们已经看到了使用 ADO.NET 连接器的两种方式:
- 直接使用连接器的类:[dao] 层依赖于连接器。更换 DBMS 需要修改 [dao] 层;
- 使用通用连接器:[dao] 层只认识抽象类和连接器的不变名称,而不变名称可以从配置文件中读取。
因此,通用连接器似乎是理想的解决方案。但在实践中,它无法隐藏 DBMS 的所有具体细节:
- 一旦超出基本命令,SQL 本身在不同 DBMS 之间就会有所不同:主键的生成、限制返回行数(MySQL 使用 [limit 10] ,SQL Server 使用 [top 10] 或 [offset … fetch] )、列类型、日期函数等;
- 参数化查询的参数语法各不相同:SQL Server、MySQL 和 SQLite 使用 [@name],但 ODBC 和 OLE DB 驱动程序以及 2008 年的 MySQL 连接器使用 [?](位置参数,没有名称),PostgreSQL 的原生 SQL 使用 [$1]、[$2] 等(Npgsql 也接受 [@name])。对于位置参数,参数添加到 [Parameters] 集合中的顺序很重要;对于命名参数,顺序则无关紧要;
- 最后,异常是 [DbException],其错误代码取决于 DBMS。
2008 年的课程提到了克服这些局限的框架:Spring.NET、iBatis.NET、NHibernate 和 LINQ to SQL。Spring.NET 和 iBatis.NET 已不再维护;LINQ to SQL 已不再开发,在当前的 .NET 中也不存在;NHibernate 仍然存在。如今,主要使用的是以下工具:
- Entity Framework Core,Microsoft 的 ORM,将在第 11 章介绍:[dao] 层处理 C# 对象并用 LINQ 编写查询;生成特定于各个 DBMS 的 SQL 的是 EF Core;
- Dapper,一个“微型 ORM”,为 ADO.NET 连接添加扩展方法,可将 [select] 的行直接转换为 C# 对象。我们仍然要编写 SQL,但不再需要编写遍历读取器的循环。
这些工具都依赖于 ADO.NET 连接器:理解 ADO.NET 对于理解它们的工作方式以及排查它们的问题仍然很有用。
10.5. 参数化查询
10.5.1. 概念
参数化查询是一种 SQL 命令,其中某些值被参数取代,这些值与查询文本分开提供。[adonet-04.cs] 程序实现了这一点:它清空 [articles] 表,使用一个参数化查询插入五条记录,然后使用另一个参数化查询重新读取它们:
| #:package MySqlConnector@2.4.0
// 参数化查询
using MySqlConnector;
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
Console.WriteLine($"Database connection string: [{ConnectionString}]");
// 创建一个包含 5 篇文章的数组
var articles = new Article[5];
for (int i = 1; i <= articles.Length; i++)
{
articles[i - 1] = new Article(0, $"article{i}", i * 100, i * 10, i);
}
try
{
// 我们删除已有的文章
Console.WriteLine($"{ExecuteUpdate(ConnectionString, "delete from articles")} row(s) deleted");
// 我们插入文章数组
InsertArticles(ConnectionString, articles);
// 我们显示表中的文章
DisplayArticles(GetArticles(ConnectionString, "%"));
// 名称以 3 结尾的文章
Console.WriteLine("Articles whose name ends with 3:");
DisplayArticles(GetArticles(ConnectionString, "%3"));
}
catch (MySqlException ex)
{
Console.WriteLine($"Database access error ({ex.Message})");
}
// 用参数化查询插入一个文章数组
static void InsertArticles(string connectionString, Article[] articles)
{
using var connection = new MySqlConnection(connectionString);
connection.Open();
// 参数化查询:@name、@price、@cs、@ms 是它的参数
const string Query = "insert into articles(name, price, currentstock, minimumstock) values(@name, @price, @cs, @ms)";
using var command = new MySqlCommand(Query, connection);
// 声明参数:名称和 SQL 类型
command.Parameters.Add("@name", MySqlDbType.VarChar, 30);
command.Parameters.Add("@price", MySqlDbType.Decimal);
command.Parameters.Add("@cs", MySqlDbType.Int32);
command.Parameters.Add("@ms", MySqlDbType.Int32);
// 由 DBMS 准备(编译)查询
command.Prepare();
// 插入各行:只有参数的值在变化
foreach (Article article in articles)
{
command.Parameters["@name"].Value = article.Name;
command.Parameters["@price"].Value = article.Price;
command.Parameters["@cs"].Value = article.CurrentStock;
command.Parameters["@ms"].Value = article.MinimumStock;
command.ExecuteNonQuery();
}
Console.WriteLine($"{articles.Length} article(s) inserted");
}
// 名称匹配 [like] 模式的文章
static List<Article> GetArticles(string connectionString, string pattern)
{
using var connection = new MySqlConnection(connectionString);
connection.Open();
using var command = new MySqlCommand(
"select id, name, price, currentstock, minimumstock from articles where name like @pattern order by id", connection);
// 在一条语句中声明参数及其值
command.Parameters.AddWithValue("@pattern", pattern);
using MySqlDataReader reader = command.ExecuteReader();
// 结果的每一行都变成一个 Article 对象
List<Article> list = [];
while (reader.Read())
{
list.Add(new Article(reader.GetInt32(0), reader.GetString(1), reader.GetDecimal(2),
reader.GetInt32("currentstock"), reader.GetInt32("minimumstock")));
}
return list;
}
// 执行 update 语句
static int ExecuteUpdate(string connectionString, string query)
{
using var connection = new MySqlConnection(connectionString);
connection.Open();
using var command = new MySqlCommand(query, connection);
return command.ExecuteNonQuery();
}
// 显示文章列表
static void DisplayArticles(List<Article> articles)
{
foreach (Article article in articles)
{
Console.WriteLine(article);
}
}
// 一篇文章:[articles] 表的一行
record Article(int Id, string Name, decimal Price, int CurrentStock, int MinimumStock);
|
结果:
| > dotnet run adonet-04.cs
Database connection string: [Server=localhost;Port=3306;Database=dbarticles;User=root;Password=]
6 row(s) deleted
5 article(s) inserted
Article { Id = 7, Name = article1, Price = 100.00, CurrentStock = 10, MinimumStock = 1 }
Article { Id = 8, Name = article2, Price = 200.00, CurrentStock = 20, MinimumStock = 2 }
Article { Id = 9, Name = article3, Price = 300.00, CurrentStock = 30, MinimumStock = 3 }
Article { Id = 10, Name = article4, Price = 400.00, CurrentStock = 40, MinimumStock = 4 }
Article { Id = 11, Name = article5, Price = 500.00, CurrentStock = 50, MinimumStock = 5 }
Articles whose name ends with 3:
Article { Id = 9, Name = article3, Price = 300.00, CurrentStock = 30, MinimumStock = 3 }
|
- 第 99 行:[Article] 对象表示 [articles] 表中的一行。它是一个 [record](C# 9,第 4 章):一种由主构造函数定义属性的类,并带有一个显示这些属性的 [ToString] 方法 (第 5–9 行);2008 年的课程讲的是一个 25 行的类。在单文件应用程序中,类型在顶层语句之后声明;
- 第 10–13 行:一个包含五件商品的数组;
- 第 19 行:删除表中的所有商品。这一次,方法 [ExecuteUpdate] 返回被删除的行数;
- 第 34 行:插入商品数组的方法:
- 第 39 行:插入一件商品的查询。它有四个参数:[@name]、[@price]、[@cs](当前库存)和 [@ms](最低库存)。在 MySqlConnector 中,参数是前面带有字符 [@] 的名称;名称可以任意取;
- 第 42–45 行:四个参数中的每一个都被声明并添加到命令的参数列表中。[Parameters.Add(string name, MySqlDbType type)] 方法指定参数名称(必须是查询中出现的名称之一)和相关列的 SQL 类型;第三种形式 [Add(name, type, size)] 还会进一步指定列的宽度,这对字符串([VarChar])很有用;
- 第 47 行:参数化查询被“预处理”(prepare),即由 DBMS 对其进行编译。此操作不是必需的;它可以提高性能。DBMS 在执行 SQL 命令时,会先进行分析和优化再执行(它会创建一个“执行计划”)。参数化查询旨在以不同的参数多次执行,但其文本保持不变:这项准备工作只需做一次。预处理之后,查询以新的参数值重复执行,但使用同一个执行计划;
- 第 49–55 行:逐一插入商品。四个参数中的每一个都通过其属性 [Value] 被赋值,然后以通常的方式执行现已完整的查询;
- 第 61 行:返回名称与 SQL 模式 [like] 匹配的商品的方法(字符 [%] 表示任意字符序列:[%3] 表示以 3 结尾的名称):
- 第 68 行:[AddWithValue(name, value)] 在一条语句中声明参数并为其赋值。参数的 SQL 类型由值的 C# 类型推断得出,此处为 [string]。当查询只执行一次时,这是最常见的形式;
- 第 72–75 行:结果的每一行都变成一个 [Article] 对象。列的类型是已知的:因此使用 [GetXxx] 方法。这些方法接受列号,在 MySqlConnector 中也接受列名。对于抽象类 [DbDataReader],列名由同名的扩展方法接受(类 [DataReaderExtensions],命名空间 [System.Data]),它们等价于 [reader.GetInt32(reader.GetOrdinal("currentstock"))]。
MySqlConnector 使用的 SQL 类型是枚举 [MySqlDbType] 的值。以下是最常见的类型及其对应的 C# 类型:
MySqlDbType 类型 | C# 类型 | MySQL 列类型 |
[Int32] | [int] | [INT] |
[Int64] | [long] | [BIGINT] |
[Decimal] | [decimal] | [DECIMAL(p,d)] |
[Double] | [double] | [DOUBLE] |
[VarChar] | [string] | [VARCHAR(n)],变长字符串 |
[DateTime] | [DateTime] | [DATETIME] |
[Date] | [DateOnly] 或 [DateTime] | [DATE] |
[Bool] | [bool] | [BOOL] ([TINYINT(1)] 的同义词) |
另一方面,抽象类使用枚举 [DbType] ([DbType.Int32]、[DbType.String]、[DbType.Decimal] 等),该枚举为所有连接器所共有,通过 [DbParameter.DbType] 属性使用。
10.5.2. SQL 注入
预处理并不是参数化查询的唯一优点,也不是最重要的优点。让我们回到插入查询。你可能想通过插入各个值,以编程方式构造其文本:
| string query = "insert into articles(name, price, currentstock, minimumstock) values('" + name + "', " + price + ", " + sa + ", " + sm + ")";
|
如果 (name, price, sa, sm) 等于 ("article1", 100, 10, 1),则查询变为:
| insert into articles(name, price, currentstock, minimumstock) values('article1', 100, 10, 1)
|
但如果名称是“l'article1”,则它变为:
| insert into articles(name, price, currentstock, minimumstock) values('l'article1', 100, 10, 1)
|
由于名称中的撇号,它在语法上是不正确的。更糟的是:如果名称来自用户输入,用户就可以输入能够改变查询含义的文本。这被称为SQL 注入,是数据库驱动的应用程序中最普遍、最严重的安全漏洞之一。程序 [adonet-05.cs] 在按名称搜索商品时演示了这一点:
| #:package MySqlConnector@2.4.0
// SQL 注入:为什么必须使用参数化查询
using MySqlConnector;
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
// 文章名称,如同用户可能键入的那样
string[] names = ["bike", "diver's oxygen tanks", "x' or '1'='1"];
Console.WriteLine("1 - query built by concatenation");
foreach (string name in names)
{
SearchByConcatenation(name);
}
Console.WriteLine("\n2 - parameterized query");
foreach (string name in names)
{
SearchWithParameter(name);
}
// 按名称搜索文章:名称被插入到查询文本中
static void SearchByConcatenation(string name)
{
string query = "select id, name, price from articles where name = '" + name + "'";
Console.WriteLine($"[{name}] -> {query}");
try
{
using var connection = new MySqlConnection(ConnectionString);
connection.Open();
using var command = new MySqlCommand(query, connection);
Display(command);
}
catch (MySqlException ex)
{
Console.WriteLine($" error: {ex.Message}");
}
}
// 按名称搜索文章:名称是参数的值
static void SearchWithParameter(string name)
{
Console.WriteLine($"[{name}]");
try
{
using var connection = new MySqlConnection(ConnectionString);
connection.Open();
using var command = new MySqlCommand("select id, name, price from articles where name = @name", connection);
command.Parameters.AddWithValue("@name", name);
Display(command);
}
catch (MySqlException ex)
{
Console.WriteLine($" error: {ex.Message}");
}
}
// 显示 select 的结果
static void Display(MySqlCommand command)
{
using MySqlDataReader reader = command.ExecuteReader();
int nbRows = 0;
while (reader.Read())
{
Console.WriteLine($" {reader.GetInt32(0)} {reader.GetString(1)} {reader.GetDecimal(2)}");
nbRows++;
}
Console.WriteLine($" {nbRows} row(s)");
}
|
结果:
| > dotnet run adonet-05.cs
1 - query built by concatenation
[bike] -> select id, name, price from articles where name = 'bike'
1 bike 500.00
1 row(s)
[diver's oxygen tanks] -> select id, name, price from articles where name = 'diver's oxygen tanks'
error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's oxygen tanks'' at line 1
[x' or '1'='1] -> select id, name, price from articles where name = 'x' or '1'='1'
1 bike 500.00
2 pump 10.00
3 bow 600.00
4 arrows - set of 6 100.00
5 diving suit 300.00
6 diver's oxygen tanks 120.00
6 row(s)
2 - parameterized query
[bike]
1 bike 500.00
1 row(s)
[diver's oxygen tanks]
6 diver's oxygen tanks 120.00
1 row(s)
[x' or '1'='1]
0 row(s)
|
- 第 9 行:用户可能输入的三个名称:一个普通名称、一个包含撇号的名称,以及恶意文本;
- 第 25 行:第一个搜索方法将名称插入查询文本中:
- 第 3 行:对于普通名称,一切正常;
- 第 6 行:名称中的撇号过早地终止了 SQL 字符串;查询无效(第 7 行)。名称中包含撇号的商品无法被找到;
- 第 8 行:用户输入的文本包含的撇号经过精心安排,从而构成一个有效的查询,但其条件 [name = 'x' or '1'='1'] 对所有行都为真:查询返回整张表(第 15 行)。如果该查询用于验证用户名和密码,同样的手法可以让用户在不知道密码的情况下登录。对于允许在单条语句中包含多条 SQL 命令的连接器(MySqlConnector 就是如此),用户甚至可以在分号之后添加自己选择的命令,例如 [delete];
- 第 49 行:第二种方法使用参数化查询。参数的值绝不会被解释为 SQL——无论其内容如何,它都只是数据。带撇号的名称可以被找到(第 22 行),而恶意文本只不过是一件不存在的商品的名称(第 25 行)。
可以通过将撇号加倍来“中和”它们,但规则取决于 DBMS,而且很容易遗漏某种情况。规则很简单:用户(或任何外部来源)提供的值绝不能与 SQL 请求的文本拼接在一起——必须始终使用参数。交互式程序 [adonet-01.cs] 就其本质而言并不遵循这条规则:它执行用户键入的 SQL 命令。它是一个管理员工具,而不是值得效仿的示例。
10.6. 事务
10.6.1. 概述
事务是以“原子方式”执行的一系列 SQL 命令:
- 要么所有操作都成功;
- 要么其中一个操作失败,此时之前的所有操作都会被回滚。
最终,事务中的所有操作要么全部成功应用,要么全部不应用。经典的例子是银行转账:从一个账户扣款和向另一个账户入账必须都完成,否则都不执行。当应用程序自己控制事务时,它使用 [COMMIT] 命令提交事务,或使用 [ROLLBACK] 命令回滚事务。
在前面的示例中,我们没有使用事务。然而事务实际上是存在的,因为在 DBMS 中,SQL 命令总是在事务中执行。如果应用程序自己没有启动显式事务,DBMS 就会使用隐式事务。此时有两种情况:
- 每条 SQL 命令都属于一个事务,该事务由 DBMS 在命令之前打开,并在命令之后提交。这称为自动提交模式。其行为就好像应用程序为每条 SQL 命令创建了一个事务;
- DBMS 不处于自动提交模式:它在事务之外发出的第一条 SQL 命令时启动一个隐式事务,并让应用程序来结束它。之后的所有 SQL 命令都属于这个隐式事务,该事务可能因各种事件(连接关闭、启动新事务等)而终止,具体遵循 DBMS 特有的规则。应避免使用这种模式。
默认模式由 DBMS 的配置决定。MySQL 在默认使用的 InnoDB 存储引擎下以自动提交模式运行。
不同用户的命令在并行运行的事务中同时执行。一个事务所执行的操作可能会影响另一个事务所执行的操作。不同用户的事务之间有四种隔离级别:
- 读未提交(Read Uncommitted);
- 读已提交(Read Committed);
- 可重复读(Repeatable Read);
- 可串行化(Serializable)。
读未提交(Read Uncommitted)
这种隔离级别也称为“脏读”(Dirty Read)。下面是在此模式下可能发生的一个例子:
- 名为 U1 的用户在表 T 上启动一个事务;
- 名为 U2 的用户在同一张表 T 上启动一个事务;
- 用户 U1 修改了表 T 中的行,但尚未提交这些更改;
- 用户 U2“看到”了这些更改,并根据所见做出决策;
- 用户 U1 使用 [ROLLBACK] 回滚其事务。
在第 4 步中,用户 U2 根据后来被证明是不正确的数据做出了决策。
读已提交(Read Committed)
这种隔离级别避免了前面的陷阱:在第 4 步中,用户 U2 不会“看到”用户 U1 对表 T 所做的更改。只有在 U1 使用 [COMMIT] 提交其事务之后,U2 才会看到这些更改。不过,在此模式下可能出现以下情况:
- 用户 U1 在表 T 上启动一个事务;
- 用户 U2 在同一张表 T 上启动一个事务;
- 用户 U2 执行一条 [select] ,计算 T 中满足某个条件的行的 C 列的平均值;
- 用户 U1 修改([update])T 的 C 列中的某些值,并提交更改([commit]);
- 用户 U2 重复第 3 步中的同一条 [select] 。他们发现,由于 U1 所做的修改,C 列的平均值发生了变化。
用户 U2 现在只能看到 U1 已提交的更改,但在同一个事务中,两次相同的操作却得到了不同的结果。这种情况称为“不可重复读”。对于想要获得表 T 一致视图的人来说,这是个问题。
可重复读(Repeatable Read)
在这种隔离级别下,只要用户停留在同一个事务内,就能保证从数据库读取到相同的结果。他们操作的是数据的一个“快照”,该快照不反映其他事务所做的更改,即使这些更改已经提交。只有在完成自己的事务之后,他们才会看到这些更改。
从理论上讲,这种隔离级别还不够完美:另一个事务可能添加([insert])满足第 3 步中条件的行,这会改变后续读取的结果。这些行被称为“幻读”。这是 MySQL(InnoDB)的默认隔离级别,对于简单读取,它也能防止幻读。
可串行化(Serializable)
在这种隔离模式下,事务之间完全隔离:同时执行的两个事务的结果,与它们一个接一个地执行的结果相同。为实现这一点,DBMS 会锁定正在读取的数据,并使试图修改这些数据的事务等待(或失败)。这一级别会降低并行度,从而降低性能。
并非所有 DBMS 都提供这四种隔离级别。默认隔离级别通常是 Read Committed(SQL Server、PostgreSQL、Oracle)或 Repeatable Read(MySQL)。应用程序创建事务时,可以显式指定该事务所需的隔离级别。
10.6.2. 事务管理 API
[DbConnection] 类具有以下方法:
名称 | 类型 | 作用 |
[BeginTransaction] | M | 启动事务。 |
该方法有两种签名:
- [DbTransaction BeginTransaction()]:启动事务并返回 [DbTransaction] 对象,该对象用于控制事务;
- [DbTransaction BeginTransaction(IsolationLevel level)]:进一步指定事务所需的隔离级别。[level] 的取值来自枚举 [IsolationLevel] (命名空间 [System.Data]):
值 | 含义 |
[ReadUncommitted] | 事务可以读取另一个事务写入但尚未提交的数据。应避免使用。 |
[ReadCommitted] | 事务不能读取另一个事务写入但尚未提交的数据。不过,在事务内连续两次读取的数据可能发生变化,因为另一个事务可能在此期间修改了这些数据;另一个事务也可能添加了行(幻影行)。 |
[RepeatableRead] | 事务读取的数据在事务结束之前不会改变。理论上,这并不能阻止另一个事务添加行。 |
[Serializable] | 一切就像事务在单独运行一样。这会降低性能,因为事务不再并行运行。 |
[Snapshot] | 事务处理的是在事务开始时创建的数据副本。SQL Server 提供该级别;在 MySQL 中,[RepeatableRead] 级别在读取时已经是这样做的。 |
事务启动后,由 [DbTransaction] 对象对其进行控制(此处为 [MySqlTransaction]),我们将使用其以下属性 P 和方法 M:
名称 | 类型 | 作用 |
[Connection] | P | [DbConnection] 连接,该连接承载事务。 |
[Commit] | M | 提交事务:事务中所发出的 SQL 命令的结果成为最终结果,并对其他事务可见。 |
[Rollback] | M | 取消事务:事务中所发出的 SQL 命令的结果被回滚。 |
[Dispose] | M | 释放事务。未提交就被释放的事务会被回滚。 |
事务中的 SQL 命令必须在事务连接上执行,所用命令的 [Transaction] 属性要引用该事务。
10.6.3. 示例程序
程序 [adonet-06.cs] 演示了事务的效果:
| #:package MySqlConnector@2.4.0
// 事务
using System.Data;
using MySqlConnector;
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
Console.WriteLine($"Database connection string: [{ConnectionString}]");
// 两篇名称相同的文章:唯一性约束 UNQ_NAME 会拒绝第二篇
Article[] articles = [new(0, "article", 100, 10, 1), new(0, "article", 200, 20, 2)];
try
{
// 1 - 不使用显式事务的插入
Console.WriteLine("\nInsertion without a transaction...");
ExecuteUpdate(ConnectionString, "delete from articles");
try
{
InsertArticlesOutOfTransaction(ConnectionString, articles);
}
catch (MySqlException ex)
{
Console.WriteLine($"Database access error ({ex.Message})");
}
DisplayArticles(ConnectionString);
// 2 - 在事务内做同样的事
Console.WriteLine("\nInsertion inside a transaction...");
ExecuteUpdate(ConnectionString, "delete from articles");
InsertArticlesInTransaction(ConnectionString, articles);
DisplayArticles(ConnectionString);
}
catch (MySqlException ex)
{
Console.WriteLine($"Database access error ({ex.Message})");
}
// 不使用显式事务插入一个文章数组
static void InsertArticlesOutOfTransaction(string connectionString, Article[] articles)
{
using var connection = new MySqlConnection(connectionString);
connection.Open();
using MySqlCommand command = CreateInsertCommand(connection);
foreach (Article article in articles)
{
InsertArticle(command, article);
}
}
// 在事务内插入一个文章数组
static void InsertArticlesInTransaction(string connectionString, Article[] articles)
{
using var connection = new MySqlConnection(connectionString);
connection.Open();
// 事务开始
using MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
using MySqlCommand command = CreateInsertCommand(connection);
// 命令必须在事务内执行
command.Transaction = transaction;
foreach (Article article in articles)
{
InsertArticle(command, article);
}
// 一切顺利:我们提交事务
transaction.Commit();
Console.WriteLine("transaction committed...");
}
catch (MySqlException ex)
{
// 某次插入失败:我们回滚事务
transaction.Rollback();
Console.WriteLine($"transaction rolled back ({ex.Message})");
}
}
// 插入一篇文章的参数化命令
static MySqlCommand CreateInsertCommand(MySqlConnection connection)
{
var command = new MySqlCommand(
"insert into articles(name, price, currentstock, minimumstock) values(@name, @price, @cs, @ms)", connection);
command.Parameters.Add("@name", MySqlDbType.VarChar, 30);
command.Parameters.Add("@price", MySqlDbType.Decimal);
command.Parameters.Add("@cs", MySqlDbType.Int32);
command.Parameters.Add("@ms", MySqlDbType.Int32);
return command;
}
// 用参数化命令插入一篇文章
static void InsertArticle(MySqlCommand command, Article article)
{
command.Parameters["@name"].Value = article.Name;
command.Parameters["@price"].Value = article.Price;
command.Parameters["@cs"].Value = article.CurrentStock;
command.Parameters["@ms"].Value = article.MinimumStock;
command.ExecuteNonQuery();
Console.WriteLine($"article [{article.Name}, {article.Price}] inserted");
}
// 执行 update 语句
static void ExecuteUpdate(string connectionString, string query)
{
using var connection = new MySqlConnection(connectionString);
connection.Open();
using var command = new MySqlCommand(query, connection);
Console.WriteLine($"{command.ExecuteNonQuery()} row(s) modified");
}
// 显示表的内容
static void DisplayArticles(string connectionString)
{
using var connection = new MySqlConnection(connectionString);
connection.Open();
using var command = new MySqlCommand("select id, name, price, currentstock, minimumstock from articles", connection);
using MySqlDataReader reader = command.ExecuteReader();
Console.WriteLine("Content of the table [articles]:");
while (reader.Read())
{
Console.WriteLine($" {reader.GetInt32(0)} {reader.GetString(1)} {reader.GetDecimal(2)} {reader.GetInt32(3)} {reader.GetInt32(4)}");
}
}
// 一篇文章:[articles] 表的一行
record Article(int Id, string Name, decimal Price, int CurrentStock, int MinimumStock);
|
结果:
| > dotnet run adonet-06.cs
Database connection string: [Server=localhost;Port=3306;Database=dbarticles;User=root;Password=]
Insertion without a transaction...
6 row(s) modified
article [article, 100] inserted
Database access error (Duplicate entry 'article' for key 'articles.UNQ_NAME')
Content of the table [articles]:
7 article 100.00 10 1
Insertion inside a transaction...
1 row(s) modified
article [article, 100] inserted
transaction rolled back (Duplicate entry 'article' for key 'articles.UNQ_NAME')
Content of the table [articles]:
|
- 第 11 行:一个包含两个同名项目 [article] 的数组。表 [articles] 上的 [UNQ_NAME] 唯一性约束使二者无法同时存在于数据库中:插入第二个项目时,DBMS 会抛出异常,并由连接器转发。为演示事务的效果,将以两种方式插入这两个项目:
- 首先,在任何显式事务之外插入。由于 MySQL 处于自动提交模式,每次插入都在一个隐式事务中执行。第一条记录会被插入,第二条则不会;
- 然后,在包含两次插入的显式事务中插入。由于第二次插入将失败,第一次插入会被回滚:最终不会插入任何记录;
- 第 15–26 行:清空表,然后在没有显式事务的情况下插入这两个项目。由于我们知道第二次插入会抛出异常,因此用 [try] / [catch] 捕获它;最后显示表的内容;
- 第 28–32 行:重复同样的流程,但使用显式事务。这一次,异常在 [InsertArticlesInTransaction] 方法中处理;
- 第 40 行:不使用事务的插入方法。它使用两个方法:[CreateInsertCommand] (第 80 行),用于创建参数化插入命令;以及 [InsertArticle] (第 92 行),用于为参数赋值并执行命令;
- 第 52 行:在事务中插入的方法:
- 第 57 行:创建事务,隔离级别为 [ReadCommitted],与 2008 年的课程中指定的一致。事务属于该连接。[using] 语句确保事务在未提交时被回滚,即被取消;
- 第 62 行:将命令置于事务之内。这一行是必需的:使用 MySqlConnector 时,如果在已有事务进行中的连接上执行命令而不指定该事务,会抛出 [InvalidOperationException];
- 第 63–65 行:插入操作;
- 第 68 行:一切顺利:事务被提交,插入的记录被永久添加到数据库中;
- 第 74 行:有一次插入失败:事务被回滚。显式的 [Rollback] 并非严格必要,因为释放未提交的事务本来就会将其回滚,但它使代码更清晰。
结果中:
- 第 5 行:由命令 [delete] 显示:表中有六行;
- 第 7 行:第二次插入引发的异常。消息表明表 [UNQ_NAME] 上的约束 [articles] 未被满足;
- 第 9 行:表不为空:第一条记录已被插入;
- 第 12 行:由命令 [delete] 显示:表中有一行;
- 第 13 行:第一次插入成功……是在事务之内;
- 第 14 行:事务失败时显示的消息;
- 第 15 行:表为空:事务的 [Rollback] 回滚了第一次插入。
10.7. ExecuteScalar 方法
在 [DbCommand] 类的方法中,有下面这个方法:
名称 | 类型 | 作用 |
[ExecuteScalar] | M | 执行只返回单个结果的 SQL [select] 命令,例如 [select count(*) from articles]。 |
更确切地说,[ExecuteScalar] 以 [object?] 的形式返回结果第一行的第一列。程序 [adonet-07.cs] 演示了它的几种用法:
| #:package MySqlConnector@2.4.0
// ExecuteScalar 方法
using MySqlConnector;
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
try
{
// select 返回的单个值
object? averagePrice = ExecuteScalar(ConnectionString, "select avg(price) from articles");
Console.WriteLine($"Average price of the articles = {averagePrice} (type {averagePrice?.GetType().Name})");
object? nbArticles = ExecuteScalar(ConnectionString, "select count(id) from articles");
Console.WriteLine($"Number of articles = {nbArticles} (type {nbArticles?.GetType().Name})");
object? name = ExecuteScalar(ConnectionString, "select name from articles where price = (select max(price) from articles)");
Console.WriteLine($"Most expensive article = {name} (type {name?.GetType().Name})");
object? missing = ExecuteScalar(ConnectionString, "select name from articles where price > 10000");
Console.WriteLine($"Article over 10000 euros: {missing ?? "null"}");
object? empty = ExecuteScalar(ConnectionString, "select max(price) from articles where price > 10000");
Console.WriteLine($"Maximum price beyond 10000 euros: {empty} (type {empty?.GetType().Name})");
// 把结果转换为预期的类型
decimal average = Convert.ToDecimal(averagePrice);
int count = Convert.ToInt32(nbArticles);
Console.WriteLine($"Rounded average = {Math.Round(average, 2)}, count = {count}");
// 直接强制转换会失败:count 返回的是 Int64(long),而不是 Int32(int)
int n = (int)nbArticles!;
Console.WriteLine($"n = {n}");
}
catch (Exception ex) when (ex is MySqlException or InvalidCastException)
{
Console.WriteLine($"{ex.GetType().Name} : {ex.Message}");
}
// 执行返回单个值的 select
static object? ExecuteScalar(string connectionString, string query)
{
using var connection = new MySqlConnection(connectionString);
connection.Open();
using var command = new MySqlCommand(query, connection);
return command.ExecuteScalar();
}
|
结果:
| > dotnet run adonet-07.cs
Average price of the articles = 271.666667 (type Decimal)
Number of articles = 6 (type Int64)
Most expensive article = bow (type String)
Article over 10000 euros: null
Maximum price beyond 10000 euros: (type DBNull)
Rounded average = 271.67, count = 6
InvalidCastException : Unable to cast object of type 'System.Int64' to type 'System.Int32'.
|
- 第 36 行:程序的 [ExecuteScalar] 方法打开连接,并使用命令的 [ExecuteScalar] 方法执行查询;
- 第 11 行:商品的平均价格。结果的 C# 类型取决于该值的 SQL 类型:[DECIMAL] 列的平均值是 [decimal] (第 2 行);
- 第 13 行:商品数量。MySQL 返回 [BIGINT] 类型的整数,在 C# 中它变成 [long] ([Int64])(第 3 行);
- 第 15 行:最贵商品的名称,是一个字符串。该查询包含一个用括号括起来的子查询;
- 第 17 行:如果查询没有返回任何行,[ExecuteScalar] 返回 [null] (第 5 行);
- 第 19 行:如果查询返回了一行,其值为 SQL 值 [NULL](空集合的最大值),[ExecuteScalar] 则返回对象 [DBNull.Value],它显示为空字符串(第 6 行)。因此必须区分两种情况:没有行([null])和值为 [NULL] ([DBNull]);
- 第 23–24 行:为了得到预期类型的结果,我们使用 [Convert] 类的方法,它可以将 [long] 转换为 [int];
- 第 27 行:直接类型转换会失败,因为该对象是“装箱”的 [long]:它只能拆箱为其确切类型。2008 年的程序写的是 [(int)ExecuteScalar(…, "select count(id) from articles")]:这在 SQL Server Compact 中可行,因为它的 [count] 产生 [int],而在 MySQL 中会失败(第 8 行)。这是通用连接器无法隐藏的 DBMS 特性的一个例子。
10.8. 异步方法
第 9 章说明过,在为大量用户提供服务的应用程序(服务器、Web 服务)或带有图形用户界面的应用程序中,输入/输出操作必须是异步的:SQL 命令在服务器上执行时,不能阻塞应用程序线程。ADO.NET 中所有与 DBMS 通信的方法都有异步版本,它返回一个任务([Task]、[Task<T>]或 [ValueTask]),并接受一个可选的 [CancellationToken]:
类 | 同步方法 | 异步方法 |
[DbConnection] | [Open]、[Close]、[BeginTransaction] | [OpenAsync]、[CloseAsync]、[BeginTransactionAsync] |
[DbCommand] | [ExecuteReader]、[ExecuteNonQuery]、[ExecuteScalar]、[Prepare] | [ExecuteReaderAsync]、[ExecuteNonQueryAsync]、[ExecuteScalarAsync]、[PrepareAsync] |
[DbDataReader] | [Read]、[NextResult]、[Close] | [ReadAsync]、[NextResultAsync]、[CloseAsync] |
[DbTransaction] | [Commit]、[Rollback] | [CommitAsync]、[RollbackAsync] |
ADO.NET 对象还实现了 [IAsyncDisposable] 接口:使用 [await using] (C# 8)释放它们,该语句会调用其 [DisposeAsync] 方法。程序 [adonet-08.cs] 使用了这些方法:
| #:package MySqlConnector@2.4.0
// ADO.NET 方法的异步版本
using System.Data.Common;
using System.Diagnostics;
using System.Runtime.CompilerServices;
using MySqlConnector;
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
// 数据源:它为同一个数据库创建(已打开的)连接
await using var dataSource = new MySqlDataSource(ConnectionString);
try
{
// 1 - 在事务内执行 update 语句
await using (MySqlConnection connection = await dataSource.OpenConnectionAsync())
{
await using MySqlTransaction transaction = await connection.BeginTransactionAsync();
await using var command = new MySqlCommand("update articles set price = price * 1.1 where price < @price", connection, transaction);
command.Parameters.AddWithValue("@price", 200);
int nbRows = await command.ExecuteNonQueryAsync();
await transaction.CommitAsync();
Console.WriteLine($"{nbRows} price(s) increased by 10 %");
}
// 2 - 一个 select,其行在读取时逐行返回
await foreach (Article article in GetArticlesAsync(dataSource))
{
Console.WriteLine(article);
}
// 3 - 单个值
await using (DbCommand command = dataSource.CreateCommand("select sum(price * currentstock) from articles"))
{
decimal stockValue = Convert.ToDecimal(await command.ExecuteScalarAsync());
Console.WriteLine($"Stock value: {stockValue} euros");
}
// 4 - 被另一个事务阻塞的查询,一秒后被取消
// [blocking] 事务修改 1 号文章且不结束:该行保持被锁定
await using MySqlConnection connection1 = await dataSource.OpenConnectionAsync();
await using MySqlTransaction blocking = await connection1.BeginTransactionAsync();
await using (var command1 = new MySqlCommand("update articles set currentstock = currentstock - 1 where id = 1", connection1, blocking))
{
await command1.ExecuteNonQueryAsync();
}
// 另一个连接想修改同一行:它必须等待 [blocking] 事务结束
using var cancellation = new CancellationTokenSource(TimeSpan.FromSeconds(1));
var stopwatch = Stopwatch.StartNew();
try
{
await using DbCommand command = dataSource.CreateCommand("update articles set price = price * 2 where id = 1");
await command.ExecuteNonQueryAsync(cancellation.Token);
}
catch (OperationCanceledException ex)
{
Console.WriteLine($"{ex.GetType().Name} after {stopwatch.Elapsed.TotalSeconds:F0} s : {ex.Message}");
}
// [blocking] 事务没有被提交:[blocking] 被释放时它会被回滚
}
catch (MySqlException ex)
{
Console.WriteLine($"Database access error ({ex.Message})");
}
// 表中的文章,逐篇返回(异步流)
static async IAsyncEnumerable<Article> GetArticlesAsync(MySqlDataSource dataSource,
[EnumeratorCancellation] CancellationToken token = default)
{
await using MySqlConnection connection = await dataSource.OpenConnectionAsync(token);
await using var command = new MySqlCommand("select id, name, price, currentstock, minimumstock from articles", connection);
await using MySqlDataReader reader = await command.ExecuteReaderAsync(token);
while (await reader.ReadAsync(token))
{
yield return new Article(reader.GetInt32(0), reader.GetString(1), reader.GetDecimal(2),
reader.GetInt32(3), reader.GetInt32(4));
}
}
// 一篇文章:[articles] 表的一行
record Article(int Id, string Name, decimal Price, int CurrentStock, int MinimumStock);
|
结果:
| > dotnet run adonet-08.cs
3 price(s) increased by 10 %
Article { Id = 1, Name = bike, Price = 500.00, CurrentStock = 10, MinimumStock = 5 }
Article { Id = 2, Name = pump, Price = 11.00, CurrentStock = 10, MinimumStock = 2 }
Article { Id = 3, Name = bow, Price = 600.00, CurrentStock = 4, MinimumStock = 1 }
Article { Id = 4, Name = arrows - set of 6, Price = 110.00, CurrentStock = 12, MinimumStock = 20 }
Article { Id = 5, Name = diving suit, Price = 300.00, CurrentStock = 8, MinimumStock = 2 }
Article { Id = 6, Name = diver's oxygen tanks, Price = 132.00, CurrentStock = 10, MinimumStock = 5 }
Stock value: 12550.00 euros
OperationCanceledException after 1 s : Query execution was interrupted
|
- 第 12 行:数据源 [MySqlDataSource],派生自 [DbDataSource] (.NET 7)的类。它使用连接字符串一次性创建,然后提供连接和命令。在应用程序中,向依赖注入容器注册的是它,而不是连接字符串。由于它管理连接池,因此在程序结束时释放,这里通过 [await using] 释放;
- 第 16–24 行:事务中的更新命令:
- 第 17 行:[OpenConnectionAsync] 返回一个已经打开的连接。打开连接是一项网络操作:它是异步的。[await using (…) { … }] 块在退出时释放连接;
- 第 19–23 行:创建事务,由 [update] 命令通过 [ExecuteNonQueryAsync] 执行,事务由 [CommitAsync] 提交。请注意 [MySqlCommand] 的三参数构造函数:请求、连接、事务;
- 第 68–76 行:一个以 异步流 [IAsyncEnumerable<Article>] (C# 8,第 9 章)的形式返回记录的方法:
- [ExecuteReaderAsync] 执行查询,然后 [await reader.ReadAsync(token)] 逐行读取。[Read] 确实可能需要等待网络:读取器分批从服务器接收行;
- [yield return] 一读到记录就立即将其返回给调用代码,而无需等待整个表读取完毕:返回一百万行的 [select] 所占用的内存并不比返回十行的 [select] 更多;
- 流结束时(或调用代码放弃该流时),连接、命令和读取器都会被释放;
- [[EnumeratorCancellation]] 特性允许调用代码通过 [GetArticlesAsync(dataSource).WithCancellation(token)] 传入取消令牌;
- 第 28 行:调用代码使用 [await foreach] 遍历该流。结果表明,价格低于 200 欧元的三件商品涨价了 10%;
- 第 33–37 行:[dataSource.CreateCommand] 创建一个将打开自己的连接(并关闭它)的命令;它返回 [DbCommand]。[ExecuteScalarAsync] 返回唯一的值;
- 第 40–58 行:取消请求:
- 第 42–46 行:第一个连接启动一个事务,修改 1 号商品但不提交。DBMS 已将被修改的行锁定,直到事务结束;
- 第 49 行:一个取消源,它将在一秒后取消其令牌(第 9 章);
- 第 54 行:第二个连接试图修改同一行。它必须等待第一个事务结束……而第一个事务不会结束。一秒后,令牌被取消:MySqlConnector 随即指示服务器中止该请求(MySQL 命令 [KILL QUERY]),并且 [ExecuteNonQueryAsync] 抛出 [OperationCanceledException](第 10 行)。如果没有取消,该请求会一直等到 MySQL 的锁超时(默认 50 秒),然后失败;
- 事务 [blocking] 永远不会被提交:当 [blocking] 在 [try] 块结束处被释放时,它会被回滚。
对于一次只执行一项任务的小型控制台程序,异步版本没有任何好处:它们不会让请求变得更快。然而,它们是服务器应用程序(例如 ASP.NET Core Web 服务,第 13 章)中的标准做法,这类应用同时为许多用户提供服务。
10.9. 批处理执行
每次执行命令都涉及应用程序与服务器之间的一次往返。需要执行多条 SQL 命令时,可以以批处理的形式,在一次交互中将它们发送给 DBMS。.NET 6 向 ADO.NET 添加了抽象类 [DbBatch] 和 [DbBatchCommand] :批处理是一个命令列表,每个命令都有自己的文本和参数,通过一次调用执行。MySqlConnector 通过 [MySqlBatch] 和 [MySqlBatchCommand] 类实现了它们。程序 [adonet-09.cs] 使用了它们:
| #:package MySqlConnector@2.4.0
// 批处理 API(DbBatch,.NET 6):多条 SQL 语句在一次交互中发送给 DBMS
using MySqlConnector;
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
try
{
await using var connection = new MySqlConnection(ConnectionString);
await connection.OpenAsync();
Console.WriteLine($"The connector can execute batches: {connection.CanCreateBatch}");
// 1 - 一个包含三条 update 语句的批处理,每条都有自己的参数
await using var batch = new MySqlBatch(connection)
{
BatchCommands =
{
new MySqlBatchCommand("insert into articles(name, price, currentstock, minimumstock) values(@name, @price, 5, 1)")
{
Parameters = { new MySqlParameter("@name", "canoe"), new MySqlParameter("@price", 800) }
},
new MySqlBatchCommand("update articles set currentstock = currentstock + @addition where currentstock < minimumstock")
{
Parameters = { new MySqlParameter("@addition", 10) }
},
new MySqlBatchCommand("delete from articles where name = 'pump'"),
}
};
int total = await batch.ExecuteNonQueryAsync();
Console.WriteLine($"Total number of modified rows: {total}");
// 2 - 一个包含两个 select 的批处理:一个读取器,两个连续的结果
await using var batch2 = new MySqlBatch(connection)
{
BatchCommands =
{
new MySqlBatchCommand("select name, currentstock from articles where currentstock < minimumstock + 5"),
new MySqlBatchCommand("select count(*), sum(price * currentstock) from articles"),
}
};
await using MySqlDataReader reader = await batch2.ExecuteReaderAsync();
Console.WriteLine("Articles to watch:");
while (await reader.ReadAsync())
{
Console.WriteLine($" {reader.GetString(0)} : {reader.GetInt32(1)}");
}
// 移到下一个结果
await reader.NextResultAsync();
await reader.ReadAsync();
Console.WriteLine($"{reader.GetInt64(0)} articles, stock value: {reader.GetDecimal(1)} euros");
}
catch (MySqlException ex)
{
Console.WriteLine($"Database access error ({ex.Message})");
}
|
结果:
| > dotnet run adonet-09.cs
The connector can execute batches: True
Total number of modified rows: 3
Articles to watch:
bow : 4
arrows - set of 6 : 22
canoe : 5
6 articles, stock value: 17200.00 euros
|
- 第 12 行:[CanCreateBatch] 属性(属于 [DbConnection])指示连接器是否能够执行批处理。对于不支持的连接器,通用方法 [connection.CreateBatch()] 会抛出 [NotSupportedException];
- 第 15–27 行:包含三条 update 语句的批处理。该批处理使用对象初始值设定项和集合初始值设定项构造:集合 [BatchCommands] 接收三个 [MySqlBatchCommand] 对象,其中前两个拥有各自的参数集合 [Parameters]。批处理中的每个命令都有自己的参数,即使它们与另一个命令的参数同名;
- 第 30 行:三条命令在一次交互中发送并执行。[ExecuteNonQueryAsync] 返回被修改行的总数:一次插入([canoe])、一次更新(箭的库存,低于最低库存)和一次删除([pump]);
- 第 34–39 行:包含两条 [select] 命令的批处理;
- 第 42 行:批处理由 [ExecuteReaderAsync] 执行,它为两个结果返回同一个读取器;
- 第 49 行:读完第一个结果的行之后,[NextResultAsync] 将读取器移到下一个结果。
同样的效果也可以通过在单个命令中编写多条以分号分隔的 SQL 命令来实现,MySqlConnector 接受这种写法。但是,这样一来所有命令的参数会混在一起,而且并非所有 DBMS 都接受这种语法;批处理 API 才是 ADO.NET 规定的标准形式。
10.10. 示例应用程序 - 第 6 版
让我们重新回到示例应用程序 [Tax]。其最新版本,即第 5 版,已在第 6 章中讨论过。它是一个三层应用程序,其 [dao] 层从文本文件、JSON 文件或自身的代码中获取数据,各层通过依赖注入容器集成。我们修改 [dao] 层,使其从 MySQL 数据库中获取数据:

2008 年的课程(第 7 版)通过将前一版本的 [ui] 层和 [business] 层的源代码重新编译到一个新的解决方案中来复用它们,并使用 Spring.NET 集成各层。我们通过项目引用原样复用第 5 版的项目,只编写新的部分:[dao] 层以及集成各层的程序。
10.10.1. 数据库
税率档次存储在名为 [dbtaxes] 的 MySQL 数据库的 [brackets] 表中,该数据库由 [sql/dbtaxes.sql] 脚本创建:
| -- creation of the database [dbtaxes] of the application [Tax] (version 6)
-- execution: mysql -uroot < dbtaxes.sql
-- the script is written in UTF-8
SET NAMES utf8mb4;
-- the database is recreated on each execution of the script
DROP DATABASE IF EXISTS dbtaxes;
CREATE DATABASE dbtaxes CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE dbtaxes;
-- the table of the tax brackets (2004 scale on 2003 income)
CREATE TABLE brackets (
id INT PRIMARY KEY AUTO_INCREMENT,
limits DECIMAL(10,2) NOT NULL,
coeffr DECIMAL(6,3) NOT NULL,
coeffn DECIMAL(10,2) NOT NULL
);
INSERT INTO brackets (limits, coeffr, coeffn) VALUES
(4962, 0, 0),
(8382, 0.068, 291.09),
(14753, 0.191, 1322.92),
(23888, 0.283, 2668.39),
(38868, 0.374, 4846.98),
(47932, 0.426, 6883.66),
(0, 0.481, 9505.54);
-- a user specific to the application, who only has rights on the database [dbtaxes]
CREATE USER IF NOT EXISTS 'admtaxes'@'localhost' IDENTIFIED BY 'pwdtaxes';
GRANT ALL PRIVILEGES ON dbtaxes.* TO 'admtaxes'@'localhost';
|
- 第 13–17 行:表 [brackets] 有四列:由 DBMS 生成的主键 [id],以及三列 [limits]、[coeffr]和 [coeffn],它们表示一个税率档次,类型为 [DECIMAL]:这些是必须准确的金额和系数。2008 年的脚本使用的是 [DOUBLE] 类型;
- 第 19–26 行:2003 年收入适用的 2004 年税率表的七个档次,自课程开头起一直使用(这是一道练习:该税率表并非最新)。各档次按其阈值升序插入,[id] 列反映了这一顺序;
- 创建 MySQL 用户 [admtaxes],密码为 [pwdtaxes],授予其完全权限([GRANT ALL PRIVILEGES]),范围是数据库 [dbtaxes] 中的所有对象([dbtaxes.*]),且仅限于这些对象。应用程序将以该用户而不是管理员 [root] 的身份运行:即使管理员的密码泄露,也只有数据库 [dbtaxes] 会面临风险。[IF NOT EXISTS] 使脚本可以多次运行。
脚本由管理员执行:
| > mysql -uroot < dbtaxes.sql
|
然后我们验证用户 [admtaxes] 可以访问这些税率档次(单独使用 [-p] 选项会提示输入密码,输入时不显示密码):
| > mysql -uadmtaxes -p -t dbtaxes -e "select * from brackets"
Enter password:
+----+----------+--------+---------+
| id | limits | coeffr | coeffn |
+----+----------+--------+---------+
| 1 | 4962.00 | 0.000 | 0.00 |
| 2 | 8382.00 | 0.068 | 291.09 |
| 3 | 14753.00 | 0.191 | 1322.92 |
| 4 | 23888.00 | 0.283 | 2668.39 |
| 5 | 38868.00 | 0.374 | 4846.98 |
| 6 | 47932.00 | 0.426 | 6883.66 |
| 7 | 0.00 | 0.481 | 9505.54 |
+----+----------+--------+---------+
|
10.10.2. TaxV6 解决方案
第 6 版是一个由三个项目组成的解决方案,位于 [10-adonet/TaxV6] 文件夹中:
| csharp-oct-2026/
06-architectures/TaxV5/ version 5, reused
Tax.Dao/ entities, ITaxDao interface, implementations of version 5
Tax.Business/ [business] layer
Tax.Ui/ [ui] layer (Dialog class) and program of version 5
10-adonet/
sql/
dbtaxes.sql database creation script
TaxV6/
TaxV6.slnx
Tax.Dao.MySql/ library: the new [dao] layer
Tax.Dao.MySql.csproj
Dao/
MySqlTax.cs
DbTax.cs
Tax.Tests.MySql/ MSTest tests of the new [dao] layer
Tax.Tests.MySql.csproj
MSTestSettings.cs
MySqlTaxTests.cs
Tax.Ui.MySql/ executable: integration of the layers
Tax.Ui.MySql.csproj
Program.cs
appsettings.json
appsettings-db.json
appsettings-base.json
appsettings-server.json
appsettings-query.json
|
它是通过在 [TaxV6] 文件夹中输入以下命令构建的:
| > dotnet new classlib -n Tax.Dao.MySql -o Tax.Dao.MySql
> dotnet new console -n Tax.Ui.MySql -o Tax.Ui.MySql
> dotnet new mstest -n Tax.Tests.MySql -o Tax.Tests.MySql
> dotnet new sln --format slnx -n TaxV6
> dotnet sln TaxV6.slnx add Tax.Dao.MySql Tax.Ui.MySql Tax.Tests.MySql
> dotnet add Tax.Dao.MySql reference ../../06-architectures/TaxV5/Tax.Dao
> dotnet add Tax.Dao.MySql package MySqlConnector --version 2.4.0
> dotnet add Tax.Ui.MySql reference Tax.Dao.MySql ../../06-architectures/TaxV5/Tax.Business ../../06-architectures/TaxV5/Tax.Ui
> dotnet add Tax.Tests.MySql reference Tax.Dao.MySql ../../06-architectures/TaxV5/Tax.Business
|
解决方案文件只包含这三个新项目:
| <Solution>
<Project Path="Tax.Dao.MySql/Tax.Dao.MySql.csproj" />
<Project Path="Tax.Tests.MySql/Tax.Tests.MySql.csproj" />
<Project Path="Tax.Ui.MySql/Tax.Ui.MySql.csproj" />
</Solution>
|
命令 [dotnet build TaxV6.slnx] 也会编译这些项目所引用的第 5 版项目。
这里体现了分层架构和类库的价值。在 2008 年,第 5 版 [business] 层和 [ui] 层的 DLL 文件包含了它们在编译时所用的 [dao] 层 的 DLL 文件的名称和版本,必须重新编译其源代码,才能使它们与新的 [dao] 层配合工作。而在这里,[business] 层和 [ui] 层只认识 [ITaxDao] 接口,该接口在第 5 版的 [Tax.Dao] 项目中定义;新的 [dao] 层在一个新项目中实现该接口。第 5 版的代码没有被修改一行。
10.10.3. [dao] 层
项目文件 [Tax.Dao.MySql.csproj] 如下:
| <Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<TargetFramework>net10.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<!-- 第 5 版的实体和 ITaxDao 接口 -->
<ProjectReference Include="../../../06-architectures/TaxV5/Tax.Dao/Tax.Dao.csproj" />
</ItemGroup>
<ItemGroup>
<!-- MySQL 的 ADO.NET 连接器 -->
<PackageReference Include="MySqlConnector" Version="2.4.0" />
</ItemGroup>
</Project>
|
- 第 11 行:对第 5 版中项目 [Tax.Dao] 的引用,该项目包含实体([TaxBracket]、[TaxException])和接口 [ITaxDao]。该路径相对于项目文件夹;
- 第 16 行:MySqlConnector 连接器。
类 [MySqlTax] 实现了接口 [ITaxDao]:
| using Entities;
using MySqlConnector;
namespace Dao;
// 从 MySQL 数据库读取税率档次的 [dao] 层
public class MySqlTax : ITaxDao
{
// 此实现特有的错误代码
public enum ErrorCodes { Access = 101, Data = 102 }
// 默认查询:档次必须按限额递增的顺序返回
public const string DefaultQuery = "select limits, coeffr, coeffn from brackets order by id";
// 税率档
public TaxBracket[] TaxBrackets { get; }
// 构造函数:税率档次一次读取,一劳永逸
public MySqlTax(string connectionString, string query = DefaultQuery)
{
List<TaxBracket> brackets = [];
try
{
using var connection = new MySqlConnection(connectionString);
connection.Open();
using var command = new MySqlCommand(query, connection);
using MySqlDataReader reader = command.ExecuteReader();
// 结果的每一行对应一个档次:limit、coeffR、coeffN
while (reader.Read())
{
brackets.Add(new TaxBracket(reader.GetDecimal(0), reader.GetDecimal(1), reader.GetDecimal(2)));
}
}
catch (MySqlException e)
{
// 数据库访问错误:我们把它包装在 TaxException 中
throw new TaxException("Error accessing the tax brackets database", e) { Code = (int)ErrorCodes.Access };
}
catch (Exception e) when (e is InvalidCastException or ArgumentOutOfRangeException)
{
// 缺少列、类型不正确的列或值为 NULL 的列
throw new TaxException("The tax brackets of the database are incorrect", e) { Code = (int)ErrorCodes.Data };
}
// 空表也是一种错误
if (brackets.Count == 0)
{
throw new TaxException("The database contains no tax bracket") { Code = (int)ErrorCodes.Data };
}
TaxBrackets = [.. brackets];
}
}
|
- 第 7 行:类 [MySqlTax] 实现了第 5 版的 [ITaxDao] 接口:一个只读的 [TaxBrackets] 属性(第 16 行);
- 第 10 行:此实现特有的错误代码,仿照 [FileTax] 和 [JsonTax] 的错误代码设计:101(2008 年课程中使用的代码)表示数据库访问错误,102 表示数据不正确;
- 第 13 行:返回税率档次的查询。[order by id] 子句很重要:[business] 层要求税率档次按其上限升序排列,而不带 [select] 的 [order by] 返回行的顺序,SQL 并不保证(2008 年的查询遗漏了它);
- 第 19 行:构造函数接收连接字符串和查询,查询带有默认值(可选参数,C# 4)。与 2008 年的版本一样,税率档次在构造函数中 一次性 全部 读取 :[dao] 层在应用程序中是单例,此后不再查询数据库。由于构造函数不能是异步的,读取操作使用 ADO.NET 的同步方法;
- 第 24–27 行:连接、命令和读取器,与前面的示例相同;
- 第 29–31 行:结果的每一行都变成一个 [TaxBracket] 对象:[reader.GetDecimal(i)] 将第 i 列读取为 [decimal];
- 第 34–37 行:数据库访问错误被封装在一个带有代码 [TaxException] 的 [Access] 中,就像 [FileTax] 和 [JsonTax] 对输入/输出错误所做的那样。[ui] 层无需了解来自 MySqlConnector 的异常;
- 第 39–42 行:查询返回的列少于三列([ArgumentOutOfRangeException]),某列的值为 [NULL],或为非数值类型([InvalidCastException]):数据不正确;
- 第 45–47 行:空表同样是数据错误;
- 第 49 行:通过集合表达式将列表转换为数组。
10.10.4. [dao] 层的测试
2008 年的课程使用 NUnit 测试 [dao] 层。与第 6 章一样,我们使用 MSTest。测试项目引用新的 [dao] 层和第 5 版的 [business] 层:
| <ItemGroup>
<!-- MSTest 测试框架 -->
<PackageReference Include="MSTest" Version="4.0.2" />
</ItemGroup>
<ItemGroup>
<!-- 被测试的 [dao] 层和第 5 版的 [business] 层 -->
<ProjectReference Include="../Tax.Dao.MySql/Tax.Dao.MySql.csproj" />
<ProjectReference Include="../../../06-architectures/TaxV5/Tax.Business/Tax.Business.csproj" />
</ItemGroup>
|
测试类如下:
| using Dao;
using Entities;
using Business;
using MySqlConnector;
namespace Tests;
// [dao] 层 MySqlTax 和 DbTax 的测试(数据库 [dbtaxes] 必须存在)
[TestClass]
public class MySqlTaxTests
{
// 测试数据库
private const string ConnectionString = "Server=localhost;Port=3306;Database=dbtaxes;User=admtaxes;Password=pwdtaxes";
// 从数据库读取的档次与 "hardwired" 版本的相同
[TestMethod]
public void SameBrackets()
{
TaxBracket[] reference = new HardwiredTax().TaxBrackets;
CollectionAssert.AreEqual(reference, new MySqlTax(ConnectionString).TaxBrackets);
CollectionAssert.AreEqual(reference,
new DbTax(MySqlConnectorFactory.Instance, ConnectionString, MySqlTax.DefaultQuery).TaxBrackets);
}
// 使用新 [dao] 层的第 5 版 [business] 层
[TestMethod]
[DataRow(true, 2, 60000, 4282)]
[DataRow(true, 3, 60000, 2959)]
[DataRow(false, 0, 60000, 11519)]
public void CalculateTax(bool married, int nbChildren, int salary, int tax)
{
ITaxBusiness business = new TaxBusiness(new MySqlTax(ConnectionString));
Assert.AreEqual(tax, business.CalculateTax(married, nbChildren, salary));
}
// 数据库不存在(或用户无权访问)
[TestMethod]
public void MissingDatabase()
{
TaxException e = Assert.ThrowsExactly<TaxException>(
() => new MySqlTax(ConnectionString.Replace("dbtaxes", "dbtaxesunknown")));
Assert.AreEqual((int)MySqlTax.ErrorCodes.Access, e.Code);
Assert.IsInstanceOfType<MySqlException>(e.InnerException);
}
// 服务器不可达
[TestMethod]
public void UnreachableServer()
{
TaxException e = Assert.ThrowsExactly<TaxException>(
() => new MySqlTax(ConnectionString.Replace("3306", "3399")));
Assert.AreEqual((int)MySqlTax.ErrorCodes.Access, e.Code);
}
// 只返回两列的查询
[TestMethod]
public void IncorrectQuery()
{
TaxException e = Assert.ThrowsExactly<TaxException>(
() => new MySqlTax(ConnectionString, "select limits, coeffr from brackets"));
Assert.AreEqual((int)MySqlTax.ErrorCodes.Data, e.Code);
}
// 不返回任何行的查询
[TestMethod]
public void NoBracket()
{
TaxException e = Assert.ThrowsExactly<TaxException>(
() => new MySqlTax(ConnectionString, "select limits, coeffr, coeffn from brackets where id < 0"));
Assert.AreEqual((int)MySqlTax.ErrorCodes.Data, e.Code);
}
}
|
- 第 13 行:测试使用数据库 [dbtaxes],该数据库必须已由脚本 [dbtaxes.sql] 创建。因此,这些不再是严格意义上的单元测试(单元测试应当孤立地测试一个类),而是 集成测试:它们验证 [dao] 层能否与 DBMS 协同工作;
- 第 17–22 行:从数据库读取的税率档次(先由 [MySqlTax] 读取,然后由下文介绍的 [DbTax] 类读取)与“硬编码”版本 [HardwiredTax] 中的档次相同。[CollectionAssert.AreEqual] 使用元素的 [Equals] 方法逐个比较元素;[record struct] 的该方法比较各属性。请注意,从数据库读取的 [decimal] 值有两位小数([4962.00]),而 [HardwiredTax] 中的值没有小数([4962]):对于 [decimal] 类型,这两个值是相等的;
- 第 27–33 行:第 5 版的 [business] 层使用新的 [dao] 层计算预期的税额;
- 第 38–43 行:数据库不存在会导致抛出代码为 [TaxException] 的 [Access] 异常,它封装了原始的 [MySqlException] 异常;
- 第 48–51 行:服务器不可达(没有服务器在端口 3399 上监听)同样会导致访问错误;
- 方法 [IncorrectQuery] (第 57 行)和 [NoBracket] (第 66 行):查询只返回两列或不返回任何行,会导致抛出代码为 [TaxException] 的 [Data] 异常。
运行测试得到以下结果:
| > dotnet test --logger "console;verbosity=normal"
Test run for bin/Debug/net10.0/Tax.Tests.MySql.dll (.NETCoreApp,Version=v10.0)
VSTest version 18.0.2 (x64)
Starting test execution, please wait...
A total of 1 test files matched the specified pattern.
Passed SameBrackets [273 ms]
Passed CalculateTax (True,2,60000,4282) [2 ms]
Passed CalculateTax (True,3,60000,2959) [1 ms]
Passed CalculateTax (False,0,60000,11519) [< 1 ms]
Passed MissingDatabase [12 ms]
Passed UnreachableServer [4 ms]
Passed IncorrectQuery [1 ms]
Passed NoBracket [< 1 ms]
Test Run Successful.
Total tests: 8
Passed: 8
Total time: 1.1413 Seconds
|
10.10.5. 集成各层
[Tax.Ui.MySql] 项目包含集成各层的程序。其项目文件如下:
| <Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<!-- 新的 [dao] 层 -->
<ProjectReference Include="../Tax.Dao.MySql/Tax.Dao.MySql.csproj" />
<!-- 第 5 版的 [business] 层和 [ui] 层,保持不变 -->
<ProjectReference Include="../../../06-architectures/TaxV5/Tax.Business/Tax.Business.csproj" />
<ProjectReference Include="../../../06-architectures/TaxV5/Tax.Ui/Tax.Ui.csproj" />
</ItemGroup>
<ItemGroup>
<!-- 依赖注入容器与 JSON 配置文件的读取 -->
<PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="10.0.12" />
<PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="10.0.12" />
</ItemGroup>
<ItemGroup>
<!-- 配置文件被复制到可执行文件旁边 -->
<None Update="appsettings*.json" CopyToOutputDirectory="PreserveNewest" />
</ItemGroup>
</Project>
|
- 第 12 行:新的 [dao] 层;
- 第 14–15 行:第 5 版的 [business] 层和 [ui] 层。[Tax.Ui] 项目是一个可执行程序,但项目可以把可执行程序当作库来引用:我们使用它的公共类 [Dialog]。它的配置文件也会被复制到输出文件夹,但同名的 [Tax.Ui.MySql] 项目的配置文件会替换它们;
- 第 20–21 行:依赖注入容器和配置读取,与第 5 版相同。
配置文件 [appsettings.json] 如下:
| {
"ConnectionStrings": {
"dbtaxes": "Server=localhost;Port=3306;Database=dbtaxes;User=admtaxes;Password=pwdtaxes"
},
"Dao": {
"Implementation": "MySqlTax",
"Query": "select limits, coeffr, coeffn from brackets order by id"
}
}
|
- 第 3 行:用户 [admtaxes] 访问数据库 [dbtaxes] 的连接字符串;
- 第 6 行:[dao] 层的实现;
- 第 7 行:返回税率档次的查询。将它放在配置中,就可以使用不同的数据库——甚至是表或列名称不同的数据库——而无需修改代码。
程序 [Program.cs] 与第 5 版相同,只是 [dao] 层的注册有所不同:
| using System.Data.Common;
using Dao;
using Entities;
using Business;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using MySqlConnector;
using Ui;
// 配置文件:默认为 appsettings.json,或作为参数传入的文件
string configFile = args.Length > 0 ? args[0] : "appsettings.json";
// DbTax 实现可使用的 ADO.NET 连接器
DbProviderFactories.RegisterFactory("MySqlConnector", MySqlConnectorFactory.Instance);
// 层的集成
ITaxBusiness business;
try
{
// 读取可执行文件旁边的配置
IConfiguration config = new ConfigurationBuilder()
.SetBasePath(AppContext.BaseDirectory)
.AddJsonFile(configFile, optional: false)
.Build();
string implementation = config["Dao:Implementation"] ?? "MySqlTax";
string connectionString = config.GetConnectionString("dbtaxes")
?? throw new TaxException("The connection string [dbtaxes] is missing from the configuration");
string query = config["Dao:Query"] ?? MySqlTax.DefaultQuery;
// 在容器中注册各层
var services = new ServiceCollection();
// [dao] 层:由配置决定使用哪种实现
services.AddSingleton<ITaxDao>(_ => implementation switch
{
"MySqlTax" => new MySqlTax(connectionString, query),
"DbTax" => new DbTax(DbProviderFactories.GetFactory(config["Dao:Connector"] ?? ""), connectionString, query),
_ => throw new TaxException($"Unknown implementation [{implementation}] of the [dao] layer")
});
// [business] 层:容器会把 [dao] 层传给它
services.AddSingleton<ITaxBusiness, TaxBusiness>();
// 创建容器,然后创建 [business] 层(从而创建 [dao] 层)
ServiceProvider provider = services.BuildServiceProvider();
business = provider.GetRequiredService<ITaxBusiness>();
}
catch (Exception e)
{
// 显示错误并结束程序
Dialog.DisplayError(e);
return 1;
}
// 与用户的对话:第 5 版的 [ui] 层
Dialog.Run(business);
return 0;
|
- 第 14 行:注册 MySqlConnector 连接器,供下一段介绍的 [DbTax] 实现使用;
- 第 26–28 行:从配置中读取的连接字符串和查询。缺少连接字符串属于错误;查询有默认值。请注意运算符 [??] 后面跟着 [throw] 表达式(C# 7);
- 第 33–37 行:[dao] 层是一个 [MySqlTax] (或 [DbTax]),使用连接字符串和查询创建;
- 第 54 行:第 5 版的对话框,未作改动。
使用 [appsettings.json] 文件执行:
| > dotnet run
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: y 2 60000
Tax=4282 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: y 3 60000
Tax=2959 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: n 0 60000
Tax=11519 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop:
|
以下是课程的参考值:年薪 60,000 欧元时,有两个孩子的夫妇应缴税 4,282 欧元,有三个孩子的夫妇应缴税 2,959 欧元,无孩子的单身人士应缴税 11,519 欧元。
使用文件 [appsettings-base.json],其连接字符串指向名为 [dbtaxesunknown] 的数据库:
| > dotnet run -- appsettings-base.json
Exception chain:
----------------------------------------
Entities.TaxException: Error accessing the tax brackets database
----------------------------------------
MySqlConnector.MySqlException: Access denied for user 'admtaxes'@'localhost' to database 'dbtaxesunknown'
----------------------------------------
|
用户 [admtaxes] 只对数据库 [dbtaxes] 拥有权限:MySQL 拒绝访问任何其他数据库,无论其是否存在(第 6 行)。
使用文件 [appsettings-server.json],其连接字符串指定端口 3399——该端口上没有服务器在监听(如果 MySQL 服务器被停止,就会出现这种情况):
| > dotnet run -- appsettings-server.json
Exception chain:
----------------------------------------
Entities.TaxException: Error accessing the tax brackets database
----------------------------------------
MySqlConnector.MySqlException: Unable to connect to any of the specified MySQL hosts.
----------------------------------------
|
使用文件 [appsettings-query.json],其查询只请求 [limits] 和 [coeffr] 两列:
| > dotnet run -- appsettings-query.json
Exception chain:
----------------------------------------
Entities.TaxException: The tax brackets of the database are incorrect
----------------------------------------
System.ArgumentOutOfRangeException: ordinal ('2') must be less than '2'. (Parameter 'ordinal')
Actual value was 2.
----------------------------------------
|
在这三种情况下,[dao] 层都将原始异常封装在 [TaxException] 中,由第 5 版的 [Dialog.DisplayError] 方法显示,然后程序终止。
10.10.6. 更换数据库
[dao] 层的 [MySqlTax] 使用MySqlConnector 连接器的类。如果更换 DBMS,就必须编写新的 [dao] 层……或者像 2008 年课程中的 [dao] 层那样使用通用连接器:这就是 [DbTax] 类的作用,它位于 [Tax.Dao.MySql] 项目中:
| using System.Data.Common;
using Entities;
namespace Dao;
// 从任意数据库读取税率档次的 [dao] 层
// 它只知道 ADO.NET 的抽象类:连接器由其工厂提供给它
public class DbTax : ITaxDao
{
// 错误代码:与 MySqlTax 的相同
public enum ErrorCodes { Access = 101, Data = 102 }
// 税率档
public TaxBracket[] TaxBrackets { get; }
// 构造函数:连接器的工厂、连接字符串、返回税率档次的查询
public DbTax(DbProviderFactory factory, string connectionString, string query)
{
List<TaxBracket> brackets = [];
try
{
using DbConnection connection = factory.CreateConnection()
?? throw new InvalidOperationException("the connector cannot create a connection");
connection.ConnectionString = connectionString;
connection.Open();
using DbCommand command = connection.CreateCommand();
command.CommandText = query;
using DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
brackets.Add(new TaxBracket(reader.GetDecimal(0), reader.GetDecimal(1), reader.GetDecimal(2)));
}
}
catch (DbException e)
{
throw new TaxException("Error accessing the tax brackets database", e) { Code = (int)ErrorCodes.Access };
}
catch (Exception e) when (e is InvalidCastException or ArgumentOutOfRangeException)
{
throw new TaxException("The tax brackets of the database are incorrect", e) { Code = (int)ErrorCodes.Data };
}
if (brackets.Count == 0)
{
throw new TaxException("The database contains no tax bracket") { Code = (int)ErrorCodes.Data };
}
TaxBrackets = [.. brackets];
}
}
|
- 第 17 行:构造函数接收连接器工厂、连接字符串和查询。代码只使用 [System.Data.Common] 中的抽象类:其中不包含任何 DBMS 的名称;
- 第 22–27 行:连接由工厂创建([throw] 表达式处理理论上工厂可能返回 [null] 的情况),命令由连接发出;
- 第 34 行:所有连接器的基础异常。
配置文件 [appsettings-db.json] 选择此实现和连接器 [MySqlConnector]:
| {
"ConnectionStrings": {
"dbtaxes": "Server=localhost;Port=3306;Database=dbtaxes;User=admtaxes;Password=pwdtaxes"
},
"Dao": {
"Implementation": "DbTax",
"Connector": "MySqlConnector",
"Query": "select limits, coeffr, coeffn from brackets order by id"
}
}
|
程序 [Program.cs] 使用以该名称注册的工厂创建 [dao] 层:
| "DbTax" => new DbTax(DbProviderFactories.GetFactory(config["Dao:Connector"] ?? ""), connectionString, query),
|
执行:
| > dotnet run -- appsettings-db.json
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: y 2 60000
Tax=4282 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop:
|
要使用 SQL Server 数据库(或 PostgreSQL),只需:
- 在 SQL Server 数据库中创建税率档次表,例如名为 [data] 、列为 [data1]、[data2]和 [data3] 的表——2008 年的课程就是这样做的,以突出将查询放入配置的好处;
- 将包 [Microsoft.Data.SqlClient] (或 [Npgsql])添加到项目 [Tax.Ui.MySql] 中;
- 在 [Program.cs] 中注册其工厂:[DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance)] (或 [DbProviderFactories.RegisterFactory("Npgsql", NpgsqlFactory.Instance)]);
- 编写类似于 [appsettings-db.json] 的配置文件:
| {
"ConnectionStrings": {
"dbtaxes": "Server=localhost;Database=dbtaxes;User Id=admtaxes;Password=pwdtaxes;TrustServerCertificate=True"
},
"Dao": {
"Implementation": "DbTax",
"Connector": "Microsoft.Data.SqlClient",
"Query": "select data1, data2, data3 from data order by id"
}
}
|
编写本课程所用的机器上只安装了 MySQL:我们没有运行这一配置。[business] 层和 [ui] 层以及 [DbTax] 类都无需修改:只有连接器的配置和注册会改变。请注意,[DbTax] 的查询没有参数,只使用标准 SQL 功能:这使得任何 DBMS 都能执行它。功能更丰富的 [dao] 层(搜索、更新)很快就会触及上述通用连接器的局限:这时就轮到 Entity Framework Core 之类的 ORM 接手了。
10.11. 结论
本章介绍了使用 ADO.NET 以连接模式访问数据库:
- 特定于 DBMS 的 连接器 (MySQL 对应 MySqlConnector)提供类 [XxxConnection]、[XxxCommand]、[XxxDataReader]和 [XxxTransaction],它们派生自 [System.Data.Common] 中的抽象类 [DbConnection]、[DbCommand]、[DbDataReader] 和 [DbTransaction];
- 连接要尽可能晚打开、尽可能早关闭,用 [using] 关闭:连接池使这种做法开销很小;
- [ExecuteReader] 执行 [select] 并返回一个读取器,用 [Read] 逐行遍历;[ExecuteNonQuery] 执行 [insert]、[update] 或 [delete],并返回被修改的行数; [ExecuteScalar] 返回单个值;
- 错误是 [DbException] (对 MySqlConnector 而言是 [MySqlException] );
- 外部值始终通过 参数传递,绝不拼接到查询文本中,这样可以防范 SQL 注入;
- 事务 ([BeginTransaction]、[Commit]、[Rollback])确保一系列命令是原子的 (SQL);
- 异步方法([OpenAsync]、[ExecuteReaderAsync]、[ReadAsync]……)和 [await using] 是服务器应用程序中的标准做法;批处理 ([DbBatch])减少与服务器的交互次数;
- 通用连接器 ([DbProviderFactory]、[DbProviderFactories])使代码独立于 DBMS,但受限于各 DBMS 自身的特性。
[Tax] 应用程序的第 6 版表明,得益于分层架构,从文件切换到数据库只需编写新的 [dao] 层并修改配置。下一章将使用 Entity Framework Core 创建该层的第七个版本,由它自己生成 SQL 命令。
延伸阅读: