ADO - Usando  Stored Procedures - IV

Voltamos a falar dos procedimentos Armazenados ( Stored Procedures ou SP , também consultas armazenadas ou QueryDefs ), pois tem muita gente que ainda não sabe usar ou não conhece o poder das SP. Por isso vou rever alguns conceitos básicos... Não se inquiete... 

Quando você cria o seu projeto no Visual Basic e resolve usar SQL para fazer o tratamento dos seus dados , com certeza , fez um escolha certa. Agora este artigo vai lhe mostrar que você pode ir além aperfeiçoando ainda mais o seu código com reflexos na produtividade , manutenção e escalonabilidade do seu sistema. Mas aonde eu estou querendo chegar ?

Sendo o mais direto possível eu já vou lhe dizer que os Procedimentos Armazenados são mais eficientes que as instruções SQL que você embute em seu código. Vamos começar com algumas comparações para você ter uma idéia:

Procedimentos Armazenados Instruçoes SQL ( Consultas SQL )
São mais rápidos pois são compilados previamente pelo mecanismo do banco de dados São mais lentos pois precisam ser interpretados em tempo de execução
Fácil manutenção  , pois qualquer alteração , apenas vai requerer que você altera o procedimento no banco de dados e o redistribua  Manutenção mais complexa, pois você vai ter que abrir o seu código , fazer as alterações , recompilar o seu projeto e redistribuí-lo novamente.
Relativamente Flexível com a utilização de parâmetros. Muito flexível com a utilização de consultas parametrizadas e variáveis.
Leitura fácil devido a sua estrutura enxuta.  Mais difícil de ler devido as concatenações , variáveis e outros caracteres.

Como os Procedimentos Armazenados residem no banco de dados , a manutenção fica centralizada , e você não tem que abrir o código do seu projeto , bastando apenas fazer as alterações e distribuir o banco de dados.

Criando um Procedimento Armazenado com o Microsoft Access 2000

Para quem esta começando pode parecer estranho ter uma instrução armazenada no banco de dados , mas é justamente ai que reside a grande vantagem dos Procedimentos Armazenados.  Se você conhece bem a sintaxe SQL pode criar os seus procedimentos armazenados para realizar as tarefas usuais de manutenção dos seus dados : Incluir , alterar, excluir e consultar registros.

A maneira mais fácil de criar um procedimento armazenado é usar o Microsoft Access e fazer uso dos recursos visuais e gráficos que ele nos oferece. Como exemplo vamos criar um procedimento armazenado usando o Access 2000.  

Vamos usar o banco de dados Biblio.mdb e criar um procedimento para exibir os dados da tabela Publishers. Nosso procedimento vai usar um critério para exibir os dados ; o critério será o campo state. Vamos dar um nome ao nosso procedimento , que tal ConsultaEditoresEstado.; vamos exibir os campos Name e Company Name  Abaixo temos a estrutura da tabela Publishers:
A tabela Publishers : Vamos exibir os campos Name e Company Name e selecionar os registros pelo campo State.

1-) Inicie o Microsoft Access 2000 e abra o banco de dados Biblio.mdb ; Selecione o menu Consulta e a seguir a opção : Criar consulta no modo estrutura
O banco de dados Biblio.mdb : Exibindo as consultas ; note que ja temos uma consulta armazenada chamada All Titles

2-) Na janela : Mostrar Tabela , selecione a tabela Publishers e clique no botão adicionar . Feito isto clique no botão Fechar pois vamos trabalhar somente com a tabela Publishers.
A janela onde vamos incluir as tabelas que iremos usar para construir nosso Procedimento Armazenado

3-) Na janela a seguir você verá a tabela Publishers com os seus campos ; Clique duas vezes no campo Name e Company Name (Os campos que queremos incluir).
Os campos selecionados sendo exibidos na ordem em que serão extraidos.

Perceba que os campos aparecem na ordem que foram inseridos.

4-) Falta informar o critério ( este critério será o nosso parâmetro) . Selecione o campo State .

5-) Agora no menu principal selecione a opção Consulta|Parâmetros e informe o nome do parâmetro(comece com a letra p)  e o tipo de dados. Utilizamos o nome pestado. Para completar , na linha Critério informe sob a coluna State o critério : =[pestado]. (Ver abaixo)

6-) Selecione a  Exibir no menu e a seguir a opção Modo SQL e você verá a consulta com o parâmetro exibida no formato SQL:

7-) Se você executar a consulta agora , menu Consulta|Excecutar ira receber uma janela solicitando o parâmetro para seleção . Informe MA e tecle Enter você deverá obter o seguinte:

8-) Agora é só dar um nome a sua consulta e salvá-la. Antes disto vamos fazer uns ajustes...

Fiz tudo isto somente para mostrar que você pode armazenar uma consulta no seu banco de dados Access. Mas o que queremos é usar esta consulta no Visual Basic , então vamos fazer alguns ajustes.  Primeiro eu tenho que ter em mente qual a consulta que quero criar(quais registros vou exibir, qual tabela usar, etc...) ;  depois tenho que definir quais parâmetros eu vou usar para selecionar os registros.Para este nosso caso no visual Basic usando a sintaxe SQL teriamos :

SELECT Name , [Company Name] From Publishers Where State = pestado

Então esta seria a consulta que iríamos armazenar no banco de dados. Vamos pois salvá-la com o nome indicado : ConsultaEditoresEstado.

Como usar esta consulta ?  Vamos mostrar como usá-la na DAO e depois com ADO.

I-) Usando o Procedimento Armazenado ( Consulta Parametrizada) na DAO. (Again)

- Inicie um novo projeto no Visual Basic e insira no formulário padrão os controles: listbox , command button , text box e label. Conforme Layout a seguir:

2-) Insira o seguint código na seção General Declarations

Private db As Database
Private qd As QueryDef
Private rs As Recordset

3-) Agora insira o seguinte código no evento Load do formulário: Estamos definindo a consulta - ConsultaEditoresEstado - já criada e armazenada no banco de dados como a consulta a ser utilizada.

Private Sub Form_Load()

Set db = OpenDatabase("c:\teste\biblio_2001.mdb")
Set qd = db.QueryDefs("ConsultaEditoresEstado")

End Sub

4-) Insira o código a seguir no evento Click do botão de comando: Invocamos a procedure ExibeEditores.

Private Sub Command1_Click()
If Text1.Text <> "" Then
   ExibeEditores
 End If
End Sub

4-) A seguir insira o seguinte código na função ExibeEditores:

Private Sub ExibeEditores()
qd.Parameters("pestado").Value = Text1.Text
Set rs = qd.OpenRecordset

If rs.RecordCount = 0 Then
   MsgBox "Não há dados para o criterio ! "
   Exit Sub
End If

While Not rs.EOF
   List1.AddItem rs("name")
   rs.MoveNext
Wend

End Sub

- Passamos o valor informado na caixa de texto Text1.text como sendo o parâmetro para nossa consulta armazenada e a seguir geramos um recordset com base nesta consulta usando o parâmetro informado. Se houver dados a serem retornados eles serão exibidos em um listbox.

II-) Usando Procedimentos Armazenados ( Consultas Parametrizadas) com ADO.

Tudo bem , você não quer mais usar DAO ( dependendo da ocasião pode ser uma péssima atitude ). Então vamos mostrar como usar  o nosso Procedimento Armazenado usando ADO. Antes um pouco de teoria:

Um objeto Parameter representa um parâmetro ou argumento associado a um objeto  Command que esta baseado em uma consulta parametrizada ou procedimento armazenado. Abaixo temos uma representação do objeto Command , a coleção Parameters e o objeto Parameter

Sabendo o nome dos parâmetros associados aos procedimentos armazenados que desejamos chamar podemos usar o método CreateParameter para criar objetos Parameter , e , usando o método Append incluir estes parâmetros na coleção Parameters. Com isto podemos definir e retornar valores sem ter que usar o método Refresh na coleção Parameters para retornar o valor do parâmetro a partir do provedor.

O método CreateParameter cria um objeto Parameter com as propriedades especificas. Sua sintaxe é a seguinte: 

Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

Onde:

Após criar o objeto Parameter você tem que incluir o parâmetro usando o método Append. A sintaxe é a seguinte: 

collection.Append object

Criando os procedimentos armazenados

Vamos mostrar como usar os procedimentos armazenados com ADO . Para isto vamos criar uma tabela chamada Clientes no banco de dados Biblio_2001.mdb ( este é o banco de dados Biblio.mdb renomeado ).

1-) Inicie o Access e abra o banco de dados Biblio.mdb ( no meu caso Biblio_2001.mdb )

2-) Selecione o objeto Tabela e a opção - Criar tabela no modo Estrutura.

3-) Crie a tabela - Clientes - com a estrutura abaixo:

  

4-) Vamos agora criar três procedimentos armazenados e salvá-los no banco de dados Biblio.mdb.

a-) Criando um procedimento armazenado para INCLUIR registros na tabela Clientes chamado -  Incluir_Dados

INSERT INTO CLIENTES ( CODIGO, NOME) VALUES ([@codigo],  [@nome]);

Se você executar esta consulta no Access irá obter duas janelas solicitando o codigo e nome para incluir no banco de dados.

 Salve este procedimento com o nome de : Incluir_Dados.

b-) Criando um procedimento armazenado para EXCLUIR registros na tabela Clientes chamado -  Excluir_Dados

Aqui o procedimento é idêntico ao item anterior , somente vamos alterar o tipo de consulta a criar ; no menu Consulta , selecione Consulta Exclusão  e defina a consulta conforme abaixo:

No menu Exibir | Modo SQL você terá:

DELETE Clientes.Codigo, Clientes.Nome
FROM Clientes
WHERE (((Clientes.Codigo)=[@Codigo]));

Salve este procedimento com o nome - Excluir_Dados

c-) Criando um procedimento armazenado para ATUALIZAR registros na tabela Clientes chamado -  Atualizar_Dados

Da mesma forma repita os passos anteriores e no menu Consulta selecione a  opção - Consulta Atualização . E defina a consulta conforme abaixo:

 

No menu Exibir | Modo SQL você verá:

UPDATE Clientes SET Clientes.Nome = [@nome]
WHERE (((Clientes.Nome)=[@codigo]));

Salve o procedimento com o nome de Atualiza_Dados

d-) Criando um procedimento armazenado para SELECIONAR registros na tabela Clientes chamado -  Seleciona_Dados

Novamente como no item anterior repita os passos e no menu Consulta selecione a  opção - Consulta Seleção . Defina a consulta como abaixo:

No menu Exibir | Modo SQL teremos:

SELECT Clientes.Codigo, Clientes.Nome
FROM Clientes
WHERE (((Clientes.Codigo)=[@codigo]));

Salve o procedimento com o nome de Seleciona_Dados.  Ao final no banco de dados vamos ter as 4 consultas armazenadas indicas assim:

Agora que já temos os procedimentos armazenados basta usá-los no VB . Vamos la´...

- Inicie um novo projeto no VB e no formulário padrão insira duas caixas de textos e sete botões de comando como no layout abaixo:

Obs: Este projeto é apenas um exemplo em que focamos a utilização das Stored Procedures via código com ADO.

Não me preocupei com a interface nem com a critica aos dados. Se você pretender usar o exemplo , deverá ajustar o código para funcionar com o tratamento de erros e com a critica aos dados.

Na seção General Declarations inclua o código:

Private con As New Connection
Private cmd As New Command
Private rst As New Recordset
Private codigo As String

- O código do evento Load do formulário : Ajuste o nome e a localização do banco de dados para o seu caso particular . !

Private Sub Form_Load()
   'abre a conexao com o banco de dados
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\teste\biblio_2001.mdb"
    rst.Open "Select codigo, nome from Clientes", con, adOpenKeyset, adLockPessimistic
    exibe_registros
End Sub

- O código do botão Incluir , onde vamos usar o procedimento armazenado - Inclui_Dados. Ao clicar neste botão , limpamos as caixas de texto e habilitamos o botão Gravar que invocara a rotina Inclui_regs.

Private Sub Command1_Click(Index As Integer)
Select Case Index
Case 0 'incluir
   limpa_ctrls
   txtnome.SetFocus
   Command2.Visible = True
Case 1 'excluir
   exclui_regs
Case 2 'atualizar
   atualiza_regs
Case 3 'selecionar
    If Command1(3).Caption = "&Filtro" Then
        codigo = InputBox("Informe o Codigo do Cliente", "Atualizar", 1)
       If codigo <> "" Then
          codigo = Val(codigo)
          seleciona_regs
          Command1(3).Caption = "&Todos"
       Else
          Exit Sub
       End If
    ElseIf Command1(3).Caption = "&Todos" Then
       exibe_todos
       Command1(3).Caption = "&Filtro"
    End If
End Select
End Sub

- Abaixo temos o código do procedure Inclui_regs. Usamos aqui o procedimento armazenado Inclui_Dados que é invocado quando o usuário clica no botão Gravar ; criamos o parâmetro usando o conteúdo da caixa de texto para inserir o dado na tabela. Como o campo codigo é autonomeração o sistema irá automaticamente incrementar o valor para este campo.

Private Sub inclui_regs()

Set cmd = New Command
Set par = New Parameter

With cmd
  Set .ActiveConnection = con
   .CommandText = "Inclui_Dados"
   .CommandType = adCmdStoredProc
    Set par = .CreateParameter("@nome", adVarChar, , 50, txtnome)
  .Parameters.Append par
  .Execute

End With

rst.MoveLast
exibe_registros

End Sub		 Private Sub Command2_Click()
   inclui_regs
   Command2.Visible = False
End Sub
Código da procedure Inclui_regs Código do botão de comando Gravar

- Abaixo o código da procedure Exclui_regs : Ela usa o procedimento Armazenado Excluir_Dados , usando como parâmetro o código do cliente informado na caixa de texto txtcodigo.

Private Sub exclui_regs()
Set cmd = New Command
Set par = New Parameter

With cmd
    Set .ActiveConnection = con
    .CommandText = "Excluir_Dados"
    .CommandType = adCmdStoredProc

     Set par = .CreateParameter("@codigo", adInteger, , , txtcodigo)
     .Parameters.Append par

     .Execute

End With
rst.MovePrevious
exibe_registros

End Sub

- O código da procedure Atualiza_regs  , a seguir ,  usa o procedimento armazenado Atualiza_Dados e os parâmetros : txtnome e txtcodigo para atualizar os dados de um registro.

Private Sub atualiza_regs()
Set cmd = New Command
Set par = New Parameter

With cmd
   Set .ActiveConnection = con
   .CommandText = "Atualiza_Dados"
   .CommandType = adCmdStoredProc

   Set par = .CreateParameter("@nome", adVarChar, , 50, txtnome)
   .Parameters.Append par

   Set par = .CreateParameter("@codigo", adInteger, , 5, txtcodigo)
   .Parameters.Append par

   .Execute

End With
exibe_registros

End Sub

- O código associado ao evento click do botão - Filtro - é o seguinte:

If Command1(3).Caption = "&Filtro" Then
    codigo = InputBox("Informe o Codigo do Cliente", "Atualizar", 1)
    If codigo <> "" Then
           codigo = Val(codigo)
           seleciona_regs
           Command1(3).Caption = "&Todos"
    Else
           Exit Sub
    End If
ElseIf Command1(3).Caption = "&Todos" Then
    exibe_todos
    Command1(3).Caption = "&Filtro"
End If

Ele solicita um número relativo ao código do cliente para Filtrar os dados com base neste valor. Se um valor válido for informado invocamos a procecure Selecina_regs , cujo código é:

Private Sub seleciona_regs()
Set cmd = New Command
Set par = New Parameter

With cmd
    Set .ActiveConnection = con
    .CommandText = "Seleciona_Dados"
    .CommandType = adCmdStoredProc

    Set par = .CreateParameter("@codigo", adInteger, , , codigo)
    .Parameters.Append par

    Set rst = .Execute

End With
exibe_registros

End Sub

O procedimento armazenado Seleciona_Dados é utilizado com o parâmetro codigo ( informado no evento anterior ). Aqui temos uma Consulta Seleção com um parâmetro.

Após Filtrar os dados , lembre-se que o recordset gerado terá apenas um registro. Para voltar a exibir todos os registros clique no botão Todos ( o mesmo botão - Filtro com a propriedade caption alterada.) que o usa o seguinte código para gerar um recordset com todos os registros do arquivo:

Private Sub exibe_todos()
Set cmd = New Command

With cmd
   Set .ActiveConnection = con
   .CommandText = "Exibe_Todos"
   .CommandType = adCmdStoredProc

   Set rst = cmd.Execute

End With
exibe_registros

End Sub

Aqui usamos um procedimento armazenado - Exibe_Todos - sem parâmetros; apenas  uma Consulta Seleção.

Para encerrar o projeto o código que exibe os registros nas caixas de texto e  o código para navegar pelos registros:

Private Sub exibe_registros()

If rst.BOF Or rst.EOF Then
    MsgBox "Chegamos ao Inicio/Fim do arquivo !"
    If rst.BOF And rst.EOF Then
         MsgBox "Não há dados no arquivo ! "
          Exit Sub
    End If
   If rst.BOF Then rst.MoveFirst
   If rst.EOF Then rst.MoveLast
Else
   txtcodigo = rst("codigo")
   txtnome = rst("nome")
End If
End Sub		 Private Sub Command3_Click(Index As Integer)
Select Case Index
  Case 0
         rst.MovePrevious
Case 1
         rst.MoveNext
End Select
exibe_registros
End Sub
Código para exibir os registros nas caixas de texto Código para navegar pelos registros

Ufa ! acho que agora terminei ...     

CopyRight-2001  - José Carlos Macoratti