7. Acesso às bases de dados
7.1. Informações gerais
Existem inúmeras bases de dados para as plataformas Windows. Para aceder às mesmas, as aplicações utilizam programas denominados controladores (drivers).
 |
No esquema acima, o controlador apresenta duas interfaces:
- a interface I1 apresentada à aplicação
- a interface I2 para a base de dados
Para evitar que uma aplicação escrita para uma base de dados B1 tenha de ser reescrita caso se migre para uma base de dados B2 diferente, foi realizado um esforço de normalização na interface I1. Se forem utilizadas bases de dados que utilizem controladores «normalizados», a base B1 será fornecida com um controlador P1, a base de dados B2 com um controlador P2, e a interface I1 destes dois controladores será idêntica. Assim, não será necessário reescrever a aplicação. Assim, será possível, por exemplo, migrar uma base de dados ACCESS para uma base de dados MySQL sem alterar a aplicação.
Existem dois tipos de controladores normalizados:
- os controladores ODBC (Open DataBase Connectivity)
- os controladores OLE e DB (Object Linking and Embedding DataBase)
Os controladores ODBC permitem o acesso a bases de dados. As fontes de dados para os controladores OLE e DB são mais variadas: bases de dados, sistemas de correio eletrónico, diretórios, etc. Não há limites. Qualquer fonte de dados pode ser alvo de um controlador OLE ou DB, se o editor assim o decidir. A vantagem é, evidentemente, grande: obtém-se um acesso uniforme a uma grande 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 controlador intermédio. As segundas permitem o acesso às fontes de dados OLE DB.
A plataforma .NET é fornecida (maio de 2002) com três controladores OLE DB para, respetivamente: SQL Server, Oracle e Microsoft Jet (Access). Se se pretender trabalhar com uma base de dados que tenha um controlador ODBC, mas não tenha os controladores OLE e DB, isso não é possível. Assim, não é possível trabalhar com o SGBD MySQL, que (em maio de 2002) não fornece os controladores OLE DB. Existe, no entanto, um conjunto de classes que permite o acesso às fontes de dados ODBC: as classes odbc.net. Estas não são fornecidas de série com o SDK, sendo necessário descarregá-las do site da Microsoft. Nos exemplos que se seguem, utilizaremos principalmente estas classes ODBC, uma vez que a maioria das bases de dados no Windows é fornecida com um controlador deste tipo. Eis, por exemplo, uma lista dos controladores ODBC instalados num computador com Windows 2000 (Menu Iniciar/Definições/Painel de controlo/Ferramentas de administração):
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 formas diferentes:
- modo ligado
- modo não conectado
No modo ligado, a aplicação
- estabelece uma ligação com a fonte de dados
- trabalha com a fonte de dados em modo de leitura/gravação
- encerra a ligação
No modo desconectado, a aplicação
- abre uma ligação com a fonte de dados
- obtém uma cópia em memória de todos ou parte dos dados da fonte
- encerra a ligação
- trabalha com a cópia em memória dos dados, em modo de leitura/gravação
- quando o trabalho estiver concluído, abre uma ligação, envia os dados alterados para a fonte de dados para que esta os registe e fecha a ligação
Em ambos os casos, é a operação de processamento e atualização dos dados que demora tempo. Imaginemos que estas atualizações sejam feitas por um utilizador que introduz dados; esta operação pode demorar dezenas de minutos. Durante todo esse tempo, no modo ligado, a ligação com a base de dados é mantida e as alterações são refletidas imediatamente. No modo desligado, não há ligação à base de dados durante a atualização dos dados. As alterações são feitas apenas na cópia em memória. 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 ligação consome muitos recursos do sistema. Se houver muitas ligações simultâneas, o modo offline permite reduzir a duração de cada uma ao mínimo. É o caso das aplicações web com milhares de utilizadores.
- A desvantagem do modo desligado é a gestão delicada das atualizações simultâneas. O utilizador U1 obtém dados no momento T1 e começa a alterá-los. No momento T2, o utilizador U2 também acede à fonte de dados e obtém os mesmos dados. Entretanto, o utilizador U1 alterou alguns dados, mas ainda não os transmitiu à fonte de dados. O U2 trabalha, portanto, com dados, alguns dos quais estão errados. As classes .NET oferecem soluções para gerir este problema, mas não é fácil de resolver.
- No modo conectado, a atualização simultânea de dados por vários utilizadores normalmente não representa um problema. Como a ligação à base de dados é mantida, é a própria base de dados que gere essas atualizações simultâneas. Assim, o Oracle bloqueia uma linha da base de dados assim que um utilizador a altera. Esta permanecerá bloqueada e, portanto, inacessível aos outros utilizadores até que aquele que a alterou valide (commit) a sua alteração ou a anule (rollback).
- Se os dados tiverem de circular na rede, deve optar-se pelo modo desconectado. Este permite obter uma imagem dos dados num objeto denominado «dataset», que representa, por si só, uma base de dados. Este objeto pode circular na rede entre máquinas.
Vamos analisar, em primeiro lugar, o modo ligado.
7.3. Acesso aos dados no modo ligado
7.3.1. As bases de dados do exemplo
Consideramos uma base de dados ACCESS, denominada articles.mdb, que possui apenas uma tabela denominada ARTICLES com a seguinte estrutura:
nome | tipo |
código | código do artigo com 4 caracteres |
nome | o seu nome (cadeia de caracteres) |
preço | o seu preço (real) |
stock_actuel | o seu stock atual (número inteiro) |
stock_minimum | o stock mínimo (número inteiro) abaixo do qual é necessário reabastecer o artigo |
O seu conteúdo inicial é o seguinte:
Utilizaremos esta base tanto através de um programa piloto ODBC comoatravés de um piloto OLE DB, para demonstrar a semelhança entre as duas abordagens e porque dispomos destes dois tipos de pilotos para o ACCESS.
Utilizaremos também uma base de dados MySQL DBARTICLES com a mesma tabela única ARTICLES, o mesmo conteúdo e à qual se acede através de um controlador ODBC, a fim de demonstrar que a aplicação escrita para explorar a base de dados ACCESS não precisa de ser alterada para utilizar a base de dados MySQL. A base de dados DBARTICLES está acessível a um utilizador denominado admarticles com a palavra-passe mdparticles. A captura de ecrã seguinte mostra o conteúdo da base 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 de dados ACCESS como fonte de dados ODBC, proceda da seguinte forma:
- ative o gestor de fontes de dados ODBC, tal como foi mostrado acima, e selecione o separador «User» DSN (DSN = Nome da fonte de dados)
- adicione uma fonte de dados através do botão Add, indique que esta fonte está acessível através de um controlador Access e execute Terminer:
- Atribua o nome articles-access à fonte de dados, introduza uma descrição livre e utilize o botão Sélectionner para indicar o ficheiro .mdb da base de dados. Termine com OK.
A nova fonte de dados aparece então na lista de fontes do utilizador DSN:
Para definir a base de dados MySQL DBARTICLES como fonte de dados ODBC, proceda da seguinte forma:
- ative o gestor de fontes de dados ODBC, tal como foi mostrado acima, e selecione o separador «User» DSN. Adicione uma nova fonte de dados com o Add e selecione o controlador ODBC do MySQL.
- Execute o Terminer. Aparecerá então uma página de configuração da fonte MySQL:
54321
- em (1), atribui-se um nome à nossa fonte de dados ODBC
- em (2), indicamos a máquina onde se encontra o servidor MySQL. Aqui, colocamos localhost para indicar que se encontra na mesma máquina que a nossa aplicação. Se o servidor MySQL estivesse numa máquina M remota, colocaríamos aqui o seu nome e a nossa aplicação funcionaria então com uma base de dados remota sem qualquer alteração.
- em (3) insere-se o nome da base de dados. Aqui, chama-se DBARTICLES.
- em (4), introduz-se o nome de utilizador admarticles e em (5) a palavra-passe mdparticles.
7.3.2. Utilização de um controlador ODBC
Numa aplicação que utilize uma base de dados em modo ligado, encontram-se geralmente os seguintes passos:
- Ligação à base de dados
- Envio de consultas SQL à base de dados
- Recepção e processamento dos resultados dessas consultas
- Encerramento da ligação
As etapas 2 e 3 são realizadas repetidamente, sendo que o encerramento da ligação só ocorre no final da utilização da base de dados. Trata-se de um esquema relativamente clássico, com o qual talvez já esteja habituado, caso já tenha utilizado uma base de dados de forma interativa. Estas etapas são as mesmas, quer a base de dados seja utilizada através de um controlador ODBC, quer de um controlador OLE ou DB. Apresentamos abaixo um exemplo com as classes .NET de gestão de fontes de dados ODBC. O programa chama-se liste e aceita como parâmetro o nome DSN de uma fonte de dados ODBC que possui uma tabela ARTICLES. Em seguida, apresenta 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 da base de dados ACCESS como o da base de dados MySQL. Vamos agora analisar o código deste 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())
' consola da aplicação
' exibe o conteúdo de uma tabela ARTICLES de uma base 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 algum parâmetro?
If args.Length <> 1 Then
' mensagem de erro
Console.Error.WriteLine(syntaxe)
' fim
Environment.Exit(1)
End If
' recuperação do parâmetro
Dim dsnArticles As String = args(0) ' la base DSN
' preparação da ligação à base de dados
Dim articlesConn As OdbcConnection = Nothing ' la connexion
Dim myReader As OdbcDataReader = Nothing ' le lecteur de données
' tentativa de acesso à base de dados
Try
' cadeia de ligação à base 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
' fecho do leitor
myReader.Close()
' encerramento da ligação
articlesConn.Close()
End Try
End Sub
End Module
As classes de gestão de fontes ODBC encontram-se no espaço de nomes Microsoft.Data.Odbc, que, por isso, temos de importar. Além disso, algumas classes encontram-se no espaço de nomes System.Data.
Imports System.Data
Imports Microsoft.Data.Odbc
Os espaços de nomes utilizados pelo programa encontram-se em diferentes assemblies. O programa é compilado 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 ligação
Uma ligação ODBC utiliza a classe OdbcConnection. O construtor desta classe aceita como parâmetro o que se denomina uma cadeia de ligação. Trata-se de uma cadeia de caracteres que define todos os parâmetros necessários para que a ligação à base de dados possa ser estabelecida. Estes parâmetros podem ser muito numerosos e, por isso, a cadeia pode ser complexa. A cadeia tem o formato "param1=valeur1;param2=valeur2;...;paramj=valeurj;". Aqui estão alguns parâmetros paramj possíveis:
nome de um utilizador que irá aceder à base de dados | |
palavra-passe desse utilizador | |
nome DSN da base de dados, caso exista | |
nome da base de dados acedida | |
Se se definir uma fonte de dados como a fonte de dados ODBC utilizando o gestor de fontes de dados ODBC, estes parâmetros já terão sido especificados e guardados. Basta, então, passar o parâmetro DSN, que fornece o nome DSN da fonte de dados. É isso que se faz aqui:
' preparação da ligação à base de dados
Dim articlesConn As OdbcConnection = Nothing ' la connexion
Dim myReader As OdbcDataReader = Nothing ' le lecteur de données
Try
' tentativa de acesso à base de dados
' cadeia de ligação à base de dados
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
Depois de criado o objeto OdbcConnection, abre-se a ligação com o método Open. Esta abertura pode falhar, tal como qualquer outra operação na base de dados. É por isso que todo o código de acesso à base de dados se encontra num try-catch. Assim que a ligação estiver estabelecida, é possível enviar consultas SQL à base 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, uma vez que estamos a utilizar uma fonte de dados ODBC. A classe OdbcCommand possui vários construtores:
- OdbcCommand(): cria um objeto Command vazio. Para o utilizar, será necessário especificar posteriormente várias propriedades:
- CommandText: o texto da consulta SQL a executar
- Conexão: o objeto OdbcConnection que representa a conexão à base de dados na qual a consulta será efetuada
- 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 por predefinição)
- CommandType.StoredProcedure: a propriedade CommandText contém o nome de um procedimento armazenado na base de dados
- CommandType.TableDirect: a propriedade CommandText contém o nome de uma tabela T. Equivalente a select * from T. Existe apenas para os controladores 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 ligação deverá ser especificada na propriedade Connection.
- OdbcCommand(string sqlText, OdbcConnection ligaçã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 ligação Connection e cria um objeto OdbcDataReader que permite oacesso a todas as linhas da tabela de resultados do select
- int ExecuteNOnQuery(): envia o pedido de atualização (INSERT, UPDATE, DELETE) de CommandText para a ligação Connection e devolve o número de linhas afetadas por esta atualização.
No nosso exemplo, após abrir a ligação à base 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 consulta 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 restantes são opcionais. Existem outras palavras-chave que não são apresentadas aqui.
- É efetuada uma junção com todas as tabelas que se encontram a seguir à palavra-chave «from»
- Apenas as colunas que se seguem à palavra-chave «select» são mantidas
- Apenas as linhas que satisfazem a condição da palavra-chave «where» são mantidas
- As linhas resultantes, ordenadas de acordo com a expressão da palavra-chave «order by», constituem o resultado da consulta.
O resultado de um select é uma tabela. Se considerarmos a tabela ARTICLES anterior e quisermos os nomes dos artigos cujo stock atual está abaixo do limiar mínimo, escreveremos:
select nom from articles where stock_actuel<stock_minimum
Se os quisermos por ordem alfabética dos nomes, 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. Este objeto permite obter sequencialmente todas as linhas do resultado e obter informações sobre as colunas desses resultados. Vamos analisar algumas propriedades e métodos desta 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, apresentado como tipo XXX (Int16, Int32, Int64, Double, String, Boolean, ...) | |
nome da coluna n.º i | |
fecha o objeto OdbcdataReader e liberta os recursos associados | |
avança uma linha na tabela de resultados. Retorna «falso» se tal não for possível. A nova linha torna-se a linha atual do leitor. |
A análise do resultado de um select é tipicamente uma análise sequencial análoga à dos ficheiros de texto: só é possível avançar na tabela, não recuar:
While myReader.Read()
' temos uma linha - estamos a processá-la
....
' linha seguinte
end while
Estas explicações são suficientes para compreender o código seguinte 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 reside 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 por linha+=myReader.Item(i).ToString() em que Item(i) é o valor da coluna i da linha atual.
7.3.2.4. Libertação de recursos
As classes OdbcReader e OdbcConnection possuem ambas um método Close() que liberta os recursos associados aos objetos assim encerrados.
7.3.3. Utilização de um controlador OLE DB
Retomamos o mesmo exemplo, desta vez com uma base de dados acedida através de um controlador OLE DB. A plataforma .NET fornece um controlador deste tipo para as bases de dados ACCESS. Por isso, vamos utilizar a mesma base de dados articles.mdb que anteriormente. Pretendemos demonstrar aqui que, embora as classes mudem, os conceitos permanecem os mesmos:
- A ligação é representada por um objeto OleDbConnection
- é emitida uma consulta SQL através de um objeto OleDbCommand
- se esta consulta for uma cláusula SELECT, será obtido em resposta um objeto OleDbDataReader para aceder às linhas da tabela de resultados
Estas classes encontram-se no espaço de nomes System.Data.OleDb. O programa anterior pode ser facilmente adaptado para gerir uma base de dados OLE DB:
- substitui-se em todo o lado OdbcXX por OleDbXX
- altera-se a cadeia de ligação. Para uma base de dados ACCESS sem nome de utilizador/palavra-passe, a cadeia de ligação é Provider=Microsoft.JET.OLEDB.4.0;Data Source=[fichier.mdb]. A parte configurável desta cadeia é o nome do ficheiro ACCESS a utilizar. Iremos alterar o nosso programa para que aceite como parâmetro o nome deste ficheiro.
- O espaço de nomes a importar é agora System.Data.OleDb.
O nosso programa passa a ser o seguinte:
' 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)
' aplicação de consola
' exibe o conteúdo de uma tabela ARRTICLES de uma base 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 algum parâmetro?
If args.Length <> 1 Then
' mensagem de erro
Console.Error.WriteLine(syntaxe)
' fim
Environment.Exit(1)
End If
' recuperação do parâmetro
Dim dbArticles As String = args(0) ' la base de données
' preparação da ligação à base de dados
Dim articlesConn As OleDbConnection = Nothing ' la connexion
Dim myReader As OleDbDataReader = Nothing ' le lecteur de données
' tentativa de acesso à base de dados
Try
' cadeia de ligação à base 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
' fecho do leitor
myReader.Close()
' encerramento da ligaçã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 limitavam-se a listar o conteúdo de uma tabela. Vamos modificar o nosso programa de gestão da base de dados de artigos para que possa alterá-la. O programa chama-se sql. Passa-se como parâmetro o nome DSN da base de dados de artigos a gerir. O utilizador introduz diretamente os comandos SQL no teclado, que o programa executa, conforme demonstrado pelos resultados seguintes, 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
O programa é o 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)
' aplicação de consola
' executa consultas SQL introduzidas via teclado numa
' tabela ARTICLES de uma base 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
' recuperamos o parâmetro
Dim dsnArticles As String = args(0)
' cadeia de ligação à base de dados
Dim connectString As String = "DSN=" + dsnArticles + ";"
' preparação da ligação à base de dados
Dim articlesConn As OdbcConnection = Nothing
Dim sqlCommand As OdbcCommand = Nothing
Try
' tenta-se aceder à base 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 + ")"))
' libertação de recursos
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(2)
End Try 'catch
' constrói-se um dicionário dos comandos SQL aceites
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 introduzidos pelo 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 introdução e execução dos comandos SQL digitados no teclado
While True
' sem erros no início
Dim erreur As Boolean = False
' envio 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
' a consulta é dividida 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
' libertaçã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
' Libertação de recursos
myReader.Close()
End Sub
End Module
Aqui, apenas comentamos o que é novo em relação ao programa anterior:
- Criamos um dicionário dos comandos SQL aceites:
' cria-se um dicionário dos comandos SQL aceites
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 aceites:
' 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 tinha sido decomposta em campos utilizando o 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 um número qualquer 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). Por isso, é necessário realizar um teste e executar uma função diferente para cada um destes 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 é aqui tratada.
- A função executeSelect retoma tudo o que foi abordado nos exemplos anteriores.
- A função executeUpdate utiliza o método ExecuteNonQuery da classe OdbcCommand, que devolve o número de linhas afetadas pelo comando.
7.3.5. IMPOTS
Retomamos o objeto impôt criado num capítulo anterior:
' opções
Option Strict On
Option Explicit On
' espaços de nomes
Imports System
Public Class impôt
' os 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 3 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
' está tudo bem
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 quotas
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-lhe um novo construtor que permite inicializar as tabelas limites, coeffR e coeffN a partir de uma base 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 da base de dados ODBC DSNimpots
' colLimites, colCoeffR, colCoeffN são as três colunas desta tabela
' pode lançar 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 aceder à base de dados
impotsConn = New OdbcConnection(connectString)
impotsConn.Open()
' está a ser criado um objeto de comando
sqlCommand = New OdbcCommand(selectCommand, impotsConn)
' executa-se a consulta
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' Análise da tabela recuperada
While myReader.Read()
' os dados da linha atual são colocados nas tabelas
tLimites.Add(myReader(colLimites))
tCoeffR.Add(myReader(colCoeffR))
tCoeffN.Add(myReader(colCoeffN))
End While
' libertaçã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 é o seguinte: recebe como argumentos os parâmetros a transmitir ao construtor da classe impôt. Depois de construir um objeto impôt, efetua 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 utilizador introduz três dados através do teclado: casado nbEnfants salário
' o programa apresenta 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
' recuperam-se os 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
' solicitam-se 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 se deve fazer?
If paramètres Is Nothing Or paramètres = "" Then
Exit While
End If
' verificação do número de argumentos na linha introduzida
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 — calcula-se 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 :