Como Usar SQLite em uma Aplicação .NET

A Base de Dados SQLite é conhecida por ser muito leve, rápida e por ser utilizada em várias aplicações que necessitam de uma base de dados confiável e robusta. Atualmente a base de dados SQLite é muito utilizada em dispositivos móveis como Android, Iphone e Ipad.

Antigamente para utilizar a base de dados SQLite em .NET era um pouco complicado, porque precisavamos utilizar as DLLs do SQLite através do PInvoke em .NET. Agora no site oficial do SQLite foi inserida a biblioteca  System.Data.SQLite para ser utilizada em aplicações .NET.

Para este post criei uma Aplicação Console para manipular as informações de uma base de dados de livros. Nesta aplicação o usuário poderá cadastrar, excluir, alterar e visualizar os livros existentes na base de dados.

Para utilizar a biblioteca do SQLite em .NET é necessário fazer o download da biblioteca e instalar na máquina de desenvolvimento. A biblioteca pode ser baixada no site oficial do SQLite.

No projeto Console no Visual Studio é necessário incluir uma referência à Dll System.Data.SQLite.dll, que normalmente é instalada no diretório C:\Arquivos de Programas\System.Data.SQLite\2008\bin.

No projeto Console foi criada a classe SqliteDb que possui os métodos para inserir, alterar e excluir dados da base SQLite,  a classe auxiliar Livro (Listagem 1) que é utilizada para armazenar os dados do livro que está sendo utilizado pelos outros métodos e a classe Program que possui o método Main e outros métodos auxiliares para executar a aplicação.

Listagem 1:

    public class Livro
    {
        public long id { get; set; }
        public string titulo { get; set; }
        public string autor { get; set; }
        public string ano { get; set; }
        public string genero { get; set; }
    }

A seguir são detalhados os métodos que fazem parte da classe SqliteDb:
Foram criadas duas constantes, uma para definir o nome da base de dados e a outra para definir a conexão com a base de dados SQLite.

        const string DATABASE_NAME = "livros_classicos.db";
        const string CONNECTION = "Data Source=" + DATABASE_NAME;

O método CreateDatabase() cria a base de dados livros_classicos.db, se não existir e cria a tabela livros:

        public void CreateDatabase()
        {
            SQLiteConnection conn = new SQLiteConnection(CONNECTION);
            try
            {
                if (!File.Exists(DATABASE_NAME))
                {
                    SQLiteConnection.CreateFile(DATABASE_NAME);

                    // Cria a tabela livros
                    conn.Open();
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "CREATE TABLE livros (" +
                        "id INTEGER NOT NULL PRIMARY KEY," + 
                        "titulo TEXT," + 
                        "autor TEXT," + 
                        "ano_publicacao TEXT," + 
                        "genero TEXT)";

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

O método InsertLivro(Livro) insere um livro na base:

        public bool InsertLivro(Livro livro)
        {
            bool resultado = false;
            SQLiteConnection conn = new SQLiteConnection();

            try
            {
                conn.ConnectionString = CONNECTION;
                conn.Open();

                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddWithValue("@Titulo", livro.titulo);
                cmd.Parameters.AddWithValue("@Autor", livro.autor);
                cmd.Parameters.AddWithValue("@Ano", livro.ano);
                cmd.Parameters.AddWithValue("@Genero", livro.genero);

                cmd.CommandText = "INSERT INTO livros " +
                    "(titulo, autor, ano_publicacao, genero) " +
                    "VALUES (@Titulo, @Autor, @Ano, @Genero)";

                int registro = cmd.ExecuteNonQuery();

                // Verifica se o registro foi inserido
                if (registro == 1)
                {
                    resultado = true;
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }

O método SearchLivro(string) faz a procura de livros por título e retorna a lista de livros filtrados. Neste método optei em fazer uma busca utilizando o operador LIKE do SQL para fazer uma busca por padrão e não uma busca exata.

        public List<Livro> SearchLivro(string titulo)
        {
            List<Livro> livros = new List<Livro>();

            SQLiteConnection conn = new SQLiteConnection();

            try
            {
                conn.ConnectionString = CONNECTION;
                conn.Open();

                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddWithValue("@Titulo", titulo);

                cmd.CommandText = "SELECT * FROM livros WHERE titulo LIKE '%"
                    + titulo + "%'";

                SQLiteDataReader reader = cmd.ExecuteReader();

                while (reader.Read() == true)
                {
                    Livro livro = new Livro();
                    livro.id = (long)reader["id"];
                    livro.titulo = (string)reader["titulo"];
                    livro.autor = (string)reader["autor"];
                    livro.ano = (string)reader["ano_publicacao"];
                    livro.genero = (string)reader["genero"];

                    livros.Add(livro);
                
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return livros;
        }

O método UpdateLivro(Livro) atualiza os dados do livro.

        public string UpdateLivro(Livro livro)
        {
            string resultado = "";
            SQLiteConnection conn = new SQLiteConnection();

            try
            {

                conn.ConnectionString = CONNECTION;
                conn.Open();

                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddWithValue("@Id", livro.id);
                cmd.Parameters.AddWithValue("@Titulo", livro.titulo);
                cmd.Parameters.AddWithValue("@Autor", livro.autor);
                cmd.Parameters.AddWithValue("@Ano", livro.ano);
                cmd.Parameters.AddWithValue("@Genero", livro.genero);

                cmd.CommandText = "UPDATE livros SET titulo=@Titulo, autor=@Autor," +
                    " ano_publicacao=@Ano, genero=@Genero WHERE id=@Id";

                int registroAtualizado = cmd.ExecuteNonQuery();


                if (registroAtualizado == 1)
                {
                    resultado = "O livro foi atualizado com sucesso.";
                }
                else
                {
                    resultado = "O livro não foi atualizado.";
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return resultado;
        }

O método DeleteLivro(string) apaga o livro filtrado, na base de dados, através do id do livro.

        public string DeleteLivro(string id)
        {
            string resultado = "";
            SQLiteConnection conn = new SQLiteConnection();

            try
            {
                conn.ConnectionString = CONNECTION;
                conn.Open();

                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddWithValue("@Id", id);

                cmd.CommandText = "DELETE FROM livros WHERE id=@Id";

                int registroAtualizado = cmd.ExecuteNonQuery();


                if (registroAtualizado == 1)
                {
                    resultado = "O livro foi excluído com sucesso.";
                }
                else
                {
                    resultado = "O livro não foi excluído.";
                }
            }
            catch (Exception ex)
            {
                resultado = ex.Message;
            }

            return resultado;
        }
    }

A seguir são detalhados os métodos que fazem parte da classe Program:

O método Main é o responsável pela inicialização e execução da aplicação:

        static void Main(string[] args)
        {
            ConsoleKeyInfo key = new ConsoleKeyInfo();
            Console.WriteLine("Digite S a qualquer momento" +
                " para sair da aplicação!");

            SqliteDb sqlitedb = new SqliteDb();

            // Cria a base de dados se não existir
            sqlitedb.CreateDatabase();

            while (true)
            {
                Console.WriteLine("Digite I para incluir, A para alterar," +
                    " E para excluir, P para pesquisar e V para visualizar todos os livros:");

                // Lê a tecla digitada
                key = Console.ReadKey(true);

                // Sai da aplicação
                if (key.KeyChar == 'S' || key.KeyChar == 's')
                {                    
                    break;
                }

                // Pesquisa um livro por título
                if (key.KeyChar == 'P' || key.KeyChar == 'p')
                {
                    Console.WriteLine("Digite o título do livro a ser pesquisado:");
                    string titulo = Console.ReadLine();

                    // Exibe as informações do livro
                    BuscarLivro(titulo);
                }

                // Inclui um livro
                if (key.KeyChar == 'I' || key.KeyChar == 'i')
                {
                    // Lê as informações do livro a ser inserido
                    Livro livro = GetLivro();

                    // Insere o livro
                    if (sqlitedb.InsertLivro(livro) == true)
                    {
                        Console.WriteLine("O livro foi incluído com sucesso!");
                    }
                }

                // Exclui um livro
                if (key.KeyChar == 'E' || key.KeyChar == 'e')
                {
                    Console.WriteLine("Digite o título do livro a ser excluído:");
                    string titulo = Console.ReadLine();

                    // Buscar o livro
                    List<Livro> livros = BuscarLivro(titulo);

                    // Obtêm o Id do livro a ser excluído
                    string id = GetId(livros);

                    if (id != "")
                    {
                        // Exclui o livro
                        string resultado = sqlitedb.DeleteLivro(id);
                        Console.WriteLine(resultado);
                    }
                }

                // Altera um livro
                if (key.KeyChar == 'A' || key.KeyChar == 'a')
                {
                    Console.WriteLine("Digite o título do livro a ser alterado:");
                    string titulo = Console.ReadLine();

                    // Buscar livro
                    List<Livro> livros = BuscarLivro(titulo);

                    // Obtêm o Id do livro a ser alterado
                    string id = GetId(livros);

                    if (id != "")
                    {
                        // Lê as novas informações do livro a ser alterado
                        Livro livro = GetLivro();

                        // Atualiza o valor do id
                        livro.id = long.Parse(id); ;

                        // Altera as informações do livro
                        string resultado = sqlitedb.UpdateLivro(livro);
                        Console.WriteLine(resultado);
                    }
                }
            }
        }

O método possui um loop while que apresenta um menu para que o usuário possa selecionar a função que deseja executar ou sair da aplicação. Quando a aplicação é executada é apresentado o seguinte menu:

Digite S a qualquer momento para sair da aplicação!
Digite I para incluir, A para alterar, E para excluir, P para pesquisar e V para
visualizar todos os livros:

Além do método Main a classe Program possui também os métodos BuscarLivro(string), GetId(List<Livro) e GetLivro().

O método BuscarLivro(string) faz a busca dos livros por título e retorna uma lista de livros.

        private static List<Livro> BuscarLivro(string titulo)
        {
            SqliteDb sqlitedb = new SqliteDb();

            List<Livro> livros = sqlitedb.SearchLivro(titulo);
            Console.WriteLine();
            foreach (Livro item in livros)
            {
                Console.WriteLine(item.id + "\t" + item.titulo + "\t" + item.autor + "\t" +
                    item.ano + "\t" + item.genero);
            }
            Console.WriteLine();

            return livros;
        }

O método GetId(List<Livro> livros) faz a busca dos livros e retorna o id do livro.

        private static string GetId(List<Livro> livros)
        {
            // Digitar o id do livro a ser alterado se houver mais de 
            // um livro como resultado
            string id = "";
            if (livros.Count == 0)
            {
                Console.WriteLine("O livro não foi encontrado.");
            }
            else if (livros.Count == 1)
            {
                id = livros[0].id.ToString();
            }
            else
            {
                Console.WriteLine("Digite o id do livro a ser alterado:");
                id = Console.ReadLine();
            }

            return id;
        }

O método GetLivro() retorna um objeto livro com os detalhes do livro.

        private static Livro GetLivro()
        {
            Livro livro = new Livro();
            Console.WriteLine("Digite o título do livro:");
            livro.titulo = Console.ReadLine();
            Console.WriteLine("Digite o autor do livro:");
            livro.autor = Console.ReadLine();
            Console.WriteLine("Digite o ano de publicação do livro:");
            livro.ano = Console.ReadLine();
            Console.WriteLine("Digite o gênero do livro:");
            livro.genero = Console.ReadLine();

            return livro;

        }