public List<City> GetAllCity()
{
List<City> CityList = new List<City>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT ct.[id],ct.[Name],ct.About,ct.[Dwellers],ct.Location,ct.Weather,ct.[Country_Id],cn.Name as CountryName,cn.About as CountryAbout FROM tbl_CityEntry ct inner join [dbo].[tbl_Country] cn on cn.id=ct.[Country_Id] order By ct.Name";
SqlCommand aCommand = new SqlCommand(query, connection);
connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader();
while (aReader.Read())
{
City aCity = new City();
aCity.Id = (int)aReader["id"];
aCity.Name = (string)aReader["Name"];
aCity.About = (string)aReader["About"];
aCity.Dwellers = (int)aReader["Dwellers"];
aCity.Location = (string)aReader["Location"];
aCity.Weather = (string)aReader["Weather"];
aCity.Country_Id = (int)aReader["Country_Id"];
aCity.CountryName = (string)aReader["CountryName"];
aCity.CountryAbout = (string)aReader["CountryAbout"];
CityList.Add(aCity);
}
aReader.Close();
connection.Close();
return CityList;
}
protected void btnSearch_Click(object sender, EventArgs e)
{
try
{
if (RdoCity.Checked ==true && txtCityName.Text == "")
{
alert.Show("Please enter yout City Name !");
txtCityName.Focus();
return;
}
if (RdoCountry.Checked == true && ddlCountry.SelectedIndex < 0)
{
alert.Show("Please Slect Country Name !");
ddlCountry.Focus();
return;
}
City _City = new City();
if(RdoCity.Checked)
{
_City.Name = txtCityName.Text.Trim();
}
else if (RdoCountry.Checked)
{
_City.Country_Id = Int16.Parse(ddlCountry.SelectedValue.ToString());
}
GrdCityList.DataSource = _CityManager.GetCityByName(_City);
GrdCityList.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
msgLabel.Text = "";
if (txtCityName.Text.Trim() == "")
{
alert.Show("City Name Can't Be Empty !.");
txtCityName.Focus();
return;
}
if (txtAbout.Text.Trim() == "")
{
alert.Show("Country About Can't Be Empty !.");
txtAbout.Focus();
return;
}
City aCity = new City();
aCity.Name = txtCityName.Text;
aCity.About = txtAbout.Text;
aCity.Dwellers = Int16.Parse(txtDwellers.Text);
aCity.Location = txtLocation.Text;
aCity.Weather = txtWeather.Text;
aCity.Country_Id =Int16.Parse(ddlCountry.SelectedValue.ToString());
alert.Show(_CityManager.Save(aCity));
GrdCityList.DataSource = _CityManager.GetAllCities();
GrdCityList.DataBind();
}
public List<City> GetCityListByName(City _City)
{
List<City> CityList = new List<City>();
SqlConnection connection = new SqlConnection(connectionString);
string query="";
string queryCond="";
if (_City.Name != null && _City.Country_Id == 0)
{
queryCond = " ct.Name Like '" + _City.Name + "%' order By ct.Name";
}
else if (_City.Name == null && _City.Country_Id > 0)
{
queryCond = " cn.id =" + _City.Country_Id + " order By ct.Name";
}
else
{
queryCond = "";
}
query = "SELECT ct.[id],ct.[Name],ct.About,ct.[Dwellers],ct.Location,ct.Weather,ct.[Country_Id],cn.Name as CountryName,cn.About as CountryAbout FROM tbl_CityEntry ct inner join [dbo].[tbl_Country] cn on cn.id=ct.[Country_Id] Where ";
query = query + queryCond;
SqlCommand aCommand = new SqlCommand(query, connection);
connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader();
while (aReader.Read())
{
City aCity = new City();
aCity.Id = (int)aReader["id"];
aCity.Name = (string)aReader["Name"];
aCity.About = (string)aReader["About"];
aCity.Dwellers = (int)aReader["Dwellers"];
aCity.Location = (string)aReader["Location"];
aCity.Weather = (string)aReader["Weather"];
aCity.Country_Id = (int)aReader["Country_Id"];
aCity.CountryName = (string)aReader["CountryName"];
aCity.CountryAbout = (string)aReader["CountryAbout"];
CityList.Add(aCity);
}
aReader.Close();
connection.Close();
return CityList;
}
public string Save(City aCity)
{
if (IsNameExists(aCity.Name))
{
return "Name Already Exists";
}
else
{
if (aGateway.Save(aCity) > 0)
{
return "Saved Successfully";
}
else
{
return "Failed";
}
}
}
public List<City> GetCity()
{
List<City> CityList = new List<City>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM tbl_CityEntry order By Name";
SqlCommand aCommand = new SqlCommand(query, connection);
connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader();
while (aReader.Read())
{
City aCity = new City();
aCity.Id = (int)aReader["id"];
aCity.Name = (string)aReader["Name"];
aCity.About = (string)aReader["About"];
CityList.Add(aCity);
}
aReader.Close();
connection.Close();
return CityList;
}
public int Save(City aCity)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "INSERT INTO tbl_CityEntry VALUES ('" + aCity.Name + "', '" + aCity.About + "', '" + aCity.Dwellers + "', '" + aCity.Location + "', '" + aCity.Weather + "', '" + aCity.Country_Id + "')";
SqlCommand aCommand = new SqlCommand(query, connection);
connection.Open();
int rowAffected = aCommand.ExecuteNonQuery();
connection.Close();
return rowAffected;
}
public List<City> GetCityByName(City _City)
{
return aGateway.GetCityListByName(_City);
}
