C# Exercise: 200 Database query

In this exercise, you need to create a program that displays the data about the books that your previous program has stored in the SQLite database. The goal of this exercise is to learn how to retrieve and display data from a database in a C# application. Through this exercise, you will practice executing SQL queries to retrieve information previously stored in a database, specifically about books (title, author, genre, and summary). The data entered and stored in the previous exercise will be displayed on the console or in a graphical interface, depending on how you decide to present the information.

By completing this exercise, you will learn how to work with SELECT queries in SQLite, how to retrieve data from a database, and how to organize that information in a way that is clear and useful to the user. This exercise is essential for understanding how to interact with databases in C# applications, managing input and output of information effectively and efficiently.

 Exercise

Database query

 Objetive

Create a program to display the data about books which your previous program has stored.

 Example Code

// 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

// Define a class to handle the database operations for querying the books
class BookDataQuery
{
    private string connectionString; // Connection string to connect to the SQLite database

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

    // 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
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 BookDataQuery to handle database query operations
        BookDataQuery query = new BookDataQuery(connectionString);

        // Display all books stored in the database
        query.DisplayBooks();
    }
}