public override bool Equals(object obj)
{
if (obj == null)
{
return(false);
}
CapitalCity c = obj as CapitalCity;
if (c == null)
{
return(false);
}
return(this.Id == c.Id);
}
public List <object> AllCountriesFromFirstLetter(string FirstLetter)
{
List <object> list = new List <object>();
using (SQLiteCommand cmd = new SQLiteCommand($"SELECT * FROM COUNTRY JOIN CoupitalCity ON COUNTRY.CAPITALCITY_ID == CoupitalCity.ID WHERE COUNTRY.NAME LIKE '{FirstLetter}%'", connection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Country CurrentCountry = new Country
{
Id = (int)reader["COUNTRY_ID"],
Name = (string)reader["NAME"],
Size_km = (int)reader["SIZE_KM"],
Birth_Year = (int)reader["BIRTH_YEAR"],
CapitalCityId = (int)reader["CAPITALCITY_ID"]
};
CapitalCity CurrentCity = new CapitalCity
{
Id = (int)reader["CAPITALCITY_ID"],
CName = (string)reader["CNAME"],
NumCitizens = (int)reader["NUMCITIZENS"],
CountryId = (int)reader["COUNTRY_ID"]
};
var result = new
{
CurrentCountry.Id,
CurrentCountry.Name,
CurrentCity.CName
};
list.Add(result);
}
}
}
return(list);
}
public object GetCountyAndItsCapitalCityDetails(int countryId)
{
using (SQLiteCommand cmd = new SQLiteCommand($"SELECT * FROM COUNTRY JOIN CoupitalCity ON COUNTRY.CAPITALCITY_ID == CoupitalCity.ID WHERE COUNTRY.ID == {countryId}", connection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Country CurrentCountry = new Country
{
Id = (int)reader["COUNTRY_ID"],
Name = (string)reader["NAME"],
Size_km = (int)reader["SIZE_KM"],
Birth_Year = (int)reader["BIRTH_YEAR"],
CapitalCityId = (int)reader["CAPITALCITY_ID"]
};
CapitalCity CurrentCity = new CapitalCity
{
Id = (int)reader["CAPITALCITY_ID"],
CName = (string)reader["CNAME"],
NumCitizens = (int)reader["NUMCITIZENS"],
CountryId = (int)reader["COUNTRY_ID"]
};
var result = new
{
CurrentCountry.Id,
CurrentCountry.Name,
CurrentCity.CName,
CurrentCity.NumCitizens,
CurrentCity.CountryId
};
return(result);
}
}
}
return(null);
}
public List <CapitalCity> GetAllCities()
{
List <CapitalCity> cities = new List <CapitalCity>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM CoupitalCity", connection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
CapitalCity CurrentCity = new CapitalCity
{
Id = (int)reader["ID"],
CName = (string)reader["CNAME"],
NumCitizens = (int)reader["NUMCITIZENS"],
CountryId = (int)reader["COUNTRY_ID"]
};
cities.Add(CurrentCity);
}
}
}
return(cities);
}
