VB .NET - Usando o banco de dados SQLite

VB .NET - Usando o Banco de dados SQLite - 2

Na primeira parte deste artigo apresentei o SQLite , apontei os recursos necessários para trabalhar com ele , criei um banco de dados SQLite e um projeto no Visual Studio 2010 SP1 para mostrar como realizar as operações básicas.

Vamos então mostrar como realizar as tarefas básicas de manutenção de dados realizando as operações de inclusão, alteração e exclusão de dados. Apenas para recordar, até o momento nosso projeto possui apenas um formulário - form1.vb - que exibe os dados em um DataGridView , e, a partir do qual iremos chamar os demais formulários para realizar essas operações.

Veja abaixo a figura do formulário form1.vb em tempo de projeto e de execução:

Incluindo registros no SQLite

Vamos incluir um novo formulário no projeto que será usado para incluir registros no SQLite. No menu Project Selecione Add New Item e selecione Windows Forms aceitando o nome padrão : form2.vb. (Em um projeto de produção você deve nomear todos os seus objetos de forma consistente e não usar os nomes padrão.Ok!)

Este formulário deverá ser aberto quando o usuário clica no botão Incluir do formulário principal form1.vb;

Para isso vamos incluir no evento Click do botão Incluir o código a seguir:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'incluir
        My.Forms.Form2.ShowDialog()
        carregadados()
    End Sub

No código My.Forms.Form2.ShowDialog() usamos os recursos do objeto My.Forms e exibimos o formulário form2 como uma janela modal o que implica a rotina carregadados será executada somente após que o formulário seja fechado. O método Show abre uma janela não Modal.

Nas versões anteriores do VB.NET você tinha que criar uma instância do formulário para usá-lo depois. Lembra ???

Dim frm As New Form2
frm.Show()

O Visual Basic 2005 agora oferece suporte a instâncias padrão, permitindo que você use a seguinte sintaxe(Já conhecida dos programadores VB6) :

Form2.Show()

Para ficar bem documentado é mais prudente usar a instância a partir da coleção My.Forms, assim :

My.Forms.Form2.Show()

O formulário form2.vb possui o leiaute exibido na figura abaixo e contém controles TextBox, Button e PictureBox:

Neste formulário o usuário poderá informar o nome, idade e selecionar uma imagem como foto para o cliente. Após isso basta clicar no botão Salvar para gravar os registros no banco de dados.

Vamos usar os seguintes namespaces neste formulário:

Imports System.Data.SQLite
Imports CadastroSQLite.Funcoes

O código do botão Incluir deverá abrir uma janela de diálogo OpenFileDialog para que o usuário selecione uma imagem. Veja o seu código a seguir:

 Private Sub btnIncluirFoto_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFoto.Click
        Dim d As New OpenFileDialog
        d.Filter = "Image (*.png)|*.png|All Files|*.*"
        If d.ShowDialog() = DialogResult.OK Then
            picFoto.Image = Image.FromFile(d.FileName)
            btnFoto.Text = d.FileName
        Else
            picFoto.Image = Nothing
            btnFoto.Text = "Incluir"
        End If
    End Sub

O código associado ao evento Click do botão Salvar é mostrado a seguir:

 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Try
        'define um objeto Command
        Dim SQLcommand As SQLiteCommand
        sConnectionString = "Data Source=c:\dados\Macoratti.db;Version=3;New=True;Compress=True;"
        Dim SQLconnect As New SQLite.SQLiteConnection(sConnectionString)
        SQLconnect.Open()
        SQLcommand = SQLconnect.CreateCommand
        ''@image não pode estar entre aspas simples
        SQLcommand.CommandText = "INSERT INTO Cadastro(nome,idade,foto)  VALUES ('" + txtNome.Text.Replace("'", "''") + "', " + txtIdade.Text + ", @image)"
        ''Define o parâmetro @image.
        SQLcommand.Parameters.Add(ImageToBlob("@image", btnFoto.Text))
        ''executa a consulta
        SQLcommand.ExecuteNonQuery()
        SQLcommand.Dispose()
        SQLconnect.Close()
        'limpa as caixas de texto
        txtNome.Text = String.Empty
        txtIdade.Text = String.Empty
        picFoto.Image = Nothing
        btnFoto.Text = "Incluir"
     Catch ex As Exception
          MsgBox ("Erro ao acessar o SQLite: " & ex.Message)
      End Try
    End Sub

Neste código cabe destacar apenas a string de conexão usada e o comando SQL Insert Into para incluir os registros no banco de dados. Observe que o campo id não esta sendo referenciado pois é uma chave primária auto incremental.

Alterando registros no SQLite

Inclua o formulário para alterar registros no SQLite no menu Project -> Add Windows Forms e aceite o nome padrão - form3.vb clicando em Add;

O formulário form3.vb deverá ser aberto quando o usuário clicar no botão Alterar do formulário form1.vb. O código do evento Click deste botão é visto a seguir:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'alterar
        If id >= 0 Then
            My.Forms.Form3.ShowDialog()
            carregadados()
        Else
            MsgBox("Selecione um item a alterar")
        End If
    End Sub

Verificamos se existe um id maior que zero, o que indica que um registro foi selecionado no DataGridView, e, em seguida abrirmos o formulário form3.vb;

A seguir defina o leiaute conforme o da figura abaixo onde temos a exibição dos dados que desejamos alterar no formulário e as opções para Selecionar Foto para alterar a foto existente e no botão Alterar estamos persistindo as alterações no banco de dados:

Vamos usar os seguintes namespaces neste formulário:

Imports System.Data.SQLite
Imports CadastroSQLite.Funcoes

A seguir defina as variáveis codigo usada para armazenar o id que desejamos alterar e d que representa o OpenFileDialog:

Dim codigo As Integer
Dim d As New OpenFileDialog

No evento Load do formulário devemos receber a identificação do registro que vamos alterar. Lembre-se que o usuário deve selecionar o registro que deseja alterar no form1.vb e clicar no botão Alterar. Estamos obtendo este valor do formulário form1.vb : codigo = Form1.id

Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        codigo = Form1.id
        buscaregistro(codigo)
    End Sub

A rotina buscaregistro(codigo) realiza a busca pelo registro de código selecionado e preenche os dados no formulário form3.vb usando um DataReader;

    Private Sub buscaregistro(ByVal id As Integer)
        Try
            Dim SQLconnect As New SQLite.SQLiteConnection()
            Dim SQLcommand As SQLiteCommand
            'define string de conexão com banco de dados SQLite - Macoratti.db criado na pasta c:\dados
            'usando a criptografia
            SQLconnect.ConnectionString = "Data Source=c:\dados\Macoratti.db;Version=3;New=True;Compress=True;"
            'abre a conexão
            SQLconnect.Open()
            'cria um comando
            SQLcommand = SQLconnect.CreateCommand
            'define a instrução sql
            SQLcommand.CommandText = "SELECT * FROM cadastro WHERE id = " & id
            'executa o comando e retorna um datareader
            Dim SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader()
            'percorre e exibe os dados
            While SQLreader.Read()
                txtID.Text = SQLreader("id")
                txtNome.Text = SQLreader("nome")
                txtIdade.Text = SQLreader("idade")
                If SQLreader("foto").Equals(System.DBNull.Value) Then
                    picFoto.Image = Nothing
                Else
                    picFoto.Image = BlobToImage(SQLreader("foto"))
                End If
            End While
            SQLcommand.Dispose()
            SQLconnect.Close()
            btnFoto.Text = "Selecionar Foto"
        Catch ex As Exception
            MsgBox("Erro ao acessar o SQLite: " & ex.Message)
        End Try
    End Sub

Com os dados carregados no formulário o usuário poderá alterar a imagem da foto. Para isso basta clicar no botão Selecionar Foto cujo código é dado a seguir:

Private Sub btnFoto_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFoto.Click
        d.Filter = "Image (*.png)|*.png|All Files|*.*"
        If d.ShowDialog() = DialogResult.OK Then
            picFoto.Image = Image.FromFile(d.FileName)
            btnFoto.Text = "Foto Alterada"
        Else
            picFoto.Image = Nothing
            btnFoto.Text = "Selecionar Foto"
        End If
    End Sub

Após alterar a foto, o usuário clica no botão Alterar para salvar as alterações no banco de dados. O código é mostrado a seguir:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
 Try
    Dim SQLconnect As New SQLite.SQLiteConnection()
    Dim SQLcommand As SQLiteCommand
    'define string de conexão com banco de dados SQLite - Macoratti.db criado na pasta c:\dados
    'usando a criptografia
    SQLconnect.ConnectionString = "Data Source=c:\dados\Macoratti.db;Version=3;New=True;Compress=True;"
    SQLconnect.Open()
    SQLcommand = SQLconnect.CreateCommand
    'Verifica se a imagem foi alterada
    If btnFoto.Text = "Foto Alterada" Then
        SQLcommand.CommandText = "UPDATE Cadastro SET nome = '" & txtNome.Text.Replace("'", "''") & "', idade =  " _ 
& txtIdade.Text & ", foto = @image WHERE id = " & codigo
        'Define o parâmetro para imagem
        SQLcommand.Parameters.Add(ImageToBlob("@image", d.FileName))
    Else
        SQLcommand.CommandText = "UPDATE Cadastro SET nome = '" & txtNome.Text.Replace("'", "''") & "', idade =  "  _ 
& txtIdade.Text & " WHERE id = " & codigo
    End If
   'Executa a consulta para alterar
    SQLcommand.ExecuteNonQuery()
    SQLcommand.Dispose()
    SQLconnect.Close()
    MsgBox("Registro alterado com sucesso.")
    Me.Close()
Catch ex As Exception
        MsgBox("Erro ao acessar o SQLite: " & ex.Message)
End Try
End Sub

Note que a instrução SQL usada para alterar os dados - UPDATE tabela SET campo1=valor - é montada de forma diferente pois se o usuário alterou a imagem da foto devemos passar o parâmetro da imagem na instrução SQL.

Excluindo registros no SQLite

Inclua o formulário para excluir registros no SQLite no menu Project -> Add Windows Forms e aceite o nome padrão - form4.vb clicando em Add;

O formulário form4.vb deverá ser aberto quando o usuário clicar no botão Excluir do formulário form1.vb.

O código do evento Click deste botão é visto a seguir:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        'excluir
        If id >= 0 Then
            My.Forms.Form4.ShowDialog()
            carregadados()
        Else
            MsgBox("Selecione um item a excluir")
        End If
    End Sub

Verificamos se existe um id maior que zero o que indica que um registro foi selecionado no DataGridView e em seguida abrirmos o formulário form4.vb;

A seguir defina o leiaute do formulário conforme o da figura abaixo onde temos a exibição dos dados que desejamos excluir no formulário;

Vamos usar os seguintes namespaces neste formulário:

Imports System.Data.SQLite
Imports CadastroSQLite.Funcoes

A seguir defina as variáveis codigo usada para armazenar o id que desejamos excluir e d que representa o OpenFileDialog:

Dim codigo As Integer
Dim d As New OpenFileDialog

No evento Load do formulário devemos receber a identificação do registro que vamos excluir. Lembre-se que o usuário deve selecionar o registro que deseja alterar no form1.vb e clicar no botão Excluir. Estamos obtendo este valor do formulário form1.vb : codigo = Form1.id

 Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        codigo = Form1.id
        buscaregistro(codigo)
  End Sub

A rotina buscaregistro(codigo) realiza a busca pelo registro de código selecionado e preenche os dados no formulário form4.vb;

    Private Sub buscaregistro(ByVal id As Integer)
        Try
            Dim SQLconnect As New SQLite.SQLiteConnection()
            Dim SQLcommand As SQLiteCommand
            'define string de conexão com banco de dados SQLite - Macoratti.db criado na pasta c:\dados
            'usando a criptografia
            SQLconnect.ConnectionString = "Data Source=c:\dados\Macoratti.db;Version=3;New=True;Compress=True;"
            'abre a conexão
            SQLconnect.Open()
            'cria um comando
            SQLcommand = SQLconnect.CreateCommand
            'define a instrução sql
            SQLcommand.CommandText = "SELECT * FROM cadastro WHERE id = " & id
            'executa o comando e retorna um datareader
            Dim SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader()
            'percorre e exibe os dados
            While SQLreader.Read()
                txtID.Text = SQLreader("id")
                txtNome.Text = SQLreader("nome")
                txtIdade.Text = SQLreader("idade")
                If SQLreader("foto").Equals(System.DBNull.Value) Then
                    picFoto.Image = Nothing
                Else
                    picFoto.Image = BlobToImage(SQLreader("foto"))
                End If
            End While
            SQLcommand.Dispose()
            SQLconnect.Close()
        Catch ex As Exception
            MsgBox("Erro ao acessar o SQLite: " & ex.Message)
        End Try
    End Sub

Para efetivar a exclusão o usuário clica no botão Excluir cujo código é dado a seguir:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            Dim SQLconnect As New SQLite.SQLiteConnection()
            Dim SQLcommand As SQLiteCommand
            'define string de conexão com banco de dados SQLite - Macoratti.db criado na pasta c:\dados
            'usando a criptografia
            SQLconnect.ConnectionString = "Data Source=c:\dados\Macoratti.db;Version=3;New=True;Compress=True;"
            SQLconnect.Open()
            SQLcommand = SQLconnect.CreateCommand
            'Verifica se a imagem foi alterada
            SQLcommand.CommandText = "DELETE FROM Cadastro WHERE id = " & codigo
            'Executa a consulta para alterar
            SQLcommand.ExecuteNonQuery()
            SQLcommand.Dispose()
            SQLconnect.Close()
            MsgBox("Registro excluído com sucesso.")
            Me.Close()
        Catch ex As Exception
            MsgBox("Erro ao acessar o SQLite: " & ex.Message)
        End Try
    End Sub

Concluímos assim as rotinas usadas nas operações básicas para acessar e realizar as operações CRUD no SQLite.

A seguir temos o projeto em execução e a exibição da formulário para alterar um registro:

Se você notou temos muito código duplicado no projeto o que dificulta a realização de testes e a manutenção. Como exercício você poderia criar uma camada de acesso a dados para o projeto removendo o código pertinente dos formulários. Mãos a obra...