public List <CityCountry> GetSearchCountryByCountyView(string title)
{
string query = "SELECT * FROM view_name WHERE CountryName LIKE'%" + title + "%'";
connection.ConnectionString = ConnectionString;
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <CityCountry> citycountry = new List <CityCountry>();
while (reader.Read())
{
CityCountry acitycountry = new CityCountry();
City city = new City();
Country acountry = new Country();
acitycountry.counntry.CountryName = reader["CountryName"].ToString();
acitycountry.counntry.CountryAbout = reader["CountryAbout"].ToString();
acitycountry.NoOfCity = int.Parse(reader["NoOfCity"].ToString());
acitycountry.NoOfDweller = int.Parse(reader["NoOfDwellers"].ToString());
citycountry.Add(acitycountry);
}
reader.Close();
connection.Close();
return(citycountry);
}
public List <CityCountry> GetSearchCountrys(string title)
{
string query = "SELECT * FROM Citys s FULL OUTER JOIN Countrys d ON s.Id= d.Id WHERE CityName LIKE '%" + title + "%'";
connection.ConnectionString = ConnectionString;
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <CityCountry> citycountry = new List <CityCountry>();
while (reader.Read())
{
CityCountry acitycountry = new CityCountry();
City city = new City();
Country acountry = new Country();
acitycountry.city.CityName = reader["CityName"].ToString();
acitycountry.city.CityAbout = reader["CityAbout"].ToString();
acitycountry.city.NoDwelers = Convert.ToInt32(reader["NoDwelers"]);
acitycountry.city.Location = reader["Location"].ToString();
acitycountry.city.Weather = reader["Weather"].ToString();
acitycountry.counntry.CountryName = reader["CountryName"].ToString();
acitycountry.counntry.CountryAbout = reader["CountryAbout"].ToString();
citycountry.Add(acitycountry);
}
reader.Close();
connection.Close();
return(citycountry);
}
public List<CityCountry> GetAllCountry( string quary)
{
sqlConnection.Open();
sqlCommand.CommandText = quary;
SqlDataReader reader = sqlCommand.ExecuteReader();
List<CityCountry> allCityCountryList = new List<CityCountry>();
while (reader.Read())
{
CityCountry aCityCountry = new CityCountry();
aCityCountry.Country = reader["CountryName"].ToString();
aCityCountry.TotalCity = int.Parse(reader["totalCity"].ToString());
aCityCountry.Dwellers = int.Parse(reader["totalDewllers"].ToString());
aCityCountry.AboutCountry = reader["AboutCountry"].ToString();
allCityCountryList.Add(aCityCountry);
}
reader.Close();
sqlConnection.Close();
return allCityCountryList;
}
public List<CityCountry> GetData(string allCityQuery)
{
sqlConnection.Open();
sqlCommand.CommandText = allCityQuery;
SqlDataReader reader = sqlCommand.ExecuteReader();
List<CityCountry> allCityCountryList = new List<CityCountry>();
while (reader.Read())
{
CityCountry aCityCountry = new CityCountry();
aCityCountry.City = reader["CityName"].ToString();
aCityCountry.AboutCity = reader["AboutCity"].ToString();
aCityCountry.Dwellers = int.Parse(reader["Dwellers"].ToString());
aCityCountry.Location = reader["Location"].ToString();
aCityCountry.Weather = reader["Weather"].ToString();
aCityCountry.Country = reader["CountryName"].ToString();
aCityCountry.AboutCountry = reader["AboutCountry"].ToString();
allCityCountryList.Add(aCityCountry);
}
reader.Close();
sqlConnection.Close();
return allCityCountryList;
}
