Full acces to a database Learn programming C#



Lesson:

Access To Relational Databases


Exercise:

Full acces to a database


Objetive:

Create a program that allows the user to enter information about books and browse the existing data. It should handle the case where the data file does not exist when the program starts.


Code:

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();
    }
}