public int InsertCity( City city ) {
connection.Close();
string insertQuery = "INSERT INTO City VALUES(@name, @about ,@dwellers,@location,@weather,@countryId)";
SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
connection.Open();
insertCommand.Parameters.Clear();
insertCommand.Parameters.Add("name", SqlDbType.NVarChar);
insertCommand.Parameters["name"].Value = city.CityName;
byte[] aboutBytes = Encoding.UTF8.GetBytes(city.AboutCity);
insertCommand.Parameters.Add("about", SqlDbType.VarBinary);
insertCommand.Parameters["about"].Value = aboutBytes;
insertCommand.Parameters.Add("dwellers", SqlDbType.NVarChar);
insertCommand.Parameters["dwellers"].Value = city.Dwellers;
insertCommand.Parameters.Add("location", SqlDbType.NVarChar);
insertCommand.Parameters["location"].Value = city.Location;
insertCommand.Parameters.Add("weather", SqlDbType.NVarChar);
insertCommand.Parameters["weather"].Value = city.Weather;
insertCommand.Parameters.Add("countryId", SqlDbType.Int);
insertCommand.Parameters["countryId"].Value = city.CountryID;
int affectedRow = insertCommand.ExecuteNonQuery();
connection.Close();
return affectedRow;
}
public string InsertCity( City city ) {
if (CheckCityByName(city.CityName)) {
return "City Name Already Exists!";
}
if (objGateway.InsertCity(city) > 0) {
return "Successfully Saved!";
}
return "Insertion Failed!";
}
protected void saveCityButton_Click(object sender, EventArgs e) {
int parsedDwellervalue;
string cityName = cityNameTextBox.Text;
string aboutCity = aboutTextArea.Value;
string totalDwellers = noOfDwellersTextBox.Text;
totalDwellers = totalDwellers.Replace(",", "");
string cityLocation = locationTextBox.Text;
string cityWeather = weatherTextBox.Text;
int countryIDfromDropDown;
try {
countryIDfromDropDown = Convert.ToInt32(cityCountryDropDownList.SelectedValue);
}
catch (Exception notSelectedException) {
countryIDfromDropDown = cityCountryDropDownList.SelectedIndex;
}
if (cityName == string.Empty || totalDwellers == string.Empty || cityLocation == string.Empty || cityWeather == string.Empty) {
messageLabel.Text = "Fields are empty!";
}
else if (!int.TryParse(totalDwellers, out parsedDwellervalue)) {
messageLabel.Text = "Dwellers field is incorrect";
}
else if (countryIDfromDropDown == 0) {
messageLabel.Text = "Select a Country";
}
else {
City city = new City();
city.CityName = cityName;
city.AboutCity = aboutCity;
city.Dwellers = parsedDwellervalue;
city.Location = cityLocation;
city.Weather = cityWeather;
city.CountryID = countryIDfromDropDown;
messageLabel.Text = manager.InsertCity(city);
LoadAllCities();
ClearAllFields();
}
}
public List<City> GetAll() {
connection.Close();
int count = 1;
List<City> cities = new List<City>();
string getAllQuery = "SELECT * FROM GetCityForGridview ORDER BY CountryName";
SqlCommand command = new SqlCommand(getAllQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()) {
City city = new City();
city.CitySL = count++;
city.CityName = Convert.ToString(reader["CityName"]);
////Getting binary data from database. Casting to byte array
//byte[] objBytes = (byte[])reader["AboutCity"];
////Converting to unicode string
//city.AboutCity = Encoding.UTF8.GetString(objBytes);
city.Dwellers = Convert.ToInt32(reader["Dwellers"]);
city.CountryName = Convert.ToString(reader["CountryName"]);
cities.Add(city);
}
reader.Close();
connection.Close();
return cities;
}
