public List<Country> GetCountryListByName(Country _Country)
{
List<Country> countryList = new List<Country>();
SqlConnection connection = new SqlConnection(connectionString);
string query="";
query = " SELECT cn.id,cn.Name,cn.About,isnull(ct.NoCity,0)NoCity,isnull(ct.Dwellers,0)Dwellers FROM tbl_Country cn Left join (select t.Country_Id,Count(t.id) NoCity,SUM(t.Dwellers)Dwellers from dbo.tbl_CityEntry t group By t.Country_Id)ct on ct.Country_Id=cn.id";
query = query+" where cn.Name Like '" + _Country.Name.Trim() + "%'";
SqlCommand aCommand = new SqlCommand(query, connection);
connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader();
while (aReader.Read())
{
Country aCountry = new Country();
aCountry.Id = (int)aReader["id"];
aCountry.Name = (string)aReader["Name"];
aCountry.About = (string)aReader["About"];
aCountry.NoCity = (int)aReader["NoCity"];
aCountry.Dwellers = (int)aReader["Dwellers"];
countryList.Add(aCountry);
}
aReader.Close();
connection.Close();
return countryList;
}
public int Save(Country aCountry)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "INSERT INTO tbl_Country VALUES ('" + aCountry.Name + "', '" + aCountry.About + "')";
SqlCommand aCommand = new SqlCommand(query, connection);
connection.Open();
int rowAffected = aCommand.ExecuteNonQuery();
connection.Close();
return rowAffected;
}
protected void btnsave_Click(object sender, EventArgs e)
{
lblInvalidMessage.Text = "";
msgLabel.Text = "";
if (txtCountryName.Text.Trim() == "")
{
lblInvalidMessage.Text = "Country Name Can't Be Empty !.";
txtCountryName.Focus();
return;
}
if (txtAbout.Text.Trim() == "")
{
lblInvalidMessage.Text= "Country About Can't Be Empty !.";
txtAbout.Focus();
return;
}
Country aCountry = new Country();
aCountry.Name = txtCountryName.Text;
aCountry.About = txtAbout.Text;
alert.Show(aManager.Save(aCountry));
GrdCountryList.DataSource = aManager.GetCountries();
GrdCountryList.DataBind();
}
public string Save(Country aCountry)
{
if (IsNameExists(aCountry.Name))
{
return "Name Already Exists";
}
else
{
if (aGateway.Save(aCountry) > 0)
{
return "Saved Successfully";
}
else
{
return "Failed";
}
}
}
public List<Country> GetCountry()
{
List<Country> countryList = new List<Country>();
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM tbl_Country order By Name";
SqlCommand aCommand = new SqlCommand(query,connection);
connection.Open();
SqlDataReader aReader = aCommand.ExecuteReader();
while (aReader.Read())
{
Country aCountry = new Country();
aCountry.Id = (int) aReader["id"];
aCountry.Name = (string) aReader["Name"];
aCountry.About = (string) aReader["About"];
countryList.Add(aCountry);
}
aReader.Close();
connection.Close();
return countryList;
}
public List<Country> GetCountriesByName(Country _Country)
{
return aGateway.GetCountryListByName(_Country);
}