Programação de Banco de Dados - parte 5

Este é o quinto artigo e vamos abordar o componente ADO. Não falaremos de tudo que a biblioteca ADO oferece mas com o que vai exposto será possível desenvolver aplicações completas que interagem com banco.

por Renato Mattos

Este é o quinto artigo e vamos abordar o componente ADO. Não falaremos de tudo que a biblioteca ADO oferece mas com o que vai exposto será possível desenvolver aplicações completas que interagem com banco.

ADO

Agora chegou o momento de entrar realmente na programação de banco de dados.Neste artigo exploraremos o componente ADO ( Active Dta Objects - Objeto de Dados Ativos).

O ADO é um componente para acesso a bancos de dados.Ele expõe alguns objetos muito flexíveis que expõem suas próprias propriedades, métodos e eventos.

Para usar objetos ADO em um aplicativo, você deve primeiro adicionar uma referencia ao componente ADO.Inicie um projeto Standard EXE e depois selecione Project->References.Na janela References, localizeMicrosoft ActiveX Data Objects 2.x library e marque a sua caixa de seleção.

A utilização do ADO basicamente consiste em declarar 2 variáveis.Uma responsável unicamente pela conexão, e a outra pelas consultas do banco e para isso usamos duas sub-classes da classe ADO que são "Connection" e "Recordset" respectivamente.

Existem diversas formas de se trabalhar com o ADO, já vi vários códigos usando ADO e de forma diferente.Vou mostrar nesse artigo a forma mais simples considerada por mim de usar o ADO.

Bem na programação com Banco de Dados consistem basicamente em:

• Abrir Conexão com Banco.
• Executar consultas, Inserções, Alterações e Deleções.
• Fechar Conexão com Banco.

Neste caso vamos ver cada passo desse processo.Neste artigo não vamos abordar a execução de trigger ou de procedimentos armazenados que tem parâmetros para serem executados.

Declaração das Variáveis do ADO

Neste caso basta declarar uma única variável de conexão e uma única variável de consulta (Recordset). Declare as variáveis do banco em um Modulo da seguinte forma

Public pVarConBanco as New ADODB.Connection (Variável de conexão)
Public pVarRegBanco as New ADODB.Recordset (Variável de Consulta)

Além de efetuar a conexão a variável do tipo ADODB.Connection (abordada a partir de agora somente por Connection) também executa comandos SQL com exceção do comando SELECT, pois essa variável não é do tipo Collection, não tem propriedades para tratamento de dados.No entanto não vamos usar a variável Connection para execução de comandos SQL, a variável connection somente será usada para a conexão com o banco.

Conexão com o Banco (CONNECTION)

Pela propriedade "ConnectonString do objeto connection informa a string de conexão que difere de banco para banco. Neste caso um projeto bem planejado a migração de um banco para um mais potente é extremamente fácil, bastando mudar um linha de código que é a string de conexão.

String de Conexão para Access

   pVarConBanco.ConnectionString = _
"provider = microsoft.jet.oledb.4.0;" & _
"data source=" + PathBanco + NomeBanco + ";jet oledb:database password=" + SenhaBanco

É usado o Jet 4.0 que da suporte para o Access 2000 e para os demais. (Não sei se da suporte para o Access XP)

As variáveis são o seguinte:

PathBanco = ao caminho do banco de dados.
NomeBanco= ao nome da banse de dados.
SenhaBanco= senha do banco de dados.

OBS: Caso o banco não seja protegido por senha basta colocar:

   pVarConBanco.ConnectionString = _
"provider = microsoft.jet.oledb.4.0;" & _
"data source=" + PathBanco + NomeBanco

Após informa a string de conexão basta chamar o evento OPEN que estabelece a conexão com o banco.

   pVarConBanco.Open

Não é possível abrir duas conexões ao mesmo tempo usando a mesma variável de connection.

Para fechar a conexão com o banco:

pVarConBanco.Close

Neste caso o evento Close apenas fecha a conexão com o banco, no entanto a variável continua na memória.Neste caso você pode usar Set pVarConBanco= Nothing para liberar da memória a variável.Não aconselho a fechar o banco somente usando Set Nothing.

Falaremos somente isso sobre a classe Connection, sendo que essa classe possui diversos outros comandos e especificações de conexão principalmente para banco cliente servidor no entanto para um primeira visão isso é o suficiente para criação de aplicações que usem o Banco Access , que vai ser o banco usado nesses artigos.

Uma função de conexão com banco de dados pode ser implementada dessa forma:

Public Function (ByVal NomeBanco As String,ByVal Endereco_Banco  As String,ByVal Senha_Banco As String) As 
Boolean
"Rotina generica que conecta e abre o banco em ADO
On Error GoTo T_Erro
   Conexao_Banco = False
   pVarConBanco.ConnectionString = _
     "provider = microsoft.jet.oledb.4.0;" & _
     "data source=" + Endereco_Banco + NomeBanco + ";jet oledb:database password=" +   Senha_Banco
   pVarConBanco.Open
   Conexao_Banco = True
   Exit Function
T_Erro:
  MSG_ERRO "Conexao_Banco"
  End
End Function 

Onde recebe o nome, caminho e senha do banco retornando verdadeiro caso a conexão seja sucedida.Dessa forma você pode criar um modulo único com as declarações das variáveis Connection e Recordset e com a função de conexão e usar em todos os seus projetos.

Na chamada da função caso retorne False você pode abortar a execução do programa com Unload ou END.

No tratamento de erro fiz um procedimento chamado MSG_ERRO que uso em todos os tratamento de erros de minhas aplicações, onde tem como parâmetro o nome do procedimento.

Public Sub MSG_ERRO(Procedimento As String)
MsgBox "O seguinte erro aconteceu : " + Str(Err) + Chr(10) + Error(Err) + Chr(10) + "Comunique ao Suporte 
Técnico." + Chr(10) + "Rotina " + Procedimento, vbCritical, Cabecalho
End Sub

Manipulando o Banco de Dados (RECORDSET)

Através da variável do tipo Recordset podemos manipular banco de dados.Pelo evento "OPEN" é possível executar qualquer comando SQL. Ao usar o Recordset na verdade esta se criando um cursor no banco responsável pela manipulação e recuperação dos dados. Se utilizar somente o evento "Open" sem especificar suas propriedades então todas as propriedades serão default e o código funciona normalmente.Mas se tratando de manipulação do banco é necessário criar o cursor certo para cada tipo de comando SQL e para que e como você vai manipular os dados de retorno.Para aplicações multiusuario o controle de concorrência se da pela configuração dessas propriedades.

Antes de começarmos a ver a pratica da classe Recordset vamos ver suas propriedades. Propriedades do Cursor

CursorLocation

Define em qual lado será aberto o cursor, se do lado cliente ou do lado do servidor.Neste caso válido somente para banco cliente servidor. Ao abrir um cursor ao lado do cliente as linhas qualificadas são movidas do servidor para o cliente, e a manipulação dos dados fica a cargo da maquina cliente.Tirando parte do processamento do lado do servidor.Isso é bastante importante tratando de aplicações onde se manipulam grandes quantidade de dados e para as consultas em que necessitam de grande processamento, dessa forma você pode manipular o processamento do servidor para não sobrecarrega-lo

pVarRegBanco.CursorLocation = adUseServer (definido do lado do servidor).
pVarRegBanco.CursorLocation =adUseClient (definido do lado do cliente).

OBS: No access a maior parte do processamento se da ao lado do servidor independente da propriedade CursorLocation, pois o Access não é banco cliente servidor.Com isso aplicações em rede com Access dependendo do fluxo de acesso não são aconselháveis, pois o fluxo de dados na rede é grande podendo comprometer a banda de rede.Mas isso se for um número muito alto de dados. Mesmo assim a definição do CursorLocation no lado do Servidor é importante mesmo para o Access devido ao controle de concorrência visto a seguir.

CursorType

Propriedade que define o tipo de cursor a ser aberto:

AdOpenDynamic = melhor tipo de cursor e também o mais caro (exige mais do servidor para mante-lo). Consulta abertas com esse tipo de cursor as linhas do banco não são lidas e sim acessadas dessa forma é possível pegar atualizações, deleções e INSERÇÕES de outros usuários. O Access não da suporte para esse tipo de cursor.

AdOpenForwardOnly = este cursor é somente de avanço e também o cursor default(caso você não especifique o tipo de cursor esse será definido automaticamente).Este cursor não permite retornar nas linhas acessadas só pode ser percorrido para frente.

AdOpenKeyset = este cursor associa um número para cada linha acessada, ao ser solicitado a linha ele acessa o banco, dessa forma com esse cursor é possível pegar alterações e deleções numa aplicação multiusuario.Esse seria o melhor cursor para o Access.

adOpenStatic = este cursor efetua uma copia do banco como se fosse uma foto (snapshot). Não pega alterações nem deleções.

Controle de Concorrência (LockType)

Propriedade que define o tipo de bloqueio do registro:

AdLockReadOnly= Use este tipo de bloqueio para abrir um cursor somente leitura.Neste caso não será possível alterar ou deletar o registro.Normalmente este cursor é utilizado somente para exibir os dados para o usuário.

AdLockOptimistic= Esta é a técnica mais comum de bloqueio.O Banco de dados coloca um bloqueio na(s) linha(s) enquanto a(s) atualiza.

AdLockPessimistic= Esta é a forma mais rígida de controle de concorrência.A linha é bloqueada enquanto o cursor estiver aberto.

AdLockBatchOptimistic= Alguns aplicativos podem descarregar um conjunto de registro para o cliente, cortar a conexão com o servidor, atualizar várias linhas e depois tentar gravar todas as alterações no banco de dados estabelecendo nova conexão.Este método é usado com conjuntos de registros desconectados.

O controle de concorrência determina o que acontecerá quando dois usuários tentarem atualizar a mesma linha ao mesmo tempo.No entanto não basta configurar as propriedades do cursor para construir aplicações multiusuarios, primeiro deve ter a política de concorrência do aplicativo.Antes de iniciar um projeto multiusuário deve-se traçar a estratégia de concorrência, primeiro verificar se existe a concorrência ou se o sistema é somente para consulta (o que é pouco provável) deva forma após essa avaliação analisar o tipo de cursor que melhor atenda a sua concorrência.

Por exemplo:

Dois usuários acessam o mesmo registro numa interface que é possível alterar o registro.Neste caso definindo de forma precisa os cursores somente um usuário vai conseguir alterar, caso isso seja ao mesmo tempo ou então o primeiro usuário altera, e o segundo quando for alterar vai ser a partir dos dados iniciais (sem a alteração do primeiro usuário), ou seja, não existe política de concorrência e um aplicativo assim seria uma "Torre de Babel".

O que poderia ser feito acima seria abrir o cursor com um bloqueio pessimista, ou seja, a linha acessada fica bloqueada enquanto estiver aberto o cursor. Dessa forma somente um usuário iria ter acesso ao registro para editá-lo.Mas esse tipo de bloqueio é perigoso, caso o usuário abra o registro e depois vai tomar um cafezinho o registro fica preso para todos os demais usuários.

Uma solução que proponho para um caso assim seria fazer uma interface de visualização do registro, com um botão para alterar o registro abrindo assim a janela que permite alterar.Dessa forma o registro no banco teria um campo que indicaria se o registro está sendo editado ou não.Assim se dois usuários ao mesmo tempo tentarem editar o mesmo registro somente para um deles a tela de edição será aberta, para o outro teria uma mensagem de bloqueio que pode ser tratada com uma mensagem amigável tipo: Registro sendo editado no momento. Com essa solução o registro não fica bloqueado para leitura.

Para entender melhor, suponha um sistema com duas janelas.Na primeira mostra em uma listview todos os clientes cadastrados, então ao selecionar um cliente abre outra janela com os dados desse cliente.Normalmente ao visualizar esses dados o programador aproveita a mesma janela para alterações colocando um botão "Alterar". Se neste caso ao abrir o cursor para carregar os dados do cliente for um cursor com bloqueio pessimista o registro ficará bloqueado inclusive para leitura pois não será possível abrir dois cursores pessimista para o mesmo registro.Por isso deve-se tomar cuidado ao usar cursores pessimista.

Para um caso desse tipo, basta o programador mudar os tipos de cursores, não seria necessário alterar a interface. Quando o usuário selecionar o registro na primeira janela abre um cursor somente leitura, carrega todos os campos e fecha o cursor. No botão "Alterar" abre o cursor novamente neste caso com bloqueio otimista efetua a alteração e fecha o cursor.Assim o registro praticamente não ficaria bloqueado, somente no instante do UPDATE.

Nos próximos artigos vou colocar exemplos de códigos explicados, abaixo posto um exemplo simples somente para não ficarmos só na teoria.

Em Modulo

Public Const pConsNomeBanco As String = "Banco.mdb" "Nome do Banco de Dados
Public Const pConsSenhaBanco As String = "minhasenha" "Senha do Banco de Dados
Public pVarConBanco as New ADODB.Connection
Public pVarRegBanco As New ADODB.Recordset
Public pVarPathBanco As String "Caminho do Banco de Dados

Na função Main ou no Load do primeiro Form da aplicação

If  Not Conexao_Banco(pConsNomeBanco,pVarPathBanco,pConsSenhaBanco) Then End

Exemplo para Leitura:
With pVarRegBanco
        .CursorLocation = adUseServer
        .Open "Select * from Grupo_Produto order by IdGrupo",pVarConBanco, adOpenStatic, adLockReadOnly
 End With

Primeiro define se o cursor será do lado cliente ou do servidor.Na segunda linha pelo evento OPEN coloca o comando SQL, depois o variável de conexão seguido do Tipo do cursor e do Tipo de bloqueio usado. Esse tipo de consulta seria para carregar um Grid.No termino de carregar o Grid deve fechar o cursor.

OBS: Evite ao máximo ficar com o cursor aberto, sempre tenha o controle de abrir e fechar o cursor, manter cursor aberto além de caro para o servidor pode numa queda de luz aumenta as chances de corromper a base de dados.

Exemplo para Update:

With pVarRegBanco
 	.CursorLocation = adUseServer
.Open "Select IdMesa From ContaMesa where IdMesa=" & RegSelecionado & "", pVarConBanco, adOpenStatic, 
adLockOptimistic
        If Not .EOF Then"Se retorna verdadeiro então não achou o registro
            .MoveFirst "Posiciona para o primeiro registro retornado
            Do While Not (.EOF) "Enquanto não chegar no final
                !IdMesa = 0 " Efetua a alteração de IdMesa para 0
                .Update "Efetua  o Update para o registro acessado
                .MoveNext "Avança com o cursor.
            Loop
        End If
        .Close "Fecha o cursor
End With

Repare que neste exemplo o tipo do bloqueio foi adLockOptimistic pois é para efetuar alterações.Essa é apenas uma forma de alterar registro pelo ADO, poderia simplesmente colocar a instrução SQL UPDATE para isso, no entanto caso a instrução UPDATE não encontrar o registro não acontece nada, nem mensagem de erro.Fazendo a alteração igual ao exemplo acima é possível saber se te fato houve ou não alteração.O código acima apenas trata o erro se não achar o registro, mas caso o registro não seja achado não retorna uma mensagem para o usuário.

O mesmo problema ocorre no caso da Deleção, se for através da instrução SQL , caso o registro não exista não ocorre nada.Por isso eu não uso as instruções UPDATE e DELETE do SQL, somente uso SELECT e INSERT.As alterações e deleções faço através das propriedades do ADO

Exemplo de Deleção:

With pVarRegBanco
 	.CursorLocation = adUseServer
        .Open "Select IdMesa From Mesa where IdMesa=" & RegSelecionado & "", pVarConBanco, adOpenStatic, 
adLockOptimistic
        .Delete adAffectCurrent
        .Close
    End With

A propriedade adAffectCurrente seria deletar o cursor acessado.Caso a consulta possa retornar mais de uma registro faça um Loop como o do exemplo da alteração colocando .Delete adAffectCurrente no lugar do .Update.

Outro detalhe as consultas SQL não foram abertas usando o curinga (*), se utilizar o curinga não tem problema o código funcionária da mesma forma, no entanto iria retornar campos desnecessário, resultando maior trafego na rede sem necessidade.Por isso evite abrir cursores com curinga (*) desnecessariamente, use o curinga quando de fato for para retornar todos os campos de um registro, como por exemplo, exibir um registro para o usuário.

Um Exemplo de Inserção:

    With pVarRegBanco
        .CursorLocation = adUseServer
        .Open "Insert Into Mesa values(" & Trim(FTxtIdMesa.text) & "," & 
CmbFumantes.ItemData(CmbFumantes.ListIndex) & ","" & Trim(FTxtLugares.text) & "" ,0,1,"" & 
Trim(TxtLocalizacao.text) & "" )", pVarConBanco.Conection, adOpenStatic, adLockOptimistic
    End With

Bem simples não? Basta usar o comando SQL, com a atenção para o tipo de bloqueio, neste caso "sempre"o otimista.Existe outra forma de incluir registro que seria através das propriedades do ADO usando o ADDNEW, no entanto não acho bom usá-lo, pois ele cria um registro no caso com todos os campos com NULL e depois atribui para cada campo os valores (igual ao UPDATE) e no final chama o evento do ADO .UPDATE, ou seja, a inserção pelo ADO ele simplesmente cria um novo registro e depois o altera.Caso a luz acabe justamente quando tiver sendo atribuído os valores aos campos você terá no seu banco um registro com campos NULL mesmo que tenha configurado para o campo não receber nulo.Sendo assim acho muito mais eficaz usar a instrução SQL INSERT para inserir.

No próximo artigo continuamos a abordar o ADO com mais exemplos, e também vamos abordar as transações.