Database creation C# Exercise - C# Programming Course

// Importing necessary namespaces for SQLite operations, user input, and basic functionalities
using System; // For basic functionalities like Console and Exception handling
using System.Data.SQLite; // For SQLite operations

// Define the Book class to represent a book with its properties
class Book
{
    public string Title { get; set; } // Title of the book
    public string Author { get; set; } // Author of the book
    public string Genre { get; set; } // Genre of the book
    public string Summary { get; set; } // Summary of the book

    // Constructor to initialize the book properties
    public Book(string title, string author, string genre, string summary)
    {
        Title = title; // Assign the title of the book
        Author = author; // Assign the author of the book
        Genre = genre; // Assign the genre of the book
        Summary = summary; // Assign the summary of the book
    }
}

// Define a class to handle the persistence (save/load) of book data to/from an SQLite database
class BookDataPersistence
{
    private string connectionString; // Connection string to connect to the SQLite database

    // Constructor to initialize the connection string
    public BookDataPersistence(string connectionString)
    {
        this.connectionString = connectionString; // Store the connection string
    }

    // Method to insert a new book into the SQLite database
    public void AddBook(Book book)
    {
        // SQL query to insert a new book into the Books table
        string query = "INSERT INTO Books (Title, Author, Genre, Summary) VALUES (@Title, @Author, @Genre, @Summary);";

        // Establish a connection to the SQLite database
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            // Open the connection
            conn.Open();

            // Create a SQLiteCommand to execute the query
            using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
            {
                // Add parameters to prevent SQL injection
                cmd.Parameters.AddWithValue("@Title", book.Title);
                cmd.Parameters.AddWithValue("@Author", book.Author);
                cmd.Parameters.AddWithValue("@Genre", book.Genre);
                cmd.Parameters.AddWithValue("@Summary", book.Summary);

                // Execute the query to insert the data
                cmd.ExecuteNonQuery();
            }
        }
    }

    // Method to display all books in the database
    public void DisplayBooks()
    {
        // SQL query to select all books from the Books table
        string query = "SELECT * FROM Books;";

        // Establish a connection to the SQLite database
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            // Open the connection
            conn.Open();

            // Create a SQLiteCommand to execute the query
            using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
            {
                // Execute the query and get a SQLiteDataReader to read the results
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    // Print each book from the result set
                    while (reader.Read())
                    {
                        // Display each book's data
                        Console.WriteLine($"ID: {reader["Id"]}");
                        Console.WriteLine($"Title: {reader["Title"]}");
                        Console.WriteLine($"Author: {reader["Author"]}");
                        Console.WriteLine($"Genre: {reader["Genre"]}");
                        Console.WriteLine($"Summary: {reader["Summary"]}");
                        Console.WriteLine("-------------------------------------");
                    }
                }
            }
        }
    }
}

// Main class to demonstrate the functionality
class Program
{
    static void Main(string[] args)
    {
        // Connection string to the SQLite database (SQLite creates the database file if it doesn't exist)
        string connectionString = "Data Source=Books.db;Version=3;";

        // Create an instance of BookDataPersistence to handle database operations
        BookDataPersistence persistence = new BookDataPersistence(connectionString);

        // Ask the user for book information
        Console.WriteLine("Enter book title:");
        string title = Console.ReadLine(); // Read the title of the book

        Console.WriteLine("Enter book author:");
        string author = Console.ReadLine(); // Read the author of the book

        Console.WriteLine("Enter book genre:");
        string genre = Console.ReadLine(); // Read the genre of the book

        Console.WriteLine("Enter book summary:");
        string summary = Console.ReadLine(); // Read the summary of the book

        // Create a Book object with the user's input
        Book newBook = new Book(title, author, genre, summary);

        // Add the new book to the database
        persistence.AddBook(newBook);

        // Display all books in the database
        Console.WriteLine("\nBooks in the database:");
        persistence.DisplayBooks(); // Display all books in the database
    }
}