Cities - persistence C# Exercise - C# Programming Course

// Importing necessary namespaces for ADO.NET, collections, and basic functionalities
using System; // For basic functionalities like Console and Exception handling
using System.Collections.Generic; // To use List for storing city objects
using System.Data.SqlClient; // For database operations (ADO.NET)
using System.Configuration; // To retrieve connection string from app.config

// Define the City class to represent a city
class City
{
    public int Id { get; set; } // Unique identifier for the city
    public string Name { get; set; } // Name of the city
    public string Country { get; set; } // Country where the city is located
    public int Population { get; set; } // Population of the city

    // Constructor to initialize the city properties
    public City(int id, string name, string country, int population)
    {
        Id = id; // Assign the city's ID
        Name = name; // Assign the city's name
        Country = country; // Assign the country's name
        Population = population; // Assign the city's population
    }

    // Method to represent the city as a string (for easy display)
    public override string ToString()
    {
        return $"{Name}, {Country}, Population: {Population}"; // Format the city's data as a string
    }
}

// Define a class to handle the persistence (save/load) of City data to/from a SQL Server database
class CityDataPersistence
{
    private string connectionString; // Connection string to connect to the SQL Server database

    // Constructor to initialize the connection string (from app.config or directly)
    public CityDataPersistence(string connectionString)
    {
        this.connectionString = connectionString; // Store the connection string
    }

    // Method to insert a new city into the database
    public void AddCity(City city)
    {
        // SQL query to insert a new city into the Cities table
        string query = "INSERT INTO Cities (Name, Country, Population) VALUES (@Name, @Country, @Population);";

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

            // Create a SqlCommand to execute the query
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                // Add parameters to prevent SQL injection
                cmd.Parameters.AddWithValue("@Name", city.Name);
                cmd.Parameters.AddWithValue("@Country", city.Country);
                cmd.Parameters.AddWithValue("@Population", city.Population);

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

    // Method to load all cities from the database
    public List GetCities()
    {
        List cities = new List(); // List to store all the cities from the database

        // SQL query to select all cities
        string query = "SELECT Id, Name, Country, Population FROM Cities;";

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

            // Create a SqlCommand to execute the query
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                // Execute the query and get a SqlDataReader to read the results
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    // Read each record from the result set
                    while (reader.Read())
                    {
                        // Create a new City object for each record
                        City city = new City(
                            reader.GetInt32(0), // Id
                            reader.GetString(1), // Name
                            reader.GetString(2), // Country
                            reader.GetInt32(3)  // Population
                        );

                        // Add the city to the list
                        cities.Add(city);
                    }
                }
            }
        }

        return cities; // Return the list of cities
    }
}

// Main class to demonstrate the functionality
class Program
{
    static void Main(string[] args)
    {
        // Connection string to the database (adjust this for your environment)
        string connectionString = "Server=localhost;Database=CitiesDB;Integrated Security=True;";

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

        // Create some city objects to add to the database
        City city1 = new City(0, "New York", "USA", 8419600);
        City city2 = new City(0, "Tokyo", "Japan", 13929286);
        City city3 = new City(0, "Paris", "France", 2140526);

        // Add the cities to the database
        persistence.AddCity(city1);
        persistence.AddCity(city2);
        persistence.AddCity(city3);

        // Load all cities from the database
        List cities = persistence.GetCities();

        // Display the loaded cities
        Console.WriteLine("Cities in the database:");
        foreach (var city in cities)
        {
            Console.WriteLine(city); // Print each city's data
        }
    }
}