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)
{
//SELECT * FROM table WHERE name = "%k"
List <object> list = new List <object>();
using (SQLiteCommand cmd = new SQLiteCommand($"SELECT * FROM COUNTRY JOIN CapitalCity ON COUNTRY.CAPITALCITY_ID = CapitalCity.ID WHERE COUNTRY.NAME Like '{FirstLetter}%'", connection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Country CurrentCountry = new Country
{
Id = (int)reader["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["ID"],
Name = (string)reader["NAME"],
NumCitizens = (int)reader["NUMCITIZENS"],
CountryId = (int)reader["COUNTRY_ID"]
};
var result = new
{
Country_Id = CurrentCountry.Id,
Country_Name = CurrentCountry.Name,
Capital_Name = CurrentCity.Name
};
list.Add(result);
}
}
}
return(list);
}
public object GetCountryAndItsCapitalCityDetails(int countryId)
{
using (SQLiteCommand cmd = new SQLiteCommand($"SELECT *, COUNTRY.NAME as COUNTRY_NAME, CAPITALCITY.NAME as CITY_NAME FROM COUNTRY JOIN CapitalCity ON COUNTRY.CAPITALCITY_ID = CapitalCity.ID WHERE COUNTRY.ID = {countryId}", connection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Country CurrentCountry = new Country
{
Id = (int)reader["ID"],
Name = (string)reader["COUNTRY_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["ID"],
Name = (string)reader["CITY_NAME"],
NumCitizens = (int)reader["NUMCITIZENS"],
CountryId = (int)reader["COUNTRY_ID"]
};
var result = new
{
Country_Id = CurrentCountry.Id,
Country_Name = CurrentCountry.Name,
Capital_Name = CurrentCity.Name,
Capital_Citizens = CurrentCity.NumCitizens,
Capital_Id = CurrentCountry.Id,
};
return(result);
}
}
}
return(null);
}
