private void Get_CommonWords(SqlConnection connection, HomePageData data)
{
string sql = "SELECT TOP 40 [Word],COUNT(*) AS [SongCount] FROM [SongTitleWords] GROUP BY [Word] ORDER BY COUNT(*) DESC";
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.Text;
command.CommandTimeout = 200;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
data.CommonWords.Add(new { Title = reader.GetString(0), SongCount = reader.GetInt32(1) });
}
reader.Close();
}
private void Get_UniqueWords(SqlConnection connection, HomePageData data)
{
string sql = "SELECT [Word] FROM [SongTitleWords] GROUP BY [Word] HAVING COUNT(*) = 1 ORDER BY [Word]";
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.Text;
command.CommandTimeout = 200;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
data.UniqueWords.Add(reader.GetString(0));
}
reader.Close();
}
private void Get_Genres(SqlConnection connection, HomePageData data)
{
string sql = "SELECT TOP 40 [Id],[Title],[SongCount],[Score] FROM [Genres] ORDER BY [Score] DESC";
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.Text;
command.CommandTimeout = 200;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Genre a = new Genre();
a.Id = reader.GetInt32(0);
a.Title = reader.GetString(1);
a.SongCount = reader.GetInt16(2);
a.Score = reader.GetDecimal(3);
data.Genres.Add(a);
}
reader.Close();
}
public HomePageData Get()
{
HomePageData data = new HomePageData();
SqlCommand command;
SqlDataReader reader;
// Get the data.
using (SqlConnection conn = SongChartsDatabase.Connection()) {
conn.Open();
command = new SqlCommand("SELECT [DecadeNumber],[SongCount] FROM [dbo].[DecadeSummaries] ORDER BY [DecadeNumber]", conn);
command.CommandType = CommandType.Text;
command.CommandTimeout = 200;
reader = command.ExecuteReader();
while (reader.Read())
{
Decade decade = new Decade();
decade.GetSummaryData(reader);
data.Decades.Add(decade);
}
reader.Close();
command = new SqlCommand("SELECT [YearNumber],[SongCount] FROM [dbo].[YearSummaries] ORDER BY [YearNumber]", conn);
command.CommandType = CommandType.Text;
command.CommandTimeout = 200;
reader = command.ExecuteReader();
while (reader.Read())
{
Year year = new Year();
year.GetSummaryData(reader);
data.Years.Add(year.Number, year);
}
reader.Close();
Get_Artists(conn, data);
Get_Genres(conn, data);
Get_CommonWords(conn, data);
Get_UniqueWords(conn, data);
conn.Close();
// Massage data and form output object.
//TEMP? data.DecadeNumbers = data.Decades.Keys.ToList();
//TEMP? data.YearNumbers = data.Years.Keys.ToList();
// Put the year data into the respective decade objects.
foreach (Decade decade in data.Decades)
{
short decadeNumber = decade.Number;
// decade.Years == {}
short[] yearOrdinals = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
foreach (short y in yearOrdinals)
{
short yearNumber = (short)(decadeNumber + y);
Year year;
if (data.Years.ContainsKey(yearNumber))
{
year = data.Years[yearNumber];
}
else
{
year = new Year(yearNumber);
}
decade.Years.Add(year);
}
}
// Get artist summary.
} // using
// All done.
return(data);
}
