public List<City> GetAllCitys()
{
SqlConnection connection = new SqlConnection(_connectionString);
string query = "SELECT * FROM City ORDER BY Name ASC";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List<City> citys = new List<City>();
while (reader.Read())
{
City city = new City();
city.CityID = (int) reader["CityID"];
city.Name = reader["Name"].ToString();
city.About = HttpUtility.HtmlDecode(reader["About"].ToString());
city.Location = reader["Location"].ToString();
city.NoOfDewllers = (int) reader["NoOfDewllers"];
city.Weather = reader["Weather"].ToString();
city.CountryID =(int) reader["CountryID"];
citys.Add(city);
}
reader.Close();
connection.Close();
return citys;
}
public List<CityWiseCountry> GetCountryWiseCitiesByCityName(City city)
{
SqlConnection connection = new SqlConnection(_connectionString);
string query = "SELECT ci.Name As [City Name], ci.About AS [City About], ci.NoOfDwellers, ci.Location, ci.Weather, co.Name AS [Country Name], co.About As [Country About] FROM City ci INNER JOIN Country AS co ON co.CountryID = ci.CountryID WHERE ci.Name ='" + city.Name + "' ORDER BY ci.Name ASC";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List<CityWiseCountry> countryWiseCities = new List<CityWiseCountry>(); ;
while (reader.Read())
{
CityWiseCountry cityWiseCountry = new CityWiseCountry();
cityWiseCountry.CityAbout = HttpUtility.HtmlDecode(reader["City About"].ToString());
cityWiseCountry.CityName = reader["City Name"].ToString();
cityWiseCountry.CountryAbout = HttpUtility.HtmlDecode(reader["Country About"].ToString());
cityWiseCountry.CountryName = reader["Country Name"].ToString();
cityWiseCountry.Location = reader["Location"].ToString();
cityWiseCountry.NoOfDewllers = (int)reader["NoofDwellers"];
cityWiseCountry.Weather = reader["Weather"].ToString();
countryWiseCities.Add(cityWiseCountry);
}
reader.Close();
connection.Close();
return countryWiseCities;
}
protected void searchlLinkButton_Click(object sender, EventArgs e)
{
if (countryRadioButton.Checked)
{
cityGridView.DataSource = cityManager.GetCountryWiseCitiesByCountry(Convert.ToInt32(countryDropDownList.SelectedValue));
cityGridView.DataBind();
}
else
{
City city = new City();
city.Name = searchTextBox.Text;
cityGridView.DataSource = cityManager.GetCountryWiseCitiesCityName(city);
cityGridView.DataBind();
}
}
public string SaveCountry(City city)
{
int length = city.About.Length;
if (length > 7000)
{
return "Keep Your Text limit upto 3000.";
}
if (!cityGateWay.IsExist(city))
{
if (cityGateWay.SaveCity(city)>0)
{
return "City Save Successfully.";
}
return "Failed to Save New City";
}
return "City already exist!!!!";
}
protected void saveLinkButton_Click(object sender, EventArgs e)
{
City city = new City(nameTextBox.Text,aboutTextBox.Text,Convert.ToInt32(noOfDwellersTextBox.Text),locationTextBox.Text,weatherTextBox.Text,Convert.ToInt32(countryDropDownList.SelectedValue));
string status = cityManager.SaveCountry(city);
if (status == "City Save Successfully.")
{
outLabel.ForeColor = Color.Green;
}
else
{
outLabel.ForeColor = Color.Red;
}
outLabel.Text = status;
LoadData();
}
public int SaveCity(City city)
{
SqlConnection connection = new SqlConnection(_connectionString);
string query = @"INSERT INTO City VALUES('" + city.Name + "','" + HttpUtility.HtmlEncode(city.About) + "','" + city.NoOfDewllers + "','" + city.Location + "','" + city.Weather + "'," + city.CountryID + ")";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowAffected = command.ExecuteNonQuery();
connection.Close();
return rowAffected;
}
public bool IsExist(City city)
{
SqlConnection connection = new SqlConnection(_connectionString);
string query = "SELECT * FROM City Where name='"+city.Name+"'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Close();
connection.Close();
return true;
}
reader.Close();
connection.Close();
return false;
}
public List<CityWiseCountry> GetCountryWiseCitiesCityName(City city)
{
return cityGateWay.GetCountryWiseCitiesByCityName(city);
}
