Ferramentas de Utilizador

Ferramentas de Site


dev_net:vb.net:gestao_bases_dados_access

Programação para acesso a Dados

Quando se fala em software comercial, nomeadamente aplicações de médio grande porte, 90% das vezes estamos a referir-nos a programação sob base de dados. De facto, desde a pequena aplicação que gere o nosso espólio de filmes, cd's e até livros, até à poderosa aplicação que permite efectuar operações de gestão, financeiras, de planeamento e até mesmo de análise, as base de dados suportam grande parte da lógica do programa, e fazem-nos encarar a programação numa óptica de acesso e manuseamento de dados.

Existem um conjunto de SGBD's, adequadas aos diversos níveis de exigência, requisitos e "bolsos"!

Este artigo vai debruçar-se sobre o Access (embora baste alterar os objectos para facilmente se adapte a outros SGBD’s1) e o objectivo é a criação de um conjunto de métodos e funções reutilizáveis que os vão permitir poupar tempo e linhas de código na interacção com a Base de Dados.

Módulo Base de Dados

Por norma, costumo utilizar um Módulo Público denominado bas_database, onde coloco todas as funções de base de dados.

Não esquecer de adicionar no início do código, e em todas as classe o
Imports System.Data.OleDb

Ligação

Em qualquer projecto, a ligação deve ser definida uma única vez, num local onde esta seja facilmente acedida a partir de qualquer objecto da aplicação. Este módulo é o local ideal. Assim, o módulo referido anteriormente ficará mais ou menos com a seguinte assinatura.

Imports System.Data.OleDb
Public Module bas_database
    Dim con As New OleDb.OleDbConnection
    ''' <summary>
    ''' Define a Conexão à base de dados. Testa a conexão no momento.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub DefineConexao()
        Dim strDBPath As String = Application.StartupPath & "clube_video.mdb"
                    Try
                con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & strDBPath & " "
                openConnection() 'Abre a ligação
                closeConnection() 'Fecha a ligação
            Catch ex As OleDbException
                Select Case ex.ErrorCode
                'Pode ser refinado através da inclusão e tratamento de outro erros
                Case Else
                    MsgBox("Ocorreu um erro: " & ex.Message)
                    End
            End Select
            End Try
    End Sub
'.......

Neste momento aproveitamos também para abrir a conexão à base de dados, e verificar se está tudo conforme.

Antes de qualquer procedimento, criei uma função que verifica se o ficheiro existe, e só nesse caso prossegue para o teste de ligação.

Neste caso específico, apenas testei se abria a conexão. Poderá ser melhorado, fazendo um teste específico a determinada tabela.

Inicialização da Ligação

Já definimos a Connection String2) , contudo, este método não corre sozinho. A melhor estratégia para correr este método será utilizar os ApplicationEvents. No caso concreto utilizei a definição MyApplication_Startup.3).

    Partial Friend Class MyApplication
        Public Sub MyApplication_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
            Try
                DefineConexao()
            Catch ex As Exception
 
            End Try
        End Sub
    End Class

Resumindo: Até ao momento, defini a minha ConnectionString, iniciei a minha ligação à base de dados e verifiquei se a base de dados existe, e/ou pode ser acedida. Utilizei a função FicheiroExiste, que tem retorna um booleano no caso do ficheiro testado existir.

    ''' <summary>
    ''' Testa se um ficheiro existe
    ''' </summary>
    ''' <param name="strPath">Caminho do Ficheiro</param>
    ''' <returns>Devolve um booleano</returns>
    ''' <remarks></remarks>
    Public Function FicheiroExiste(ByVal strPath As String) As Boolean
        Try
            Return IO.File.Exists(strPath)
        Catch
            Return False
        End Try
    End Function

Se reparar, no método DefineConexao, utilizei ainda duas expressões que correspondem a dois métodos que utilizo para garantir que a minha instrução de abrir e fechar a ligação à base de dados não gera um erro. Essas duas expressões correspondem aos métodos:

openConnection

''' <summary>
    ''' Função para abrir a ligação à base de dados. Testa se a conexao já está aberta, caso contrário daria erro.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub openConnection()
        Try
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
        Catch ex As OleDbException
        End Try
    End Sub

closeConnection

    ''' <summary>
    ''' Função para fechar a ligação à base de dados. Testa se a conexao já está fechada, caso contrário daria erro.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub closeConnection()
        Try
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        Catch ex As OleDbException
        End Try
    End Sub

Interacção com a base de dados

Existem essencialmente dois tipos interacção com base de dados. Consulta (select) ou alterações (concretizadas nas instruções: INSERT, UPDATE, ou DELETE).

O VS 2008, fornece um conjunto de automatismos que nos permitem em ambiente visual, fazer qualquer destas operações, contudo como todos os automatismos estes têm as suas limitações. Considero assim, que é preferível criar todas (ou grande parte) das rotinas de acesso a dados via código.

Consultas

Em termos de consultas, existem essencialmente dois tipos:

  • Aquelas que nos devolvem um conjunto de registos: (para quem vem do ACCESS/VBA, equivaleria ao recordset – não permitindo contudo o UPDATE)
  • As que retornam um único resultado

As primeiras utilizam o método .ExecuteReader4) do objecto OleDbCommand 5) enquanto que as segundas utilizam o método .ExecuteScalar6) .

Como já foi dito, o objectivo é criar métodos passíveis de ser reutilizados, portanto, porque tem o programador de perder tempo a criar todo o código para efectuar uma consulta, quando tudo que lhe interessa é somente pegar nos resultados desta consulta e fazer o que bem entender. A solução que proponho passa pela criação de uma rotina que recebe uma instrução de SQL 7) e devolve um OleDbDataReader8).

Criação de um Reader (.ExecuteReader)

    ''' <summary>
    ''' Devolve um OleDbDataReader basedado numa instrução de SQL
    ''' </summary>
    ''' <param name="strSql">Instrução de SQL</param>
    ''' <returns>OleDbDataReader</returns>
    ''' <remarks></remarks>
    Public Function ExecuteReader(ByVal strSql As String) As OleDbDataReader
        Try
            Dim cmd As New OleDbCommand(strSql, con)
            cmd.CommandType = CommandType.Text
            openConnection()
            Dim Odr As OleDbDataReader = cmd.ExecuteReader()
            If Odr.HasRows Then
                Return Odr
            Else
                Return Nothing
            End If
        Catch ex As Exception
            MsgBox(Err.Description)
            Return Nothing
        End Try
    End Function
Aplicação Prática:
       Dim strSql As String = "SELECT cat_id, cat_desc from t_categoria"
        Dim dr As OleDbDataReader
        Try
            dr = ExecuteReader(strSql)
            While dr.Read 'posso testar se retorna nothing e fazer o tratamento deste erro sem ser no try
                MsgBox(dr.Item(1)) 'Exibe uma msgbox por cada categoria de filmes que encontra
            End While
        Catch ex As OleDbException
            MsgBox(ex.Message)
        End Try
    End Sub

Neste exemplo (embora não seja uma funcionalidade particularmente importante), conseguimos obter um OleDbDataReader com recurso a poucas linhas de código, sendo que o aproveitamento de todos os métodos intermédios que utilizamos, garantem o tratamento de erros.

Retornar o Máximo (.ExecuteScalar)

Neste exemplo vamos complicar mais um bocadinho e vamos passar alguns parâmetros extra que permitem poupar tempo e tornar a função mais flexível.

''' <summary>
    ''' Retorna o valor máximo de um campo em determinada tabela
    ''' </summary>
    ''' <param name="strTable">Tabela</param>
    ''' <param name="strField">Campo a pesquisar</param>
    ''' <returns>Valor máximo - Long</returns>
    ''' <remarks></remarks>
    Public Function OleDBMax(ByVal strTable As String, ByVal strField As String) As Long
        Dim strSql As String
        strSql = "SELECT MAX(" & strField & ") " & _
            "from " & strTable
        Try
            openConnection()
            Dim Cmd As New OleDbCommand(strSql, con)
            Cmd.CommandType = CommandType.Text
            Return Cmd.ExecuteScalar()
            Cmd.Dispose()
            closeConnection()
        Catch ex As OleDbException
            MsgBox(ex.Message)
            Return 0
        End Try
    End Function
Aplicação Prática:
MsgBox(OleDBMax("t_categoria", "cat_id")) 'Retorna uma msgbox com Máximo para o campo cat_id na tabela t_categoria

Contar número de Ocorrências (.ExecuteScalar)

Muitas vezes temos necessidade de contar o número de ocorrências que satisfazem determinada condição. A função que se segue, tem de igual modo a possibilidade de receber parâmetros que permitem poupar tempo, e esforço!

Esta função tem uma variante, pode não receber todos os parâmetros. Ou seja, se eu quiser saber quantos registos tem a tabela, não preciso de passar condição. A expressão “where 1=1”, para a instrução de SQL equivale a tudo.

    ''' <summary>
    ''' Retorna o número de occorrências na tabela
    ''' </summary>
    ''' <param name="strTable">Tabela a Avaliar</param>
    ''' <param name="strExp">Critério para a pesquisa - Se nulo não aplica a condição</param>
    ''' <returns>Número de Ocorrências - Long</returns>
    ''' <remarks></remarks>
    Public Function OleDBCount(ByVal strTable As String, Optional ByVal strExp As String = "1=1") As Long
        Dim strSql As String
        strSql = "select count(1) " & _
            "from " & strTable & " " & _
            "where " & strExp
        Try
            openConnection()
            Dim Cmd As New OleDbCommand(strSql, con)
            Cmd.CommandType = CommandType.Text
            Return Cmd.ExecuteScalar()
            Cmd.Dispose()
            closeConnection()
        Catch ex As OleDbException
            MsgBox(ex.Message)
            Return 0
        End Try
    End Function
Aplicação Prática:
MsgBox(OleDBCount("t_filme", "cat_id = 1")) 'Retorna uma msgbox com o número de filmes na tabela t-filme para a categoria 1

Preencher uma ComboBox Dinamicamente

Quem trabalha com base de dados, vê-se constantemente confrontado com a necessidade de preencher uma Combobox9) para mostrar os dados de tabelas relacionadas que pertencem ao conjunto de dados principais que estamos a mostrar.

Criar código para preencher cada uma das Comboboxes, cada vez que necessitamos, pode ser uma tarefa infindável. Este método preenche uma Combobox, baseada numa instrução de SQL que do que pretendemos mostrar.

    ''' <summary>
    ''' Preenche uma Combobox
    ''' </summary>
    ''' <param name="cbo">Combobox a preencher</param>
    ''' <param name="strsql">Instrução de SQL para preencher</param>
    ''' <remarks>Deve Passar a Coluna de Id (invisivel - ValueMember), e uma de Descrição (Visivel - Display Member)</remarks>
    Public Sub ComboFill(ByVal cbo As ComboBox, ByVal strsql As String)
        Dim dt As New DataTable
        Try
            openConnection()
            Dim cmd As New OleDbCommand(strsql, con)
            Dim da As New OleDbDataAdapter(cmd)
            da.Fill(dt)
            With cbo
                .DataSource = Nothing 'para limpar se for recarregada
                .DataSource = dt
                .ValueMember = dt.Columns(0).ToString
                .DisplayMember = dt.Columns(1).ToString
            End With
            closeConnection()
        Catch ex As OleDbException
            MsgBox(ex.Message)
        End Try
    End Sub
Aplicação Prática:
Dim strSql As String
strSql = "select cat_id, cat_desc from t_categoria"
ComboFill(cboExemplo, strSql)
'Preenchimento de uma combobox com as categorias de filme possíveis

Testar nulos

Muitas vezes quando estamos a comparar valores, sejam eles possíveis strings, resultados de leituras à base de dados, objectos, etc.. somos confrontados com erros. A função que apresentamos relembra uma função utilizada em Access, o NZ, que retorna determinado valor no caso de o valor submetido ser nulo . Está preparada para avaliar:

  • Objectos com referência nula
  • Resultados de leituras à base de dados IsDBNull
  • Strings vazias
    ''' <summary>
    ''' Função para testar variáveis
    ''' </summary>
    ''' <param name="p_test">Input a testar</param>
    ''' <param name="p_ret">Valor de Retorno</param>
    ''' <returns>Devolve um valor prédefinido</returns>
    ''' <remarks></remarks>
    Public Function nz(ByVal p_test, ByVal p_ret)
        If p_test Is Nothing Then
            Return p_ret
        ElseIf IsDBNull(p_test) Then
            Return p_ret
        ElseIf Len(p_test.ToString) = 0 Then
            Return p_ret
        Else
            Return p_test
        End If
    End Function
Aplicação Prática:
Dim strSql As String
strSql = "select cat_id, cat_desc from t_categoria"
ComboFill(cboExemplo, strSql)
MsgBox(OleDBCount("t_filme", "cat_id = " & nz(cboExemplo.SelectedValue, 0)))
'no caso de a combobox não ter nenhum valor seleccionado, 
'retornará 0 o que fará que a função OleDBCount funcione, retornando no entanto 0 
'por não existir categoria de filme com id 0

Execução de Instruções SQL

Se grande parte do código que vimos até agora, é essencialmente destinado para a obtenção de dados, não nos devemos esquecer que apenas poderemos mostrar informação se ela existir. Assim, vamos passar a analisar uma função destinada a interagir com a base de dados, nas operações de INSERT, UPDATE e DELETE. Neste caso, vamos utilizar um outro método do objecto OleDbCommand, o .ExecuteNonQuery.10)

''' <summary>
    ''' Função que permite executar directamente instruções de SQL - INSERT, UPDATE, DELETE
    ''' </summary>
    ''' <param name="strSql">Instrução de SQL</param>
    ''' <returns>Numero de Linhas Afectadas</returns>
    ''' <remarks></remarks>
    Public Function oraExecute(ByVal strSql As String) As Integer
        Dim cmd As New OleDbCommand(strSql, con)
        cmd.CommandType = CommandType.Text
        openConnection()
        Try
            Dim i As Integer = cmd.ExecuteNonQuery()
            Return i
        Catch ex As OleDbException
            Select Case ex.ErrorCode
                'Tratamento personalizado de erros por exemplo não é possivel 
apagar porque existem registos relacionados registos relacionados...
                Case Else
                    MsgBox("Occorreu um Erro: " & ex.ErrorCode & " - " & ex.Message)
            End Select
        End Try
    End Function
Aplicação Prática: Para ilustrar a utilização desta função, resolvemos criar um botão que nos permite inserir novas categorias de filmes. Uma vez que a tabela filmes só tem dois campos, cat_id e cat_desc, optámos por utilizar uma inputbox, para recolher o valor do novo tema, sem ter que criar um form para o efeito. O cat_id, foi encontrado pegando no valor mais alto encontrado no id, e adicionando-lhe 1.
Dim strSql As String
        Dim lngSeq As Long
        Dim strTema As String
        lngSeq = OleDBMAx("t_categoria", "cat_id") + 1 'Vai buscar o ultimo número utilizado e adicona 1
        strTema = InputBox("Insira um Tema", "Adicionar Tema à lista") 
'Solução de Recurso para ilustrar o uso de Inputboxes
        strSql = "insert into t_categoria (cat_id, cat_desc) values (" & lngSeq & ",'" & strTema & "')" ' Instrução de SQL para inserir um novo tema na categoria
        If OleDBExecute(strSql) > 0 Then
            MsgBox("Registo inserido com sucesso")
        Else
            MsgBox("Não foi possível Inserir o registo")
        End If

Aqui está, com meia dúzia de linhas de código, estamos prontos a fazer alimentar, e manter actualizada a nossa BD.

Instruções de SQL – Sintaxes mais utilizadas.

Embora seja possível criar instruções de SQL com uma complexidade considerável, na realidade, a maior parte das operações de transacção com a BD, resumem-se às seguintes funções.

INSERT

INSERT INTO nome_da_tabela (campo1, campo2,) VALUES (value1,value2,)

Ex:

INSERT INTO t_categoria (cat_id, cat_desc) VALUES (1,’Terror’)

UPDATE

UPDATE nome_da_tabela SET (capo1=value1,campo2=value2,=.)

Com condição

UPDATE nome_da_tabela SET (campo1=value1,campo2=value2,=.) WHERE x = y

Ex:

UPDATE t_categoria SET (cat_desc = ‘Filmes de Vampiros’) WHERE cat_id = 3

DELETE

DELETE FROM nome_da_tabela

Com condição

DELETE FROM nome_da_tabela WHERE x = y

Ex:

DELETE FROM t_categoria WHERE cat_id = 3

Tratamento de dados para inserção na Base de dados

Embora, a utilização de parâmetros nos comandos para definição do tipo de dados, seja vista como preferencial que a utilização directas de SQL, na verdade, o tratamento cuidado do tipo de dados que estamos a inserir, torna esta metodologia de trabalho bastante eficaz. Assim, necessitamos de tratar essencialmente três tipos de dados: Strings, datas e números.

Tratamento de números

Esta função testa o valor submetido, retornando, o número, no caso de ser numérico, ou Null caso não o seja. O Null é também é interpretado como valor válido para um campo do tipo Number (que é diferente de dizer que campo X = “”)

''' <summary>
    ''' Avalia se é uma expressão numérica
    ''' </summary>
    ''' <param name="v">Parametro de entrada</param>
    ''' <returns>ou o valor numério, ou null</returns>
    ''' <remarks></remarks>
    Public Function ToDBNumber(ByVal v As Object) As String	
        If Integer.TryParse(v, New Integer) Then
            Return Str(v)
        Else
            Return "null"
        End If
    End Function

Tratamento de Strings

Na maior parte dos SGDB’s, a expressão de texto é enviado para a BD entre ‘ ’ (pelicas). Todos nós sabemos como é difícil conjugar ‘ com “ (aspas), e a confusão que às vezes se gera. Assim, preparou-se esta função que facilmente prepara a String para ir para a base de dados.

''' <summary>
    ''' Transforma uma string para ir para aBD
    ''' </summary>
    ''' <param name="v">String a testar</param>
    ''' <returns>Devolve uma string</returns>
    ''' <remarks></remarks>
    Public Function ToDBString(ByVal v As Object) As String
        If v.ToString = "" Or v Is Nothing Or IsDBNull(v) Then
            Return "null"
        Else
            Return "'" & Trim(Replace(v, "'", " ")) & "'"
        End If
    End Function

Adicionalmente, como o parâmetro de entrada é um objecto, pode ser ainda aproveitada para testar directamente resultados de um DataReader. Elimina também os espaços à direita e à esquerda da String.

Tratamento de Datas

Quem está habituado a trabalhar com datas em BD, sabe que é provavelmente o tipo de dados que mais dificuldades oferece, até porque depende quase sempre das definições locais do Windows, e poderemos estar a falar de formatos bastante diferentes. Para o Access, temos ainda que colocar as datas entre # para as inserir na BD.

    ''' <summary>
    ''' Prepara Data para a BD
    ''' </summary>
    ''' <param name="strdate">Data a Avaliar</param>
    ''' <returns>Devolve a data aceite pela Base de Dados, e nor formato indicado</returns>
    ''' <remarks></remarks>
    Public Function ToDbdate(ByVal strdate As String) As String
        Dim straux As String = ""
        Dim dt As Date
        If DateTime.TryParse(strdate, New DateTime) Then
            dt = FormatDateTime(strdate, DateFormat.ShortDate)
            straux = dt.ToString("yyyy-MM-dd")
            straux = "#" & straux & "#"
            Return straux
        End If
    End Function
Exemplo práticoVoltando ao exemplo que utilizamos para carregar a categoria, e imaginando que temos também uma data de auditoria (quando foi inserido o registo por exemplo), ficaríamos:
Dim strSql as String
strSql = "INSERT into t_categoria (cat_id, cat_desc, data_adicao) values (" & ToDBNumber(lngSeq) & "," & ToDBString(strTema) & "," & ToDbdate(Date.Now) & ")"

Preenchimento de uma ListView dinamicamente

As Listviews11) a par das DataGridViews, são provavelmente dos objectos mais utilizados para representar grandes quantidades de dados. A função que vos proponho agora, carrega uma Listview dinamicamente, baseada numa instrução de SQL.

''' <summary>
    ''' Preenche um ListView dinamicamente
    ''' </summary>
    ''' <param name="lv">Lista a preencher</param>
    ''' <param name="strSql">Sql para o preenchimento da lista</param>
    ''' <param name="intMaxRecords">N máximo de registos a retornar - Prédefinido 1000</param>
    ''' <remarks></remarks>
    Public Sub FillListView(ByVal lv As ListView, ByVal strSql As String, Optional ByVal intMaxRecords As Integer = 1000)
        Dim j As Integer ' guarda o numero de resultados
        Dim flag As Boolean = True 'Esta variavel é de apoio para mudar a cor das linhas da listview
        Dim lvwColumn As ColumnHeader
        Dim itmListItem As ListViewItem
        Dim dr As OleDbDataReader
        lv.Clear() 'limpa a listview
        lv.View = View.Details ' Seta para ser do tipo details
        If strSql = "" Then Exit Sub
        dr = executeReader(strSql) 'cria o reader
        If dr Is Nothing Then Exit Sub 'no caso de não obter resultados salta fora
 
        'Criação dinamica dos Cabeçaçhos (neste momento estão todo com 100).
        For i = 0 To dr.FieldCount() - 1
            lvwColumn = New ColumnHeader()
            lvwColumn.Text = dr.GetName(i).ToString ' O nome vem da instrução de sQL
            lvwColumn.Width = 100
            lv.Columns.Add(lvwColumn)
        Next
        Do While dr.Read
            'Conta os registos que encontra
            If j + 1 > intMaxRecords Then
                MsgBox("Existem mais de " & intMaxRecords & ". Refine a sua pesquisa por favor")
                'Esta função permite que em grandes listagens se restrinja o acesso à BD - Optimização.
            Else
                itmListItem = New ListViewItem()
                itmListItem.Text = nz(dr(0), "")
                Dim i As Integer
                For i = 1 To dr.FieldCount() - 1
                    If dr.IsDBNull(i) Then
                        itmListItem.SubItems.Add("")
                    Else
                        itmListItem.SubItems.Add(dr.GetValue(i).ToString)
                    End If
                Next i
            End If
            'Alterna a cor da linha - visualmente mais atractivo
            If flag Then
                itmListItem.BackColor = Color.Beige
            Else
                itmListItem.BackColor = Color.Gray
            End If
            flag = Not flag
            lv.Items.Add(itmListItem)
            j = j + 1
        Loop
    End Sub

Este método é bastante refinado, porque trata já de uma série de questões importantes como seja por exemplo a optimização do acesso à base de dados, a diferenciação das linhas por cor, e a geração de cabeçalhos de coluna automaticamente. Pode ainda criar mais uma série de opções que permitirão gerar um método capaz de 1001 personalizações no carregamento de cada Listview.

Exemplo prático

Vamos agora analisar o código associado ao botão carregar Listview.

Dim strSql As String
strSql = "SELECT mov.flm_id as ""ID"", mov.flm_desc as ""Designação"", cat.cat_desc as ""Categoria"" from t_filme mov " & _
       	"INNER JOIN t_categoria cat on mov.cat_id = cat.cat_id "
        FillListView(lvPesquisa, strSql)

Escolhi propositadamente uma instrução de SQL, que vai buscar dados a duas tabelas distintas, e recorri ao INNER JOIN (Para saber mais sobre os vários tipos de JOIN's12) ).

Mas porquê escrever aqui tanto código (e este só “une” duas tabelas, quando poderão ser n), ainda por cima, tendo que testa-lo em runtime para se certificar que a instrução SQL está correcta.

Na verdade, só pretendi ilustrar a utilização do INNER JOIN, para esclarecer alguns conceitos, é usual e aconselhável, este tipo de consultas ser efectuado do lado da base de dados, através da criação das chamadas View’s, Query’s, ou até mesmo consultas.

Instrução de SQL baseada em critérios

Complicando um pouco… vamos imaginar que o preenchimento da Listview está dependente de alguns critérios. Neste caso, criei duas caixas texto, uma para o ID, e outra para o nome do filme, e vou aproveitar a Combobox criada anteriormente para a categoria do filme. O código para a pesquisa despoletado pelo botão ficaria:

        Dim cteAnd As String = " and "
        Dim strLink As String = "" ' Parametro de ligação
        Dim strWhere As String = "" ' Contrução da clausula where
        Dim strSelect As String = "" 'instrução de select
        Dim strOrder As String = "" ' critério de ordenação
        If Not txtId.Text = "" Then
            strWhere = strWhere & strLink & "flm_id = " & txtId.Text
            'No caso de pesquisa pelo Id, sendo único não vale a pena sequer avaliar os
            'outros critérios
        Else
            If Not cboExemplo.Text = "" Then
                strWhere = strWhere & strLink & "mov.cat_id = " & nz(cboExemplo.SelectedValue, -1)
                strLink = cteAnd
            End If
            If Not txtNome.Text = "" Then
                strWhere = strWhere & strLink & "lcase(flm_desc) like '%" & LCase(ConvertToLike(txtNome.Text)) & "%'"
                strLink = cteAnd
            End If
        End If
        If Not strWhere = "" Then
            strSelect = "SELECT mov.flm_id as ""ID"", mov.flm_desc as ""Designação"", cat.cat_desc as ""Categoria"" from t_filme mov " & _
            "inner join t_categoria cat on mov.cat_id = cat.cat_id "
            strWhere = " where " & strWhere
            strOrder = ""
        Else
            strSelect = ""
        End If
 
        If Not strOrder = "" Then
            strOrder = " order by " & strOrder
        End If
        strSelect = strSelect & strWhere & strOrder
        FillListView(lvPesquisa, strSelect)
Chamo a atenção para dois pormenores interessantes:
  • Utilização do lcase na comparação de strings (assim não temos o problema das maísculas/minúsculas).
  • Utilização de uma função (ConvertToLike) que vai substituir os espaços pelo wildcard[13) %, o que permite a utilização de fragmentos do conteúdo a pesquisar.

O resultado de uma instrução de SQL para enviar para a base de dados, utilizando os dois critérios cumulativamente seria:

vbnet
"SELECT mov.flm_id as "ID", mov.flm_desc as "Designação", cat.cat_desc as "Categoria" from t_filme mov INNER JOIN t_categoria cat on mov.cat_id = cat.cat_id  where mov.cat_id = 1 and lcase(flm_desc) like '%long%tran%'"
Que devolveria o filme que consta da BD “As longas tranças de um careca”, na categoria 1 – Romance.

Preparação de string para pesquisa

 ''' <summary>
    ''' Função que Substitui os * e espaços por % - Wildcard
    ''' </summary>
    ''' <param name="v">expressão de entrada</param>
    ''' <returns>String de pesquisa</returns>
    ''' <remarks></remarks>
    Public Function ConvertToLike(ByVal v As String) As String
        ConvertToLike = Replace(Trim(v), "*", "%")
        Return Replace(ConvertToLike, " ", "%")
    End Function
Em suma:

Se reparar, com poucas funções, criámos rotinas que nos vão permitir efectuar operações redundantes, com recurso a muito menos código. E, isto .. é o início … a imaginação (na maior parte das vezes é o limite). Tal como diz um grande amigo meu… “Isto só não frita batatas porque não tem recipiente para o óleo”

Bom trabalho e boa programação.

Nota: As funções aqui demonstradas são resultado de uma compilação feita ao longo de anos, estão estáveis, testadas e, em produção.

dev_net/vb.net/gestao_bases_dados_access.txt · Esta página foi modificada pela última vez em: 2018/05/14 21:37 (Edição externa)