/*
#####################################################################
# Q2
# customerCity._City
# customerCity._Quantity
#####################################################################
*/
public static List <CustomersCities> GetCustomersCities()
{
List <CustomersCities> customerCities = new List <CustomersCities>();
CustomersCities customerCity;
if (Connect())
{
using (SqlCommand sqlCommand = new SqlCommand("Q2", _Connection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_GetPersonsCities";
SqlDataReader reader;
try
{
reader = sqlCommand.ExecuteReader();
}
catch (Exception ex)
{
return(null);
}
while (reader.Read())
{
customerCity = new CustomersCities();
customerCity._City = Convert.ToString(reader[0]);
customerCity._Quantity = Convert.ToInt32(reader[1]);
customerCities.Add(customerCity);
}
reader.Close();
}
}
else
{
return(null);
}
return(customerCities);
}
/*
#####################################################################
# Q2
# customerCity._City
# customerCity._Quantity
#####################################################################
*/
public static List <CustomersCities> GetCustomersCities()
{
string Q2Query = "select a.city ,count( *) personcount from person p " +
" inner join ADDRESS a on p.ADDRESSCODE = a.ADDRESSCODE " +
" group by a.city " +
" order by personcount desc ,city asc ";
List <CustomersCities> customerCities = new List <CustomersCities>();
if (Connect())
{
//we are going to call query called Q2
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = Q2Query;
sqlCommand.Connection = _Connection;
SqlDataReader reader;
try { reader = sqlCommand.ExecuteReader(); }
catch (Exception ex)
{
return(null);
}
//Converting query results to PersonPhoneAddress objects
while (reader.Read())
{
CustomersCities customerCity = new CustomersCities();
customerCity._City = Convert.ToString(reader[0]);
customerCity._Quantity = Convert.ToInt32(reader[1]);
customerCities.Add(customerCity);
}
reader.Close();
}
}
return(customerCities);
}
