In this article, we will discuss how to load GDP (Gross Domestic Product) data for various countries into a SQL database using C#. The goal is to create an application that connects to the World Bank API, retrieves the data, and stores it in separate tables for each country. Additionally, we will present a code snippet that allows creating or deleting an existing database, as well as the corresponding tables.
Loading Country GDP Data into a SQL Database with C
In this article, we will discuss how to load GDP (Gross Domestic Product) data for various countries into a SQL database using C#. The goal is to create an application that connects to the World Bank API, retrieves the data, and stores it in separate tables for each country. Additionally, we will present a code snippet that allows creating or deleting an existing database, as well as the corresponding tables.
Code Structure
The code is organized into several main parts:
- Definition of the
CountryClass: Represents a country with its name and code. - Database Connection: The code prompts the user to specify the database name and checks if it already exists.
- Database Manipulation: The code can create or delete the database and its tables as needed.
- Data Retrieval from the API: It uses the World Bank API to fetch GDP data and inserts it into the appropriate tables.
Code Functionality
- Database Connection: The code connects to the SQL Server and asks for the database name. It checks whether the database exists and asks the user whether to drop it or insert data into the existing database.
- Table Creation: For each country, a table is created to store the GDP data. If the table already exists, it is dropped before creating a new one.
- API Query: For each country, the code queries the API and retrieves GDP data between the years 1989 and 2023. The data is then inserted into the corresponding table.
Explanatory Table
Below is a table that explains the main parts of the code:
| Code Part | Description |
|---|---|
Country | Class representing a country, containing properties for the country name and code. |
Main | Main method executing the program flow. |
SqlConnection | Used to connect to SQL Server. |
HttpClient | Used to make HTTP requests to the World Bank API. |
SqlCommand | Executes SQL commands in the database, such as creating or dropping tables and inserting data. |
JArray | Used to parse the JSON response from the World Bank API and extract the necessary data. |
foreach | Loop structure used to iterate over the list of countries and process each one. |
Complete Code
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Newtonsoft.Json.Linq;
namespace WbSqlLoadCountrys
{
class Country
{
public string Name { get; }
public string Code { get; }
public Country(string name, string code)
{
Name = name;
Code = code;
}
}
internal class Program
{
static async Task Main(string[] args)
{
Console.WriteLine("Connecting to Database");
// Ask for the database name
Console.Write("Enter the database name: ");
string dbName = Console.ReadLine();
// SQL Database connection string without the specific database
string connectionString = $"Data Source=localhost;Initial Catalog=master;Integrated Security=True;";
// Check if the database exists
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string checkDbQuery = $"SELECT database_id FROM sys.databases WHERE name = '{dbName}'";
SqlCommand checkDbCommand = new SqlCommand(checkDbQuery, connection);
var dbExists = checkDbCommand.ExecuteScalar();
if (dbExists != null)
{
// Database exists, ask user if they want to drop it
Console.Write("The database already exists. Do you want to drop it? (y/n): ");
var response = Console.ReadLine().ToLower();
if (response == "y")
{
// Drop the existing database
string dropDbQuery = $"DROP DATABASE {dbName}";
SqlCommand dropDbCommand = new SqlCommand(dropDbQuery, connection);
dropDbCommand.ExecuteNonQuery();
Console.WriteLine($"Database '{dbName}' dropped successfully.");
}
else
{
Console.WriteLine($"Inserting data into the existing database '{dbName}'.");
}
}
else
{
// Create the new database
string createDbQuery = $"CREATE DATABASE {dbName}";
SqlCommand createDbCommand = new SqlCommand(createDbQuery, connection);
createDbCommand.ExecuteNonQuery();
Console.WriteLine($"Database '{dbName}' created successfully.");
}
}
// Now connect to the newly created or existing database
connectionString = $"Data Source=localhost;Initial Catalog={dbName};Integrated Security=True;";
// List of countries with their codes
var countries = new List<Country>
{
new Country("Brazil", "BR"),
new Country("Argentina", "AR"),
new Country("United States", "US"),
new Country("China", "CN"),
new Country("Japan", "JP"),
new Country("Germany", "DE"),
new Country("India", "IN"),
new Country("United Kingdom", "GB"),
new Country("France", "FR"),
new Country("Italy", "IT"),
new Country("Canada", "CA"),
new Country("South Korea", "KR"),
new Country("Australia", "AU"),
new Country("Spain", "ES"),
new Country("Mexico", "MX"),
new Country("Indonesia", "ID"),
new Country("Netherlands", "NL"),
new Country("Saudi Arabia", "SA"),
new Country("Turkey", "TR"),
new Country("Taiwan", "TW"),
new Country("Switzerland", "CH")
};
int startYear = 1989;
int endYear = 2023;
Console.WriteLine("Creating Tables");
// Connect to SQL Database and create tables
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
foreach (var country in countries)
{
string tableName = $"GDP_Data_{country.Name.Replace(" ", "_")}";
// Drop existing table
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.");
}
// Create new table
string createTableQuery = $@"
CREATE TABLE {tableName} (
Id INT IDENTITY(1,1) PRIMARY KEY,
CountryCode NVARCHAR(3),
GDP DECIMAL(18, 2),
Year INT
);";
using (SqlCommand createCommand = new SqlCommand(createTableQuery, connection))
{
createCommand.ExecuteNonQuery();
Console.WriteLine($"Table '{tableName}' created successfully.");
}
// Get GDP data from World Bank API
string apiUrl = $"https://api.worldbank.org/v2/country/{country.Code}/indicator/NY.GDP.MKTP.CD?date={startYear}:{endYear}&format=json";
Console.WriteLine("Connecting to web service");
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);
Console.WriteLine("Loading Data");
foreach (var item in jsonData[1])
{
string countryCode = item["countryiso3code"].ToString();
string gdp = item["value"]?.ToString() ?? "0";
string year = item["date"].ToString();
// Insert data into SQL table
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 from API for {country.Name}");
}
}
}
}
}
}
}
Conclusion
In this article, we demonstrated how to develop a C# application to load GDP data for countries into a SQL database. The code encompasses everything from connecting to the database to manipulating data obtained from an API. You can adapt this code to add new countries or indicators as needed.
edvaldo b. guimarães filho 