protected void saveButton_Click(object sender, EventArgs e)
{
try
{
CIty city = new CIty();
city.Name = nameTextBox.Text;
city.About = aboutTextBox.Text;
city.NoOfDwellers = Convert.ToInt32(noOfDwellersTextBox.Text);
city.Location = locationTextBox.Text;
city.Weather = weatherTextBox.Text;
// city.Country.CountryId = Convert.ToInt32(countryDropDownList.SelectedItem);
city.Country.CountryId = Convert.ToInt32(countryDropDownList.SelectedValue);
int rowsAffected = cityManager.Save(city);
if (rowsAffected > 0)
{
messageLable.Text = "<h3>Data inserted successfully</h3>";
messageLable.ForeColor = Color.Green;
BindGridview();
}
else
{
messageLable.Text = "</h3>Data insert failed</h3>";
messageLable.ForeColor = Color.Red;
}
}
catch (Exception exception)
{
messageLable.Text = exception.Message;
messageLable.ForeColor = Color.Red;
}
}
private CIty GetAllCityByCityName(string cityName)
{
SqlConnection con = new SqlConnection(connectionString);
string query = "select cityName,city.about,noOfDwellers,location,weather,country.countryName,country.about from city inner join country on country.countryName = city.countryName where cityName ='" + cityName + "'";
SqlCommand command = new SqlCommand(query, con);
con.Open();
CIty city = null;
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
city = new CIty();
city.Name = reader["cityName"].ToString();
city.About = reader["about"].ToString();
city.NoOfDwellers = Convert.ToInt32(reader["noOfDwellers"]);
city.Location = reader["location"].ToString();
city.Weather = reader["weather"].ToString();
city.Country.Name = reader["countryName"].ToString();
city.Country.About = reader["about"].ToString();
reader.Close();
}
con.Close();
return(city);
}
public List <CIty> GetAllCities()
{
List <CIty> CItyList = new List <CIty>();
SqlConnection connection = new SqlConnection(connectionString);
string query =
"select cityName,city.about,noOfDwellers,location,weather,country.countryName,country.about from city inner join country on country.countryId = city.countryId ";
// string query = "SELECT * FROM CIty ORDER BY CItyId DESC";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CIty city = new CIty();
city.Name = reader["cityName"].ToString();
city.About = reader["about"].ToString();
city.NoOfDwellers = Convert.ToInt32(reader["noOfDwellers"]);
city.Location = reader["location"].ToString();
city.Weather = reader["weather"].ToString();
city.Country.Name = reader["countryName"].ToString();
city.Country.About = reader["about"].ToString();
CItyList.Add(city);
}
reader.Close();
}
connection.Close();
return(CItyList);
}
public int Save(CIty city)
{
if (IsCityNameExist(city.Name))
{
throw new Exception("<h3>City Name already exist</h3>");
}
return(cItyGateway.Save(city));
}
private bool IsCityExists(string cityName)
{
bool isCountryExists = false;
CIty city = GetAllCityByCityName(cityName);
if (city != null)
{
isCountryExists = true;
}
return(isCountryExists);
}
public bool IsCountryNameExist(string cityName)
{
bool isCountryNameExist = false;
CIty city = GetCountryByName(cityName);
if (city != null)
{
isCountryNameExist = true;
}
return(isCountryNameExist);
}
public int Save(CIty city)
{
// Connect to the database
SqlConnection connection = new SqlConnection(connectionString);
// write query
string query = "INSERT INTO CIty(CItyName,about,noOfDwellers,location,weather,countryId) VALUES('" +
city.Name + "','" + city.About + "','" + city.NoOfDwellers + "','" + city.Location + "','" +
city.Weather + "','" + city.Country.CountryId + "')";
SqlCommand command = new SqlCommand(query, connection);
// execute query
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
return(rowsAffected);
}
//protected void BindGridview(CIty cityName)
//{
// List<CIty> myCIties = cityManager.GetCityByName(cityName.Name);
//DataSet ds = new DataSet();
//using (SqlConnection con = new SqlConnection(connectionString))
//{
// con.Open();
// if (cityNameRadioButton.Checked)
// {
// string name = cityNameTextBox.Text;
// SqlCommand cmd = new SqlCommand("select cityName,city.about,noOfDwellers,location,weather,country.countryName,country.about from city inner join country on country.countryName = city.countryName where cityName ='"+name+"'", con);
// SqlDataAdapter da = new SqlDataAdapter(cmd);
// da.Fill(ds);
// }
// else if (countryRadioButton.Checked)
// {
// string name = cityNameTextBox.Text;
// SqlCommand cmd = new SqlCommand("select cityName,city.about,noOfDwellers,location,weather,country.countryName,country.about from city inner join country on country.countryName = city.countryName where country.countryName ='" + countryDropDownList.SelectedItem + "'", con);
// SqlDataAdapter da = new SqlDataAdapter(cmd);
// da.Fill(ds);
// }
// con.Close();
// cityListGridView.DataSource = ds;
// cityListGridView.DataBind();
//}
//}
protected void BindGridview()
{
cityListGridView.Visible = false;
try
{
CIty city = new CIty();
city.Name = cityNameTextBox.Text;
bool isCountryNameExist;
if (cityNameRadioButton.Checked)
{
isCountryNameExist = cityManager.IsCityNameExist(city.Name);
if (isCountryNameExist == true)
{
CIty getCityByName = cityManager.GetCityByName(city.Name);
List <CIty> aCountry = new List <CIty>();
aCountry.Add(getCityByName);
cityListGridView.DataSource = aCountry;
cityListGridView.DataBind();
if (getCityByName.Name != "")
{
cityListGridView.Visible = true;
messageLabel.Text = "";
}
else
{
messageLabel.Text = "<h3>Please type a Country Name.</h3>";
messageLabel.ForeColor = Color.Red;
}
}
else
{
messageLabel.Text = "<h3>City Not Found</h3>";
messageLabel.ForeColor = Color.Red;
}
}
else if (countryRadioButton.Checked)
{
isCountryNameExist = cityManager.IsCountryNameExist(countryDropDownList.SelectedItem.ToString());
if (isCountryNameExist == true)
{
CIty GetCountryByName = cityManager.GetCountryByName(countryDropDownList.SelectedItem.ToString());
List <CIty> aCountry = new List <CIty>();
aCountry.Add(GetCountryByName);
cityListGridView.DataSource = aCountry;
cityListGridView.DataBind();
if (GetCountryByName.Name != "")
{
cityListGridView.Visible = true;
messageLabel.Text = "";
}
else
{
messageLabel.Text = "<h3>Please type a Country Name.</h3>";
messageLabel.ForeColor = Color.Red;
}
}
else
{
messageLabel.Text = "<h3>country Not Found</h3>";
messageLabel.ForeColor = Color.Red;
}
}
}
catch (Exception exception)
{
messageLabel.Text = exception.Message;
messageLabel.ForeColor = Color.Red;
}
}