Full acces to a database C# Exercise - C# Programming Course

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

// Define a class to handle the database operations (CRUD operations)
class BookDataHandler
{
    private string connectionString; // Connection string to connect to the SQLite database

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

    // Method to create the Books table in the database if it does not exist
    public void CreateTableIfNotExists()
    {
        // SQL command to create the Books table
        string createTableQuery = @"CREATE TABLE IF NOT EXISTS Books (
                                      Id INTEGER PRIMARY KEY AUTOINCREMENT,
                                      Title TEXT NOT NULL,
                                      Author TEXT NOT NULL,
                                      Genre TEXT NOT NULL,
                                      Summary TEXT NOT NULL);";

        // 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(createTableQuery, conn))
            {
                // Execute the query to create the table
                cmd.ExecuteNonQuery();
            }
        }
    }

    // Method to insert a new book into the database
    public void InsertBook(string title, string author, string genre, string summary)
    {
        // SQL query to insert a new book into the Books table
        string insertQuery = "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(insertQuery, conn))
            {
                // Add parameters to prevent SQL injection
                cmd.Parameters.AddWithValue("@Title", title);
                cmd.Parameters.AddWithValue("@Author", author);
                cmd.Parameters.AddWithValue("@Genre", genre);
                cmd.Parameters.AddWithValue("@Summary", summary);

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

    // Method to display all books stored in the SQLite 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
                    Console.WriteLine("Books in the database:");
                    Console.WriteLine("-------------------------------------");
                    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 of the program
class Program
{
    static void Main(string[] args)
    {
        // Connection string to the SQLite database (SQLite creates the database file if it doesn't exist)
        string databaseFile = "Books.db"; // Path to the database file
        string connectionString = $"Data Source={databaseFile};Version=3;";

        // Check if the database file exists; if not, inform the user
        if (!File.Exists(databaseFile))
        {
            Console.WriteLine("Database file does not exist. A new database will be created.");
        }

        // Create an instance of BookDataHandler to handle database operations
        BookDataHandler dataHandler = new BookDataHandler(connectionString);

        // Create the Books table if it does not exist
        dataHandler.CreateTableIfNotExists();

        // Menu for the user to choose an action
        while (true)
        {
            Console.WriteLine("1. Add a new book");
            Console.WriteLine("2. View all books");
            Console.WriteLine("3. Exit");
            Console.Write("Enter your choice: ");
            string choice = Console.ReadLine();

            if (choice == "1")
            {
                // Prompt the user for book details
                Console.Write("Enter the book title: ");
                string title = Console.ReadLine();

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

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

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

                // Insert the new book into the database
                dataHandler.InsertBook(title, author, genre, summary);
                Console.WriteLine("Book added successfully!");
            }
            else if (choice == "2")
            {
                // Display all books in the database
                dataHandler.DisplayBooks();
            }
            else if (choice == "3")
            {
                // Exit the program
                break;
            }
            else
            {
                // Invalid choice
                Console.WriteLine("Invalid choice. Please try again.");
            }
        }
    }
}