7. Acesso às bases de dados
7.1. Informações gerais
Existem inúmeras bases de dados para as plataformas Windows. Para acessá-las, os aplicativos utilizam programas chamados drivers.
 |
No esquema acima, o driver apresenta duas interfaces:
- a interface I1, apresentada ao aplicativo
- a interface I2 voltada para o banco de dados
Para evitar que um aplicativo escrito para um banco de dados B1 precise ser reescrito caso haja migração para um banco de dados B2 diferente, foi realizado um trabalho de padronização na interface I1. Se forem utilizadas bases de dados que utilizem drivers “normalizados”, a base B1 será fornecida com um driver P1, o banco de dados B2 com um driver P2, e a interface I1 desses dois drivers será idêntica. Assim, não será necessário reescrever o aplicativo. Assim, será possível, por exemplo, migrar um banco de dados ACCESS para um banco de dados MySQL sem alterar o aplicativo.
Existem dois tipos de drivers padronizados:
- os drivers ODBC (Open DataBase Connectivity)
- os drivers OLE e DB (Object Linking and Embedding DataBase)
Os drivers ODBC permitem o acesso a bancos de dados. As fontes de dados para os drivers OLE e DB são mais variadas: bancos de dados, sistemas de mensagens, diretórios, etc. Não há limite. Qualquer fonte de dados pode ser objeto de um driver OLE ou DB, caso o desenvolvedor assim o decida. A vantagem é, obviamente, grande: obtém-se um acesso uniforme a uma ampla variedade de dados.
A plataforma .NET é fornecida com dois tipos de classes de acesso aos dados:
- as classes SQL e Server.NET
- as classes OLE e Db.NET
As primeiras classes permitem o acesso direto ao servidor SGBD SQL da Microsoft sem driver intermediário. As segundas permitem o acesso às fontes de dados OLE DB.
A plataforma .NET é fornecida (maio de 2002) com três drivers OLE DB para, respectivamente: SQL Server, Oracle e Microsoft Jet (Access). Se quisermos trabalhar com um banco de dados que possua um driver ODBC, mas não possua os drivers OLE e DB, isso não será possível. Portanto, não é possível trabalhar com o SGBD e o MySQL, que (em maio de 2002) não fornecem os drivers OLE e DB. No entanto, existe uma série de classes que permitem o acesso às fontes de dados ODBC, as classes odbc.net. Elas não são fornecidas por padrão com o SDK e é preciso baixá-las do site da Microsoft. Nos exemplos a seguir, utilizaremos principalmente essas classes ODBC, pois a maioria dos bancos de dados no Windows vem com um driver desse tipo. Aqui está, por exemplo, uma lista dos drivers ODBC instalados em um computador com Windows 2000 (Menu Iniciar/Configurações/Painel de Controle/Ferramentas Administrativas):
Selecione o ícone “Fonte de dados” ODBC:
7.2. Os dois modos de utilização de uma fonte de dados
A plataforma .NET permite a utilização de uma fonte de dados de duas maneiras diferentes:
- modo conectado
- modo desconectado
No modo conectado, o aplicativo
- abre uma conexão com a fonte de dados
- trabalha com a fonte de dados em modo de leitura/gravação
- encerra a conexão
No modo desconectado, o aplicativo
- abre uma conexão com a fonte de dados
- obtém uma cópia em memória de todos ou parte dos dados da fonte
- encerra a conexão
- trabalha com a cópia na memória dos dados em modo de leitura/gravação
- quando o trabalho estiver concluído, abre uma conexão, envia os dados modificados à fonte de dados para que ela os registre e fecha a conexão
Em ambos os casos, é a operação de processamento e atualização dos dados que leva tempo. Imaginemos que essas atualizações sejam feitas por um usuário que está inserindo dados; essa operação pode levar dezenas de minutos. Durante todo esse tempo, no modo conectado, a conexão com o banco de dados é mantida e as alterações são refletidas imediatamente. No modo desconectado, não há conexão com o banco de dados durante a atualização dos dados. As alterações são feitas apenas na cópia em memória. Elas são refletidas na fonte de dados de uma só vez, quando tudo estiver concluído.
Quais são as vantagens e desvantagens dos dois métodos?
- Uma conexão consome muitos recursos do sistema. Se houver muitas conexões simultâneas, o modo offline permite reduzir ao mínimo a duração de cada uma delas. Esse é o caso de aplicativos web com milhares de usuários.
- A desvantagem do modo desconectado é o gerenciamento complexo das atualizações simultâneas. O usuário U1 obtém dados no momento T1 e começa a modificá-los. No momento T2, o usuário U2 também acessa a fonte de dados e obtém os mesmos dados. Nesse intervalo, o usuário U1 alterou alguns dados, mas ainda não os transmitiu para a fonte de dados. U2, portanto, trabalha com dados, alguns dos quais estão incorretos. As classes .NET oferecem soluções para lidar com esse problema, mas ele não é fácil de resolver.
- No modo conectado, a atualização simultânea de dados por vários usuários normalmente não representa um problema. Como a conexão com o banco de dados é mantida, é o próprio banco de dados que gerencia essas atualizações simultâneas. Assim, o Oracle bloqueia uma linha do banco de dados assim que um usuário a modifica. Ela permanecerá bloqueada e, portanto, inacessível aos outros usuários até que aquele que a modificou confirme (commit) sua alteração ou a reverta (rollback).
- Se os dados precisarem circular pela rede, deve-se optar pelo modo desconectado. Ele permite obter um instantâneo dos dados em um objeto chamado “dataset”, que representa um banco de dados por si só. Esse objeto pode circular pela rede entre máquinas.
Vamos estudar primeiro o modo conectado.
7.3. Acesso aos dados no modo conectado
7.3.1. Os bancos de dados do exemplo
Consideramos um banco de dados ACCESS, denominado articles.mdb, que possui apenas uma tabela chamada ARTICLES com a seguinte estrutura:
nome | tipo |
código | código do artigo com 4 caracteres |
nome | seu nome (sequência de caracteres) |
preço | seu preço (real) |
stock_actuel | seu estoque atual (número inteiro) |
stock_minimum | o estoque mínimo (número inteiro) abaixo do qual é necessário reabastecer o item |
Seu conteúdo inicial é o seguinte:
Utilizaremos essa base tanto por meio de um piloto ODBC quantoum piloto OLE DB, a fim de demonstrar a semelhança entre as duas abordagens e porque dispomos desses dois tipos de pilotos para o ACCESS.
Também utilizaremos um banco de dados MySQL DBARTICLES com a mesma tabela única ARTICLES, o mesmo conteúdo e acessada por meio de um driver ODBC, a fim de demonstrar que o aplicativo desenvolvido para utilizar o banco de dados ACCESS não precisa ser modificado para utilizar o banco de dados MySQL. O banco de dados DBARTICLES está acessível a um usuário chamado admarticles com a senha mdparticles. A captura de tela a seguir mostra o conteúdo do banco de dados MySQL:
C:\mysql\bin>mysql --database=dbarticles --user=admarticles --password=mdparticles
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.49-max-debug
Type 'help' for help.
mysql> show tables;
+----------------------+
| Tables_in_dbarticles |
+----------------------+
| articles |
+----------------------+
1 row in set (0.01 sec)
mysql> select * from articles;
+------+--------------------------------+------+--------------+---------------+
| code | nom | prix | stock_actuel | stock_minimum |
+------+--------------------------------+------+--------------+---------------+
| a300 | vÚlo | 2500 | 10 | 5 |
| b300 | pompe | 56 | 62 | 45 |
| c300 | arc | 3500 | 10 | 20 |
| d300 | flÞches - lot de 6 | 780 | 12 | 20 |
| e300 | combinaison de plongÚe | 2800 | 34 | 7 |
| f300 | bouteilles d'oxygÞne | 800 | 10 | 5 |
+------+--------------------------------+------+--------------+---------------+
6 rows in set (0.02 sec)
mysql> describe articles;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| code | text | YES | | NULL | |
| nom | text | YES | | NULL | |
| prix | double | YES | | NULL | |
| stock_actuel | smallint(6) | YES | | NULL | |
| stock_minimum | smallint(6) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> exit
Bye
Para definir a base ACCESS como fonte de dados ODBC, proceda da seguinte forma:
- ative o gerenciador de fontes de dados ODBC conforme mostrado acima e selecione a guia “User” DSN (DSN = Nome da fonte de dados)
- adicione uma fonte de dados usando o botão Add, indique que essa fonte de dados é acessível por meio de um driver Access e execute Terminer:
- Dê o nome articles-access à fonte de dados, insira uma descrição livre e use o botão Sélectionner para indicar o arquivo .mdb do banco de dados. Termine com OK.
A nova fonte de dados aparecerá então na lista de fontes do usuário DSN:
Para definir o banco de dados MySQL DBARTICLES como fonte de dados ODBC, proceda da seguinte forma:
- ative o gerenciador de fontes de dados ODBC conforme mostrado acima e selecione a guia “Usuário” DSN. Adicione uma nova fonte de dados com o Add e selecione o driver ODBC do MySQL.
- Execute o Terminer. Em seguida, será exibida uma página de configuração da fonte MySQL:
54321
- em (1), atribuímos um nome à nossa fonte de dados ODBC
- em (2), indicamos a máquina na qual está localizado o servidor MySQL. Aqui, colocamos localhost para indicar que ele está na mesma máquina que nosso aplicativo. Se o servidor MySQL estivesse em uma máquina M remota, colocaríamos o nome dela nesse campo e nosso aplicativo funcionaria com um banco de dados remoto sem nenhuma modificação.
- em (3) insere-se o nome do banco de dados. Aqui, ele se chama DBARTICLES.
- em (4), insere-se o login admarticles e, em (5), a senha mdparticles.
7.3.2. Uso de um driver ODBC
Em um aplicativo que utiliza um banco de dados no modo conectado, geralmente são seguidas as etapas a seguir:
- Conexão com o banco de dados
- Envio de consultas SQL ao banco de dados
- Recebimento e processamento dos resultados dessas consultas
- Encerramento da conexão
As etapas 2 e 3 são realizadas repetidamente, sendo que o encerramento da conexão ocorre somente ao final da consulta ao banco de dados. Trata-se de um esquema relativamente clássico, com o qual você talvez já esteja familiarizado caso já tenha consultado um banco de dados de forma interativa. Essas etapas são as mesmas, independentemente de o banco de dados ser utilizado por meio de um driver ODBC ou de um driver OLE DB. Apresentamos a seguir um exemplo com as classes .NET de gerenciamento de fontes de dados ODBC. O programa se chama liste e aceita como parâmetro o nome DSN de uma fonte de dados ODBC que possui uma tabela ARTICLES. Em seguida, ele exibe o conteúdo dessa tabela:
dos>liste
syntaxe : pg dsnArticles
dos>liste articles-access
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
dos>liste mysql-artices
Erreur d'exploitation de la base de données (ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified)
dos>liste mysql-articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
Nos resultados acima, vemos que o programa listou tanto o conteúdo do banco de dados ACCESS quanto o do banco de dados MySQL. Vamos agora analisar o código desse programa:
' opções
Option Explicit On
Option Strict On
' espaços de nomes
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports Microsoft.VisualBasic
Module db1
Sub main(ByVal args As String())
' aplicativo de console
' exibe o conteúdo de uma tabela ARTICLES de um banco de dados DSN
' cujo nome é passado como parâmetro
Const syntaxe As String = "syntaxe : pg dsnArticles"
Const tabArticles As String = "articles" ' la table des articles
' verificação dos parâmetros
' existe 1 parâmetro?
If args.Length <> 1 Then
' mensagem de erro
Console.Error.WriteLine(syntaxe)
' fim
Environment.Exit(1)
End If
' recuperando o parâmetro
Dim dsnArticles As String = args(0) ' la base DSN
' preparação da conexão com o banco de dados
Dim articlesConn As OdbcConnection = Nothing ' la connexion
Dim myReader As OdbcDataReader = Nothing ' le lecteur de données
' tentando acessar o banco de dados
Try
' cadeia de conexão com o banco de dados
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
' execução de um comando SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOdbcCommand As New OdbcCommand(sqlText)
myOdbcCommand.Connection = articlesConn
myReader = myOdbcCommand.ExecuteReader()
' Análise da tabela recuperada
' exibição das colunas
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' exibição dos dados
While myReader.Read()
' análise da linha atual
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
Catch ex As Exception
Console.Error.WriteLine(("Erreur d'exploitation de la base de données " + ex.Message + ")"))
Environment.Exit(2)
Finally
' Fechamento do leitor
myReader.Close()
' Fechamento da conexão
articlesConn.Close()
End Try
End Sub
End Module
As classes de gerenciamento de fontes ODBC estão no espaço de nomes Microsoft.Data.Odbc, que, portanto, deve ser importado. Além disso, algumas classes estão no espaço de nomes System.Data.
Imports System.Data
Imports Microsoft.Data.Odbc
Os espaços de nomes utilizados pelo programa estão em diferentes assemblies. Compila-se o programa com o seguinte comando:
dos>vbc /r:microsoft.data.odbc.dll /r:microsoft.visualbasic.dll /r:system.dll /r:system.data.dll db1.vb
7.3.2.1. A fase de conexão
Uma conexão ODBC utiliza a classe OdbcConnection. O construtor dessa classe aceita como parâmetro o que é chamado de string de conexão. Trata-se de uma sequência de caracteres que define todos os parâmetros necessários para que a conexão com o banco de dados possa ser estabelecida. Esses parâmetros podem ser muito numerosos e, portanto, a string pode ser complexa. A string tem o formato "param1=valeur1;param2=valeur2;...;paramj=valeurj;". Aqui estão alguns parâmetros possíveis para paramj:
nome de um usuário que acessará o banco de dados | |
senha desse usuário | |
nome DSN do banco de dados, caso haja um | |
nome do banco de dados acessado | |
Se uma fonte de dados for definida como ODBC por meio do gerenciador de fontes de dados ODBC, esses parâmetros já terão sido fornecidos e gravados. Basta, então, passar o parâmetro DSN, que fornece o nome DSN da fonte de dados. É isso que é feito aqui:
' preparação da conexão com o banco de dados
Dim articlesConn As OdbcConnection = Nothing ' la connexion
Dim myReader As OdbcDataReader = Nothing ' le lecteur de données
Try
' tentativa de acesso ao banco de dados
' cadeia de conexão com o banco de dados
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
Depois que o objeto OdbcConnection é criado, abre-se a conexão com o método Open. Essa abertura pode falhar, assim como qualquer outra operação no banco de dados. É por isso que todo o código de acesso ao banco de dados está em um try-catch. Uma vez estabelecida a conexão, é possível enviar consultas SQL ao banco de dados.
7.3.2.2. Enviar consultas SQL
Para enviar consultas SQL, precisamos de um objeto Command; neste caso, mais especificamente, um objeto OdbcCommand, já que estamos utilizando uma fonte de dados ODBC. A classe OdbcCommand possui vários construtores:
- OdbcCommand(): cria um objeto Command vazio. Para utilizá-lo, será necessário definir posteriormente diversas propriedades:
- CommandText: o texto da consulta SQL a ser executada
- Conexão: o objeto OdbcConnection que representa a conexão com o banco de dados no qual a consulta será feita
- CommandType: o tipo da consulta SQL. Existem três valores possíveis
- CommandType.Text: a propriedade CommandText contém o texto de uma consulta SQL (valor padrão)
- CommandType.StoredProcedure: a propriedade CommandText contém o nome de um procedimento armazenado no banco de dados
- CommandType.TableDirect: a propriedade CommandText contém o nome de uma tabela T. Equivalente a `select * from T`. Existe apenas para os drivers OLE e DB.
- OdbcCommand(string sqlText): o parâmetro sqlText será atribuído à propriedade CommandText. Este é o texto da consulta SQL a ser executada. A conexão deverá ser especificada na propriedade Connection.
- OdbcCommand(string sqlText, OdbcConnection conexão): o parâmetro sqlText será atribuído à propriedade CommandText e o parâmetro connexion à propriedade Connection.
Para emitir a consulta SQL, existem dois métodos:
- OdbcdataReader ExecuteReader(): envia a consulta SELECT de CommandText para a conexão Connection e cria um objeto OdbcDataReader que permite oacesso a todas as linhas da tabela de resultados do select
- int ExecuteNOnQuery(): envia a solicitação de atualização (INSERT, UPDATE, DELETE) de CommandText para a conexão Connection e retorna o número de linhas afetadas por essa atualização.
Em nosso exemplo, após abrir a conexão com o banco de dados, emitimos uma consulta SQL SELECT para obter o conteúdo da tabela ARTICLES:
' execução de um comando SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOdbcCommand As New OdbcCommand(sqlText)
myOdbcCommand.Connection = articlesConn
myReader = myOdbcCommand.ExecuteReader()
Uma consulta é, normalmente, uma instrução do tipo:
select col1, col2,... from table1, table2,...
where condition
order by expression
...
Apenas as palavras-chave da primeira linha são obrigatórias; as demais são opcionais. Existem outras palavras-chave não apresentadas aqui.
- É realizada uma junção com todas as tabelas que se encontram após a palavra-chave from
- Somente as colunas que aparecem após a palavra-chave `select` são mantidas
- Somente as linhas que atendem à condição da palavra-chave `where` são mantidas
- As linhas resultantes, ordenadas de acordo com a expressão da palavra-chave `order by`, formam o resultado da consulta.
O resultado de um select é uma tabela. Se considerarmos a tabela ARTICLES anterior e quisermos os nomes dos itens cujo estoque atual está abaixo do limite mínimo, escreveremos:
select nom from articles where stock_actuel<stock_minimum
Se quisermos que os nomes apareçam em ordem alfabética, escreveremos:
select nom from articles where stock_actuel<stock_minimum order by nom
7.3.2.3. Análise do resultado de uma consulta SELECT
O resultado de uma consulta SELECT no modo não conectado é um objeto DataReader; neste caso, um objeto OdbcDataReader. Esse objeto permite obter sequencialmente todas as linhas do resultado e obter informações sobre as colunas desses resultados. Vamos examinar algumas propriedades e métodos dessa classe:
o número de colunas da tabela | |
Item(i) representa a coluna nº i da linha atual do resultado | |
o valor da coluna nº i da linha atual, representado como tipo XXX (Int16, Int32, Int64, Double, String, Boolean, ...) | |
nome da coluna nº i | |
fecha o objeto OdbcdataReader e libera os recursos associados | |
avança uma linha na tabela de resultados. Retorna falso se isso não for possível. A nova linha passa a ser a linha atual do leitor. |
A análise do resultado de um select é tipicamente uma análise sequencial análoga à dos arquivos de texto: só é possível avançar na tabela, não retroceder:
While myReader.Read()
' há uma linha — estamos processando-a
....
' próxima linha
end while
Essas explicações são suficientes para compreender o código a seguir do nosso exemplo:
' Análise da tabela recuperada
' exibição das colunas
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' exibição de dados
While myReader.Read()
' análise da linha atual
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
A única dificuldade está na instrução em que os valores das diferentes colunas da linha atual são concatenados:
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
A notação linha+=myReader(i).ToString é traduzida como linha+=myReader.Item(i).ToString(), em que Item(i) é o valor da coluna i da linha atual.
7.3.2.4. Liberação de recursos
As classes OdbcReader e OdbcConnection possuem, ambas, um método Close() que libera os recursos associados aos objetos assim fechados.
7.3.3. Uso de um driver OLE DB
Retomamos o mesmo exemplo, desta vez com um banco de dados acessado por meio de um driver OLE DB. A plataforma .NET fornece um driver desse tipo para bancos de dados ACCESS. Portanto, utilizaremos o mesmo banco de dados articles.mdb de antes. Nosso objetivo aqui é mostrar que, embora as classes mudem, os conceitos permanecem os mesmos:
- A conexão é representada por um objeto OleDbConnection
- uma consulta SQL é emitida por meio de um objeto OleDbCommand
- se essa consulta for uma cláusula SELECT, será retornado um objeto OleDbDataReader para acessar as linhas da tabela de resultados
Essas classes estão no espaço de nomes System.Data.OleDb. O programa anterior pode ser facilmente adaptado para lidar com um banco de dados OLE DB:
- substitui-se em todos os lugares OdbcXX por OleDbXX
- altera-se a string de conexão. Para um banco de dados ACCESS sem login/senha, a string de conexão é Provider=Microsoft.JET.OLEDB.4.0;Data Source=[fichier.mdb]. A parte configurável dessa string é o nome do arquivo ACCESS a ser utilizado. Vamos modificar nosso programa para que ele aceite como parâmetro o nome desse arquivo.
- O namespace a ser importado agora é System.Data.OleDb.
Nosso programa fica assim:
' opções
Option Explicit On
Option Strict On
' espaços de nomes
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Module db2
Public Sub Main(ByVal args() As String)
' aplicativo de console
' exibe o conteúdo de uma tabela ARRTICLES de um banco de dados DSN
' cujo nome é passado como parâmetro
Const syntaxe As String = "syntaxe : pg base_access_articles"
Const tabArticles As String = "articles" ' la table des articles
' verificação dos parâmetros
' existe 1 parâmetro?
If args.Length <> 1 Then
' mensagem de erro
Console.Error.WriteLine(syntaxe)
' fim
Environment.Exit(1)
End If
' recuperando o parâmetro
Dim dbArticles As String = args(0) ' la base de données
' preparação da conexão com o banco de dados
Dim articlesConn As OleDbConnection = Nothing ' la connexion
Dim myReader As OleDbDataReader = Nothing ' le lecteur de données
' tentando acessar o banco de dados
Try
' cadeia de conexão com o banco de dados
Dim connectString As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + dbArticles + ";"
articlesConn = New OleDbConnection(connectString)
articlesConn.Open()
' execução de um comando SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOleDbCommand As New OleDbCommand(sqlText)
myOleDbCommand.Connection = articlesConn
myReader = myOleDbCommand.ExecuteReader()
' Análise da tabela recuperada
' exibição das colunas
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' exibição dos dados
While myReader.Read()
' análise da linha atual
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
Catch ex As Exception
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
Environment.Exit(2)
Finally
' Fechamento do leitor
myReader.Close()
' Encerramento da conexão
articlesConn.Close()
End Try
' fim
Environment.Exit(0)
End Sub
End Module
Resultados obtidos:
dos>vbc liste.vb
E:\data\serge\MSNET\vb.net\adonet\6>dir
07/05/2002 15:09 2 325 liste.CS
07/05/2002 15:09 4 608 liste.exe
20/08/2001 11:54 86 016 ARTICLES.MDB
dos>liste articles.mdb
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
7.3.4. Atualização de uma tabela
Os exemplos anteriores se limitavam a listar o conteúdo de uma tabela. Vamos modificar nosso programa de gerenciamento do banco de dados de artigos para que ele possa alterá-lo. O programa se chama sql. Passamos como parâmetro o nome DSN do banco de dados de artigos a ser gerenciado. O usuário digita diretamente os comandos SQL no teclado, e o programa os executa, conforme mostram os resultados a seguir, obtidos na base de artigos MySQL:
dos>vbc /r:microsoft.data.odbc.dll sql.vb
dos>sql mysql-articles
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
Requête SQL (fin pour arrêter) : select * from articles where stock_actuel<stock_minimum
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
Requête SQL (fin pour arrêter) : insert into articles values ("1","1",1,1,1)
Il y a eu 1 ligne(s) modifiée(s)
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
1 1 1 1 1
Requête SQL (fin pour arrêter) : update articles set nom="2" where nom="1"
Il y a eu 1 ligne(s) modifiée(s)
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
1 2 1 1 1
Requête SQL (fin pour arrêter) : delete from articles where code="1"
Il y a eu 1 ligne(s) modifiée(s)
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
Requête SQL (fin pour arrêter) : select * from articles order by nom asc
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
c300 arc 3500 10 20
f300 bouteilles d'oxygène 800 10 5
e300 combinaison de plongée 2800 34 7
d300 flèches - lot de 6 780 12 20
b300 pompe 56 62 45
a300 vélo 2500 10 5
Requête SQL (fin pour arrêter) : fin
A programação é a seguinte:
' opções
Option Explicit On
Option Strict On
' espaços de nomes
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports System.Data.OleDb
Imports System.Text.RegularExpressions
Imports System.Collections
Imports Microsoft.VisualBasic
Module db3
Public Sub Main(ByVal args() As String)
' aplicativo de console
' executa consultas SQL digitadas no teclado em uma
' tabela ARTICLES de um banco de dados DSN cujo nome é passado como parâmetro
Const syntaxe As String = "syntaxe : pg dsnArticles"
' verificação dos parâmetros
' existem 2 parâmetros
If args.Length <> 1 Then
' mensagem de erro
Console.Error.WriteLine(syntaxe)
' fim
Environment.Exit(1)
End If 'if
' recuperando o parâmetro
Dim dsnArticles As String = args(0)
' cadeia de conexão com o banco de dados
Dim connectString As String = "DSN=" + dsnArticles + ";"
' preparação da conexão com o banco de dados
Dim articlesConn As OdbcConnection = Nothing
Dim sqlCommand As OdbcCommand = Nothing
Try
' tentando acessar o banco de dados
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
' cria-se um objeto de comando
sqlCommand = New OdbcCommand("", articlesConn)
'try
Catch ex As Exception
' mensagem de erro
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
' liberação de recursos
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(2)
End Try 'catch
' construindo um dicionário dos comandos SQL aceitos
Dim commandesSQL() As String = {"select", "insert", "update", "delete"}
Dim dicoCommandes As New Hashtable
Dim i As Integer
For i = 0 To commandesSQL.Length - 1
dicoCommandes.Add(commandesSQL(i), True)
Next i
' leitura e execução dos comandos SQL digitados no teclado
Dim requête As String = Nothing ' texte de la requête SQL
Dim champs() As String ' les champs de la requête
Dim modèle As New Regex("\s+")
' Ciclo de entrada e execução dos comandos SQL digitados no teclado
While True
' sem erros no início
Dim erreur As Boolean = False
' solicitação da consulta
Console.Out.Write(ControlChars.Lf + "Requête SQL (fin pour arrêter) : ")
requête = Console.In.ReadLine().Trim().ToLower()
' concluído?
If requête = "fin" Then
Exit While
End If
' decompondo a consulta em campos
champs = modèle.Split(requête)
' consulta válida?
If champs.Length = 0 Or Not dicoCommandes.ContainsKey(champs(0)) Then
' mensagem de erro
Console.Error.WriteLine("Requête invalide. Utilisez select, insert, update, delete")
' próxima consulta
erreur = True
End If
If Not erreur Then
' preparação do objeto Command para executar a consulta
sqlCommand.CommandText = requête
' execução da consulta
Try
If champs(0) = "select" Then
executeSelect(sqlCommand)
Else
executeUpdate(sqlCommand)
End If
Catch ex As Exception
' mensagem de erro
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
End Try
End If
End While
' liberação de recursos
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(0)
End Sub
' execução de uma consulta de atualização
Sub executeUpdate(ByVal sqlCommand As OdbcCommand)
' executa sqlCommand, consulta de atualização
Dim nbLignes As Integer = sqlCommand.ExecuteNonQuery()
' exibição
Console.Out.WriteLine(("Il y a eu " & nbLignes & " ligne(s) modifiée(s)"))
End Sub
' execução de uma consulta SELECT
Sub executeSelect(ByVal sqlCommand As OdbcCommand)
' executa sqlCommand, consulta SELECT
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' Análise da tabela recuperada
' exibição das colunas
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' exibição dos dados
While myReader.Read()
' análise da linha atual
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
' exibição
Console.WriteLine(ligne)
End While
' liberação de recursos
myReader.Close()
End Sub
End Module
Aqui, comentamos apenas as novidades em relação ao programa anterior:
- Criamos um dicionário dos comandos SQL aceitos:
' criação de um dicionário de comandos SQL aceitos
Dim commandesSQL() As String = {"select", "insert", "update", "delete"}
Dim dicoCommandes As New Hashtable
Dim i As Integer
For i = 0 To commandesSQL.Length - 1
dicoCommandes.Add(commandesSQL(i), True)
Next i
o que nos permite, em seguida, verificar facilmente se a primeira palavra (campo [0]) da consulta digitada é um dos quatro comandos aceitos:
' consulta válida?
If champs.Length = 0 Or Not dicoCommandes.ContainsKey(champs(0)) Then
' mensagem de erro
Console.Error.WriteLine("Requête invalide. Utilisez select, insert, update, delete")
' próxima consulta
erreur = True
End If 'if
- Anteriormente, a consulta havia sido decomposta em campos por meio do método Split da classe RegEx:
Dim modèle As New Regex("\s+")
....
' a consulta é decomposta em campos
champs = modèle.Split(requête)
As palavras que compõem a consulta podem ser separadas por qualquer número de espaços.
- A execução de uma consulta select não utiliza o mesmo método que o de uma consulta de atualização (insert, update, delete). Portanto, é necessário realizar um teste e executar uma função diferente para cada um desses dois casos:
' preparação do objeto Command para executar a consulta
sqlCommand.CommandText = requête
' execução da consulta
Try
If champs(0) = "select" Then
executeSelect(sqlCommand)
Else
executeUpdate(sqlCommand)
End If 'try
Catch ex As Exception
' mensagem de erro
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
End Try
A execução de uma consulta SQL pode gerar uma exceção, que é tratada aqui.
- A função executeSelect retoma tudo o que foi visto nos exemplos anteriores.
- A função executeUpdate utiliza o método ExecuteNonQuery da classe OdbcCommand, que retorna o número de linhas afetadas pelo comando.
7.3.5. IMPOTS
Retomamos o objeto impôt criado em um capítulo anterior:
' opções
Option Strict On
Option Explicit On
' espaços de nomes
Imports System
Public Class impôt
' dados necessários para o cálculo do imposto
' provêm de uma fonte externa
Private limites(), coeffR(), coeffN() As Decimal
' fabricante
Public Sub New(ByVal LIMITES() As Decimal, ByVal COEFFR() As Decimal, ByVal COEFFN() As Decimal)
' verifica-se se as três tabelas têm o mesmo tamanho
Dim OK As Boolean = LIMITES.Length = COEFFR.Length And LIMITES.Length = COEFFN.Length
If Not OK Then
Throw New Exception("Les 3 tableaux fournis n'ont pas la même taille(" & LIMITES.Length & "," & COEFFR.Length & "," & COEFFN.Length & ")")
End If
' tudo certo
Me.limites = LIMITES
Me.coeffR = COEFFR
Me.coeffN = COEFFN
End Sub
' cálculo do imposto
Public Function calculer(ByVal marié As Boolean, ByVal nbEnfants As Integer, ByVal salaire As Integer) As Long
' cálculo do número de cotas
Dim nbParts As Decimal
If marié Then
nbParts = CDec(nbEnfants) / 2 + 2
Else
nbParts = CDec(nbEnfants) / 2 + 1
End If
If nbEnfants >= 3 Then
nbParts += 0.5D
End If
' cálculo do rendimento tributável e do quociente familiar
Dim revenu As Decimal = 0.72D * salaire
Dim QF As Decimal = revenu / nbParts
' cálculo do imposto
limites((limites.Length - 1)) = QF + 1
Dim i As Integer = 0
While QF > limites(i)
i += 1
End While
' retorno do resultado
Return CLng(revenu * coeffR(i) - nbParts * coeffN(i))
End Function
End Class
Adicionamos a ele um novo construtor que permite inicializar as tabelas limites, coeffR e coeffN a partir de um banco de dados ODBC:
Imports System.Data
Imports Microsoft.Data.Odbc
Imports System.Collections
...
' função 2
Public Sub New(ByVal DSNimpots As String, ByVal Timpots As String, ByVal colLimites As String, ByVal colCoeffR As String, ByVal colCoeffN As String)
' inicializa as três tabelas de limites, coeffR, coeffN a partir
' do conteúdo da tabela Timpots do banco de dados ODBC DSNimpots
' colLimites, colCoeffR, colCoeffN são as três colunas dessa tabela
' pode gerar uma exceção
Dim connectString As String = "DSN=" + DSNimpots + ";" ' chaîne de connexion à la base
Dim impotsConn As OdbcConnection = Nothing ' la connexion
Dim sqlCommand As OdbcCommand = Nothing ' la commande SQL
' a consulta SELECT
Dim selectCommand As String = "select " + colLimites + "," + colCoeffR + "," + colCoeffN + " from " + Timpots
' tabelas para recuperar os dados
Dim tLimites As New ArrayList
Dim tCoeffR As New ArrayList
Dim tCoeffN As New ArrayList
' tenta-se acessar o banco de dados
impotsConn = New OdbcConnection(connectString)
impotsConn.Open()
' está sendo criado um objeto de comando
sqlCommand = New OdbcCommand(selectCommand, impotsConn)
' executando a consulta
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' Análise da tabela recuperada
While myReader.Read()
' os dados da linha atual são inseridos nas tabelas
tLimites.Add(myReader(colLimites))
tCoeffR.Add(myReader(colCoeffR))
tCoeffN.Add(myReader(colCoeffN))
End While
' liberação dos recursos
myReader.Close()
impotsConn.Close()
' as tabelas dinâmicas são transferidas para tabelas estáticas
Me.limites = New Decimal(tLimites.Count) {}
Me.coeffR = New Decimal(tLimites.Count) {}
Me.coeffN = New Decimal(tLimites.Count) {}
Dim i As Integer
For i = 0 To tLimites.Count - 1
limites(i) = Decimal.Parse(tLimites(i).ToString())
coeffR(i) = Decimal.Parse(tCoeffR(i).ToString())
coeffN(i) = Decimal.Parse(tCoeffN(i).ToString())
Next i
End Sub
O programa de teste funciona da seguinte forma: ele recebe como argumentos os parâmetros a serem transmitidos ao construtor da classe impôt. Após construir um objeto impôt, ele realiza os cálculos do imposto a pagar:
Option Explicit On
Option Strict On
' espaços de nomes
Imports System
Imports Microsoft.VisualBasic
' página de teste
Module testimpots
Sub Main(ByVal arguments() As String)
' programa interativo de cálculo de impostos
' o usuário digita três dados no teclado: casado nbEnfants salário
' o programa exibe então o imposto a pagar
Const syntaxe1 As String = "pg DSNimpots tabImpots colLimites colCoeffR colCoeffN"
Const syntaxe2 As String = "syntaxe : marié nbEnfants salaire" + ControlChars.Lf + "marié : o pour marié, n pour non marié" + ControlChars.Lf + "nbEnfants : nombre d'enfants" + ControlChars.Lf + "salaire : salaire annuel en F"
' verificação dos parâmetros do programa
If arguments.Length <> 5 Then
' mensagem de erro
Console.Error.WriteLine(syntaxe1)
' fim
Environment.Exit(1)
End If 'if
' recuperação dos argumentos
Dim DSNimpots As String = arguments(0)
Dim tabImpots As String = arguments(1)
Dim colLimites As String = arguments(2)
Dim colCoeffR As String = arguments(3)
Dim colCoeffN As String = arguments(4)
' criação de um objeto de imposto
Dim objImpôt As impôt = Nothing
Try
objImpôt = New impôt(DSNimpots, tabImpots, colLimites, colCoeffR, colCoeffN)
Catch ex As Exception
Console.Error.WriteLine(("L'erreur suivante s'est produite : " + ex.Message))
Environment.Exit(2)
End Try
' loop infinito
While True
' inicialmente, sem erros
Dim erreur As Boolean = False
' solicitando os parâmetros para o cálculo do imposto
Console.Out.Write("Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :")
Dim paramètres As String = Console.In.ReadLine().Trim()
' o que fazer?
If paramètres Is Nothing Or paramètres = "" Then
Exit While
End If
' verificação do número de argumentos na linha inserida
Dim args As String() = paramètres.Split(Nothing)
Dim nbParamètres As Integer = args.Length
If nbParamètres <> 3 Then
Console.Error.WriteLine(syntaxe2)
erreur = True
End If
Dim marié As String
Dim nbEnfants As Integer
Dim salaire As Integer
If Not erreur Then
' verificação da validade dos parâmetros
' casado
marié = args(0).ToLower()
If marié <> "o" And marié <> "n" Then
Console.Error.WriteLine((syntaxe2 + ControlChars.Lf + "Argument marié incorrect : tapez o ou n"))
erreur = True
End If
' nbEnfants
nbEnfants = 0
Try
nbEnfants = Integer.Parse(args(1))
If nbEnfants < 0 Then
Throw New Exception
End If
Catch
Console.Error.WriteLine(syntaxe2 + "\nArgument nbEnfants incorrect : tapez un entier positif ou nul")
erreur = True
End Try
' salário
salaire = 0
Try
salaire = Integer.Parse(args(2))
If salaire < 0 Then
Throw New Exception
End If
Catch
Console.Error.WriteLine(syntaxe2 + "\nArgument salaire incorrect : tapez un entier positif ou nul")
erreur = True
End Try
End If
If Not erreur Then
' os parâmetros estão corretos — calculamos o imposto
Console.Out.WriteLine(("impôt=" & objImpôt.calculer(marié = "o", nbEnfants, salaire).ToString + " F"))
End If
End While
End Sub
End Module
A base utilizada é uma base MySQL com o nome DSN mysql-impots:
C:\mysql\bin>mysql --database=impots --user=admimpots --password=mdpimpots
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.49-max-debug
Type 'help' for help.
mysql> show tables;
+------------------+
| Tables_in_impots |
+------------------+
| timpots |
+------------------+
mysql> select * from timpots;
+---------+--------+---------+
| limites | coeffR | coeffN |
+---------+--------+---------+
| 12620 | 0 | 0 |
| 13190 | 0.05 | 631 |
| 15640 | 0.1 | 1290.5 |
| 24740 | 0.15 | 2072.5 |
| 31810 | 0.2 | 3309.5 |
| 39970 | 0.25 | 4900 |
| 48360 | 0.3 | 6898 |
| 55790 | 0.35 | 9316.5 |
| 92970 | 0.4 | 12106 |
| 127860 | 0.45 | 16754 |
| 151250 | 0.5 | 23147.5 |
| 172040 | 0.55 | 30710 |
| 195000 | 0.6 | 39312 |
| 0 | 0.65 | 49062 |
+---------+--------+---------+
A execução do programa de teste produz os seguintes resultados:
dos>D:\data\devel\vbnet\poly\chap6\impots>vbc /r:system.data.dll /r:microsoft.data.odbc.dll /r:system.dll /t:library impots.vb
dos>vbc /r:impots.dll testimpots.vb
dos>test mysql-impots timpots limites coeffr coeffn
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :o 2 200000
impôt=22506 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :n 2 200000
impôt=33388 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :o 3 200000
impôt=16400 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :n 3 300000
impôt=50082 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :n 3 200000
impôt=22506 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :