/*
#####################################################################
# Q3
# cityBOD._MinDOB
# cityBOD._MaxDOB
#####################################################################
*/
public static List <CityBOD> GetCityDOB(string city)
{
string Q3Query = " select a.city, min ( p.DOB) as mindob, max(p.DOB) as maxdob " +
" from person p " +
" inner join ADDRESS a on p.ADDRESSCODE = a.ADDRESSCODE " +
" and a.city= '" + city + "'" +
" group by a.city ";
List <CityBOD> cityBODs = new List <CityBOD>();
if (Connect())
{
//we are going to call query called Q3
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = Q3Query;
sqlCommand.Connection = _Connection;
SqlDataReader reader;
try {
reader = sqlCommand.ExecuteReader();
}
catch (Exception ex)
{
return(null);
}
//Converting query results to PersonPhoneAddress objects
while (reader.Read())
{
CityBOD cityBOD = new CityBOD();
cityBOD._MinDOB = Convert.ToString(reader[1]);
cityBOD._MaxDOB = Convert.ToString(reader[2]);
cityBODs.Add(cityBOD);
}
reader.Close();
}
}
return(cityBODs);
}
/*
#####################################################################
# Q3
# cityBOD._MinDOB
# cityBOD._MaxDOB
#####################################################################
*/
public static List <CityBOD> GetCityDOB(string city)
{
List <CityBOD> cityBODs = new List <CityBOD>();
CityBOD cityBOD;
if (Connect())
{
using (SqlCommand sqlCommand = new SqlCommand("Q3", _Connection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_GetCustomersCities";
sqlCommand.Parameters.Add("@city", SqlDbType.VarChar).Value = city;
SqlDataReader reader;
try
{
reader = sqlCommand.ExecuteReader();
}
catch (Exception ex)
{
return(null);
}
while (reader.Read())
{
cityBOD = new CityBOD();
cityBOD._MinDOB = Convert.ToString(reader[0]);
cityBOD._MaxDOB = Convert.ToString(reader[1]);
cityBODs.Add(cityBOD);
}
reader.Close();
}
}
else
{
return(null);
}
return(cityBODs);
}
