Acceso completo a una base de datos Curso de Programación C# Sharp

Lección:

Acceso a Bases Datos Relacionales


Ejercicio:

Acceso completo a una base de datos 6


Objetivo:

Cree un programa que permita al usuario ingresar datos sobre libros y navegar por los datos existentes. Debe comportarse correctamente si el archivo de datos no sale al iniciarse.


Código:

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