public List <ViewCountryModel> AllCountryLoodInGriddView(List <Country> listOfCountries) { List <ViewCountryModel> aViewCountryViewsList = new List <ViewCountryModel>(); foreach (Country aCountry in listOfCountries) { string query = "SELECT CountryName,AboutCountry,COUNT(CityName) NoOfCities,SUM(NoOfDwellers) NoOfDwellers FROM viewCitiesview WHERE CountryName='" + aCountry.Name + "' GROUP BY CountryName,AboutCountry;"; SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { ViewCountryModel aViewCountryView = new ViewCountryModel(); aViewCountryView.NameCountry = reader["CountryName"].ToString(); aViewCountryView.AboutCountry = reader["AboutCountry"].ToString(); aViewCountryView.NoOfCitys = Convert.ToInt32(reader["NoOfCities"]); aViewCountryView.NoOfDwellers = Convert.ToInt32(reader["NoOfDwellers"]); aViewCountryViewsList.Add(aViewCountryView); } connection.Close(); } return(aViewCountryViewsList); }
public List <ViewCountryModel> SearchCountryLoodInGridView(string searchItem) { List <ViewCountryModel> aViewCountryViewsList = new List <ViewCountryModel>(); string query = "SELECT CountryName,AboutCountry,COUNT(CityName) NoOfCities,SUM(NoOfDwellers) NoOfDwellers FROM viewCitiesview WHERE CountryName LIKE '%" + searchItem + "%' GROUP BY CountryName,AboutCountry"; SqlConnection aconnection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand(query, aconnection); aconnection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { ViewCountryModel aViewCountryView = new ViewCountryModel(); aViewCountryView.NameCountry = reader["CountryName"].ToString(); aViewCountryView.AboutCountry = reader["AboutCountry"].ToString(); aViewCountryView.NoOfCitys = Convert.ToInt32(reader["NoOfCities"]); aViewCountryView.NoOfDwellers = Convert.ToInt32(reader["NoOfDwellers"]); aViewCountryViewsList.Add(aViewCountryView); } aconnection.Close(); return(aViewCountryViewsList); }