public List<City> GetCountryCity()
{
SqlConnection con = CountryGateway.Connection();
con.Open();
string sql = string.Format("SELECT * FROM GetCountryCity ORDER BY CityName");
SqlCommand cmd=new SqlCommand(sql,con);
SqlDataReader reader = cmd.ExecuteReader();
List<City> cityList = new List<City>();
int a = 0;
while (reader.Read())
{
a++;
City city = new City();
city.CityID = int.Parse(reader["CityID"].ToString());
city.CityName = reader["CityName"].ToString();
city.CityAbout = reader["CityAbout"].ToString();
city.NoOfDwellers = double.Parse(reader["No_Of_Dwellers"].ToString());
city.Location = reader["Location"].ToString();
city.Weather = reader["Weather"].ToString();
city.MyCountry.CountryName = reader["CountryName"].ToString();
city.MyCountry.CountryID = int.Parse(reader["CountryID"].ToString());
city.MyCountry.CountryAbout = reader["CountryAbout"].ToString();
city.SerialNo = a;
cityList.Add(city);
}
reader.Close();
con.Close();
return cityList;
}
private bool IsFieldEmpty(City city)
{
if (city.CityName.Equals(string.Empty) || city.CityAbout.Equals(string.Empty) || city.NoOfDwellers.Equals(0) || city.Location.Equals(string.Empty) || city.Weather.Equals(string.Empty))
{
return true;
}
else
{
return false;
}
}
public string Insert(City city)
{
if (IsFieldEmpty(city))
{
return "Please Fill The Field First";
}
else if (cityGateway.Insert(city))
{
return "Insertion Successfull";
}
else
{
return "City Name Already Exist";
}
}
public List<City> GetDataListByID(string attribute, string value)
{
int a = 0;
SqlConnection con = CountryGateway.Connection();
con.Open();
string sql = string.Format("SELECT * FROM GetCountryCity WHERE {0} ={1}", attribute, value);
SqlCommand cmd = new SqlCommand(sql, con);
var reader = cmd.ExecuteReader();
List<City> cityList = new List<City>();
while (reader.Read())
{
a++;
City city = new City();
city.CityID = int.Parse(reader["CityID"].ToString());
city.CityName = reader["CityName"].ToString();
city.CityAbout = reader["CityAbout"].ToString();
city.NoOfDwellers = double.Parse(reader["No_Of_Dwellers"].ToString());
city.Location = reader["Location"].ToString();
city.Weather = reader["Weather"].ToString();
city.MyCountry.CountryName = reader["CountryName"].ToString();
city.MyCountry.CountryID = int.Parse(reader["CountryID"].ToString());
city.MyCountry.CountryAbout = reader["CountryAbout"].ToString();
city.SerialNo = a;
cityList.Add(city);
}
return cityList;
}
public bool Insert(City city)
{
try
{
SqlConnection con= CountryGateway.Connection();
con.Open();
string sql =string.Format("INSERT INTO CityTable VALUES ('{0}','{1}',{2},'{3}','{4}','{5}')",city.CityName,city.CityAbout,city.NoOfDwellers,city.Location,city.Weather,city.MyCountry.CountryID);
SqlCommand cmd=new SqlCommand(sql,con);
int rowAffected=cmd.ExecuteNonQuery();
con.Close();
return true;
}
catch (Exception e)
{
return false;
}
}
private City SetFormDataToCity()
{
var city = new City();
city.CityName = nameTextBox.Text;
city.CityAbout = aboutTextBox.Value;
city.NoOfDwellers = Convert.ToDouble(noOfDwellersTextBox.Text);
city.Location = locationTextBox.Text;
city.Weather = weatherTextBox.Text;
city.MyCountry.CountryID= Convert.ToInt32(countryDropDownList.SelectedValue);
return city;
}
