public List<CountryCity> getCountryCity()
{
SqlConnection connection = new SqlConnection(connectionString);
// string query = "Select DISTINCT cn.CountryName AS CountryName,cs.CityName AS CityName,cs.About AS AboutCity , cs.NoOfDwellers AS NoOfDwellers," +
// "cs.Location AS Location,cs.Weather AS Weather,cn.About AS AboutCountry from Countries cn join cities cs ON cn.CountryId = cs.CountryId order by cityName ASC";
string query = "Select * from Countries cn join cities cs on cs.countryId = cn.countryId";
SqlCommand command = new SqlCommand(query,connection);
List<CountryCity> countryCityList = new List<CountryCity>();
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CountryCity countryCity = new CountryCity();
countryCity.CityName = reader["CityName"].ToString();
countryCity.AboutCity = reader["About"].ToString();
countryCity.NoOfDwellers = (int)reader["NoOfDwellers"];
countryCity.Location = reader["Location"].ToString();
countryCity.Weather = reader["Weather"].ToString();
countryCity.CountryName = reader["CountryName"].ToString();
countryCity.AboutCountry = reader["About"].ToString();
countryCity.CountryId = (int)reader["CountryId"];
countryCityList.Add(countryCity);
}
reader.Close();
connection.Close();
return countryCityList;
}
public List<CountryCity> CountryCityList()
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "Select cs.CityName AS CityName, cs.NoOfDwellers AS NoOfDwellers," +
"cn.CountryName AS CountryName " +
"from Countries cn join cities cs " +
"ON cn.CountryId = cs.CountryId order by cityName ASC";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List<CountryCity> countryList = new List<CountryCity>();
CountryCity countryCity = null;
while (reader.Read())
{
countryCity = new CountryCity();
countryCity.CountryName = reader["CountryName"].ToString();
countryCity.CityName = reader["CityName"].ToString();
countryCity.NoOfDwellers = (int) reader["NoOfDwellers"];
countryList.Add(countryCity);
}
reader.Close();
connection.Close();
return countryList;
}
public List<CountryCity> GetCountry()
{
SqlConnection connection = new SqlConnection(connectionString);
List<CountryCity> countryCityByName = new List<CountryCity>();
string query = "Select Distinct CountryName from Countries order by countryName";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CountryCity countryCity = new CountryCity();
countryCity.CountryName = reader["CountryName"].ToString();
countryCityByName.Add(countryCity);
}
reader.Close();
connection.Close();
return countryCityByName;
}
public List<CountryCity> GetCountryByName(string countryName)
{
SqlConnection connection = new SqlConnection(connectionString);
List<CountryCity> getCountryByNameList = new List<CountryCity>();
string query =
"select cn.CountryName AS CountryName,cn.About AS AboutCountry,COUNT(cs.cityName) AS CityName,SUM(cs.NoOfDwellers) AS NoOfDwellers from Countries cn join Cities cs ON cs.countryId = cn.CountryId WHERE countryName LIKE '%" + countryName + "%' group by cn.CountryName,cn.About";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CountryCity countryCity = new CountryCity();
countryCity.CountryName = reader["CountryName"].ToString();
countryCity.AboutCountry = reader["AboutCountry"].ToString();
countryCity.CityName = reader["CityName"].ToString();
countryCity.NoOfDwellers = (int) reader["NoOfDwellers"];
getCountryByNameList.Add(countryCity);
}
reader.Close();
connection.Close();
return getCountryByNameList;
}
public List<CountryCity> GetCountryCityByCityName(string cityName)
{
SqlConnection connection = new SqlConnection(connectionString);
List<CountryCity>countryCityByName = new List<CountryCity>();
string query = "Select * from Countries cn join cities cs on cs.countryId = cn.countryId WHERE CityName LIKE '%"+cityName +"%'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
CountryCity countryCity = new CountryCity();
countryCity.CityName = reader["CityName"].ToString();
countryCity.AboutCity = reader["About"].ToString();
countryCity.NoOfDwellers = (int)reader["NoOfDwellers"];
countryCity.Location = reader["Location"].ToString();
countryCity.Weather = reader["Weather"].ToString();
countryCity.CountryName = reader["CountryName"].ToString();
countryCity.AboutCountry = reader["About"].ToString();
countryCityByName.Add(countryCity);
}
reader.Close();
connection.Close();
return countryCityByName;
}
