/// <summary>
/// Returns collection of SurveySubTotal objects from all surveys
/// in survey_result table aggregated by table field parkCode
/// </summary>
/// <returns>List of all SurveySubtotal objects</returns>
public IList <SurveySubtotal> GetSurveySubtotals()
{
IList <SurveySubtotal> surveySubtotalList = new List <SurveySubtotal>();
const string sql = "SELECT survey_result.parkCode,park.parkName, " +
"COUNT(survey_result.parkCode) AS count_survey " +
"FROM survey_result JOIN park ON park.parkCode = survey_result.parkCode " +
"GROUP BY survey_result.parkCode, park.parkName " +
"HAVING COUNT(survey_result.parkCode) >= 1 " +
"ORDER BY count_survey DESC, park.parkName ASC";
using (SqlConnection conn = new SqlConnection(_connectionString))
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SurveySubtotal surveyTotal = GetSurveySubtotalFromReader(reader);
surveySubtotalList.Add(surveyTotal);
}
}
}
return(surveySubtotalList);
}
/// <summary>
/// Helper method to populate and return SurveySubtotal
/// object from survey_result table query
/// </summary>
/// <param name="reader">A datarow from survey_result table</param>
/// <returns>SurveySubtotal object</returns>
private SurveySubtotal GetSurveySubtotalFromReader(SqlDataReader reader)
{
SurveySubtotal surveySubtotal = new SurveySubtotal();
surveySubtotal.ParkCode = Convert.ToString(reader["parkCode"]);
surveySubtotal.ParkName = Convert.ToString(reader["parkName"]);
surveySubtotal.Total = Convert.ToInt32(reader["count_survey"]);
return(surveySubtotal);
}