7. Acesso à base de dados
7.1. Geral
Existem muitas bases de dados disponíveis para plataformas Windows. Para aceder às mesmas, as aplicações utilizam programas chamados controladores.
 |
No diagrama acima, o controlador possui duas interfaces:
- a interface I1 apresentada à aplicação
- a interface I2 para a base de dados
Para evitar que uma aplicação escrita para a base de dados B1 tenha de ser reescrita caso seja migrada para uma base de dados diferente, a B2, foram envidados esforços de normalização na interface I1. Se forem utilizadas bases de dados que utilizem controladores «normalizados», a base de dados B1 será fornecida com o controlador P1, a base de dados B2 com o controlador P2, e a interface I1 destes dois controladores será idêntica. Assim, a aplicação não precisará de ser reescrita. Por exemplo, é possível migrar uma base de dados ACCESS para uma base de dados MySQL sem alterar a aplicação.
Existem dois tipos de controladores padronizados:
- Drivers ODBC (Open DataBase Connectivity)
- Drivers OLE DB (Object Linking and Embedding DataBase)
Os controladores ODBC proporcionam acesso a bases de dados. As fontes de dados para os controladores OLE DB são mais variadas: bases de dados, sistemas de e-mail, diretórios, etc. Não há limites. Qualquer fonte de dados pode ser objeto de um controlador OLE DB, se um fornecedor assim o decidir. A vantagem é obviamente significativa: tem acesso uniforme a uma grande variedade de dados.
A plataforma .NET inclui dois tipos de classes de acesso a dados:
- Classes SQL Server.NET
- Classes OLE DB.NET
As primeiras classes permitem o acesso direto ao SGBD SQL Server da Microsoft sem um controlador intermédio. As segundas classes permitem o acesso a fontes de dados OLE DB.
A plataforma .NET é fornecida (em maio de 2002) com três controladores OLE DB para o SQL Server, Oracle e Microsoft Jet (Access), respetivamente. Se pretender trabalhar com uma base de dados que tenha um controlador ODBC mas não um controlador OLE DB, não o poderá fazer. Assim, não poderá trabalhar com o SGBD MySQL, que (em maio de 2002) não fornece um controlador OLE DB. No entanto, existe um conjunto de classes que permite o acesso a fontes de dados ODBC: as classes odbc.net. Estas não estão incluídas por predefinição no SDK e têm de ser descarregadas 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 vem com um controlador deste tipo. Aqui, por exemplo, está uma lista de controladores ODBC instalados numa máquina com Windows 2000 (Menu Iniciar/Definições/Painel de Controlo/Ferramentas Administrativas):
Selecione o ícone «Fonte de dados ODBC»:
7.2. As duas formas de utilizar uma fonte de dados
A plataforma .NET permite-lhe utilizar uma fonte de dados de duas formas diferentes:
- modo conectado
- modo desconectado
No modo conectado, a aplicação
- abre uma ligação à fonte de dados
- trabalha com a fonte de dados no modo de leitura/gravação
- fecha a ligação
No modo offline, a aplicação
- abre uma ligação à fonte de dados
- recupera uma cópia na memória de todos ou parte dos dados da fonte
- fecha a conexão
- trabalha com a cópia na memória dos dados no modo de leitura/gravação
- quando o trabalho estiver concluído, abre uma ligação, envia os dados modificados para a fonte de dados para que possam ser atualizados e fecha a ligação
Em ambos os casos, é o processo de processamento e atualização dos dados que leva tempo. Imagine que estas atualizações são realizadas por um utilizador que introduz dados; este processo pode demorar dezenas de minutos. Durante todo este tempo, no modo conectado, a ligação à base de dados é mantida e as alterações são refletidas imediatamente. No modo offline, não há ligação à base de dados enquanto os dados estão a ser atualizados. As alterações são feitas apenas na cópia na 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 recursos do sistema. Se houver muitas ligações simultâneas, o modo offline ajuda a minimizar a sua duração. É o caso das aplicações web com milhares de utilizadores.
- A desvantagem do modo offline é o tratamento delicado das atualizações simultâneas. O utilizador U1 recupera dados no momento T1 e começa a modificá-los. No momento T2, o utilizador U2 também acede à fonte de dados e recupera os mesmos dados. Entretanto, o utilizador U1 modificou alguns dados, mas ainda não os transmitiu para a fonte de dados. O U2 está, portanto, a trabalhar com dados, alguns dos quais estão incorretos. As classes .NET oferecem soluções para gerir este problema, mas não é fácil de resolver.
- No modo conectado, as atualizações simultâneas de dados por vários utilizadores normalmente não representam um problema. Uma vez que a ligação à base de dados é mantida, a própria base de dados gere estas atualizações simultâneas. Assim, a Oracle bloqueia uma linha na base de dados assim que um utilizador a modifica. Esta permanecerá bloqueada — e, portanto, inacessível a outros utilizadores — até que o utilizador que a modificou confirme a alteração ou a reverta.
- Se for necessário partilhar dados através da rede, deve optar pelo modo offline. Este modo fornece um instantâneo dos dados num objeto chamado conjunto de dados, que funciona como uma base de dados autónoma. Este objeto pode ser partilhado através da rede entre máquinas.
Vamos primeiro examinar o modo conectado.
7.3. Aceder aos dados no modo conectado
7.3.1. As bases de dados no exemplo
Estamos a considerar uma base de dados do Access chamada articles.mdb que contém apenas uma tabela chamada ARTICLES com a seguinte estrutura:
nome | tipo |
código | código do artigo de 4 caracteres |
nome | o seu nome (cadeia de caracteres) |
preço | o seu preço (real) |
stock_atual | stock atual (inteiro) |
stock_mínimo | o stock mínimo (número inteiro) abaixo do qual o artigo deve ser reabastecido |
O seu conteúdo inicial é o seguinte:
Iremos utilizar esta base de dados através de um controlador ODBC e de um controlador OLE DB para demonstrar a semelhança entre as duas abordagens e porque dispomos de ambos os tipos de controladores para o ACCESS.
Iremos também utilizar uma base de dados MySQL denominada DBARTICLES, que possui a mesma tabela única ARTICLES, o mesmo conteúdo e é acedida através de um controlador ODBC, para demonstrar que a aplicação escrita para utilizar a base de dados Access não precisa de ser modificada para utilizar a base de dados MySQL. A base de dados DBARTICLES é 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 uma fonte de dados ODBC, proceda da seguinte forma:
- Abra o Gestor de Fontes de Dados ODBC, conforme mostrado acima, e selecione o separador DSN do utilizador (DSN = Nome da Fonte de Dados)
- Adicione uma fonte utilizando o botão Adicionar, especifique que esta fonte é acessível através de um controlador do Access e clique em Concluir:
- Dê à fonte de dados o nome «articles-access», introduza uma descrição à sua escolha e utilize o botão «Selecionar» para especificar o ficheiro .mdb da base de dados. Conclua clicando em «OK».
A nova fonte de dados aparecerá então na lista de DSNs do utilizador:
Para definir a base de dados MySQL DBARTICLES como uma fonte de dados ODBC, proceda da seguinte forma:
- Abra o Gestor de Fontes de Dados ODBC, conforme mostrado acima, e selecione o separador DSN do utilizador. Adicione uma nova fonte de dados utilizando Adicionar e selecione o controlador ODBC do MySQL.
- Clique em Concluir. Será então apresentada uma página de configuração da fonte MySQL:
54321
- Em (1), atribua um nome à sua fonte de dados ODBC
- Em (2), especifique a máquina onde o servidor MySQL está localizado. Aqui, introduzimos `localhost` para indicar que está na mesma máquina que a nossa aplicação. Se o servidor MySQL estivesse numa máquina remota `M`, introduziríamos o seu nome aqui, e a nossa aplicação funcionaria então com uma base de dados remota sem quaisquer alterações.
- Em (3), introduza o nome da base de dados. Aqui, chama-se DBARTICLES.
- Em (4), introduza 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 no modo conectado, geralmente são necessários os seguintes passos:
- Ligar-se à base de dados
- Envio de consultas SQL para a base de dados
- Receber e processar os resultados dessas consultas
- Encerrar a ligação
Os passos 2 e 3 são executados repetidamente, sendo que a ligação só é encerrada no final das operações da base de dados. Trata-se de um padrão relativamente comum com o qual poderá estar familiarizado se já tiver trabalhado com uma base de dados de forma interativa. Estes passos são os mesmos, quer a base de dados seja acedida através de um controlador ODBC ou de um controlador OLE DB. Segue-se um exemplo que utiliza as classes .NET para gerir fontes de dados ODBC. O programa chama-se liste e recebe como parâmetro o nome DSN de uma fonte de dados ODBC que contém uma tabela denominada ARTICLES. Em seguida, apresenta o conteúdo desta 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
A partir dos resultados acima, podemos ver que o programa listou o conteúdo tanto da base de dados ACCESS como da base de dados MySQL. Vamos agora examinar o código deste programa:
' options
Option Explicit On
Option Strict On
' namespaces
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports Microsoft.VisualBasic
Module db1
Sub main(ByVal args As String())
' application console
' displays the contents of a ARTICLES table in a DSN database
' whose name is passed in parameter
Const syntaxe As String = "syntaxe : pg dsnArticles"
Const tabArticles As String = "articles" ' table of articles
' parameter verification
' do we have 1 parameter
If args.Length <> 1 Then
' error msg
Console.Error.WriteLine(syntaxe)
' end
Environment.Exit(1)
End If
' parameter is retrieved
Dim dsnArticles As String = args(0) ' the DSN database
' preparing the connection to the comic
Dim articlesConn As OdbcConnection = Nothing ' the connection
Dim myReader As OdbcDataReader = Nothing ' the data reader
' attempt to access the database
Try
' base connection chain
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
' execution of a SQL command
Dim sqlText As String = "select * from " + tabArticles
Dim myOdbcCommand As New OdbcCommand(sqlText)
myOdbcCommand.Connection = articlesConn
myReader = myOdbcCommand.ExecuteReader()
' Using the recovered table
' column display
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' data display
While myReader.Read()
' current line operation
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
Catch ex As Exception
Console.Error.WriteLine(("Erreur d'exploitation de la base de données " + ex.Message + ")"))
Environment.Exit(2)
Finally
' drive lock
myReader.Close()
' locking connection
articlesConn.Close()
End Try
End Sub
End Module
As classes de gestão de fontes ODBC estão localizadas no namespace Microsoft.Data.Odbc, que deve, portanto, ser importado. Além disso, várias classes estão localizadas no namespace System.Data.
Imports System.Data
Imports Microsoft.Data.Odbc
Os namespaces utilizados pelo programa encontram-se em assemblies diferentes. Compile o programa utilizando 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 recebe como parâmetro o que se denomina «cadeia de ligação». Trata-se de uma sequência de caracteres que define todos os parâmetros necessários para estabelecer a ligação à base de dados. Podem existir muitos desses parâmetros, o que torna a cadeia complexa. A cadeia tem o formato «param1=valor1;param2=valor2;...;paramj=valorj;». Aqui estão alguns parâmetros possíveis para paramj:
nome de utilizador do utilizador que irá aceder à base de dados | |
a palavra-passe deste utilizador | |
o nome DSN da base de dados, se existir | |
nome da base de dados a que se está a aceder | |
Se definir uma fonte de dados como uma fonte de dados ODBC utilizando o Gestor de Fontes de Dados ODBC, estes parâmetros já terão sido especificados e guardados. Nesse caso, basta passar o parâmetro DSN, que fornece o nome DSN da fonte de dados. É isso que se faz aqui:
' preparing the connection to the comic
Dim articlesConn As OdbcConnection = Nothing ' the connection
Dim myReader As OdbcDataReader = Nothing ' the data reader
Try
' attempt to access the database
' base connection chain
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
Assim que o objeto OdbcConnection é criado, abrimos a ligação utilizando o método Open. Esta operação pode falhar, tal como qualquer outra operação de base de dados. É por isso que todo o código de acesso à base de dados está incluído num bloco try-catch. Assim que a ligação é estabelecida, podemos executar consultas SQL na base de dados.
7.3.2.2. Executar consultas SQL
Para executar consultas SQL, precisamos de um objeto Command — 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, terá de especificar várias propriedades posteriormente:
- CommandText: o texto da consulta SQL a ser executada
- Connection: o objeto OdbcConnection que representa a ligação à base de dados na qual a consulta será executada
- 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 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 controladores OLE 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 deve ser especificada na propriedade Connection.
- OdbcCommand(string sqlText, OdbcConnection connection): o parâmetro sqlText é atribuído à propriedade CommandText e o parâmetro connection à propriedade Connection.
Para executar a consulta SQL, estão disponíveis dois métodos:
- OdbcDataReader ExecuteReader(): envia a consulta SELECT de CommandText para Connection e cria um objeto OdbcDataReader que fornece acesso a todas as linhas na tabela de resultados da consulta SELECT
- int ExecuteNonQuery(): envia a consulta de atualização (INSERT, UPDATE, DELETE) de CommandText para Connection e retorna o número de linhas afetadas pela atualização.
No nosso exemplo, após abrir a ligação à base de dados, emitimos uma consulta SQL SELECT para recuperar o conteúdo da tabela ARTICLES:
' exécution d'une commande SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOdbcCommand As New OdbcCommand(sqlText)
myOdbcCommand.Connection = articlesConn
myReader = myOdbcCommand.ExecuteReader()
Uma consulta é normalmente do seguinte tipo:
Apenas as palavras-chave na primeira linha são obrigatórias; as restantes são opcionais. Existem outras palavras-chave que não são apresentadas aqui.
- É realizada uma junção em todas as tabelas listadas após a 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 na 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 artigos cujo stock atual está abaixo do limite mínimo, escreveríamos:
Se quisermos que sejam ordenados alfabeticamente por nome, escreveríamos:
7.3.2.3. Trabalhar com o resultado de uma consulta SELECT
O resultado de uma consulta SELECT no modo desconectado é um objeto DataReader, neste caso, um objeto OdbcDataReader. Este objeto permite recuperar todas as linhas do resultado sequencialmente e obter informações sobre as colunas desses resultados. Vamos examinar algumas propriedades e métodos desta classe:
o número de colunas na tabela | |
Item(i) representa a coluna número i da linha atual no resultado | |
o valor da coluna i da linha atual, devolvido como tipo XXX (Int16, Int32, Int64, Double, String, Boolean, ...) | |
nome da coluna número i | |
fecha o objeto OdbcdataReader e liberta os recursos associados | |
avança uma linha na tabela de resultados. Retorna false se isso não for possível. A nova linha torna-se a linha atual do leitor. |
O processamento do resultado de uma instrução SELECT é tipicamente uma operação sequencial semelhante à dos ficheiros de texto: só é possível avançar na tabela, não recuar:
While myReader.Read()
' on a une ligne - on l'exploite
....
' ligne suivante
end while
Estas explicações são suficientes para compreender o código seguinte no nosso exemplo:
' Exploitation de la table récupérée
' affichage des colonnes
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' affichage des données
While myReader.Read()
' exploitation ligne courante
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
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 para linha+=myReader.Item(i).ToString(), onde Item(i) é o valor da coluna i na 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 que estão a ser fechados.
7.3.3. Utilização de um controlador OLE DB
Vamos utilizar o mesmo exemplo, desta vez com uma base de dados acedida através de um controlador OLE DB. A plataforma .NET fornece esse tipo de controlador para bases de dados Access. Por isso, vamos utilizar a mesma base de dados articles.mdb de antes. O nosso objetivo aqui é mostrar que, embora as classes possam mudar, os conceitos permanecem os mesmos:
- a ligação é representada por um objeto OleDbConnection
- uma consulta SQL é emitida utilizando um objeto OleDbCommand
- se esta consulta for uma instrução SELECT, é devolvido um objeto OleDbDataReader para aceder às linhas da tabela de resultados
Estas classes encontram-se no namespace System.Data.OleDb. O programa anterior pode ser facilmente adaptado para funcionar com uma base de dados OLE DB:
- Substitua OdbcXX por OleDbXX em todos os locais
- modifique 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=[file.mdb]. A parte configurável desta cadeia é o nome do ficheiro ACCESS a utilizar. Iremos modificar o nosso programa para que aceite o nome deste ficheiro como parâmetro.
- O namespace a importar é agora System.Data.OleDb.
O nosso programa fica assim:
' options
Option Explicit On
Option Strict On
' namespaces
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Module db2
Public Sub Main(ByVal args() As String)
' application console
' displays the contents of a ARRTICLES table in a DSN database
' whose name is passed in parameter
Const syntaxe As String = "syntaxe : pg base_access_articles"
Const tabArticles As String = "articles" ' table of articles
' parameter verification
' do we have 1 parameter
If args.Length <> 1 Then
' error msg
Console.Error.WriteLine(syntaxe)
' end
Environment.Exit(1)
End If
' parameter is retrieved
Dim dbArticles As String = args(0) ' the database
' preparing the connection to the comic
Dim articlesConn As OleDbConnection = Nothing ' the connection
Dim myReader As OleDbDataReader = Nothing ' the data reader
' attempt to access the database
Try
' base connection chain
Dim connectString As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + dbArticles + ";"
articlesConn = New OleDbConnection(connectString)
articlesConn.Open()
' execution of a SQL command
Dim sqlText As String = "select * from " + tabArticles
Dim myOleDbCommand As New OleDbCommand(sqlText)
myOleDbCommand.Connection = articlesConn
myReader = myOleDbCommand.ExecuteReader()
' Using the recovered table
' column display
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' data display
While myReader.Read()
' current line operation
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
Catch ex As Exception
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
Environment.Exit(2)
Finally
' drive lock
myReader.Close()
' locking connection
articlesConn.Close()
End Try
' end
Environment.Exit(0)
End Sub
End Module
Os 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 produtos para que possa alterar a base de dados. O programa chama-se sql. Passamos o nome DSN da base de dados de produtos a ser gerida como parâmetro. O utilizador digita comandos SQL diretamente no teclado, que o programa executa, conforme demonstrado pelos seguintes resultados obtidos da base de dados de produtos 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:
' options
Option Explicit On
Option Strict On
' namespaces
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports System.Data.OleDb
Imports System.Text.RegularExpressions
Imports System.Collections
Imports Microsoft.VisualBasic
Module db3
Public Sub Main(ByVal args() As String)
' application console
' executes SQL requests typed on the keyboard on a
' table ARTICLES from a database DSN whose name is passed as a parameter
Const syntaxe As String = "syntaxe : pg dsnArticles"
' parameter verification
' do we have 2 parameters
If args.Length <> 1 Then
' error msg
Console.Error.WriteLine(syntaxe)
' end
Environment.Exit(1)
End If 'if
' parameter is retrieved
Dim dsnArticles As String = args(0)
' base connection chain
Dim connectString As String = "DSN=" + dsnArticles + ";"
' preparing the connection to the comic
Dim articlesConn As OdbcConnection = Nothing
Dim sqlCommand As OdbcCommand = Nothing
Try
' attempt to access the database
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
' create a command object
sqlCommand = New OdbcCommand("", articlesConn)
'try
Catch ex As Exception
' error msg
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
' freeing up resources
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(2)
End Try 'catch
' build a dictionary of accepted sql commands
Dim commandesSQL() As String = {"select", "insert", "update", "delete"}
Dim dicoCommandes As New Hashtable
Dim i As Integer
For i = 0 To commandesSQL.Length - 1
dicoCommandes.Add(commandesSQL(i), True)
Next i
' read-execute SQL commands typed on the keyboard
Dim requête As String = Nothing ' query text SQL
Dim champs() As String ' query fields
Dim modèle As New Regex("\s+")
' input-execution loop for SQL commands typed on keyboard
While True
' no error at start
Dim erreur As Boolean = False
' request for query
Console.Out.Write(ControlChars.Lf + "Requête SQL (fin pour arrêter) : ")
requête = Console.In.ReadLine().Trim().ToLower()
' finished?
If requête = "fin" Then
Exit While
End If
' the query is broken down into fields
champs = modèle.Split(requête)
' valid request?
If champs.Length = 0 Or Not dicoCommandes.ContainsKey(champs(0)) Then
' error msg
Console.Error.WriteLine("Requête invalide. Utilisez select, insert, update, delete")
' following request
erreur = True
End If
If Not erreur Then
' prepare the Command object to execute the request
sqlCommand.CommandText = requête
' query execution
Try
If champs(0) = "select" Then
executeSelect(sqlCommand)
Else
executeUpdate(sqlCommand)
End If
Catch ex As Exception
' error msg
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
End Try
End If
End While
' freeing up resources
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(0)
End Sub
' execute an update request
Sub executeUpdate(ByVal sqlCommand As OdbcCommand)
' executes sqlCommand, update request
Dim nbLignes As Integer = sqlCommand.ExecuteNonQuery()
' display
Console.Out.WriteLine(("Il y a eu " & nbLignes & " ligne(s) modifiée(s)"))
End Sub
' executing a Select query
Sub executeSelect(ByVal sqlCommand As OdbcCommand)
' executes sqlCommand, select query
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' Using the recovered table
' column display
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' data display
While myReader.Read()
' current line operation
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
' display
Console.WriteLine(ligne)
End While
' freeing up resources
myReader.Close()
End Sub
End Module
Aqui, iremos apenas comentar o que há de novo em comparação com o programa anterior:
- Criamos um dicionário de comandos SQL aceites:
' on construit un dictionnaire des commandes sql acceptées
Dim commandesSQL() As String = {"select", "insert", "update", "delete"}
Dim dicoCommandes As New Hashtable
Dim i As Integer
For i = 0 To commandesSQL.Length - 1
dicoCommandes.Add(commandesSQL(i), True)
Next i
Isto permite-nos então verificar simplesmente se a primeira palavra (fields[0]) da consulta digitada é um dos quatro comandos aceites:
' requête valide ?
If champs.Length = 0 Or Not dicoCommandes.ContainsKey(champs(0)) Then
' msg d'erreur
Console.Error.WriteLine("Requête invalide. Utilisez select, insert, update, delete")
' requête suivante
erreur = True
End If 'if
- Anteriormente, a consulta tinha sido dividida em campos utilizando o método Split da classe RegEx:
Dim modèle As New Regex("\s+")
....
' the query is broken down into fields
champs = modèle.Split(requête)
As palavras na 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 a execução de uma consulta de atualização (INSERT, UPDATE, DELETE). Por isso, devemos realizar uma verificação e executar uma função diferente para cada um destes dois casos:
' préparation de l'objet Command pour exécuter la requête
sqlCommand.CommandText = requête
' exécution de la requête
Try
If champs(0) = "select" Then
executeSelect(sqlCommand)
Else
executeUpdate(sqlCommand)
End If 'try
Catch ex As Exception
' msg d'erreur
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
End Try
A execução de uma consulta SQL pode gerar uma exceção, que é tratada aqui.
- A função executeSelect abrange 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. Cálculo de impostos
Reutilizamos o objeto tax criado num capítulo anterior:
' options
Option Strict On
Option Explicit On
' namespaces
Imports System
Public Class impôt
' data required for tax calculation
' come from an external source
Private limites(), coeffR(), coeffN() As Decimal
' manufacturer
Public Sub New(ByVal LIMITES() As Decimal, ByVal COEFFR() As Decimal, ByVal COEFFN() As Decimal)
' we check that the 3 tablaeux are the same size
Dim OK As Boolean = LIMITES.Length = COEFFR.Length And LIMITES.Length = COEFFN.Length
If Not OK Then
Throw New Exception("Les 3 tableaux fournis n'ont pas la même taille(" & LIMITES.Length & "," & COEFFR.Length & "," & COEFFN.Length & ")")
End If
' it's good
Me.limites = LIMITES
Me.coeffR = COEFFR
Me.coeffN = COEFFN
End Sub
' tAX CALCULATION
Public Function calculer(ByVal marié As Boolean, ByVal nbEnfants As Integer, ByVal salaire As Integer) As Long
' calculating the number of shares
Dim nbParts As Decimal
If marié Then
nbParts = CDec(nbEnfants) / 2 + 2
Else
nbParts = CDec(nbEnfants) / 2 + 1
End If
If nbEnfants >= 3 Then
nbParts += 0.5D
End If
' calculation of taxable income & family quota
Dim revenu As Decimal = 0.72D * salaire
Dim QF As Decimal = revenu / nbParts
' tAX CALCULATION
limites((limites.Length - 1)) = QF + 1
Dim i As Integer = 0
While QF > limites(i)
i += 1
End While
' return result
Return CLng(revenu * coeffR(i) - nbParts * coeffN(i))
End Function
End Class
Adicionamos um novo construtor para inicializar as matrizes de limites, coeffR e coeffN a partir de uma base de dados ODBC:
Imports System.Data
Imports Microsoft.Data.Odbc
Imports System.Collections
...
' builder 2
Public Sub New(ByVal DSNimpots As String, ByVal Timpots As String, ByVal colLimites As String, ByVal colCoeffR As String, ByVal colCoeffN As String)
' initializes the three limit arrays, coeffR, coeffN from
' the contents of the Timpots table in the ODBC DSNimpots database
' colLimites, colCoeffR, colCoeffN are the three columns of this table
' can throw an exception
Dim connectString As String = "DSN=" + DSNimpots + ";" ' base connection chain
Dim impotsConn As OdbcConnection = Nothing ' the connection
Dim sqlCommand As OdbcCommand = Nothing ' the SQL command
' the SELECT query
Dim selectCommand As String = "select " + colLimites + "," + colCoeffR + "," + colCoeffN + " from " + Timpots
' tables to retrieve data
Dim tLimites As New ArrayList
Dim tCoeffR As New ArrayList
Dim tCoeffN As New ArrayList
' attempt to access the database
impotsConn = New OdbcConnection(connectString)
impotsConn.Open()
' create a command object
sqlCommand = New OdbcCommand(selectCommand, impotsConn)
' execute the query
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' Using the recovered table
While myReader.Read()
' the data of the current line are put in the tables
tLimites.Add(myReader(colLimites))
tCoeffR.Add(myReader(colCoeffR))
tCoeffN.Add(myReader(colCoeffN))
End While
' freeing up resources
myReader.Close()
impotsConn.Close()
' dynamic tables are placed in static tables
Me.limites = New Decimal(tLimites.Count) {}
Me.coeffR = New Decimal(tLimites.Count) {}
Me.coeffN = New Decimal(tLimites.Count) {}
Dim i As Integer
For i = 0 To tLimites.Count - 1
limites(i) = Decimal.Parse(tLimites(i).ToString())
coeffR(i) = Decimal.Parse(tCoeffR(i).ToString())
coeffN(i) = Decimal.Parse(tCoeffN(i).ToString())
Next i
End Sub
O programa de teste é o seguinte: recebe como argumentos os parâmetros a serem passados ao construtor da classe de imposto. Após construir um objeto de imposto, calcula o imposto devido:
Option Explicit On
Option Strict On
' namespaces
Imports System
Imports Microsoft.VisualBasic
' test pg
Module testimpots
Sub Main(ByVal arguments() As String)
' interactive tax calculator
' the user enters three data points on the keyboard: married nbEnfants salary
' the program then displays the tax payable
Const syntaxe1 As String = "pg DSNimpots tabImpots colLimites colCoeffR colCoeffN"
Const syntaxe2 As String = "syntaxe : marié nbEnfants salaire" + ControlChars.Lf + "marié : o pour marié, n pour non marié" + ControlChars.Lf + "nbEnfants : nombre d'enfants" + ControlChars.Lf + "salaire : salaire annuel en F"
' checking program parameters
If arguments.Length <> 5 Then
' error msg
Console.Error.WriteLine(syntaxe1)
' end
Environment.Exit(1)
End If 'if
' retrieve the arguments
Dim DSNimpots As String = arguments(0)
Dim tabImpots As String = arguments(1)
Dim colLimites As String = arguments(2)
Dim colCoeffR As String = arguments(3)
Dim colCoeffN As String = arguments(4)
' tax object creation
Dim objImpôt As impôt = Nothing
Try
objImpôt = New impôt(DSNimpots, tabImpots, colLimites, colCoeffR, colCoeffN)
Catch ex As Exception
Console.Error.WriteLine(("L'erreur suivante s'est produite : " + ex.Message))
Environment.Exit(2)
End Try
' infinite loop
While True
' initially no errors
Dim erreur As Boolean = False
' tax calculation parameters are requested
Console.Out.Write("Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :")
Dim paramètres As String = Console.In.ReadLine().Trim()
' anything to do?
If paramètres Is Nothing Or paramètres = "" Then
Exit While
End If
' check the number of arguments in the input line
Dim args As String() = paramètres.Split(Nothing)
Dim nbParamètres As Integer = args.Length
If nbParamètres <> 3 Then
Console.Error.WriteLine(syntaxe2)
erreur = True
End If
Dim marié As String
Dim nbEnfants As Integer
Dim salaire As Integer
If Not erreur Then
' checking parameter validity
' married
marié = args(0).ToLower()
If marié <> "o" And marié <> "n" Then
Console.Error.WriteLine((syntaxe2 + ControlChars.Lf + "Argument marié incorrect : tapez o ou n"))
erreur = True
End If
' nbEnfants
nbEnfants = 0
Try
nbEnfants = Integer.Parse(args(1))
If nbEnfants < 0 Then
Throw New Exception
End If
Catch
Console.Error.WriteLine(syntaxe2 + "\nArgument nbEnfants incorrect : tapez un entier positif ou nul")
erreur = True
End Try
' salary
salaire = 0
Try
salaire = Integer.Parse(args(2))
If salaire < 0 Then
Throw New Exception
End If
Catch
Console.Error.WriteLine(syntaxe2 + "\nArgument salaire incorrect : tapez un entier positif ou nul")
erreur = True
End Try
End If
If Not erreur Then
' parameters are correct - tax is calculated
Console.Out.WriteLine(("impôt=" & objImpôt.calculer(marié = "o", nbEnfants, salaire).ToString + " F"))
End If
End While
End Sub
End Module
A base de dados utilizada é uma base de dados MySQL com o nome DSN mysql-imports:
C:\mysql\bin>mysql --database=impots --user=admimpots --password=mdpimpots
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.49-max-debug
Type 'help' for help.
mysql> show tables;
+------------------+
| Tables_in_impots |
+------------------+
| timpots |
+------------------+
mysql> select * from timpots;
+---------+--------+---------+
| limites | coeffR | coeffN |
+---------+--------+---------+
| 12620 | 0 | 0 |
| 13190 | 0.05 | 631 |
| 15640 | 0.1 | 1290.5 |
| 24740 | 0.15 | 2072.5 |
| 31810 | 0.2 | 3309.5 |
| 39970 | 0.25 | 4900 |
| 48360 | 0.3 | 6898 |
| 55790 | 0.35 | 9316.5 |
| 92970 | 0.4 | 12106 |
| 127860 | 0.45 | 16754 |
| 151250 | 0.5 | 23147.5 |
| 172040 | 0.55 | 30710 |
| 195000 | 0.6 | 39312 |
| 0 | 0.65 | 49062 |
+---------+--------+---------+
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 :