Installation and Configuration
Programming Methodology
Video Tutorials

Persisting Cities Data in C# with Database

This exercise focuses on creating a new version of the 'cities database' program. Instead of using text files, this version will utilize persistence to store the city data in a database. You will create a simple database connection to store and retrieve data related to cities. The program demonstrates how to interact with a database to store and manage data in a more structured and persistent way.



Group

Object Persistence in C#

Objective

1. Create a City class with properties such as Name, Country, and Population.
2. Implement methods to connect to a database and insert, retrieve, and display city data.
3. Use a simple SQLite database or any relational database to store the city information.
4. Implement functionality to persist the city data in the database, and ensure it can be retrieved when the program is run again.

Create a new version of the "cities database", using persistence to store its data instead of text files.

Example C# Exercise

 Copy C# Code 
using System;
using System.Data.SQLite; // SQLite library for database operations

class City
{
    // Properties of the City class
    public string Name { get; set; }
    public string Country { get; set; }
    public int Population { get; set; }

    // Constructor for the City class
    public City(string name, string country, int population)
    {
        Name = name;
        Country = country;
        Population = population;
    }
}

class CityDatabase
{
    // SQLite connection string
    private string connectionString = "Data Source=cityDatabase.db;Version=3;";

    // Method to create the database and the Cities table
    public void CreateDatabase()
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            conn.Open();
            string createTableQuery = "CREATE TABLE IF NOT EXISTS Cities (Name TEXT, Country TEXT, Population INTEGER)";
            SQLiteCommand cmd = new SQLiteCommand(createTableQuery, conn);
            cmd.ExecuteNonQuery();
        }
    }

    // Method to insert a new city into the database
    public void InsertCity(City city)
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            conn.Open();
            string insertQuery = "INSERT INTO Cities (Name, Country, Population) VALUES (@Name, @Country, @Population)";
            SQLiteCommand cmd = new SQLiteCommand(insertQuery, conn);
            cmd.Parameters.AddWithValue("@Name", city.Name);
            cmd.Parameters.AddWithValue("@Country", city.Country);
            cmd.Parameters.AddWithValue("@Population", city.Population);
            cmd.ExecuteNonQuery();
        }
    }

    // Method to retrieve all cities from the database
    public void DisplayCities()
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            conn.Open();
            string selectQuery = "SELECT * FROM Cities";
            SQLiteCommand cmd = new SQLiteCommand(selectQuery, conn);
            SQLiteDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine($"Name: {reader["Name"]}, Country: {reader["Country"]}, Population: {reader["Population"]}");
            }
        }
    }
}

class Program
{
    static void Main()
    {
        CityDatabase database = new CityDatabase();

        // Create the database and the Cities table if they don't exist
        database.CreateDatabase();

        // Inserting some city data into the database
        City city1 = new City("Madrid", "Spain", 3200000);
        City city2 = new City("Berlin", "Germany", 3700000);
        City city3 = new City("Tokyo", "Japan", 14000000);
        
        database.InsertCity(city1);
        database.InsertCity(city2);
        database.InsertCity(city3);

        // Displaying all cities from the database
        Console.WriteLine("Cities in the database:");
        database.DisplayCities();
    }
}

 Output

Cities in the database:
Name: Madrid, Country: Spain, Population: 3200000
Name: Berlin, Country: Germany, Population: 3700000
Name: Tokyo, Country: Japan, Population: 14000000

Share this C# Exercise

More C# Practice Exercises of Object Persistence in C#

Explore our set of C# Practice Exercises! Specifically designed for beginners, these exercises will help you develop a solid understanding of the basics of C#. From variables and data types to control structures and simple functions, each exercise is crafted to challenge you incrementally as you build confidence in coding in C#.

  • Working with Arrays and Binary Files in C#

    This exercise focuses on working with arrays and binary files in C#. You will create a program that allows you to dump the contents of an array into a binary file and restore the d...

  • Working with Classes, Arrays, and Binary Files in C#

    This exercise focuses on working with classes, arrays, and binary files in C#. The goal is to create three classes: Table, SetOfTables, and a test program. The SetOfTables class wi...

  • Persisting Insects Data in C#

    This exercise focuses on persisting data using a storage method such as a database or file system. You will create a new version of the 'insects' exercise where the data about inse...