C#  - CRUD básico no SQLite com VS 2015

Neste artigo vou mostrar como realizar um CRUD básico usando o banco de dados SQLite em uma aplicação Windows forms com o VS Community 2015 e a linguagem C#.

Neste artigo eu vou mostrar como realizar um CRUD básico usando o SQLite e a linguagem C# fazendo algumas considerações sobre como usar a ADO .NET.

Recursos usados:

• Visual Studio Community 2015 (update 3)

• SQLite

Nota: Baixe e use a versão Community 2015 do VS ela é grátis e é equivalente a versão Professional.

Criando o banco de dados via SQLite Administrador

Existem muitas ferramentas para o gerenciamento e a criação de banco de dados no SQLite, no link abaixo você pode encontrar muitas dessas ferramentas: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

Eu vou usar o SQLite Administrator que você pode baixar aqui: http://sqliteadmin.orbmu2k.de/;

Após fazer o download do SQLite, instalar, e, em seguida instalar o provedor para ADO .NET, instale por último o SQL Administrator.

Após a instalação ao abrir o programa e selecionar o idioma português você verá a seguinte tela:

Para criar o banco de dados basta selecionar no menu Database a opção Novo e informar a localização e nome do arquivo.

Para o exemplo deste artigo eu vou criar o banco de dados Cadastro na pasta c:\dados\SQLite; (Seria criado o arquivo Cadastro.s3db )

Para criar uma tabela clique no item Tabelas e selecione Criar Tabela;

Informe o nome da tabela e clique no botão adicionar campo para incluir os campos informando o tipo de do campo e o valor padrão;

Eu vou criar uma tabela chamada Alunos com a seguinte estrutura:

Podemos inclusive obter a instrução SQL para gerar a tabela via script:

Para incluir dados na tabela podemos usar a janela de consulta SQL informando a instrução SQL :

Para editar os dados temos a janela Edita Dados:

Dessa forma já temos o nosso banco de dados e tabela criados no SQLite e prontos para serem usados na nossa aplicação C#.

Criando a solução no VS 2015 Community

Abra o Visual Studio Community 2015 e clique em New Project;

Selecione Other Project Types -> Visual Studio Solution e Blank Solution

Informe o nome CRUD_SQLite e clique no botão OK.

Informe o nome CRUD_SQLite  e clique no botão OK.  Com isso criamos a solução.

Agora que temos a solução criada vamos incluir um projeto do tipo Windows Forms onde eu irei mostrar como fazer o CRUD usando uma abordagem sem camadas.

Nessa abordagem irei incluir todo o código ADO .NET no formulário Windows Forms para mostrar uma maneira não muito aderente, mas muito usada, de acessar e persistir informações em banco de dados.

A seguir irei mostrar a abordagem em camadas, que é mais robusta e a mais indicada.

Incluindo o projeto Windows para a abordagem sem camadas

Vamos agora incluir o projeto Windows Forms para realizar o CRUD.

No menu File clique em Add -> New Project;

Selecione o template Windows -> Windows Forms Application e informe o nome : CRUD_SQLite.SemCamadas;

Clique em OK.

Instalando o pacote do SQLite via Nuget

No menu Tools clique em Nuget Packet Manager e a seguir em Manage Nuget Packages for Solution;

Clique na guia Browse, informe SQLite e a seguir escolha o pacote do SQLite marcando o projeto para o qual deseja instalar e clique em Install;

Ao final você verá em References na janela Solution Explorer os pacotes referenciados no projeto.

Criando a classe de domínio Aluno

Vamos criar uma classe chamada Aluno que irá representar o nosso domínio e será usada para armazenar e transferir informações de um aluno.

Essa classe terá 4 propriedades espelhando a tabela alunos que foi criada no banco de dados Cadastro.

No menu Project clique em Add Class e informe o nome Aluno. A seguir inclua o código abaixo nesta classe:

    public class Aluno
    {
        public int Id { get; set; }
        public string nome { get; set; }
        public string email { get; set; }
        public int idade { get; set; }
    }

Essa classe irá nos  ajudar quando formos definir as operações CRUD pois poderemos passar o objeto aluno e não valores primitivos que representariam os valores das informações dos alunos.

Definindo a interface com o usuário

Agora vamos definir a interface no formulário form1.cs incluindo a partir da ToolBox os seguintes controles:

• 5 Panels

• 1 Picturebox

• 1 DataGridView - dgvAlunos

• 1 Label

• 1 TextBox - txtCriterio

• 4 Buttons - btnIncluir, btnAlterar, btnExcluir e btnSair

Disponha os controles conforme o leiaute da figura abaixo:

Para poder incluir e alterar dados vamos precisar de um formulário que exiba os dados a serem alterados ou um formulário apenas para o usuário informar os novos dados.

No menu Project clique em Add Windows Forms e aceite o nome padrão Form2.cs.

A seguir inclua 3 Labels , 3 TextBox (txtNome, txtEmail e txtIdade) e 2 Buttons (btnSalvar e btnSair) conforme o leiaute abaixo:

Agora podemos implementar o acesso e a persistência das informações usando ADO .NET para o SQLite.

Existem diversas formas de acessar e persistir dados mas em geral a sequência usada é a seguinte:

• Fazer a conexão com o banco de dados usando a string de conexão
• Executar um comando SQL contra o banco de dados para recuperar,  excluir ou alterar informações;
• Fechar a conexão com o banco de dados;

Seguindo este fluxo, podemos usar o código abaixo par carregar informações e exibir no DataGridView - dgvAlunos :

       private void CarregaDados()
        {
            DataTable dt = new DataTable();
            SQLiteConnection conn = null;

            String sql = "select * from Alunos";
            String strConn = @"Data Source=C:\dados\SQLite\Cadastro.s3db";
            try
            {
                conn = new SQLiteConnection(strConn);
                SQLiteDataAdapter da = new SQLiteDataAdapter(sql, strConn);
                da.Fill(dt);
                dgvAlunos.DataSource = dt.DefaultView;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erro :" + ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

O código acima funciona, mas possui muitas responsabilidades e pode ser melhorado. Destaques negativos do código acima:

• Muito verboso;
• Serve somente para acessar o SQLite;
• Usa a string de conexão embutida o que dificulta alterações na conexão;
• Usa o comando SQL embutido o que dificulta alterações no comando sql;
• Utiliza o componente DataGridView o que dificulta alterações na interface;

Vejamos a seguir outras duas opções de código que obtém o mesmo resultado mas que apresentam melhorias:

        public DataTable LeDados1<T>(string query) where T : IDbConnection, new()
        {
            using (var conn = new T())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = query;
                    cmd.Connection.ConnectionString = _connectionString;
                    cmd.Connection.Open();
                    var table = new DataTable();
                    table.Load(cmd.ExecuteReader());
                    return table;
                }
            }
        }

       public DataTable LeDados2<S, T>(string query) where S : IDbConnection, new()
                                           where T : IDbDataAdapter, IDisposable, new()
        {
            using (var conn = new S())
            {
                using (var da = new T())
                {
                    using (da.SelectCommand = conn.CreateCommand())
                    {
                        da.SelectCommand.CommandText = query;
                        da.SelectCommand.Connection.ConnectionString = _connectionString;
                        DataSet ds = new DataSet(); 
                        da.Fill(ds);
                        return ds.Tables[0];
                    }
                }
            }
        }

Esta abordagem possui as seguintes vantagens:

• Os objetos ADO .NET agora são genéricos e podemos usar esses métodos para acessar quaisquer banco de dados;
• A string de conexão pode ser alterada sem afetar o código; (podemos armazenar a string de conexão em um arquivo de configuração da aplicação)
• O comando SQL pode ser alterado sem afetar o código;
• O código retorna um DataTable que pode ser usado em qualquer componente de interface;
• O código possui uma única responsabilidade : retornar um DataTable.

Para chamar o método LeDados1() usamos :   dgvAlunos.DataSource = LeDados1<SQLiteConnection>("Select * from Alunos");

Para chamar o método LeDados2() usamos :   dgvAlunos.DataSource = LeDados2<SQLiteConnection, SQLiteDataAdapter>("Select * from Alunos");

O  resultado final irá exibir todos os registros da tabela Alunos no DataGridView.

Dessa forma quando for implementar sua camada de acesso a dados considere usar Generics para obter uma maior flexibilidade.

Nota: Devemos atentar também para o fato de que usar um comando SQL que retorna todos os dados de uma tabela não é uma boa prática.

Implementando o CRUD

Para realizar a manutenção de dados geralmente usamos as instruções SQL : INSERT INTO, UPDATE, DELETE FROM, SELECT

Existem três abordagens que podemos usar para realizar esta tarefa:

• Usar consultas SQL inline - "SELECT * FROM alunos Where Id =" + txtId.text

Essa instrução sql seleciona todos os dados da tabela alunos usando o critério da id que é fornecido e obtido na caixa de texto txtId;
Essa abordagem é muito usada mas não deve ser usada pois abre uma porta para que seja feita um ataque conhecido como injeção SQL, onde um código malicioso pode ser injetado no seu código.

• Usar consultas SQL parametrizadas - "SELECT * FROM alunos Where Id = @Id"

Para evitar esse tipo de problema devemos usar parâmetros, pois qualquer informação colocada como parâmetro será tratada como um campo de dados, e não como parte da instrução SQL tornando assim sua aplicação mais segura.

Na construção de uma consulta parametrizada devemos seguir 3 etapas:

• Construir uma string para o comando SQL com parâmetros;
• Declarar o objeto ADO.NET, atribuindo os valores apropriados;
• Atribuir o objeto ADO .NET para a propriedade Parameters do objeto ADO .NET;

• Usar Stored Procedures - Esse recurso pode ser usado no SQL Server, MySQL mas o SQLite não suporta Stored Procedures.

Os procedimentos armazenados são como pequenos programas em miniaturas que ficam 'dentro' do banco de dados prontos para serem usados. Geralmente uma stored procedure(sp) consiste em instruções SQL que realizam tarefas como selecionar , inclui , alterar e excluir dados.

Na próxima parte do artigo iremos iniciar a implementação do CRUD usando ADO .NET.

"Porque estas nações, que hás de possuir, ouvem os prognosticadores e os adivinhadores; porém a ti o Senhor teu Deus não permitiu tal coisa."
Deuteronômio 18:14

  Gostou ?   Compartilhe no Facebook   Compartilhe no Twitter

Referências:

• Seção VB .NET do Site Macoratti.net

• Super DVD .NET - A sua porta de entrada na plataforma .NET

• Super DVD Vídeo Aulas - Vídeo Aula sobre VB .NET, ASP .NET e C#

• Seção C# do site Macoratti.net

• Super DVD C#

• Super DVD Visual Basic

• Curso Básico VB .NET - Vídeo Aulas

• Curso C# Básico - Vídeo Aulas

• Visual Studio - Dica de produtividade - Quick Launch - Macoratti.net

• Visual Studio - Dica de produtividade - Nuget - Macoratti.net

• C# - CRUD com ADO .NET e Stored Procedures - Macoratti.net

• C# - Usando SQLite - Macoratti.net

• VB .NET - Acessando o SQLite com o VS 2013 - II - Macoratti.net

• VB .NET - Usando o banco de dados SQLite - 2 - Macoratti.net

José Carlos Macoratti