Skip to content

10. Acessando bancos de dados com ADO.NET

10.1. O conector ADO.NET

Voltemos à arquitetura em camadas usada repetidamente desde o Capítulo 6. Até agora, a camada [dao] da aplicação [Tax] utilizou três tipos de fontes de dados:

  • dados fixados no código ([HardwiredTax]);
  • dados de um arquivo de texto ([FileTax]);
  • dados de um arquivo JSON ([JsonTax]).

Neste capítulo, examinamos o caso em que os dados vêm de um banco de dados. A arquitetura passa a ser a seguinte:

Image

No diagrama acima, a camada [dao] [1] comunica-se com o SGBD (Sistema de Gerenciamento de Banco de Dados) [3], que gerencia o banco de dados [4], por meio de uma biblioteca de classes específica do SGBD utilizado [2]. Essa biblioteca implementa funcionalidades padronizadas reunidas sob o nome ADO.NET (ADO de “ActiveX Data Objects”, nome da tecnologia de acesso a dados que a precedeu no Windows). Ela é chamada de “provedor de dados” ou “conector”. Todos os SGBDs comuns têm um conector ADO.NET.

As classes de um conector recebem o nome do SGBD: [MySqlConnection] para o MySQL, [SqlConnection] para o SQL Server, [NpgsqlConnection] para o PostgreSQL… Se a camada [dao] usa essas classes, seu código contém o nome do conector, e trocar de SGBD exige modificá-lo. Essa arquitetura é ao mesmo tempo eficiente — porque um conector escrito para um SGBD específico pode aproveitá-lo ao máximo — e rígida, porque mudar de SGBD exige alterar a camada [dao]. Este segundo ponto deve ser relativizado: as empresas não mudam de SGBD com muita frequência. Além disso, veremos que, a partir do .NET 2.0, o ADO.NET oferece um “conector genérico” que proporciona flexibilidade sem sacrificar o desempenho.

O curso de 2008 usava, sucessivamente, os SGBDs SQL Server Compact (descontinuado desde então pela Microsoft), SQL Server 2005 Express, MySQL 5 e depois os drivers ODBC e OLE DB, com bancos de dados criados pelos assistentes do Visual Studio. Aqui usamos um único SGBD, o MySQL 8, que o apêndice instala com o Laragon, e um de seus conectores ADO.NET mais utilizados atualmente, o MySqlConnector. Os bancos de dados são criados por scripts SQL executados com o cliente de linha de comando [mysql]; o apêndice também mostra como criá-los com o cliente gráfico HeidiSQL incluído no Laragon.

10.2. As duas maneiras de usar uma fonte de dados

O ADO.NET permite usar uma fonte de dados de duas maneiras diferentes:

  • modo conectado;
  • modo desconectado.

No modo conectado, a aplicação:

  • abre uma conexão com a fonte de dados;
  • trabalha com a fonte de dados em modo de leitura/escrita;
  • fecha a conexão.

No modo desconectado, a aplicação:

  • abre uma conexão com a fonte de dados;
  • recupera para a memória uma cópia de todos ou de parte dos dados da fonte;
  • fecha a conexão;
  • trabalha com a cópia dos dados em memória, lendo e escrevendo nela;
  • quando o trabalho termina, abre uma conexão, envia os dados modificados à fonte de dados para que ela seja atualizada e fecha a conexão.

O modo desconectado baseava-se nas classes [DataSet] (um pequeno banco de dados em memória, com suas tabelas [DataTable], suas linhas [DataRow] e seus relacionamentos) e [DataAdapter] (a ponte entre um [DataSet] e o banco de dados). Essas classes ainda existem no .NET 10, mas raramente são usadas em novas aplicações: trabalhar com uma cópia dos dados em memória e depois salvar as alterações é hoje tarefa de um ORM (“Object-Relational Mapper”), que manipula objetos C# comuns em vez de linhas de tabelas. O ORM do .NET, Entity Framework Core, é o assunto do Capítulo 11. Como no curso de 2008, vamos nos concentrar aqui apenas no modo conectado, no qual o Entity Framework Core se baseia.

10.3. Conceitos básicos de operações com bancos de dados

10.3.1. O banco de dados de exemplo

Explicaremos os principais conceitos do uso de um banco de dados com um banco de dados MySQL chamado [dbarticles]. Ele contém uma única tabela, [articles], na qual cada linha descreve um item de uma loja:

  • [ID]: um identificador único do item, que é a chave primária da tabela;
  • [NAME]: o nome do item, que também é único;
  • [PRICE]: seu preço;
  • [CURRENTSTOCK]: seu estoque atual;
  • [MINIMUMSTOCK]: o nível mínimo de estoque abaixo do qual o item deve ser reabastecido.

O banco de dados é criado pelo seguinte script SQL [sql/dbarticles.sql]:

-- criação do banco de dados [dbarticles] e de sua tabela [articles]
-- execução: mysql -uroot < dbarticles.sql

-- o script é escrito em UTF-8
SET NAMES utf8mb4;

-- o banco de dados é recriado a cada execução do script
DROP DATABASE IF EXISTS dbarticles;
CREATE DATABASE dbarticles CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- escolha do banco de dados atual
USE dbarticles;

-- criação da tabela articles
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,
    -- restrições de integridade
    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)
);

-- inserção de dados na tabela
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);
  • Linha 5: o script é um arquivo de texto codificado em UTF-8. [SET NAMES utf8mb4] informa isso ao servidor; caso contrário, caracteres acentuados poderiam ser mal interpretados pelo cliente [mysql];
  • Linhas 8–9: o banco de dados é excluído se existir e depois recriado. O script pode assim ser executado várias vezes para restaurar o banco de dados ao seu estado inicial. [utf8mb4] é a codificação Unicode completa do MySQL (a codificação histórica [utf8] do MySQL codifica apenas caracteres de até três bytes);
  • linha 12: [dbarticles] passa a ser o banco de dados atual: os comandos SQL seguintes serão executados nele;
  • linhas 15–26: a definição da tabela [articles]:
    • linha 16: a coluna [ID] é a chave primária da tabela ([PRIMARY KEY]). O atributo [AUTO_INCREMENT], específico do MySQL, indica que seu valor é gerado pelo próprio SGBD quando uma linha é inserida: 1, 2, 3… Outros SGBDs têm conceitos equivalentes, mas com sintaxe diferente ([IDENTITY] no SQL Server, [GENERATED … AS IDENTITY] no PostgreSQL);
    • Linha 18: o preço é um número decimal exato de 10 dígitos com 2 casas após a vírgula decimal. O script de 2008 usava o tipo [DOUBLE PRECISION], um número de ponto flutuante, inadequado para valores monetários (veja a diferença entre [double] e [decimal] no Capítulo 3). O tipo SQL [DECIMAL] corresponde ao tipo C# [decimal];
    • Linhas 22–26: restrições de integridade. [UNQ_NAME] impede que dois itens tenham o mesmo nome; as restrições [CHECK] impedem um nome vazio, um preço negativo ou zero, ou um estoque negativo. O script de 2008 já acrescentava essas restrições, mas o MySQL as ignorava silenciosamente; o MySQL passou a aplicá-las a partir da versão 8.0.16;
  • Linhas 30–36: inserção de seis linhas. A coluna [ID] não é inicializada: o SGBD atribui um valor a ela.

O script é executado com o cliente de linha de comando [mysql], na pasta [sql] (o usuário [root] do MySQL não tem senha na instalação do apêndice; caso contrário, acrescente a opção [-p], e o cliente a solicitará):

> mysql -uroot < dbarticles.sql

O comando não exibe nada se tudo correr bem. Você pode verificar a estrutura e o conteúdo da tabela com o mesmo cliente:

> 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 |
+----+----------------------+--------+--------------+--------------+
  • Linha 1: a opção [-t] solicita uma exibição em forma de tabela; a opção [-e] especifica os comandos SQL a executar, e a opção [--default-character-set=utf8mb4] garante que os caracteres acentuados sejam exibidos corretamente em um terminal UTF-8;
  • linhas 3–9: a estrutura da tabela, obtida com o comando MySQL [describe];
  • linhas 12–19: o conteúdo da tabela. Os valores da coluna [ID] foram gerados pelo SGBD.

Uma observação sobre diferenciação de maiúsculas e minúsculas: os nomes de colunas nunca diferenciam maiúsculas de minúsculas ([name] e [NAME] referem-se à mesma coluna), mas os nomes de tabelas são nomes de arquivos do servidor. Eles não diferenciam maiúsculas de minúsculas no Windows, mas diferenciam no Linux. Para garantir que os exemplos funcionem em qualquer lugar, a tabela se chama [articles], em minúsculas, e as consultas se referem a ela assim.

Agora que temos um banco de dados, poderemos usá-lo. Antes disso, vamos rever alguns conceitos de SQL.

10.3.2. Os quatro comandos básicos da linguagem SQL

SQL (“Structured Query Language”) é uma linguagem parcialmente padronizada para consultar e atualizar bancos de dados. Todos os dialetos dos SGBDs respeitam a parte padronizada do SQL, mas acrescentam à linguagem extensões proprietárias que aproveitam seus recursos específicos. Já vimos dois exemplos disso: a geração automática de chaves primárias e os tipos de dados permitidos para as colunas das tabelas dependem, muitas vezes, do SGBD.

Os quatro comandos básicos da linguagem SQL que apresentamos aqui são padronizados e aceitos por todos os SGBDs:

Comando
Função
[select col1, col2, … from table1, table2, … where condition order by expression]
A consulta usada para recuperar dados de um banco de dados. Somente as palavras-chave [select] e [from] são obrigatórias. É feita uma junção com todas as tabelas que seguem a palavra-chave [from]; somente as colunas que seguem a palavra-chave [select] são mantidas; somente as linhas que satisfazem a condição da palavra-chave [where] são mantidas; as linhas resultantes, ordenadas conforme a expressão da palavra-chave [order by], formam o resultado da consulta. Esse resultado é uma tabela.
[insert into table(col1, col2, …) values (val1, val2, …)]
Insere uma linha em [table]. [(col1, col2, …)] especifica as colunas da linha que serão preenchidas com os valores de [(val1, val2, …)].
[update table set col1=val1, col2=val2, … where condition]
Atualiza as linhas de [table] conforme [condition] (todas as linhas se não houver [where]). Para essas linhas, a coluna [coli] recebe o valor [vali].
[delete from table where condition]
Exclui todas as linhas de [table] que satisfazem [condition] (todas as linhas se não houver [where]).

Escreveremos uma aplicação de console que emite comandos SQL sobre [dbarticles]. Eis um exemplo da saída. Recomenda-se que o leitor compreenda os comandos SQL emitidos e seus resultados:

> 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): 
  • linha 2: a string de conexão: ela contém todos os parâmetros necessários para conectar-se ao banco de dados;
  • linha 4: o conteúdo da tabela [articles] é recuperado. Os preços são exibidos com ponto decimal;
  • linha 17: uma nova linha é inserida. Observe que a coluna [id] não é inicializada nessa operação, pois o SGBD gerará seu valor;
  • Linha 20: verificação. Linha 32: a linha de fato foi adicionada, com [id] 7. Com [select *], os nomes das colunas são os da tabela, em maiúsculas;
  • Linha 34: aumentamos em 10% o preço do item que acabou de ser adicionado;
  • Linha 43: o aumento de preço entrou em vigor;
  • linha 45: o item adicionado anteriormente é excluído;
  • linha 48: verificamos que restam apenas seis itens;
  • linha 56: o programa aceita apenas os quatro comandos [select], [insert], [update], e [delete];
  • linha 60: um erro informado pelo SGBD: a tabela [article] não existe;
  • linha 63: a restrição de unicidade [UNQ_NAME] impede a adição de um segundo item [bike]. As mensagens de erro do MySQL, como as do .NET, estão em inglês.

10.3.3. Classes base do ADO.NET para o modo conectado

Voltemos ao diagrama de uma aplicação que acessa um banco de dados por meio de um conector ADO.NET:

Image

No modo conectado, a aplicação:

  • abre uma conexão com a fonte de dados;
  • trabalha com a fonte de dados em modo de leitura/escrita;
  • fecha a conexão.

Três tipos de objetos participam principalmente dessas operações:

  • a conexão, que encapsula as propriedades e os métodos da conexão com o banco de dados;
  • o comando, que encapsula as propriedades e os métodos do comando SQL executado;
  • o data reader, que encapsula as propriedades e os métodos do resultado de um comando SQL [select].

Cada conector define suas próprias classes ([MySqlConnection], [MySqlCommand], [MySqlDataReader] para o MySqlConnector), derivadas de classes abstratas comuns a todos os conectores, definidas no namespace [System.Data.Common]: [DbConnection], [DbCommand], [DbDataReader]. O curso de 2008 apresentava as interfaces [IDbConnection], [IDbCommand] e [IDataReader] do namespace [System.Data], que essas classes implementam. Hoje, preferem-se as classes abstratas: elas oferecem mais funcionalidades que as interfaces, às quais deixou de ser possível acrescentar métodos sem quebrar os conectores existentes (os métodos de interface padrão ainda não existiam). As versões assíncronas dos métodos ([OpenAsync], [ExecuteReaderAsync], [ReadAsync]…), por exemplo, existem apenas nas classes abstratas.

10.3.3.1. A classe DbConnection

Ela serve para gerenciar a conexão com o banco de dados. Os métodos M e as propriedades P dessa classe que usaremos são os seguintes:

Nome
Tipo
Função
[ConnectionString]
P
String de conexão com o banco de dados. Ela especifica todos os parâmetros necessários para estabelecer uma conexão com um banco de dados específico.
[Open]
M
Abre a conexão com o banco de dados definido por [ConnectionString].
[Close]
M
fecha a conexão. [Dispose] também a fecha.
[BeginTransaction]
M
inicia uma transação.
[CreateCommand]
M
cria um comando associado a esta conexão.
[State]
P
Estado da conexão: [ConnectionState.Closed], [ConnectionState.Open], [ConnectionState.Connecting], [ConnectionState.Executing], [ConnectionState.Fetching], [ConnectionState.Broken].

Com o MySqlConnector, uma conexão pode ser estabelecida da seguinte forma:

1
2
3
DbConnection connection = new MySqlConnection();
connection.ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
connection.Open();

ou, de maneira mais simples, passando a string de conexão ao construtor:

var connection = new MySqlConnection("Server=localhost;Port=3306;Database=dbarticles;User=root;Password=");
connection.Open();

10.3.3.2. A classe DbCommand

Ela serve para executar um comando SQL ou uma stored procedure. Os métodos M e as propriedades P dessa classe que usaremos são os seguintes:

Nome
Tipo
Função
[CommandType]
P
especifica o que deve ser executado. Seus valores vêm da enumeração [CommandType]: [CommandType.Text] executa o comando SQL definido na propriedade [CommandText] (é o valor padrão); [CommandType.StoredProcedure] executa uma stored procedure do banco de dados.
[CommandText]
P
o texto do comando SQL a ser executado se [CommandType] for igual a [CommandType.Text], o nome da stored procedure a ser executada se [CommandType] for igual a [CommandType.StoredProcedure].
[Connection]
P
a conexão [DbConnection] a ser usada para executar o comando SQL.
[Transaction]
P
a transação [DbTransaction] na qual o comando SQL será executado.
[Parameters]
P
a lista de parâmetros de um comando SQL parametrizado. O comando [update articles set price=price*1.1 where id=@id] tem o parâmetro [@id].
[ExecuteReader]
M
Executa um comando SQL [select]. Ele retorna um objeto [DbDataReader] que representa o resultado do [select].
[ExecuteNonQuery]
M
executa um comando SQL [update], [insert] ou [delete]. Ele retorna o número de linhas afetadas pela operação (atualizadas, inseridas, excluídas).
[ExecuteScalar]
M
executa um comando SQL [select] que retorna apenas um único resultado, como [select count(*) from articles].
[CreateParameter]
M
cria um parâmetro [DbParameter] de um comando SQL parametrizado.
[Prepare]
M
permite otimizar a execução de uma consulta parametrizada quando ela é executada várias vezes com parâmetros diferentes.

A execução de um comando SQL sem transação tem a seguinte forma:

// abertura da conexão
var connection = new MySqlConnection(connectionString);
connection.Open();
// preparação do comando
var command = new MySqlCommand();
command.Connection = connection;
// execução de uma instrução select
command.CommandText = "select ...";
MySqlDataReader reader = command.ExecuteReader();
...
// execução de uma instrução update, insert, delete
command.CommandText = "insert ...";
int nbInsertedRows = command.ExecuteNonQuery();
...
// fechamento da conexão
connection.Close();

10.3.3.3. A classe DbDataReader

Ela encapsula os resultados de um comando SQL [select]. Um objeto [DbDataReader] representa uma tabela com linhas e colunas, que são processadas sequencialmente: primeiro a primeira linha, depois a segunda, e assim por diante, sem possibilidade de voltar atrás. Nem todas as linhas são carregadas na memória: o reader as recebe do servidor à medida que são lidas. Os métodos M e as propriedades P dessa classe que usaremos são os seguintes:

Nome
Tipo
Função
[FieldCount]
P
O número de colunas da tabela de resultados.
[GetName]
M
[GetName(i)] retorna o nome da coluna de número i da tabela de resultados (as colunas são numeradas a partir de 0).
[GetOrdinal]
M
[GetOrdinal(name)] retorna o número da coluna chamada [name].
[Item]
P
O indexador [reader[i]] representa a coluna de número i da linha atual, enquanto [reader["name"]] representa a coluna chamada [name], na forma de um [object].
[Read]
M
avança para a próxima linha da tabela de resultados. Retorna [true] se a linha foi lida com sucesso e [false] se não há mais linhas.
[Close]
M
fecha o reader. [Dispose] também o fecha.
[GetInt32]
M
[GetInt32(i)] retorna o valor inteiro da coluna i da linha atual. Outros métodos semelhantes são [GetBoolean], [GetDateTime], [GetDecimal], [GetDouble], [GetFloat], [GetInt16], [GetInt64], [GetString]…
[GetValue]
M
[GetValue(i)] retorna o valor da coluna i da linha atual como tipo [object].
[IsDBNull]
M
[IsDBNull(i)] retorna [true] se a coluna i da linha atual não tem valor, o que é representado pelo valor SQL [NULL]. O valor correspondente em C# é [DBNull.Value].

O processamento de um objeto [DbDataReader] costuma ter a seguinte forma:

// abertura da conexão
var connection = new MySqlConnection(connectionString);
connection.Open();
// preparação do comando
var command = new MySqlCommand("select ...", connection);
// execução da instrução select
MySqlDataReader reader = command.ExecuteReader();
// processamento dos resultados
while (reader.Read())
{
    // processa a linha atual
    ...
}
// fechamento do reader
reader.Close();
// fechamento da conexão
connection.Close();

10.3.4. Tratamento de erros

A camada [dao] pode encontrar numerosos erros ao operar o banco de dados: servidor fora do ar, banco de dados inexistente, senha incorreta, erro de sintaxe SQL, violação de restrição de integridade etc. Esses erros são sinalizados por exceções lançadas pelo conector ADO.NET. O código da camada [dao] deve tratá-los e, qualquer que seja o resultado, liberar os recursos que adquiriu: a conexão e o reader devem ser fechados. O código mostrado acima para processar o resultado de um comando [select] passa a ser o seguinte:

MySqlConnection? connection = null;
try
{
    // abertura da conexão
    connection = new MySqlConnection(connectionString);
    connection.Open();
    // execução da instrução select
    var command = new MySqlCommand("select ...", connection);
    MySqlDataReader reader = command.ExecuteReader();
    try
    {
        // processamento dos resultados
        while (reader.Read())
        {
            ...
        }
    }
    finally
    {
        // fechamento do reader
        reader.Close();
    }
}
catch (DbException ex)
{
    // tratamento da exceção
    ...
}
finally
{
    // fechamento da conexão
    connection?.Close();
}

O fechamento da conexão e do reader pode ser automatizado com a instrução [using]: a conexão, o comando e o reader implementam a interface [IDisposable], e seu método [Dispose] os fecha. A partir do C# 8, a declaração [using] (sem bloco) libera o recurso ao final do bloco que contém a declaração:

try
{
    // a conexão será fechada ao sair do bloco try, seja qual for a forma como saímos dele
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    using var command = new MySqlCommand("select ...", connection);
    using MySqlDataReader reader = command.ExecuteReader();
    // processamento dos resultados
    while (reader.Read())
    {
        ...
    }
}
catch (DbException ex)
{
    // tratamento da exceção
    ...
}
  • Linha 4: a declaração [using] garante que a conexão será fechada ao sair do bloco [try], seja a saída normal ou por uma exceção. Isso economiza uma cláusula [finally], mas o benefício não está nessa pequena economia: a declaração [using] evita que o desenvolvedor precise fechar a conexão manualmente. Ora, esquecer de fechar uma conexão pode passar despercebido e fazer a aplicação “travar” de maneira aparentemente aleatória quando o SGBD atinge o número máximo de conexões abertas que consegue suportar;
  • Linha 7: o mesmo procedimento se aplica ao reader. Os recursos são liberados na ordem inversa de sua declaração: o reader, depois o comando, depois a conexão.

As exceções lançadas pelos conectores derivam todas da classe abstrata [DbException] (namespace [System.Data.Common]). Cada conector tem sua própria classe derivada, [MySqlException] no caso do MySqlConnector, que fornece informações adicionais sobre o erro: seu número MySQL ([Number]), o mesmo número em forma de enumeração ([ErrorCode], do tipo [MySqlErrorCode]) e o código de erro SQL padronizado ([SqlState]). O programa [adonet-02.cs] gera vários erros e os exibe:

#:package MySqlConnector@2.4.0

// tratamento dos erros de acesso ao banco de dados
using System.Data.Common;
using MySqlConnector;

// a string de conexão, no início do programa
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";

// operações que falham, cada uma por um motivo diferente
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)");

// executa uma instrução SQL e exibe o possível erro
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)
    {
        // um erro específico, reconhecido pelo seu código
        Console.WriteLine($"this article already exists ({ex.Message})");
    }
    catch (MySqlException ex)
    {
        // os demais erros do MySQL
        Console.WriteLine($"{ex.GetType().Name} : Number={ex.Number}, ErrorCode={ex.ErrorCode}, SqlState={ex.SqlState}");
        Console.WriteLine($"  {ex.Message}");
    }
    catch (DbException ex)
    {
        // um erro de outro conector ADO.NET (não acontece aqui)
        Console.WriteLine($"{ex.GetType().Name} : {ex.Message}");
    }
}

Resultados:

> 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.
  • Linha 1: a diretiva [#:package] em uma aplicação de arquivo único (Capítulo 2) adiciona ao programa o pacote NuGet [MySqlConnector], versão 2.4.0,. Voltaremos a isso no próximo parágrafo;
  • linha 8: a string de conexão com o banco de dados, escrita no início do programa;
  • linhas 11–20: seis operações que falham: banco de dados inexistente; servidor inacessível (nenhum servidor MySQL escuta na porta 3399, o que produz o mesmo erro de um servidor parado); erro de sintaxe; tabela inexistente; nome de item duplicado; preço negativo;
  • linha 23: o método [Execute] é uma função local (C# 7) do programa principal. A palavra-chave [static] (C# 8) impede que ela use as variáveis locais do programa principal: ela trabalha apenas com seus parâmetros (e constantes);
  • linha 34: um filtro de exceção (C# 6) permite tratar separadamente um erro específico, neste caso uma violação de restrição de unicidade (erro MySQL nº 1062). Nesse caso, uma aplicação exibiria uma mensagem que o usuário possa entender;
  • Linha 39: os outros erros do MySQL. Os resultados mostram seus números e códigos: [UnknownDatabase] (1049), [UnableToConnectToHost] (1042), [ParseError] (1064), [NoSuchTable] (1146). A violação de uma restrição [CHECK] (erro 3819, linha 17) não tem nome na enumeração [MySqlErrorCode] do MySqlConnector 2.4.0: o número é exibido no lugar;
  • linha 45: a cláusula que capturaria a exceção de qualquer conector. Aqui ela não tem utilidade, mas é a que uma camada [dao] escrita independentemente do conector usaria. Observe a ordem das cláusulas [catch]: da mais específica ([MySqlException]) à mais geral ([DbException]).

10.3.5. Configuração do projeto de exemplo

O conector MySqlConnector é um pacote NuGet. Em uma aplicação de arquivo único, ele é adicionado com a seguinte diretiva:

#:package MySqlConnector@2.4.0

Em um projeto, ele é adicionado com o comando [dotnet add package], digitado na pasta do projeto:

> dotnet add package MySqlConnector --version 2.4.0

que grava a referência ao pacote no arquivo [.csproj]:

1
2
3
<ItemGroup>
  <PackageReference Include="MySqlConnector" Version="2.4.0" />
</ItemGroup>

Existem dois conectores ADO.NET para MySQL:

  • [MySql.Data] (“Connector/NET”), publicado pela Oracle, proprietária do MySQL. É o conector usado no curso de 2008, na versão 5.2;
  • [MySqlConnector], um conector de código aberto (licença MIT) desenvolvido independentemente da Oracle. Ele é totalmente assíncrono (seus métodos [XxxAsync] são realmente assíncronos, ao passo que os do [MySql.Data] foram por muito tempo métodos síncronos disfarçados), mais rápido, e funciona com MySQL e MariaDB. É o que é usado pelo [Pomelo.EntityFrameworkCore.MySql], o provedor MySQL do Entity Framework Core que usaremos no Capítulo 11.

As classes dos dois conectores têm os mesmos nomes ([MySqlConnection], [MySqlCommand]…), mas em namespaces diferentes: [MySqlConnector] para o primeiro, [MySql.Data.MySqlClient] para o segundo.

A string de conexão não deve ser fixada no código em uma aplicação real: ela depende da máquina em que a aplicação está instalada. O curso de 2008 a colocava na seção [connectionStrings] do arquivo XML [App.config], lida com a classe [ConfigurationManager]. O arquivo de configuração atual do .NET é o arquivo JSON [appsettings.json], já usado no Capítulo 6, que também tem uma seção dedicada às strings de conexão, [ConnectionStrings]. O primeiro programa deste capítulo usa o seguinte arquivo [appsettings.json], localizado na pasta [10-adonet]:

1
2
3
4
5
{
  "ConnectionStrings": {
    "dbarticles": "Server=localhost;Port=3306;Database=dbarticles;User=root;Password="
  }
}
  • Linha 2: a seção das strings de conexão. Ela pode conter várias strings, cada uma identificada por um nome;
  • linha 3: a string de conexão chamada [dbarticles]. Uma string de conexão tem a forma “parâmetro1=valor1;parâmetro2=valor2;…”. Seus parâmetros dependem do conector; os do MySqlConnector são os seguintes:
    • [Server]: o nome da máquina em que está o SGBD MySQL, neste caso [localhost], isto é, a máquina em que o programa está sendo executado;
    • [Port]: a porta em que o servidor MySQL escuta as conexões; o padrão é 3306;
    • [Database]: o nome do banco de dados, aqui [dbarticles];
    • [User] (ou [Uid]): o usuário MySQL, aqui [root], o administrador do SGBD;
    • [Password] (ou [Pwd]): a senha desse usuário, deixada em branco aqui.

O site connectionstrings.com fornece o formato das strings de conexão dos principais SGBDs, e a documentação do MySqlConnector fornece a lista completa de suas opções: MySqlConnector Connection Options.

Os outros programas deste capítulo, que são mais curtos, guardam sua string de conexão em uma constante no início do programa, como [adonet-02.cs]: isso é mais simples para exemplos, mas não deve ser feito em uma aplicação real.

10.3.6. O programa de exemplo

O programa [adonet-01.cs], do qual já vimos um exemplo de execução, é o seguinte (programa principal):

#:package MySqlConnector@2.4.0
#:package Microsoft.Extensions.Configuration.Json@10.0.12

// aplicação console: executa instruções SQL digitadas no teclado
// em um banco de dados cuja string de conexão é lida de um arquivo de configuração
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.Extensions.Configuration;
using MySqlConnector;

// leitura da string de conexão em [appsettings.json] (pasta atual)
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;
}

// exibição da string de conexão
Console.WriteLine($"Database connection string: [{connectionString}]");

// as instruções SQL aceitas
HashSet<string> sqlCommands = ["select", "insert", "update", "delete"];

// laço de entrada-execução das instruções SQL digitadas no teclado
while (true)
{
    // solicitação da consulta
    Console.Write("\nSQL query (nothing to stop): ");
    string query = Console.ReadLine()?.Trim() ?? "";
    // terminou?
    if (query == "")
    {
        break;
    }
    // a primeira palavra da consulta deve ser uma das quatro instruções aceitas
    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;
    }
    // execução da consulta
    if (firstWord == "select")
    {
        ExecuteSelect(connectionString, query);
    }
    else
    {
        ExecuteUpdate(connectionString, query);
    }
}
  • linhas 1–2: os dois pacotes usados: o conector MySQL e o arquivo de configuração JSON;
  • linha 10: o namespace das classes do conector. Isso representa uma dependência de um namespace específico do conector: o programa precisará ser modificado se o conector do SGBD for trocado;
  • Linhas 16–19: leitura do arquivo [appsettings.json], conforme descrito no Capítulo 6. O arquivo é procurado no diretório atual ([Directory.GetCurrentDirectory]), aquele a partir do qual [dotnet run adonet-01.cs] é iniciado. Uma aplicação de arquivo único é, na verdade, compilada em uma pasta temporária, separada do arquivo-fonte: [AppContext.BaseDirectory] não se referiria à pasta [10-adonet];
  • linha 20: [GetConnectionString(name)] é um atalho para [config["ConnectionStrings:name"]]. Ele retorna [null] se a string não existe, caso tratado nas linhas 27–29;
  • linha 37: os quatro comandos SQL aceitos, em um conjunto [HashSet] (Capítulo 5), inicializado por uma expressão de coleção (C# 12). O curso de 2008 usava um dicionário cujos valores não serviam para nada;
  • linha 40: o laço de entrada dos comandos SQL digitados no teclado e de sua execução no banco de dados;
  • linha 51: a primeira palavra da consulta, obtida com uma expressão regular (Capítulo 5): [\S+] designa uma sequência de caracteres que não são espaços. Ela é convertida em minúsculas para a comparação, mas a consulta em si não é modificada: o programa de 2008 convertia a consulta inteira em minúsculas, o que impedia a inserção de um artigo chamado “Bike”;
  • linhas 52–55: se a consulta for inválida, uma mensagem de erro é exibida e o programa passa para a consulta seguinte;
  • linhas 58–64: o comando SQL digitado é executado. A execução tem uma forma diferente conforme se trate de um comando [select] ou de um comando [insert], [update] ou [delete]: no primeiro caso, o comando recupera dados do banco sem modificá-lo; no segundo, ele atualiza o banco sem recuperar nenhum dado. Em ambos os casos, a execução é delegada a um método que recebe a string de conexão e o comando SQL a executar.

10.3.7. Execução de uma consulta SELECT

A execução de comandos SQL envolve as seguintes etapas:

  • conectar-se ao banco de dados;
  • enviar os comandos SQL ao banco de dados;
  • processar os resultados dos comandos SQL;
  • fechar a conexão.

As etapas 2 e 3 podem ser repetidas; a conexão só é fechada ao final do processamento do banco de dados. As conexões abertas são recursos limitados em um SGBD: elas devem ser poupadas, e procuramos sempre limitar o tempo de vida de uma conexão aberta. Em nosso programa, a conexão é fechada após cada comando SQL, e uma nova conexão é aberta para o comando seguinte.

No entanto, abrir uma conexão é custoso: é preciso estabelecer uma conexão de rede com o servidor, possivelmente negociar a criptografia e se autenticar. Para reduzir esse custo, os conectores gerenciam um pool de conexões: quando a aplicação fecha uma conexão, o conector não a fecha de fato, mas a devolve a um pool; quando a aplicação abre uma nova conexão com a mesma string de conexão, ela recebe uma das conexões do pool, já estabelecida. A vantagem desse sistema é que ele é transparente para o desenvolvedor: o programa não precisa ser modificado para usá-lo. O pool é ativado por padrão no MySqlConnector (como nos outros conectores atuais) e é configurado por parâmetros da string de conexão ([Pooling], [MaximumPoolSize], [MinimumPoolSize]…). A boa prática é, portanto, abrir uma conexão no início de cada operação e fechá-la no final com um [using].

Vamos tratar primeiro da execução de comandos SQL [select]. O método [ExecuteSelect] de nosso programa de exemplo e o método [DisplayReader] que ele usa são os seguintes:

// execução de uma instrução select
static void ExecuteSelect(string connectionString, string query)
{
    try
    {
        using var connection = new MySqlConnection(connectionString);
        // abertura da conexão
        connection.Open();
        // execução da instrução SQL
        using var command = new MySqlCommand(query, connection);
        using MySqlDataReader reader = command.ExecuteReader();
        // exibição dos resultados
        DisplayReader(reader);
    }
    catch (DbException ex)
    {
        Console.WriteLine($"Database access error ({ex.Message})");
    }
}

// exibição das linhas do resultado de um select
static void DisplayReader(DbDataReader reader)
{
    // os nomes das colunas
    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");
    // as linhas, uma após a outra
    while (reader.Read())
    {
        var row = new StringBuilder();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            row.Append(reader[i]).Append(' ');
        }
        Console.WriteLine(row);
    }
}
  • Linha 89: o método recebe dois parâmetros:
    • a string de conexão [connectionString], que permite conectar-se ao banco de dados;
    • o comando SQL [select] [query] a ser executado nessa conexão;
  • Linha 91: qualquer operação em um banco de dados pode lançar uma exceção. Isso é ainda mais provável aqui, porque os comandos SQL fornecidos pelo usuário podem conter erros de sintaxe. Precisamos poder informar isso ao usuário: por isso, todo o código deve estar contido em um [try] / [catch];
  • linha 93: várias coisas aqui:
    • A conexão com o banco de dados é inicializada com a string de conexão [connectionString]. Ela ainda não está aberta; será aberta na linha 95;
    • A declaração [using] garante que a conexão seja fechada (devolvida ao pool) ao sair do bloco [try];
    • A conexão é de um tipo específico do conector: [MySqlConnection];
  • linha 95: a conexão é aberta. É nesse momento que os parâmetros da string de conexão são usados e que ocorrem os erros de conexão;
  • linha 97: o comando SQL é atribuído a um objeto [MySqlCommand], inicializado com duas informações: o comando SQL a emitir e a conexão a usar. O mesmo tipo de objeto serve para executar um comando [select] e comandos [update], [insert] e [delete] ;
  • linha 98: o comando [select] é executado pelo método [ExecuteReader] do comando, que retorna um objeto [MySqlDataReader], derivado de [DbDataReader];
  • linha 100: a exibição dos resultados fica a cargo do método [DisplayReader];
  • linha 102: qualquer exceção é capturada pelo seu tipo base, [DbException];
  • linha 109: o método recebe um objeto [DbDataReader]. Observe que ele usa a classe abstrata em vez da classe [MySqlDataReader] do conector: ele serviria para qualquer conector;
  • Linhas 112–119: os nomes das colunas da tabela de resultados do [select] são exibidos. São as colunas [coli] da consulta [select col1, col2, … from table …]. [string.Join] as concatena, separando-as por vírgulas, e o construtor [new string('-', n)] produz uma string de hifens;
  • linhas 121–128: a tabela de resultados é percorrida, e os valores de cada linha são exibidos;
  • linha 126: o tipo da coluna i do resultado é desconhecido, porque a consulta digitada pelo usuário é desconhecida. Portanto, a sintaxe [reader.GetXxx(i)], em que [Xxx] é o tipo da coluna, não pode ser usada. Usamos então o indexador [reader[i]], que retorna o valor na forma de um [object]; [StringBuilder.Append(object)] o adiciona por meio de seu método [ToString]. Para a coluna [price], de tipo SQL [DECIMAL], o objeto é um [decimal], exibido com duas casas decimais e um ponto decimal.

10.3.8. Execução de um comando de atualização: INSERT, UPDATE, DELETE

O código do método [ExecuteUpdate] é o seguinte:

// execução de uma instrução de atualização (insert, update, delete)
static void ExecuteUpdate(string connectionString, string query)
{
    try
    {
        using var connection = new MySqlConnection(connectionString);
        // abertura da conexão
        connection.Open();
        // execução da instrução SQL
        using var command = new MySqlCommand(query, connection);
        int nbRows = command.ExecuteNonQuery();
        // exibição do resultado
        Console.WriteLine($"{nbRows} row(s) modified");
    }
    catch (DbException ex)
    {
        Console.WriteLine($"Database access error ({ex.Message})");
    }
}

A execução de um comando de atualização difere da de um comando [select] apenas pelo método do comando usado: [ExecuteReader] para [select], [ExecuteNonQuery] para [update], [insert], [delete]:

  • Linha 78: o comando [update], [insert] ou [delete] é executado pelo método [ExecuteNonQuery] do comando. Em caso de sucesso, esse método retorna o número de linhas atualizadas ([update]), inseridas ([insert]) ou excluídas ([delete]);
  • Linha 80: esse número de linhas é exibido.

Recomenda-se ao leitor rever o exemplo de execução apresentado na seção “Os quatro comandos básicos da linguagem SQL”.

10.4. Outros conectores ADO.NET

10.4.1. Conectores atuais

O código que examinamos é específico de um conector: ele usa classes do namespace [MySqlConnector]. O curso de 2008 construía em seguida o mesmo programa com os conectores do SQL Server Compact, do SQL Server 2005, do MySQL 5 e, depois, com os drivers ODBC e OLE DB, para demonstrar que apenas os nomes das classes mudavam. Essa observação continua verdadeira, e não repetiremos o exercício, que exigiria instalar SGBDs adicionais. Eis os principais conectores ADO.NET de hoje, todos distribuídos como pacotes NuGet:

SGBD
Pacote NuGet
Conexão
Observações
MySQL, MariaDB
[MySqlConnector]
[MySqlConnection]
Conector independente, usado neste curso
MySQL
[MySql.Data]
[MySqlConnection]
o conector da Oracle; namespace [MySql.Data.MySqlClient]
SQL Server, Azure SQL
[Microsoft.Data.SqlClient]
[SqlConnection]
Substitui o antigo [System.Data.SqlClient] do .NET Framework, usado em 2008
PostgreSQL
[Npgsql]
[NpgsqlConnection]
 
SQLite
[Microsoft.Data.Sqlite]
[SqliteConnection]
Um banco de dados em um arquivo, sem servidor: o sucessor do SQL Server Compact para pequenas aplicações
Oracle
[Oracle.ManagedDataAccess.Core]
[OracleConnection]
Namespace [Oracle.ManagedDataAccess.Client]

Salvo indicação em contrário, o namespace das classes tem o nome do pacote.

Para passar de um SGBD para outro, trocam-se o pacote, o namespace ([using]), o prefixo das classes ([MySqlCommand] passa a [SqlCommand], [NpgsqlCommand]…) e a string de conexão; o restante do código permanece igual.

ODBC (“Open DataBase Connectivity”) e OLE DB (“Object Linking and Embedding DataBase”) eram, em 2008, uma forma de acessar qualquer SGBD dotado de um driver desse tipo com um único código, ainda que ao custo de um desempenho reduzido. São tecnologias anteriores ao .NET, específicas do Windows no caso do OLE DB. Elas continuam disponíveis (pacotes [System.Data.Odbc] e [System.Data.OleDb] ) para acessar fontes de dados que não têm um conector ADO.NET, mas já não são escolhidas para novas aplicações: todos os SGBDs comuns têm seu próprio conector ADO.NET, e é o conector genérico descrito a seguir que garante a independência em relação ao SGBD.

10.4.2. O conector genérico

A arquitetura utilizada é a seguinte:

Image

O “conector genérico” oferece uma interface padrão às aplicações que o usam sem sacrificar o desempenho, pois se apoia nos conectores específicos de cada SGBD. A aplicação usa apenas as classes abstratas de [System.Data.Common] ([DbConnection], [DbCommand], [DbDataReader], [DbParameter]…). Os objetos concretos são criados por uma fábrica (factory) fornecida pelo conector: uma classe derivada de [DbProviderFactory], cujos métodos [CreateConnection], [CreateCommand], [CreateParameter]… retornam objetos do conector na forma de classes abstratas. Cada conector tem sua própria factory, acessível por meio de sua propriedade estática [Instance]: [MySqlConnectorFactory.Instance] para o MySqlConnector, [SqlClientFactory.Instance] para o SQL Server e [NpgsqlFactory.Instance] para o PostgreSQL.

A classe estática [DbProviderFactories] mantém um registro de factories, cada uma identificada por um nome chamado invariante. Com o .NET Framework, esse registro era preenchido pelas instalações dos conectores no arquivo de configuração da máquina, e o curso de 2008 exibia os seis conectores instalados na máquina do autor. Com o .NET, o registro está vazio na inicialização: é a própria aplicação que adiciona os conectores que suporta, usando o método [DbProviderFactories.RegisterFactory]. O conector pode então ser selecionado por configuração, por meio de seu nome invariante. É o que faz o programa [adonet-03.cs], que recebe como argumento o nome invariante do conector a usar:

#:package MySqlConnector@2.4.0

// o "conector genérico": o código usa apenas as classes abstratas de System.Data.Common
// o conector realmente usado é designado pelo seu nome invariante (1º argumento)
using System.Data;
using System.Data.Common;
using MySqlConnector;

// o nome invariante do conector e a string de conexão
string connectorName = args.Length > 0 ? args[0] : "MySqlConnector";
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";

// registro dos conectores conhecidos pela aplicação, sob seu nome invariante
DbProviderFactories.RegisterFactory("MySqlConnector", MySqlConnectorFactory.Instance);

// a lista dos conectores registrados
Console.WriteLine("Registered connectors:");
DataTable connectors = DbProviderFactories.GetFactoryClasses();
foreach (DataRow row in connectors.Rows)
{
    Console.WriteLine($"  {row["InvariantName"]} : {row["AssemblyQualifiedName"]}");
}

// obtemos a factory do conector solicitado
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}");

// o restante do código conhece apenas as classes abstratas 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";
    // um parâmetro criado pela factory
    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})");
}

Resultados com o conector [MySqlConnector] (valor padrão):

1
2
3
4
5
6
7
8
> 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

Resultados com um conector não registrado:

1
2
3
4
> 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.
  • Linha 10: o nome invariante do conector, passado como argumento; o padrão é [MySqlConnector];
  • linha 14: o registro da factory do MySqlConnector no registro, sob o nome invariante [MySqlConnector]. É a única linha do programa que cita uma classe de conector. Uma aplicação que também suportasse o SQL Server acrescentaria a linha [DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance)] (e a referência ao pacote [Microsoft.Data.SqlClient]);
  • linhas 18–21: [GetFactoryClasses] retorna a lista dos conectores registrados como uma tabela em memória, um objeto [DataTable] em modo desconectado: suas linhas [DataRow] são indexadas pelo nome da coluna. A coluna [AssemblyQualifiedName] contém o nome completo da classe da factory; observe que a versão do assembly MySqlConnector é 2.0.0.0: apenas a versão do pacote (2.4.0) muda de uma atualização secundária para outra;
  • linha 28: a factory do conector é obtida por seu nome invariante. Se o nome não tiver sido registrado, [GetFactory] lança uma [ArgumentException] (linha 4 do segundo conjunto de resultados);
  • Linhas 40–42: a conexão é criada pela factory, configurada com a string de conexão e depois aberta. Os métodos [CreateXxx] retornam um tipo anulável ([DbConnection?]): uma factory pode não saber criar certos objetos. O operador [!] informa ao compilador que sabemos que o resultado não é [null];
  • linhas 43–45: o comando é criado da mesma forma e, em seguida, recebe sua conexão e seu texto. Também poderíamos ter escrito [connection.CreateCommand()];
  • linhas 47–50: a consulta contém um parâmetro, [@price]. As consultas parametrizadas são tratadas na próxima seção; por ora, observe que o parâmetro também é criado pela factory;
  • linha 52: os resultados mostram que os objetos manipulados por meio das classes abstratas são objetos do conector MySqlConnector ([MySqlConnection], [MySqlCommand], [MySqlDataReader]);
  • Linha 55: como os tipos das colunas são conhecidos, são usados os métodos [GetInt32], [GetString] e [GetDecimal] , que retornam diretamente um valor do tipo esperado.

A partir do .NET 7, o ADO.NET também oferece a classe abstrata [DbDataSource], que combina uma factory e uma string de conexão: [factory.CreateDataSource(connectionString)] retorna uma fonte de dados cujo método [OpenConnection] retorna uma conexão aberta. Usaremos isso com métodos assíncronos.

10.4.3. Qual conector escolher?

Vimos duas maneiras de usar um conector ADO.NET:

  • usar diretamente as classes do conector: a camada [dao] depende do conector. Trocar de SGBD exige modificar a camada [dao];
  • usar o conector genérico: a camada [dao] conhece apenas as classes abstratas e o nome invariante do conector, que pode ser lido de um arquivo de configuração.

Parece, portanto, que o conector genérico é a solução ideal. Na prática, porém, ele não consegue ocultar todos os detalhes específicos de um SGBD:

  • o próprio SQL difere de um SGBD para outro assim que se vai além dos comandos básicos: geração de chaves primárias, limitação do número de linhas retornadas ([limit 10] no MySQL, [top 10] ou [offset … fetch] no SQL Server), tipos de colunas, funções de data etc.;
  • a sintaxe dos parâmetros das consultas parametrizadas varia: [@name] no SQL Server, no MySQL e no SQLite, mas [?] (parâmetros posicionais, sem nome) nos drivers ODBC e OLE DB e no conector MySQL de 2008, e [$1], [$2]… no SQL nativo do PostgreSQL (o Npgsql também aceita [@name]). Com parâmetros posicionais, a ordem em que os parâmetros são adicionados à coleção [Parameters] importa; com parâmetros nomeados, a ordem não importa;
  • por fim, as exceções são [DbException], cujos códigos de erro dependem do SGBD.

O curso de 2008 citava frameworks que superam essas limitações: Spring.NET, iBatis.NET, NHibernate e LINQ to SQL. Spring.NET e iBatis.NET não são mais mantidos; o LINQ to SQL não é mais desenvolvido e não existe no .NET atual; o NHibernate ainda existe. Hoje, usam-se principalmente:

  • o Entity Framework Core, o ORM da Microsoft, apresentado no Capítulo 11: a camada [dao] manipula objetos C# e escreve suas consultas em LINQ; é o EF Core que gera o SQL específico de cada SGBD;
  • o Dapper, um “micro-ORM”, que adiciona às conexões ADO.NET métodos de extensão que transformam diretamente as linhas de um [select] em objetos C#. Continuamos escrevendo o SQL, mas não escrevemos mais os laços sobre o reader.

Todas essas ferramentas se apoiam em conectores ADO.NET: entender o ADO.NET continua sendo útil para compreender o que elas fazem e para diagnosticar problemas com elas.

10.5. Consultas parametrizadas

10.5.1. O conceito

Uma consulta parametrizada é um comando SQL em que certos valores são substituídos por parâmetros, cujos valores são fornecidos separadamente do texto da consulta. O programa [adonet-04.cs] a implementa: ele esvazia a tabela [articles], insere cinco registros usando uma consulta parametrizada e depois os relê usando outra consulta parametrizada:

#:package MySqlConnector@2.4.0

// consultas parametrizadas
using MySqlConnector;

const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
Console.WriteLine($"Database connection string: [{ConnectionString}]");

// criação de um array de 5 artigos
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
{
    // excluímos os artigos existentes
    Console.WriteLine($"{ExecuteUpdate(ConnectionString, "delete from articles")} row(s) deleted");
    // inserimos o array de artigos
    InsertArticles(ConnectionString, articles);
    // exibimos os artigos da tabela
    DisplayArticles(GetArticles(ConnectionString, "%"));
    // os artigos cujo nome termina com 3
    Console.WriteLine("Articles whose name ends with 3:");
    DisplayArticles(GetArticles(ConnectionString, "%3"));
}
catch (MySqlException ex)
{
    Console.WriteLine($"Database access error ({ex.Message})");
}

// inserção de um array de artigos com uma consulta parametrizada
static void InsertArticles(string connectionString, Article[] articles)
{
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    // a consulta parametrizada: @name, @price, @cs, @ms são seus parâmetros
    const string Query = "insert into articles(name, price, currentstock, minimumstock) values(@name, @price, @cs, @ms)";
    using var command = new MySqlCommand(Query, connection);
    // declaração dos parâmetros: nome e tipo 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);
    // preparação (compilação) da consulta pelo SGBD
    command.Prepare();
    // inserção das linhas: apenas o valor dos parâmetros muda
    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");
}

// os artigos cujo nome corresponde ao padrão [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);
    // declaração do parâmetro e de seu valor em uma única instrução
    command.Parameters.AddWithValue("@pattern", pattern);
    using MySqlDataReader reader = command.ExecuteReader();
    // cada linha do resultado se torna um objeto 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;
}

// execução de uma instrução de atualização
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();
}

// exibição de uma lista de artigos
static void DisplayArticles(List<Article> articles)
{
    foreach (Article article in articles)
    {
        Console.WriteLine(article);
    }
}

// um artigo: uma linha da tabela [articles]
record Article(int Id, string Name, decimal Price, int CurrentStock, int MinimumStock);

Resultados:

> 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 }
  • Linha 99: um objeto [Article] representa uma linha da tabela [articles]. Trata-se de um [record] (C# 9, Capítulo 4): uma classe cujo construtor primário define as propriedades, com um método [ToString] que exibe essas propriedades (linhas 5–9); o curso de 2008 tratava de uma classe de 25 linhas. Em uma aplicação de arquivo único, os tipos são declarados após as instruções de nível superior;
  • linhas 10–13: um array de cinco itens;
  • linha 19: todos os itens da tabela são excluídos. Desta vez, o método [ExecuteUpdate] retorna o número de linhas excluídas;
  • linha 34: o método de inserção de um array de itens:
    • linha 39: a consulta de inserção de um item. Ela tem quatro parâmetros: [@name], [@price], [@cs] (estoque atual) e [@ms] (estoque mínimo). Com o MySqlConnector, um parâmetro é um nome precedido do caractere [@]; o nome pode ser qualquer um;
    • Linhas 42–45: cada um dos quatro parâmetros é declarado e adicionado à lista de parâmetros do comando. O método [Parameters.Add(string name, MySqlDbType type)] especifica o nome do parâmetro, que deve ser um dos da consulta, e o tipo SQL da coluna correspondente; uma terceira forma, [Add(name, type, size)], especifica ainda a largura da coluna, o que é útil para strings de caracteres ([VarChar]);
    • Linha 47: a consulta parametrizada é “preparada”, ou seja, compilada pelo SGBD. Essa operação não é obrigatória; ela melhora o desempenho. Quando um SGBD executa um comando SQL, ele faz uma análise e uma otimização antes de executá-lo (cria um “plano de execução”). Uma consulta parametrizada se destina a ser executada várias vezes com parâmetros diferentes, mas seu texto permanece inalterado: esse trabalho de preparação só precisa ser feito uma vez. Depois de preparada, a consulta é executada repetidamente com novos valores de parâmetros, mas com o mesmo plano de execução;
    • linhas 49–55: os itens são inseridos um a um. Cada um dos quatro parâmetros recebe um valor por meio de sua propriedade [Value], e depois a consulta, agora completa, é executada da maneira habitual;
  • linha 61: o método que retorna os itens cujos nomes correspondem a um padrão [like] do SQL (o caractere [%] designa qualquer sequência de caracteres: [%3] designa nomes que terminam em 3):
    • linha 68: [AddWithValue(name, value)] declara o parâmetro e lhe atribui um valor em uma única instrução. O tipo SQL do parâmetro é inferido a partir do tipo C# do valor, neste caso [string]. É a forma mais comum quando a consulta é executada apenas uma vez;
    • Linhas 72–75: cada linha do resultado se torna um objeto [Article]. Os tipos das colunas são conhecidos: usam-se os métodos [GetXxx]. Esses métodos aceitam o número da coluna ou, com o MySqlConnector, o nome da coluna. Com a classe abstrata [DbDataReader], o nome é aceito por métodos de extensão de mesmo nome (classe [DataReaderExtensions], namespace [System.Data]), que equivalem a [reader.GetInt32(reader.GetOrdinal("currentstock"))].

Os tipos SQL usados com o MySqlConnector são os valores da enumeração [MySqlDbType]. Eis os mais comuns e os tipos C# correspondentes:

Tipo MySqlDbType
Tipo C#
Tipo de coluna MySQL
[Int32]
[int]
[INT]
[Int64]
[long]
[BIGINT]
[Decimal]
[decimal]
[DECIMAL(p,d)]
[Double]
[double]
[DOUBLE]
[VarChar]
[string]
[VARCHAR(n)], string de comprimento variável
[DateTime]
[DateTime]
[DATETIME]
[Date]
[DateOnly] ou [DateTime]
[DATE]
[Bool]
[bool]
[BOOL] (sinônimo de [TINYINT(1)])

As classes abstratas, por sua vez, usam a enumeração [DbType] ([DbType.Int32], [DbType.String], [DbType.Decimal]…), comum a todos os conectores, por meio da propriedade [DbParameter.DbType].

10.5.2. A injeção de SQL

A preparação não é a única vantagem das consultas parametrizadas, nem a mais importante. Voltemos à consulta de inserção. Poderíamos querer construir seu texto programaticamente, inserindo os valores:

string query = "insert into articles(name, price, currentstock, minimumstock) values('" + name + "', " + price + ", " + sa + ", " + sm + ")";

Se (name, price, sa, sm) for igual a ("article1", 100, 10, 1), a consulta se torna:

insert into articles(name, price, currentstock, minimumstock) values('article1', 100, 10, 1)

Mas se o nome for “l'article1”, ela se torna:

insert into articles(name, price, currentstock, minimumstock) values('l'article1', 100, 10, 1)

e é sintaticamente incorreta por causa do apóstrofo no nome. Pior: se o nome vem de uma entrada do usuário, o usuário pode digitar um texto que muda o sentido da consulta. Isso é conhecido como injeção de SQL, uma das vulnerabilidades de segurança mais difundidas e graves em aplicações que usam bancos de dados. O programa [adonet-05.cs] demonstra isso ao buscar um artigo pelo nome:

#:package MySqlConnector@2.4.0

// injeção de SQL: por que se deve usar consultas parametrizadas
using MySqlConnector;

const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";

// nomes de artigos, como um usuário poderia digitá-los
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);
}

// busca de um artigo pelo nome: o nome é inserido no texto da consulta
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}");
    }
}

// busca de um artigo pelo nome: o nome é o valor de um parâmetro
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}");
    }
}

// exibição do resultado de um 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)");
}

Resultados:

> 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)
  • Linha 9: três nomes que um usuário poderia digitar: um nome comum, um nome contendo um apóstrofo e um texto malicioso;
  • linha 25: o primeiro método de busca insere o nome no texto da consulta:
    • linha 3: com um nome comum, tudo funciona bem;
    • linha 6: o apóstrofo do nome encerra prematuramente a string SQL; a consulta é inválida (linha 7). Um artigo cujo nome contém um apóstrofo não pode ser encontrado;
    • linha 8: o usuário digitou um texto com apóstrofos posicionados de modo a produzir uma consulta válida, mas para a qual a condição [name = 'x' or '1'='1'] é verdadeira para todas as linhas: a consulta retorna a tabela inteira (linha 15). Se a consulta servisse para verificar um nome de usuário e uma senha, o mesmo procedimento permitiria a um usuário entrar sem conhecer a senha. Com um conector que aceita vários comandos SQL em uma única instrução (como é o caso do MySqlConnector), o usuário poderia até acrescentar, depois de um ponto e vírgula, um comando de sua escolha, por exemplo um [delete];
  • Linha 49: o segundo método usa uma consulta parametrizada. O valor do parâmetro nunca é interpretado como SQL: é simplesmente um dado, qualquer que seja seu conteúdo. O nome com apóstrofo é encontrado (linha 22), e o texto malicioso é simplesmente o nome de um item que não existe (linha 25).

Poderíamos “neutralizar” os apóstrofos duplicando-os, mas as regras dependem do SGBD, e é fácil esquecer algum caso. A regra é simples: valores fornecidos pelo usuário (ou por qualquer fonte externa) nunca devem ser concatenados ao texto de uma requisição SQL; deve-se sempre usar parâmetros. O programa interativo [adonet-01.cs] não segue essa regra por sua própria natureza: ele executa os comandos SQL digitados pelo usuário. É uma ferramenta de administrador, não um exemplo a ser seguido.

10.6. Transações

10.6.1. Generalidades

Uma transação é uma sequência de comandos SQL executados de forma "atômica":

  • ou todas as operações têm sucesso;
  • ou uma delas falha, caso em que todas as operações anteriores são desfeitas.

No fim das contas, ou todas as operações de uma transação são aplicadas com sucesso, ou nenhuma é. O exemplo clássico é uma transferência bancária: o débito de uma conta e o crédito em outra devem ser ambos concluídos ou nenhum dos dois. Quando a própria aplicação controla a transação, ela a confirma com um comando [COMMIT] ou a desfaz com um comando [ROLLBACK].

Em nossos exemplos anteriores, não usamos transação. E, no entanto, havia transações, porque em um SGBD um comando SQL é sempre executado dentro de uma transação. Se a aplicação não inicia ela mesma uma transação explícita, o SGBD usa uma transação implícita. Há então dois casos:

  • cada comando SQL faz parte de uma transação, aberta pelo SGBD antes do comando e confirmada depois dele. Fala-se então em modo autocommit. Tudo se passa como se a aplicação criasse uma transação para cada comando SQL;
  • o SGBD não está em modo autocommit: ele inicia uma transação implícita com o primeiro comando SQL emitido fora de uma transação e deixa que a aplicação a conclua. Todos os comandos SQL seguintes fazem parte dessa transação implícita, que pode ser encerrada por diversos eventos (fechamento da conexão, início de uma nova transação etc.), segundo regras próprias do SGBD. Esse modo deve ser evitado.

O modo padrão é determinado pela configuração do SGBD. O MySQL, com o mecanismo de armazenamento InnoDB usado por padrão, opera em modo autocommit.

Os comandos de usuários diferentes são executados simultaneamente em transações que correm em paralelo. As operações realizadas por uma transação podem afetar as realizadas por outra. Existem quatro níveis de isolamento entre transações de usuários diferentes:

  • Read Uncommitted;
  • Read Committed;
  • Repeatable Read;
  • Serializable.

Read Uncommitted

Esse nível de isolamento também é conhecido como “Dirty Read” (leitura suja). Eis um exemplo do que pode acontecer nesse modo:

  • um usuário chamado U1 inicia uma transação na tabela T;
  • um usuário chamado U2 inicia uma transação na mesma tabela T;
  • o usuário U1 modifica linhas da tabela T, mas ainda não confirmou as alterações;
  • o usuário U2 “vê” essas alterações e toma decisões com base no que vê;
  • o usuário U1 desfaz sua transação com [ROLLBACK].

Na etapa 4, o usuário U2 tomou uma decisão com base em dados que depois se revelaram incorretos.

Read Committed

Esse nível de isolamento evita a armadilha anterior: na etapa 4, o usuário U2 não “vê” as alterações feitas pelo usuário U1 na tabela T. Só as verá depois que U1 confirmar sua transação com um [COMMIT]. Nesse modo, porém, pode ocorrer a seguinte situação:

  • um usuário U1 inicia uma transação na tabela T;
  • um usuário U2 inicia uma transação na mesma tabela T;
  • o usuário U2 executa um [select] para calcular a média da coluna C das linhas de T que satisfazem certa condição;
  • o usuário U1 modifica ([update]) certos valores da coluna C de T e confirma as alterações ([commit]);
  • o usuário U2 repete o mesmo [select] da etapa 3. Ele descobre que a média da coluna C mudou devido às modificações feitas por U1.

O usuário U2 passa a ver apenas as alterações confirmadas por U1, mas, ainda dentro da mesma transação, duas operações idênticas produzem resultados diferentes. Essa situação é chamada de “leitura não repetível”. Ela é problemática para quem quer uma visão consistente da tabela T.

Repeatable Read

Nesse nível de isolamento, o usuário tem a garantia de obter os mesmos resultados nas leituras do banco de dados enquanto permanecer na mesma transação. Ele trabalha sobre um “instantâneo” (snapshot) dos dados que não reflete as alterações feitas por outras transações, mesmo que elas tenham sido confirmadas. Ele só verá essas alterações depois de concluir sua própria transação.

Esse nível de isolamento ainda não é perfeito em teoria: outra transação pode adicionar ([insert]) linhas que satisfazem a condição da etapa 3, o que alteraria o resultado de uma leitura posterior. Essas linhas são chamadas de “leituras fantasmas”. É o nível de isolamento padrão do MySQL (InnoDB), que também impede leituras fantasmas nas leituras simples.

Serializable

Nesse modo de isolamento, as transações ficam completamente isoladas umas das outras: o resultado de duas transações executadas simultaneamente é o mesmo que se elas tivessem sido executadas uma após a outra. Para isso, o SGBD bloqueia os dados lidos e faz as transações que tentam modificá-los esperarem (ou falharem). Esse nível reduz o paralelismo e, por consequência, o desempenho.

Os quatro níveis de isolamento não estão disponíveis em todos os SGBDs. O nível de isolamento padrão costuma ser Read Committed (SQL Server, PostgreSQL, Oracle) ou Repeatable Read (MySQL). O nível de isolamento desejado para uma transação pode ser especificado explicitamente quando a aplicação cria a transação.

10.6.2. A API de gerenciamento de transações

A classe [DbConnection] tem o seguinte método:

Nome
Tipo
Função
[BeginTransaction]
M
inicia uma transação.

Esse método tem duas assinaturas:

  • [DbTransaction BeginTransaction()]: inicia uma transação e retorna o objeto [DbTransaction], que serve para controlá-la;
  • [DbTransaction BeginTransaction(IsolationLevel level)]: especifica ainda o nível de isolamento desejado para a transação. [level] assume seus valores da enumeração [IsolationLevel] (namespace [System.Data]):
Valor
Significado
[ReadUncommitted]
A transação pode ler dados gravados por outra transação que ainda não os confirmou. Deve ser evitado.
[ReadCommitted]
A transação não pode ler dados gravados por outra transação que ainda não os confirmou. No entanto, dados lidos duas vezes seguidas dentro da transação podem mudar, porque outra transação pode tê-los modificado nesse meio-tempo; outra transação também pode ter adicionado linhas (linhas fantasmas).
[RepeatableRead]
Os dados lidos pela transação não mudam até que ela termine. Em teoria, isso não impede que outra transação adicione linhas.
[Serializable]
Tudo se passa como se a transação estivesse sendo executada sozinha. Isso reduz o desempenho, porque as transações deixam de ser executadas em paralelo.
[Snapshot]
A transação trabalha sobre uma cópia dos dados criada no início da transação. Disponível no SQL Server; no MySQL, é isso que o nível [RepeatableRead] já faz nas leituras.

Depois que a transação é iniciada, ela é controlada pelo objeto [DbTransaction] (aqui [MySqlTransaction]), do qual usaremos as seguintes propriedades P e métodos M:

Nome
Tipo
Função
[Connection]
P
a conexão [DbConnection] que dá suporte à transação.
[Commit]
M
confirma a transação: os resultados dos comandos SQL emitidos na transação tornam-se definitivos e visíveis para as outras transações.
[Rollback]
M
cancela a transação: os resultados dos comandos SQL emitidos na transação são desfeitos.
[Dispose]
M
libera a transação. Uma transação liberada sem ter sido confirmada é desfeita.

Os comandos SQL da transação devem ser executados na conexão da transação, usando comandos cuja propriedade [Transaction] se refere à transação.

10.6.3. O programa de exemplo

O programa [adonet-06.cs] demonstra o efeito de uma transação:

#:package MySqlConnector@2.4.0

// transações
using System.Data;
using MySqlConnector;

const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
Console.WriteLine($"Database connection string: [{ConnectionString}]");

// dois artigos com o mesmo nome: a restrição de unicidade UNQ_NAME recusará o segundo
Article[] articles = [new(0, "article", 100, 10, 1), new(0, "article", 200, 20, 2)];

try
{
    // 1 - inserção sem transação explícita
    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 - a mesma coisa dentro de uma transação
    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})");
}

// inserção de um array de artigos sem transação explícita
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);
    }
}

// inserção de um array de artigos dentro de uma transação
static void InsertArticlesInTransaction(string connectionString, Article[] articles)
{
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    // início da transação
    using MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
    try
    {
        using MySqlCommand command = CreateInsertCommand(connection);
        // o comando deve ser executado dentro da transação
        command.Transaction = transaction;
        foreach (Article article in articles)
        {
            InsertArticle(command, article);
        }
        // tudo correu bem: confirmamos a transação (commit)
        transaction.Commit();
        Console.WriteLine("transaction committed...");
    }
    catch (MySqlException ex)
    {
        // uma inserção falhou: desfazemos a transação (rollback)
        transaction.Rollback();
        Console.WriteLine($"transaction rolled back ({ex.Message})");
    }
}

// o comando parametrizado que insere um artigo
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;
}

// inserção de um artigo com o comando parametrizado
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");
}

// execução de uma instrução de atualização
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");
}

// exibição do conteúdo da tabela
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)}");
    }
}

// um artigo: uma linha da tabela [articles]
record Article(int Id, string Name, decimal Price, int CurrentStock, int MinimumStock);

Resultados:

> 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]:
  • Linha 11: um array de dois itens com o mesmo nome, [article]. A restrição de unicidade [UNQ_NAME] da tabela [articles] impede que os dois existam no banco de dados ao mesmo tempo: a inserção do segundo provocará uma exceção lançada pelo SGBD e repassada pelo conector. Para demonstrar o efeito de uma transação, os dois itens serão inseridos de duas maneiras:
    • primeiro, fora de qualquer transação explícita. Como o MySQL está em modo autocommit, cada inserção é feita em uma transação implícita. O primeiro registro será inserido, mas o segundo não;
    • depois, dentro de uma transação explícita que engloba as duas inserções. Como a segunda inserção falhará, a primeira será desfeita: no final, nenhuma inserção será feita;
  • linhas 15–26: a tabela é esvaziada e, em seguida, os dois itens são inseridos sem transação explícita. Como sabemos que a segunda inserção lançará uma exceção, ela é capturada por um [try] / [catch]; por fim, a tabela é exibida;
  • linhas 28–32: a mesma sequência é repetida, mas com uma transação explícita. Desta vez, a exceção é tratada no método [InsertArticlesInTransaction];
  • linha 40: o método de inserção sem transação. Ele usa dois métodos: [CreateInsertCommand] (linha 80), que cria o comando de inserção parametrizado, e [InsertArticle] (linha 92), que atribui valores aos parâmetros e executa o comando;
  • linha 52: o método de inserção dentro de uma transação:
    • linha 57: a transação é criada com o nível de isolamento [ReadCommitted], como especificado no curso de 2008. Ela pertence à conexão. A instrução [using] garante que ela seja desfeita, e portanto cancelada, caso não tenha sido confirmada;
    • linha 62: o comando é colocado dentro da transação. Essa linha é obrigatória: com o MySqlConnector, um comando executado em uma conexão que tem uma transação em andamento, sem especificar essa transação, lança uma [InvalidOperationException];
    • Linhas 63–65: as inserções;
    • linha 68: tudo correu bem: a transação é confirmada, e as inserções são adicionadas definitivamente ao banco de dados;
    • Linha 74: uma inserção falhou: a transação é desfeita. O [Rollback] explícito não é estritamente necessário, já que a liberação da transação não confirmada a desfaria de qualquer forma, mas torna o código mais claro.

Nos resultados:

  • Linha 5: exibida pelo comando [delete]: havia seis linhas na tabela;
  • linha 7: a exceção causada pela segunda inserção. A mensagem indica que a restrição [UNQ_NAME] da tabela [articles] não foi satisfeita;
  • linha 9: a tabela não está vazia: o primeiro registro foi inserido;
  • linha 12: exibida pelo comando [delete]: havia uma linha na tabela;
  • Linha 13: a primeira inserção foi bem-sucedida… dentro da transação;
  • linha 14: a mensagem exibida quando a transação falha;
  • linha 15: a tabela está vazia: o [Rollback] da transação desfez a primeira inserção.

10.7. O método ExecuteScalar

Entre os métodos da classe [DbCommand], havia o seguinte:

Nome
Tipo
Função
[ExecuteScalar]
M
executa um comando SQL [select] que retorna apenas um único resultado, como [select count(*) from articles].

Mais precisamente, [ExecuteScalar] retorna a primeira coluna da primeira linha do resultado, na forma de um [object?]. O programa [adonet-07.cs] demonstra alguns usos disso:

#:package MySqlConnector@2.4.0

// o método ExecuteScalar
using MySqlConnector;

const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";

try
{
    // os valores únicos retornados por selects
    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})");

    // conversão do resultado para o tipo esperado
    decimal average = Convert.ToDecimal(averagePrice);
    int count = Convert.ToInt32(nbArticles);
    Console.WriteLine($"Rounded average = {Math.Round(average, 2)}, count = {count}");
    // um cast direto falha: count retorna um Int64 (long) e não um 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}");
}

// execução de um select que retorna um único valor
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();
}

Resultados:

1
2
3
4
5
6
7
8
> 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'.
  • Linha 36: o método [ExecuteScalar] do programa abre uma conexão e executa a consulta usando o método [ExecuteScalar] do comando;
  • linha 11: o preço médio dos itens. O tipo C# do resultado depende do tipo SQL do valor: a média de uma coluna [DECIMAL] é um [decimal] (linha 2);
  • linha 13: o número de itens. O MySQL retorna um inteiro do tipo [BIGINT], que se torna um [long] ([Int64]) em C# (linha 3);
  • linha 15: o nome do item mais caro, uma string. A consulta contém uma subconsulta, entre parênteses;
  • linha 17: se a consulta não retorna nenhuma linha, [ExecuteScalar] retorna [null] (linha 5);
  • linha 19: se a consulta retorna uma linha com o valor SQL [NULL] (o máximo de um conjunto vazio), [ExecuteScalar] retorna o objeto [DBNull.Value], que é exibido como uma string vazia (linha 6). É preciso, portanto, distinguir dois casos: nenhuma linha ([null]) e um valor [NULL] ([DBNull]);
  • linhas 23–24: para obter o resultado no tipo esperado, usamos os métodos da classe [Convert], que pode converter um [long] em um [int];
  • linha 27: uma conversão de tipo (cast) direta falha porque o objeto é um [long] “boxed”: ele só pode sofrer unboxing para seu tipo exato. O programa de 2008 escrevia [(int)ExecuteScalar(…, "select count(id) from articles")]: isso funcionava com o SQL Server Compact, cujo [count] produz um [int], e falharia com o MySQL (linha 8). É um exemplo das peculiaridades dos SGBDs que o conector genérico não oculta.

10.8. Métodos assíncronos

O Capítulo 9 mostrou que as operações de entrada/saída devem ser assíncronas em aplicações que atendem a muitos usuários (servidores, serviços web) ou que têm uma interface gráfica: enquanto um comando SQL é executado no servidor, a thread da aplicação não deve ficar bloqueada. Todos os métodos do ADO.NET que se comunicam com o SGBD têm uma versão assíncrona, que retorna uma tarefa ([Task], [Task<T>], ou [ValueTask]) e aceita um [CancellationToken] opcional:

Classe
Método síncrono
Método assíncrono
[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]

Os objetos do ADO.NET também implementam a interface [IAsyncDisposable]: eles são liberados com [await using] (C# 8), que chama seu método [DisposeAsync]. O programa [adonet-08.cs] usa esses métodos:

#:package MySqlConnector@2.4.0

// as versões assíncronas dos métodos do 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=";

// a fonte de dados: ela produz conexões (já abertas) com um mesmo banco de dados
await using var dataSource = new MySqlDataSource(ConnectionString);

try
{
    // 1 - uma instrução update, dentro de uma transação
    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 - um select cujas linhas são retornadas à medida que são lidas
    await foreach (Article article in GetArticlesAsync(dataSource))
    {
        Console.WriteLine(article);
    }

    // 3 - um valor único
    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 - uma consulta bloqueada por outra transação, cancelada após um segundo
    // a transação [blocking] modifica o artigo nº 1 e não termina: a linha permanece bloqueada
    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();
    }
    // outra conexão quer modificar a mesma linha: ela deve esperar o fim da transação [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}");
    }
    // a transação [blocking] não foi confirmada: ela é desfeita quando [blocking] é liberada
}
catch (MySqlException ex)
{
    Console.WriteLine($"Database access error ({ex.Message})");
}

// os artigos da tabela, retornados um a um (fluxo assíncrono)
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));
    }
}

// um artigo: uma linha da tabela [articles]
record Article(int Id, string Name, decimal Price, int CurrentStock, int MinimumStock);

Resultados:

> 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
  • Linha 12: uma fonte de dados [MySqlDataSource], uma classe derivada de [DbDataSource] (.NET 7). Ela é criada uma única vez com a string de conexão e depois fornece conexões e comandos. Em uma aplicação, é ela que é registrada no contêiner de injeção de dependência, em vez da string de conexão. Como gerencia o pool de conexões, ela é liberada no final do programa, aqui por meio de [await using];
  • linhas 16–24: um comando de atualização dentro de uma transação:
    • linha 17: [OpenConnectionAsync] retorna uma conexão já aberta. Abrir uma conexão é uma operação de rede: ela é assíncrona. O bloco [await using (…) { … }] libera a conexão ao sair;
    • linhas 19–23: a transação é criada, o comando [update] é executado por [ExecuteNonQueryAsync], e a transação é confirmada por [CommitAsync]. Observe o construtor de três parâmetros de [MySqlCommand]: consulta, conexão, transação;
  • Linhas 68–76: um método que retorna os registros como um fluxo assíncrono, [IAsyncEnumerable<Article>] (C# 8, Capítulo 9):
    • [ExecuteReaderAsync] executa a consulta e, em seguida, [await reader.ReadAsync(token)] lê as linhas uma a uma. [Read] pode de fato esperar pela rede: o reader recebe as linhas do servidor em lotes;
    • [yield return] devolve cada registro ao código chamador assim que é lido, sem esperar a leitura da tabela inteira: um [select] que retorna um milhão de linhas não usa mais memória do que um [select] que retorna dez;
    • A conexão, o comando e o reader são liberados quando o fluxo termina (ou é abandonado pelo código chamador);
    • o atributo [[EnumeratorCancellation]] permite que o código chamador passe um token de cancelamento por meio de [GetArticlesAsync(dataSource).WithCancellation(token)];
  • Linha 28: o código chamador percorre o fluxo usando [await foreach]. Os resultados mostram que os três preços abaixo de 200 euros aumentaram 10%;
  • linhas 33–37: [dataSource.CreateCommand] cria um comando que abrirá sua própria conexão (e a fechará); ele retorna um [DbCommand]. [ExecuteScalarAsync] retorna o valor único;
  • linhas 40–58: cancelamento de uma requisição:
    • linhas 42–46: uma primeira conexão inicia uma transação que modifica o item nº 1, mas não a confirma. O SGBD bloqueou a linha modificada até o fim da transação;
    • linha 49: uma fonte de cancelamento que cancelará seu token após um segundo (Capítulo 9);
    • linha 54: uma segunda conexão tenta modificar a mesma linha. Ela precisa esperar o fim da primeira transação… o que nunca acontecerá. Após um segundo, o token é cancelado: o MySqlConnector então instrui o servidor a abortar a requisição (comando MySQL [KILL QUERY]), e [ExecuteNonQueryAsync] lança uma [OperationCanceledException] (linha 10). Sem o cancelamento, a requisição teria esperado o tempo limite de bloqueio do MySQL, 50 segundos por padrão, e depois falhado;
    • a transação [blocking] nunca é confirmada: ela é desfeita quando [blocking] é liberada, ao final do bloco [try].

As versões assíncronas não trazem nenhum benefício a um pequeno programa de console que executa uma tarefa por vez: elas não tornam uma requisição mais rápida. No entanto, são o padrão em aplicações de servidor, como os serviços web ASP.NET Core (Capítulo 13), que atendem a muitos usuários simultaneamente.

10.9. Execução em lote

Cada execução de comando implica uma ida e volta entre a aplicação e o servidor. Quando vários comandos SQL precisam ser executados, eles podem ser enviados ao SGBD em uma única troca, na forma de um lote (batch). O .NET 6 acrescentou ao ADO.NET as classes abstratas [DbBatch] e [DbBatchCommand] : um lote é uma lista de comandos, cada um com seu próprio texto e seus próprios parâmetros, executada por uma única chamada. O MySqlConnector as implementa por meio das classes [MySqlBatch] e [MySqlBatchCommand]. O programa [adonet-09.cs] as utiliza:

#:package MySqlConnector@2.4.0

// a API de lote (DbBatch, .NET 6): várias instruções SQL enviadas ao SGBD em uma única troca
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 - um lote de três instruções update, cada uma com seus parâmetros
    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 - um lote de dois selects: um reader, dois resultados sucessivos
    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)}");
    }
    // passar para o próximo resultado
    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})");
}

Resultados:

1
2
3
4
5
6
7
8
> 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
  • Linha 12: a propriedade [CanCreateBatch] de [DbConnection] indica se o conector consegue executar lotes. Com um conector que não consegue, o método genérico [connection.CreateBatch()] lança uma [NotSupportedException];
  • Linhas 15–27: um lote de três instruções update. O lote é construído com inicializadores de objeto e de coleção: a coleção [BatchCommands] recebe três objetos [MySqlBatchCommand], dos quais os dois primeiros têm sua própria coleção de parâmetros [Parameters]. Cada comando do lote tem seus próprios parâmetros, mesmo que tenham o mesmo nome que um parâmetro de outro comando;
  • Linha 30: os três comandos são enviados e executados em uma única troca. [ExecuteNonQueryAsync] retorna o número total de linhas modificadas: uma inserção ([canoe]), uma atualização (o estoque de flechas, que está abaixo do estoque mínimo) e uma exclusão ([pump]);
  • linhas 34–39: um lote de dois comandos [select];
  • linha 42: o lote é executado por [ExecuteReaderAsync], que retorna um único reader para os dois resultados;
  • linha 49: depois de ler as linhas do primeiro resultado, [NextResultAsync] move o reader para o resultado seguinte.

O mesmo efeito poderia ser obtido escrevendo vários comandos SQL separados por pontos e vírgulas dentro de um único comando, o que o MySqlConnector aceita. No entanto, os parâmetros de todos os comandos ficariam então misturados, e essa sintaxe não é aceita por todos os SGBDs; a API de lote é a forma padrão especificada pelo ADO.NET.

10.10. Aplicação de exemplo - Versão 6

Retomemos a aplicação de exemplo [Tax]. A última versão, a versão 5, foi tratada no Capítulo 6. Era uma aplicação de três camadas cuja camada [dao] obtinha seus dados de um arquivo de texto, de um arquivo JSON ou de seu próprio código, e cujas camadas eram integradas por um contêiner de injeção de dependência. Modificamos a camada [dao] para que ela obtenha seus dados de um banco de dados MySQL:

Image

O curso de 2008 (Versão 7) reutilizava as camadas [ui] e [business] da versão anterior recompilando seu código-fonte em uma nova solução, e integrava as camadas com o Spring.NET. Reutilizamos os projetos da Versão 5 como estão, por meio de referências de projeto, e escrevemos apenas as partes novas: a camada [dao] e o programa que integra as camadas.

10.10.1. O banco de dados

As faixas de imposto são armazenadas na tabela [brackets] de um banco de dados MySQL chamado [dbtaxes], criado pelo script [sql/dbtaxes.sql]:

-- criação do banco de dados [dbtaxes] da aplicação [Tax] (versão 6)
-- execução: mysql -uroot < dbtaxes.sql

-- o script é escrito em UTF-8
SET NAMES utf8mb4;

-- o banco de dados é recriado a cada execução do script
DROP DATABASE IF EXISTS dbtaxes;
CREATE DATABASE dbtaxes CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE dbtaxes;

-- a tabela das faixas de imposto (tabela de 2004 sobre a renda de 2003)
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);

-- um usuário específico da aplicação, que só tem direitos sobre o banco de dados [dbtaxes]
CREATE USER IF NOT EXISTS 'admtaxes'@'localhost' IDENTIFIED BY 'pwdtaxes';
GRANT ALL PRIVILEGES ON dbtaxes.* TO 'admtaxes'@'localhost';
  • Linhas 13–17: a tabela [brackets] tem quatro colunas: a chave primária [id], gerada pelo SGBD, seguida das três colunas [limits], [coeffr] e [coeffn], que representam uma faixa de imposto do tipo [DECIMAL]: são valores e coeficientes que precisam ser exatos. O script de 2008 usava o tipo [DOUBLE];
  • linhas 19–26: as sete faixas da tabela de imposto de 2004 para a renda de 2003, usadas desde o início do curso (trata-se de um exercício: a tabela de imposto não está atualizada). As faixas são inseridas em ordem crescente de seus limites, e a coluna [id] reflete essa ordem;
  • linhas 29–30: criação de um usuário MySQL [admtaxes], com a senha [pwdtaxes], a quem são concedidas todas as permissões ([GRANT ALL PRIVILEGES]) sobre todos os objetos do banco de dados [dbtaxes] ([dbtaxes.*]), e somente sobre esses objetos. A aplicação será executada com esse usuário, e não com o administrador [root]: se a senha do administrador fosse comprometida, apenas o banco de dados [dbtaxes] estaria em risco. [IF NOT EXISTS] permite executar o script várias vezes.

O script é executado pelo administrador:

> mysql -uroot < dbtaxes.sql

em seguida, verificamos que o usuário [admtaxes] tem acesso às faixas (a opção [-p] sozinha solicita a senha, que não é exibida enquanto é digitada):

> 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. A solução TaxV6

A versão 6 é uma solução composta por três projetos, localizados na pasta [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

Ela foi construída com os seguintes comandos, digitados na pasta [TaxV6]:

1
2
3
4
5
6
7
8
9
> 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

O arquivo da solução contém apenas os três novos projetos:

1
2
3
4
5
<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>

O comando [dotnet build TaxV6.slnx] também compila os projetos da Versão 5 referenciados por esses projetos.

Aqui vemos o valor de uma arquitetura em camadas e de bibliotecas de classes. Em 2008, as DLLs das camadas [business] e [ui] da versão 5 continham o nome e a versão da DLL da camada [dao] com a qual haviam sido compiladas, e seu código-fonte precisava ser recompilado para funcionar com uma nova camada [dao]. Aqui, as camadas [business] e [ui] conhecem apenas a interface [ITaxDao], definida no projeto [Tax.Dao] da versão 5; a nova camada [dao] implementa essa interface em um novo projeto. Nenhuma linha de código da versão 5 é modificada.

10.10.3. A camada [dao]

O arquivo de projeto [Tax.Dao.MySql.csproj] é o seguinte:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <!-- as entidades e a interface ITaxDao da versão 5 -->
    <ProjectReference Include="../../../06-architectures/TaxV5/Tax.Dao/Tax.Dao.csproj" />
  </ItemGroup>

  <ItemGroup>
    <!-- o conector ADO.NET do MySQL -->
    <PackageReference Include="MySqlConnector" Version="2.4.0" />
  </ItemGroup>

</Project>
  • Linha 11: a referência ao projeto [Tax.Dao] da versão 5, que contém as entidades ([TaxBracket], [TaxException]) e a interface [ITaxDao]. O caminho é relativo à pasta do projeto;
  • Linha 16: o conector MySqlConnector.

A classe [MySqlTax] implementa a interface [ITaxDao]:

using Entities;
using MySqlConnector;

namespace Dao;

// uma camada [dao] que lê as faixas de imposto de um banco de dados MySQL
public class MySqlTax : ITaxDao
{
    // códigos de erro específicos desta implementação
    public enum ErrorCodes { Access = 101, Data = 102 }

    // a consulta padrão: as faixas devem ser retornadas em ordem crescente dos limites
    public const string DefaultQuery = "select limits, coeffr, coeffn from brackets order by id";

    // faixas de imposto
    public TaxBracket[] TaxBrackets { get; }

    // construtor: as faixas são lidas uma vez por todas
    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();
            // uma faixa por linha do resultado: limite, coeffR, coeffN
            while (reader.Read())
            {
                brackets.Add(new TaxBracket(reader.GetDecimal(0), reader.GetDecimal(1), reader.GetDecimal(2)));
            }
        }
        catch (MySqlException e)
        {
            // erro de acesso ao banco de dados: o encapsulamos em uma TaxException
            throw new TaxException("Error accessing the tax brackets database", e) { Code = (int)ErrorCodes.Access };
        }
        catch (Exception e) when (e is InvalidCastException or ArgumentOutOfRangeException)
        {
            // uma coluna ausente, de tipo incorreto ou com valor NULL
            throw new TaxException("The tax brackets of the database are incorrect", e) { Code = (int)ErrorCodes.Data };
        }
        // uma tabela vazia também é um erro
        if (brackets.Count == 0)
        {
            throw new TaxException("The database contains no tax bracket") { Code = (int)ErrorCodes.Data };
        }
        TaxBrackets = [.. brackets];
    }
}
  • linha 7: a classe [MySqlTax] implementa a interface [ITaxDao] da versão 5: uma propriedade [TaxBrackets] somente de leitura (linha 16);
  • linha 10: códigos de erro específicos dessa implementação, modelados a partir dos de [FileTax] e [JsonTax]: 101 (o código usado no curso de 2008) para um erro de acesso ao banco de dados, 102 para dados incorretos;
  • linha 13: a consulta que retorna as faixas. A cláusula [order by id] é importante: a camada [business] espera as faixas em ordem crescente de seus limites, e um [select] sem [order by] retorna as linhas em uma ordem que o SQL não garante (a consulta de 2008 a omitia);
  • linha 19: o construtor recebe a string de conexão e a consulta, que tem um valor padrão (parâmetro opcional, C# 4). Como na versão de 2008, as faixas são lidas uma vez e para sempre no construtor: a camada [dao] é um singleton na aplicação, e o banco de dados não é consultado de novo depois disso. Como um construtor não pode ser assíncrono, a leitura usa os métodos síncronos do ADO.NET;
  • linhas 24–27: a conexão, o comando e o reader, como nos exemplos anteriores;
  • linhas 29–31: cada linha do resultado se torna um objeto [TaxBracket]: [reader.GetDecimal(i)] lê a coluna de número i como [decimal];
  • linhas 34–37: um erro de acesso ao banco de dados é encapsulado em uma [TaxException] com o código [Access], assim como [FileTax] e [JsonTax] faziam com os erros de entrada/saída. A camada [ui] não precisa conhecer as exceções do MySqlConnector;
  • linhas 39–42: uma consulta que retorna menos de três colunas ([ArgumentOutOfRangeException]), uma coluna cujo valor é [NULL], ou um tipo não numérico ([InvalidCastException]): os dados estão incorretos;
  • linhas 45–47: uma tabela vazia também é um erro de dados;
  • linha 49: a lista é convertida em um array por uma expressão de coleção.

10.10.4. Testes da camada [dao]

O curso de 2008 testava a camada [dao] com NUnit. Como no Capítulo 6, usamos MSTest. O projeto de testes referencia a nova camada [dao] e a camada [business] da versão 5:

  <ItemGroup>
    <!-- o framework de testes MSTest -->
    <PackageReference Include="MSTest" Version="4.0.2" />
  </ItemGroup>

  <ItemGroup>
    <!-- a camada [dao] testada e a camada [business] da versão 5 -->
    <ProjectReference Include="../Tax.Dao.MySql/Tax.Dao.MySql.csproj" />
    <ProjectReference Include="../../../06-architectures/TaxV5/Tax.Business/Tax.Business.csproj" />
  </ItemGroup>

A classe de teste é a seguinte:

using Dao;
using Entities;
using Business;
using MySqlConnector;

namespace Tests;

// testes das camadas [dao] MySqlTax e DbTax (o banco de dados [dbtaxes] deve existir)
[TestClass]
public class MySqlTaxTests
{
    // o banco de dados de teste
    private const string ConnectionString = "Server=localhost;Port=3306;Database=dbtaxes;User=admtaxes;Password=pwdtaxes";

    // as faixas lidas do banco de dados são as da versão "fixa no código"
    [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);
    }

    // a camada [business] da versão 5 com a nova camada [dao]
    [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));
    }

    // banco de dados inexistente (ou proibido para o usuário)
    [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);
    }

    // servidor inacessível
    [TestMethod]
    public void UnreachableServer()
    {
        TaxException e = Assert.ThrowsExactly<TaxException>(
            () => new MySqlTax(ConnectionString.Replace("3306", "3399")));
        Assert.AreEqual((int)MySqlTax.ErrorCodes.Access, e.Code);
    }

    // consulta que retorna apenas duas colunas
    [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);
    }

    // consulta que não retorna nenhuma linha
    [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);
    }
}
  • Linha 13: os testes usam o banco de dados [dbtaxes], que deve ter sido criado pelo script [dbtaxes.sql]. Portanto, deixam de ser testes unitários no sentido estrito — que testariam uma classe isoladamente — e passam a ser testes de integração: verificam que a camada [dao] funciona com o SGBD;
  • Linhas 17–22: as faixas lidas do banco de dados (por [MySqlTax], depois pela classe [DbTax] descrita adiante) são idênticas às da versão "fixa no código" [HardwiredTax]. [CollectionAssert.AreEqual] compara os elementos um a um usando seu método [Equals]; o método do [record struct] compara as propriedades. Observe que os valores [decimal] lidos do banco de dados têm duas casas decimais ([4962.00]), enquanto os de [HardwiredTax] não têm nenhuma ([4962]): para o tipo [decimal], esses dois valores são iguais;
  • linhas 27–33: a camada [business] da versão 5 calcula os impostos esperados usando a nova camada [dao];
  • linhas 38–43: um banco de dados inexistente provoca uma [TaxException] com o código [Access], que encapsula a exceção original [MySqlException];
  • linhas 48–51: um servidor inacessível (nenhum servidor está escutando na porta 3399) também provoca um erro de acesso;
  • Métodos [IncorrectQuery] (linha 57) e [NoBracket] (linha 66): uma consulta que retorna apenas duas colunas, ou que não retorna nenhuma linha, provoca uma [TaxException] com o código [Data].

A execução dos testes produz os seguintes resultados:

> 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. Integração das camadas

O projeto [Tax.Ui.MySql] contém o programa que integra as camadas. Seu arquivo de projeto é o seguinte:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <!-- a nova camada [dao] -->
    <ProjectReference Include="../Tax.Dao.MySql/Tax.Dao.MySql.csproj" />
    <!-- as camadas [business] e [ui] da versão 5, inalteradas -->
    <ProjectReference Include="../../../06-architectures/TaxV5/Tax.Business/Tax.Business.csproj" />
    <ProjectReference Include="../../../06-architectures/TaxV5/Tax.Ui/Tax.Ui.csproj" />
  </ItemGroup>

  <ItemGroup>
    <!-- contêiner de injeção de dependência e leitura de um arquivo de configuração JSON -->
    <PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="10.0.12" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="10.0.12" />
  </ItemGroup>

  <ItemGroup>
    <!-- os arquivos de configuração são copiados ao lado do executável -->
    <None Update="appsettings*.json" CopyToOutputDirectory="PreserveNewest" />
  </ItemGroup>

</Project>
  • linha 12: a nova camada [dao];
  • linhas 14–15: as camadas [business] e [ui] da versão 5. O projeto [Tax.Ui] é um executável, mas um projeto pode referenciar um executável como biblioteca: usamos sua classe pública [Dialog]. Seus arquivos de configuração também são copiados para a pasta de saída, mas os do projeto [Tax.Ui.MySql], que têm o mesmo nome, os substituem;
  • Linhas 20–21: o contêiner de injeção de dependência e a leitura da configuração, como na versão 5.

O arquivo de configuração [appsettings.json] é o seguinte:

1
2
3
4
5
6
7
8
9
{
  "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"
  }
}
  • Linha 3: a cadeia de conexão do usuário [admtaxes] ao banco de dados [dbtaxes];
  • linha 6: a implementação da camada [dao];
  • linha 7: a consulta que retorna as faixas. Incluí-la na configuração permite usar outro banco de dados — mesmo um cujas tabelas ou colunas tenham nomes diferentes — sem modificar o código.

O programa [Program.cs] é o da versão 5, exceto pelo registro da camada [dao]:

using System.Data.Common;
using Dao;
using Entities;
using Business;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using MySqlConnector;
using Ui;

// o arquivo de configuração: appsettings.json por padrão, ou o passado como argumento
string configFile = args.Length > 0 ? args[0] : "appsettings.json";

// os conectores ADO.NET utilizáveis pela implementação DbTax
DbProviderFactories.RegisterFactory("MySqlConnector", MySqlConnectorFactory.Instance);

// integração das camadas
ITaxBusiness business;
try
{
    // leitura da configuração, ao lado do executável
    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;

    // registro das camadas no contêiner
    var services = new ServiceCollection();
    // a camada [dao]: a implementação é escolhida pela configuração
    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")
    });
    // a camada [business]: o contêiner lhe passará a camada [dao]
    services.AddSingleton<ITaxBusiness, TaxBusiness>();

    // criação do contêiner, depois da camada [business] (e portanto da camada [dao])
    ServiceProvider provider = services.BuildServiceProvider();
    business = provider.GetRequiredService<ITaxBusiness>();
}
catch (Exception e)
{
    // exibição do erro e fim do programa
    Dialog.DisplayError(e);
    return 1;
}

// o diálogo com o usuário: a camada [ui] da versão 5
Dialog.Run(business);
return 0;
  • linha 14: o registro do conector MySqlConnector, usado pela implementação [DbTax] descrita no parágrafo seguinte;
  • linhas 26–28: a cadeia de conexão e a consulta, lidas da configuração. A ausência da cadeia de conexão é um erro; a consulta tem um valor padrão. Observe o operador [??] seguido de uma expressão [throw] (C# 7);
  • linhas 33–37: a camada [dao] é um [MySqlTax] (ou um [DbTax]), criado com a cadeia de conexão e a consulta;
  • linha 54: o diálogo da versão 5, inalterado.

Execução com o arquivo [appsettings.json]:

1
2
3
4
5
6
7
8
> 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: 

Estes são os valores de referência do curso: 4.282 euros para um casal com dois filhos, 2.959 euros para um casal com três filhos e 11.519 euros para uma pessoa solteira sem filhos, com um salário anual de 60.000 euros.

Com o arquivo [appsettings-base.json], cuja cadeia de conexão se refere a um banco de dados chamado [dbtaxesunknown]:

1
2
3
4
5
6
7
> 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'
----------------------------------------

O usuário [admtaxes] tem direitos apenas sobre o banco de dados [dbtaxes]: o MySQL nega o acesso a qualquer outro banco de dados, exista ou não (linha 6).

Com o arquivo [appsettings-server.json], cuja cadeia de conexão especifica a porta 3399 — na qual nenhum servidor está escutando (é o que aconteceria se um servidor MySQL fosse parado) —:

1
2
3
4
5
6
7
> 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.
----------------------------------------

Com o arquivo [appsettings-query.json], cuja consulta solicita apenas as colunas [limits] e [coeffr]:

1
2
3
4
5
6
7
8
> 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.
----------------------------------------

Nos três casos, a camada [dao] encapsulou a exceção original em uma [TaxException], que foi exibida pelo método [Dialog.DisplayError] da versão 5, e o programa termina.

10.10.6. Mudar de banco de dados

A camada [dao] [MySqlTax] usa as classes do conector MySqlConnector. Se você trocar de SGBD, precisará escrever uma nova camada [dao]… ou usar o conector genérico, como fazia a camada [dao] do curso de 2008: esse é o papel da classe [DbTax] no projeto [Tax.Dao.MySql]:

using System.Data.Common;
using Entities;

namespace Dao;

// uma camada [dao] que lê as faixas de imposto de qualquer banco de dados
// ela conhece apenas as classes abstratas do ADO.NET: o conector lhe é fornecido pela sua factory
public class DbTax : ITaxDao
{
    // códigos de erro: os mesmos do MySqlTax
    public enum ErrorCodes { Access = 101, Data = 102 }

    // faixas de imposto
    public TaxBracket[] TaxBrackets { get; }

    // construtor: a factory do conector, a string de conexão, a consulta que retorna as faixas
    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];
    }
}
  • linha 17: o construtor recebe a fábrica de conectores, a cadeia de conexão e a consulta. O código usa apenas as classes abstratas de [System.Data.Common]: ele não contém o nome de nenhum SGBD;
  • Linhas 22–27: a conexão é criada pela fábrica (a expressão [throw] trata o caso teórico de uma fábrica que retornaria [null]), e o comando é emitido pela conexão;
  • linha 34: a exceção base de todos os conectores.

O arquivo de configuração [appsettings-db.json] seleciona essa implementação e o conector [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"
  }
}

e o programa [Program.cs] cria a camada [dao] com a fábrica registrada sob esse nome:

        "DbTax" => new DbTax(DbProviderFactories.GetFactory(config["Dao:Connector"] ?? ""), connectionString, query),

Execução:

1
2
3
4
> 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: 

Para usar um banco de dados SQL Server (ou PostgreSQL), basta:

  • criar a tabela de faixas em um banco de dados SQL Server, por exemplo, uma tabela chamada [data] com as colunas [data1], [data2] e [data3]—como fazia o curso de 2008 para destacar as vantagens de colocar a consulta na configuração;
  • adicionar o pacote [Microsoft.Data.SqlClient] (ou [Npgsql]) ao projeto [Tax.Ui.MySql];
  • registrar sua fábrica em [Program.cs]: [DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance)] (ou [DbProviderFactories.RegisterFactory("Npgsql", NpgsqlFactory.Instance)]);
  • escrever um arquivo de configuração semelhante a [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"
  }
}

Apenas o MySQL está instalado na máquina usada para escrever este curso: não executamos essa configuração. As camadas [business] e [ui] e a classe [DbTax] não precisam ser modificadas: mudam apenas a configuração e o registro do conector. Observe que a consulta de [DbTax] não tem parâmetros e usa apenas recursos padrão do SQL: é isso que permite que seja executada por qualquer SGBD. Uma camada [dao] mais rica em recursos (buscas, atualizações) logo atingiria os limites do conector genérico descrito acima: é aí que um ORM como o Entity Framework Core assume.

10.11. Conclusão

Este capítulo abordou o acesso a bancos de dados em modo conectado usando ADO.NET:

  • um conector específico do SGBD (MySqlConnector para MySQL) fornece as classes [XxxConnection], [XxxCommand], [XxxDataReader] e [XxxTransaction], derivadas das classes abstratas [DbConnection], [DbCommand], [DbDataReader] e [DbTransaction] de [System.Data.Common];
  • uma conexão é aberta o mais tarde possível e fechada o mais cedo possível por um [using]: o pool de conexões torna essa prática barata;
  • [ExecuteReader] executa um [select] e retorna um reader que é percorrido linha a linha com [Read]; [ExecuteNonQuery] executa um [insert], [update] ou [delete] e retorna o número de linhas modificadas; [ExecuteScalar] retorna um único valor;
  • Os erros são [DbException] ([MySqlException] para o MySqlConnector);
  • os valores externos são sempre passados por meio de parâmetros, nunca concatenados ao texto da consulta, o que protege contra injeção de SQL;
  • as transações ([BeginTransaction], [Commit], [Rollback]) garantem que uma sequência de comandos seja atômica (SQL);
  • os métodos assíncronos ([OpenAsync], [ExecuteReaderAsync], [ReadAsync]…) e [await using] são padrão em aplicações de servidor; os lotes ([DbBatch]) reduzem o número de trocas com o servidor;
  • o conector genérico ([DbProviderFactory], [DbProviderFactories]) torna o código independente do SGBD, dentro dos limites das características específicas de cada SGBD.

A versão 6 da aplicação [Tax] demonstrou que, graças à arquitetura em camadas, passar de um arquivo para um banco de dados exige apenas escrever uma nova camada [dao] e modificar a configuração. O próximo capítulo criará uma sétima versão dessa camada usando o Entity Framework Core, que gera os próprios comandos SQL.

Para saber mais: