public List<CountryCity> GetCountryCity(string query)
{
SqlConnection aConnection = new SqlConnection(connectionString);
SqlCommand aCommand = new SqlCommand(query, aConnection);
aConnection.Open();
SqlDataReader aReader = aCommand.ExecuteReader();
List<CountryCity> countriesCities = new List<CountryCity>();
while (aReader.Read())
{
CountryCity aCountryCity = new CountryCity()
{
CityName = aReader["CityName"].ToString(),
CityAbout = aReader["CityABout"].ToString(),
Location = aReader["Location"].ToString(),
Weather = aReader["Weather"].ToString(),
NoOfDwellers = Convert.ToInt32(aReader["NoOfDewells"].ToString()),
CountryName = aReader["CountryName"].ToString(),
CountryAbout = aReader["CountryAbout"].ToString()
};
countriesCities.Add(aCountryCity);
}
return countriesCities;
}
public List<CountryCity> GetCountry(string query)
{
SqlConnection aConnection = new SqlConnection(connectionString);
//"SELECT tbl_Country.CountryName, tbl_Country.CountryAbout, COUNT(*)TotalCities, SUM(NoOfDewells)TotalDwellers FROM tbl_Country JOIN tbl_City ON tbl_Country.id = tbl_City.CountryId GROUP BY CountryName,CountryAbout HAVING CountryName LIKE '%" + searchCountryName + "%'";
//"SELECT tbl_Country.CountryName, tbl_Country.CountryAbout, COUNT(*)TotalCities, SUM(NoOfDewells)TotalDwellers FROM tbl_Country JOIN tbl_City ON tbl_Country.CountryId = tbl_City.CountryId GROUP BY CountryName, CountryAbout HAVING CountryName LIKE '%" + searchCountryName + "%' ";
SqlCommand aCommand = new SqlCommand(query, aConnection);
aConnection.Open();
SqlDataReader aReader = aCommand.ExecuteReader();
List<CountryCity> countryCities = new List<CountryCity>();
while (aReader.Read())
{
CountryCity aCountryCity = new CountryCity()
{
CountryName = aReader["CountryName"].ToString(),
CountryAbout = aReader["CountryAbout"].ToString(),
NoOfCities = Convert.ToInt32(aReader["TotalCities"].ToString()),
NoOfDwellers = Convert.ToInt32(aReader["TotalDwellers"].ToString())
};
countryCities.Add(aCountryCity);
}
aReader.Close();
aConnection.Close();
return countryCities;
}