Building a C# Console App to Load GDP Data from the World Bank API Based on User Input

In this article, we will build a C# console application that connects to a SQL Server database and retrieves GDP data for a specific country from the World Bank API. Users can specify the country they want to load data for, making the app dynamic and interactive.

The tutorial covers:

Setting up the SQL database connection in C#
Retrieving country-specific GDP data from the World Bank API
Allowing the user to choose a country based on the name or code before loading the data
Creating and managing SQL tables to store the GDP data for each selected country

Requirements

A SQL Server instance (local or cloud-based).
.NET Framework with C# for coding.
Internet connection for API access.

Project Structure

The application contains:

A Country class to store the country name and code.
The Program class, where the main logic lives, including database connection, table creation, and data retrieval from the World Bank API.

Step 1: Setting Up the Country List

First, create a Country class that defines the country name and code. We’ll populate this list with a few common countries and their codes. Here’s how the code looks:

class Country
{
    public string Name { get; }
    public string Code { get; }

    public Country(string name, string code)
    {
        Name = name;
        Code = code;
    }
}

With the Country class ready, we’ll create a list of countries to allow users to choose which one they want to load data for.

var countries = new List<Country>
{
    new Country("Brazil", "BR"),
    new Country("Argentina", "AR"),
    new Country("United States", "US"),
    // Add more countries as needed
};

Step 2: Connecting to the SQL Database

To connect to SQL Server, we use a connection string, which is specific to your environment. The code uses Integrated Security=True for local authentication, but this can be adjusted depending on your database setup:

string connectionString = "Data Source=localhost;Initial Catalog=wb;Integrated Security=True;";

Step 3: Asking the User for a Country Selection

We want the user to enter a country name or code to specify which country’s GDP data they’re interested in. Here’s how the prompt and user input are handled:

Console.WriteLine("Enter the country name or code to load GDP data:");
string userInput = Console.ReadLine().Trim();

// Find the country in the list based on the input
var selectedCountry = countries.FirstOrDefault(c => 
    c.Name.Equals(userInput, StringComparison.OrdinalIgnoreCase) || 
    c.Code.Equals(userInput, StringComparison.OrdinalIgnoreCase));

if (selectedCountry == null)
{
    Console.WriteLine("Country not found. Please check the name or code and try again.");
    return;
}

Using FirstOrDefault, we search through the countries list for a match. If the country is not found, the program exits, letting the user know that the input is invalid.

Step 4: Creating and Managing Tables in SQL

We’ll create a table for the chosen country if it doesn’t already exist, and drop the existing table each time to keep the data fresh. The table will store GDP, year, and country code. Here’s the SQL logic embedded in C#:

string tableName = $"GDP_Data_{selectedCountry.Name.Replace(" ", "_")}";
string dropTableQuery = $"IF OBJECT_ID('{tableName}', 'U') IS NOT NULL DROP TABLE {tableName};";
using (SqlCommand dropCommand = new SqlCommand(dropTableQuery, connection))
{
    dropCommand.ExecuteNonQuery();
    Console.WriteLine($"Table '{tableName}' dropped successfully.");
}

// Creating the new table
string createTableQuery = $@"
CREATE TABLE {tableName} (
    Id INT IDENTITY(1,1),
    CountryCode NVARCHAR(3),
    GDP DECIMAL(18, 2),
    Year INT PRIMARY KEY 
);";
using (SqlCommand createCommand = new SqlCommand(createTableQuery, connection))
{
    createCommand.ExecuteNonQuery();
    Console.WriteLine($"Table '{tableName}' created successfully.");
}

In this code:

IF OBJECT_ID checks if the table exists, and if so, it drops the table.
Then, the CREATE TABLE command is used to generate a new table for the selected country.

Step 5: Fetching GDP Data from the World Bank API

To retrieve the data, we call the World Bank API with the chosen country’s code and the year range. Here’s the structure of this step:

string apiUrl = $"https://api.worldbank.org/v2/country/{selectedCountry.Code}/indicator/NY.GDP.MKTP.CD?date=1989:2023&format=json";
using (HttpClient client = new HttpClient())
{
    HttpResponseMessage response = await client.GetAsync(apiUrl);
    if (response.IsSuccessStatusCode)
    {
        string jsonResponse = await response.Content.ReadAsStringAsync();
        JArray jsonData = JArray.Parse(jsonResponse);

        foreach (var item in jsonData[1])
        {
            string countryCode = item["countryiso3code"].ToString();
            string gdp = item["value"]?.ToString() ?? "0";
            string year = item["date"].ToString();

            // Insert GDP data into SQL
            string insertQuery = $@"
            INSERT INTO {tableName} (CountryCode, GDP, Year)
            VALUES (@CountryCode, @GDP, @Year)";
            using (SqlCommand insertCommand = new SqlCommand(insertQuery, connection))
            {
                insertCommand.Parameters.AddWithValue("@CountryCode", countryCode);
                insertCommand.Parameters.AddWithValue("@GDP", decimal.Parse(gdp));
                insertCommand.Parameters.AddWithValue("@Year", int.Parse(year));
                insertCommand.ExecuteNonQuery();
            }
        }

        Console.WriteLine($"Data added to table '{tableName}' successfully.");
    }
    else
    {
        Console.WriteLine($"Error retrieving data for {selectedCountry.Name}");
    }
}

In this code:

We fetch the GDP data from the World Bank API based on the selected country’s code.
The data is inserted into SQL using parameterized SQL commands, which prevent SQL injection.

Complete Program Code

Here’s the complete code for reference:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Http;
using System.Threading.Tasks;
using Newtonsoft.Json.Linq;

namespace WbSqlLoadCountrys
{
    // Define Country class
    class Country { ... }

    internal class Program
    {
        static async Task Main(string[] args)
        {
            Console.WriteLine("Connecting to Database...");
            string connectionString = "Data Source=localhost;Initial Catalog=wb;Integrated Security=True;";

            // List of countries
            var countries = new List<Country> { ... };

            // Ask user for country name or code
            Console.WriteLine("Enter the country name or code:");
            string userInput = Console.ReadLine().Trim();
            var selectedCountry = countries.FirstOrDefault(c => 
                c.Name.Equals(userInput, StringComparison.OrdinalIgnoreCase) || 
                c.Code.Equals(userInput, StringComparison.OrdinalIgnoreCase));

            if (selectedCountry == null) { ... }

            // Connect to SQL and create table
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string tableName = $"GDP_Data_{selectedCountry.Name.Replace(" ", "_")}";
                string dropTableQuery = $"IF OBJECT_ID('{tableName}', 'U') IS NOT NULL DROP TABLE {tableName};";
                using (SqlCommand dropCommand = new SqlCommand(dropTableQuery, connection)) { ... }
                string createTableQuery = $"..."; // Full table creation code
                using (SqlCommand createCommand = new SqlCommand(createTableQuery, connection)) { ... }

                // Retrieve data from World Bank API
                string apiUrl = $"https://api.worldbank.org/v2/country/{selectedCountry.Code}/indicator/NY.GDP.MKTP.CD?date=1989:2023&format=json";
                using (HttpClient client = new HttpClient()) { ... }
            }
        }
    }
}

Conclusion

This application demonstrates the integration of user input with SQL database management and data retrieval from an external API, making it highly flexible and efficient for loading GDP data. You can easily expand this by adding more data sources or refining the UI to create a more polished application.