10.03 - Full acces to a database

Objetive

Create a program to allow the user both entering data about books and surfing the existing data. It must behave correctly if the data file does not exit on start.

Solution

using System;
using System.Data.SQLite;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        bool finish = false;
        string option;
        string title, author, genere, sumary;
        SQLiteCommand cmd;

       
        SQLiteConnection conection = new SQLiteConnection
            ("Data Source=ejemplo01.sqlite;Version=3;New=True;Compress=True;");
        conection.Open();

        if (!File.Exists("ejemplo01.sqlite"))
        {
            
            Console.WriteLine("Creando la base de datos...");
        }
        string creacion = "CREATE TABLE IF NOT EXISTS books(title varchar(20),author varchar(20),genere varchar(20), sumary varchar(20));";
        cmd = new SQLiteCommand(creacion, conection);
        cmd.ExecuteNonQuery();


        do
        {
            Console.WriteLine();
            Console.WriteLine("Books database");
            Console.WriteLine();
            Console.WriteLine("1.- Add a new Book");
            Console.WriteLine("2.- View all Books");
            Console.WriteLine("0.- Exit");
            Console.WriteLine();
            Console.Write("Choose an Option: ");
            option = Console.ReadLine();

            switch (option)
            {
                case "0":
                    finish = true;
                    break;
                case "1":
                    string continueOption;
                    do
                    {
                        Console.Write("Enter the title: ");
                        title = Console.ReadLine();

                        Console.Write("Enter the author: ");
                        author = Console.ReadLine();

                        Console.Write("Enter the genere: ");
                        genere = Console.ReadLine();

                        Console.Write("Enter the sumary: ");
                        sumary = Console.ReadLine();

                        string insercion = "insert into books values ('" + title + "', '" + author + "', '" + genere + "', '" + sumary + "');";
                        cmd = new SQLiteCommand(insercion, conection);

                        int cantidad = cmd.ExecuteNonQuery();
                        if (cantidad < 1)
                            Console.WriteLine("Insert Fails");

                        Console.Write("Enter another book (y/n): ");
                        continueOption = Console.ReadLine();
                    }
                    while (continueOption.ToString().ToLower() == "y");
                    break;
                case "2":
                    string consulta = "select * from books";
                    cmd = new SQLiteCommand(consulta, conection);
                    SQLiteDataReader datos = cmd.ExecuteReader();
                    int rowCount = 1;
                    Console.WriteLine();
                    while (datos.Read())
                    {
                        title = Convert.ToString(datos[0]);
                        author = Convert.ToString(datos[1]);
                        genere = Convert.ToString(datos[2]);
                        sumary = Convert.ToString(datos[3]);
                      
                        System.Console.WriteLine("{0}- Title: {1}, Author: {2}, Genere: {3}, Sumary: {4}",
       rowCount, title, author, genere, sumary);
                        rowCount++;
                    }
                    Console.WriteLine();
                    break;
            }
        }
        while (!finish);
        Console.WriteLine("Bye!!");
        conection.Close();
    }
}


Popular Posts