VB - Exibindo dados no layout Mestre - Detalhes

Recebo muitas consultas sobre como exibir dados relacionados entre tabelas , o clássico layout Mestre/Detalhe). Na verdade a dificuldade as vezes não está em como fazer isto no VB mas em ter as tabelas normalizadas e prontas para que isto seja possível.

Se você estiver tentando exibir dados entre tabelas relacionadas e elas não estão relacionadas fica difícil não é mesmo ...

Vou mostrar como fazer isto usando ADO , depois DAO e a seguir no VB.NET. Lembrando que existem muitas outras formas de realizar esta tarefa.

Preparando as tabelas

Eu não vou perder tempo criando e normalizando tabelas vou pegar o banco de dados Nwind.mdb que já vem com o Access e com o VB e usar duas tabelas contidas neste banco de dados e que já estão relacionadas. Abaixo estou exibindo o relacionamento entre as tabelas do banco de dados Nwind.mdb.

Vou dar um zoom nas tabelas Pedidos e Detalhes do Pedido para ver o relacionamento entre elas:

- Existe um relacionamento entre a tabela Detalhes do Pedido e Pedidos do tipo : UM para MUITOS ( 1-N )

- Temos a coluna NúmeroDoPedido como chave primária nas duas tabelas

- A coluna NúmeroDoPedido estão relacionadas entre as duas tabelas

Para cada registro na tabela Pedidos existem n registros na tabela Detalhes do Pedido relacionados pela coluna NúmeroDoPedido

Como podemos ver as tabelas estão preparadas e prontos para serem usadas. Espero que você entenda que a  normalização de dados e o relacionamento entre as tabelas é fundamental para que possamos trabalhar com as tabelas no layout Mestre/Detalhe.

Agora você vai ver como é fácil tratar os dados no VB. Vou começar usando a tecnologia ADO...

Exibindo dados relacionados com ADO

Vou usar os controles vinculados para mostrar como é fácil acessar e exibir dados com ADO. Você já deve saber que o para ADO temos o controle de dados vinculado ADO Data Control - Adodc . Vou usar também o controle Data Grid para exibir os dados no formato de uma grade de dados. (Não podemos usar o DBGrid com o Adodc).

Nosso projeto constará de dois ado data control - adodc , dois datagrids e uma label ,conforme o layout abaixo:

No menu Project | Components inclua os controles Microsoft Ado Data Control  e Microsot Data Grid Control no seu projeto.

Percebeu que eu destaquei a coluna Sub Total e a label - Total para o Pedido ? Pois bem , além de exibir os dados relacionados entre as tabelas Pedidos e Detalhes do Pedido vou calcular o subtotal para cada número pedido e o total para o pedido usando comandos SQL.

Na seção General Declarations do formulário vou declarar as variáveis objeto usadas para a conexão e o recordset:

Option Explicit
'define as variáveis objetos usadas no projeto
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

No evento Load do formulário estou definindo a conexão , o recordset  a string de conexão com o banco de dados nwind.mdb para os controles vinculados : datDetalhe e datMestre e atribuindo os controles a seus respectivos datagrids.

Private Sub Form_Load()
     
    'define objetos conn e rs
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset


    'define o cursor e a string de conexão
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\teste\nwind.mdb;Persist Security Info=False"
    
    ' Define o nome do banco de dados para o detalhe
    datDetalhe.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\teste\Nwind.mdb;Persist Security Info=False"


    ' define o nome do banco de dados para o master
    datMestre.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\teste\Nwind.mdb;Persist Security Info=False"
    
    datMestre.RecordSource = "Select NúmeroDoPedido, CódigoDoCliente , DataDoPedido, DataDeEntrega,PaísDeDestino,_
 Frete FROM Pedidos"
    datMestre.Refresh
    
    Set grdDetalhe.DataSource = datDetalhe
    datDetalhe.Refresh
    Set grdMestre.DataSource = datMestre
    
End Sub

No evento MoveComplete do controle vinculado datMestre estou definindo duas consultas SQL que são o coração da aplicação:

1- A consulta selecao :

 subtotal = "SELECT DISTINCTROW [Detalhes do Pedido].NúmeroDoPedido, _
                Sum(CCur([Detalhes do Pedido].[PreçoUnitário]*[Quantidade]*(1-[Desconto])/100)*100) AS Subtotal"
 subtotal = subtotal & " From [Detalhes do Pedido]"
 subtotal = subtotal & " GROUP BY [Detalhes do Pedido].NúmeroDoPedido"
 subtotal = subtotal & " HAVING [Detalhes do Pedido].NúmeroDoPedido=" & datMestre.Recordset.Fields("NúmeroDoPedido")

Esta selecionando os detalhes do pedido para cada pedido selecionado na tabela Pedidos. Nela estou fazendo o cálculo , usando a função agregada SQL Sum - Sum(CCur([Detalhes do Pedido].[PreçoUnitário]*[Quantidade]*(1-[Desconto])/100)*100) AS Subtotal"

2- A consulta total  calcula o total do pedido fazendo uma soma de todos os produtos para o  pedido relacionado na tabela pedidos:

total = "SELECT DISTINCTROW [Detalhes do Pedido].NúmeroDoPedido, _
                Sum(CCur([Detalhes do Pedido].[PreçoUnitário]*[Quantidade]*(1-[Desconto])/100)*100) AS Subtotal"
 total = subtotal & " From [Detalhes do Pedido]"
 total = subtotal & " GROUP BY [Detalhes do Pedido].NúmeroDoPedido"
 total = subtotal & " HAVING [Detalhes do Pedido].NúmeroDoPedido=" & datMestre.Recordset.Fields("NúmeroDoPedido")

Nota : As principais funções agregadas para o SQL padrão Access são :

Funcão Descrição
AVG(coluna) Retorna o valor médio de uma coluna
COUNT(coluna) Retorna o número de linhas (sem Null) de uma coluna.
COUNT(*) Retorna o número de linhas selecionadas.
FIRST(coluna) Retorna o valor do primeiro registro na coluna especificicada.
LAST(coluna) Retorna o valor do último registro no campo especificado.
MAX(coluna) Retorna o maior valor da coluna.
MIN(coluna) Retorna o menor valor da coluna.
SUM(coluna) Retorna a soma total da coluna.
A sintaxe geral é : SELECT funcão(coluna) FROM tabela

O código do evento MoveComplete é dado abaixo:

' Quando selecinar um novo registro no master
' selecionar os registros correspondentes no detalhe
Private Sub datMestre_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As _ 
ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
Dim selecao As String
Dim total As String


 'consulta que exibe os detalhes do pedido para cada pedido selecionado na tabela pedidos
 selecao = "SELECT NúmeroDoPedido, CódigoDoProduto, PreçoUnitário, quantidade, Desconto , _
                CCur([Detalhes do Pedido].[PreçoUnitário]*[Quantidade]*(1-[Desconto])/100)*100 AS PreçoTotal FROM [Detalhes do Pedido] _
                WHERE NúmeroDoPedido = " & _
                datMestre.Recordset.Fields("NúmeroDoPedido")
    
 'esta consulta calcula o valor total para o pedido
 total = "SELECT DISTINCTROW [Detalhes do Pedido].NúmeroDoPedido, _
                Sum(CCur([Detalhes do Pedido].[PreçoUnitário]*[Quantidade]*(1-[Desconto])/100)*100) AS Subtotal"
 total = subtotal & " From [Detalhes do Pedido]"
 total = subtotal & " GROUP BY [Detalhes do Pedido].NúmeroDoPedido"
 total = subtotal & " HAVING [Detalhes do Pedido].NúmeroDoPedido=" & datMestre.Recordset.Fields("NúmeroDoPedido")
    
    'abre a conexão e executa a consulta retornando um recordset com duas colunas :
    ' rs(0) - número do pedido  rs(1) - valor total para o pedido
    conn.Open
    Set rs = conn.Execute(total)
    
    'exibe na label o valor total para o pedido
    lblTotalPedido.Caption = Format(rs(1), "##,##0.00")
    
    'atribui a seleção SQL ao data control e refresca o controle
    datDetalhe.RecordSource = selecao
    datDetalhe.Refresh
    
    'fecha a conexão
    rs.close
    conn.Close
    Set rs = Nothing


End Sub

 

Note que eu estou abrindo uma conexão e executando uma consulta SQL usando o método Excecute do objeto Connection.

O método Execute executa uma consulta definida e armazena o resultado em um recordset do tipo read-only , forward-only. A sintaxe é a seguinte : 

objconn.Execute commandtext,ra,options
onde :
Parametro Descrição
commandtext Requerido. O texto com a instrução SQL ou store procedure a ser executada.
ra Opcional. O número de registros afetados pela consulta.
options Opcional. Define como o provedor irá avaliar o parâmetro commandtext . Podemos ter os valores:
- CommandTypeEnum
- ExecuteOptionEnum
O valor padrão é adCmdUnspecified

Os valores para - CommandTypeEnum - são :

Constante Valor Descrição
adCmdUnspecified -1 Não define o argumento de tipo de comando.
adCmdText 1 Avalia CommandText como um Texto ou um chamada de procedimento armazenado.
adCmdTable 2 Avalia CommandText como uma tabela.
adCmdStoredProc 4 Avalia CommandText como um nome de procedimento armazenado.
adCmdUnknown 8 Indica que o tipo de comando na propriedade  CommandText não é conhecido.
adCmdFile 256 Avalia CommandText como o nome do arquivo de um procedimento armazenado.
adCmdTableDirect 512 Avalia CommandText como um nome de tabela cujas colunas serão todas retornadas. Para usar o método seek o recordset deve ser aberto com com adCmdTableDirect.

Os valores para - ExecuteOptionEnum  - são :

Constante Valor Descricão
adOptionUnspecified -1 indica que o comando não esta especificado.
adAsyncExecute 16 indica que o comando será executado de forma assíncrona.
adAsyncFetch 32 indica as linhas restantes depois da quantidade inicial definida na propriedade CacheSize serão retornadas de forma assíncrona.
adAsyncFetchNonBlocking 64 indica que a thread principal nunca bloqueia enquanto estiver retornando.
adExecuteNoRecords 128 indica o texto do comando não retorna linhas. ( Ex: somente insere dados).
adExecuteStream 256 indica que o resultado da execução do comando será retornado como um stream.(fluxo de dados)
adExecuteRecord 512 indica que CommandText é um comando ou stored procedure que retorna uma única linha.

Rodando o projeto e selecionado um determinado pedido você irá obter os detalhes do pedido o sub-total para cada produto em detalhes do pedido e o valor total do pedido conforme figura abaixo:


Nota: Para configurar as colunas e a aparência do datagrid faça o seguinte :

Exibindo dados relacionados com DAO

Para fazer a mesma coisa com DAO vou usar os controles vinculados Data Control e o controle DBGrid pois não posso usar DataGrid com Data Control.

No menu Project | Components inclua os controles Microsoft Data Bound Grid Control no seu projeto.(O controle vinculado para DAO já esta presente na barra de ferramentas basta selecionar e incluir no formulário.)

A aparência será idêntica á do projeto ADO , por isto , só vou mostrar o código do projeto:

Código de declaração das variáveis objetos da seção - General Declarations do formulário:

Option Explicit
Dim daoDB As DAO.Database
Dim daoRS As DAO.Recordset

Código do evento Load do formulário :

Private Sub Form_Load()


    ' define o nome do banco de dados para o master
    datMestre.DatabaseName = "C:\teste\nwind.mdb"
    datMestre.RecordsetType = 1
    datMestre.RecordSource = "Select NúmeroDoPedido, CódigoDoCliente , DataDoPedido, DataDeEntrega,PaísDeDestino, Frete _
    FROM Pedidos"
    datMestre.Refresh
    
    ' define o nome do banco de dados para o detalhes
    datDetalhe.DatabaseName = "C:\teste\nwind.mdb"
    datDetalhe.RecordsetType = 1
    datDetalhe.Refresh
    
End Sub

 

O controle vinculado Data Control possui o evento Reposition que é equivalente ao MoveComplete do Ado data control. O código do evento reposition é o seguinte :

Private Sub datMestre_Reposition()


' Quando selecinar um novo registro no master
' selecionar os registros correspondentes no detalhe
Dim selecao As String
Dim total As String


    selecao = "SELECT NúmeroDoPedido, CódigoDoProduto, PreçoUnitário, quantidade, Desconto ,_
                   CCur([Detalhes do Pedido].[PreçoUnitário]*[Quantidade]*(1-[Desconto])/100)*100 AS PreçoTotal  _
                   FROM [Detalhes do Pedido] WHERE NúmeroDoPedido = " & _
                   datMestre.Recordset.Fields("NúmeroDoPedido")
    
    'esta consulta calcula o valor total para o pedido
    total = "SELECT DISTINCTROW [Detalhes do Pedido].NúmeroDoPedido, _
              Sum(CCur([Detalhes do Pedido].[PreçoUnitário]*[Quantidade]*(1-[Desconto])/100)*100) AS Subtotal"
    total = subtotal & " From [Detalhes do Pedido]"
    total = subtotal & " GROUP BY [Detalhes do Pedido].NúmeroDoPedido"
    total = subtotal & " HAVING [Detalhes do Pedido].NúmeroDoPedido=" & datMestre.Recordset.Fields("NúmeroDoPedido")
    
    'abre a conexão e executa a consulta retornando um recordset com duas colunas :
    ' rs(0) - número do pedido  rs(1) - valor total para o pedido
    Set daoDB = DBEngine.Workspaces(0).OpenDatabase("C:\teste\nwind.mdb")
    Set daoRS = daoDB.OpenRecordset(total, dbOpenSnapshot)


    'exibe na label o valor total para o pedido
    lbTotal.Caption = Format(daoRS(1), "##,##0.00")
    
    'atribui a seleção SQL ao data control e refresca o controle
    datDetalhe.RecordSource = selecao
    datDetalhe.Refresh
    
    'fecha a conexão
    daoRS.Close
    daoDB.Close


End Sub

Rodando o projeto teremos o mesmo resultado obtido com ADO:

Vou mostrar como fazer o mesmo com VB.NET em outro artigo , este já ficou muito extenso...

Nota: Reparem que a carga do projeto para DAO é ligeiramente mais rápida do que com ADO.

Até breve ...

José Carlos Macoratti