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; }