public List <ChoreCount> GetChoreCounts()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT COUNT(c.Name) as NumberOfChores, r.FirstName
FROM Roommate r
LEFT JOIN RoommateChore rc on rc.RoommateId = r.Id
LEFT JOIN Chore c on c.Id = rc.ChoreId
GROUP BY r.Id, r.FirstName";
SqlDataReader reader = cmd.ExecuteReader();
List <ChoreCount> theChoreCount = new List <ChoreCount>();
while (reader.Read())
{
ChoreCount choreCount = new ChoreCount
{
Name = reader.GetString(reader.GetOrdinal("FirstName")),
NumberOfChores = reader.GetInt32(reader.GetOrdinal("NumberOfChores")),
};
theChoreCount.Add(choreCount);
}
reader.Close();
return(theChoreCount);
}
}
}
public List <ChoreCount> GetChoreCounts()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT r.FirstName, COUNT(j.RoommateId) AS ChoreCount
FROM RoommateChore j
JOIN Roommate r ON j.RoommateId = r.Id
GROUP BY r.FirstName";
SqlDataReader reader = cmd.ExecuteReader();
List <ChoreCount> choreCounts = new List <ChoreCount>();
while (reader.Read())
{
string roommateName = reader.GetString(reader.GetOrdinal("FirstName"));
int count = reader.GetInt32(reader.GetOrdinal("ChoreCount"));
ChoreCount choreChount = new ChoreCount
{
RoommateName = roommateName,
Count = count
};
choreCounts.Add(choreChount);
}
reader.Close();
return(choreCounts);
}
}
}
public List <ChoreCount> GetChoreCounts()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT r.FirstName, COUNT(rc.Id) AS Count
FROM RoommateChore rc
JOIN Roommate r on rc.RoommateId = r.Id
GROUP BY r.FirstName";
SqlDataReader reader = cmd.ExecuteReader();
List <ChoreCount> counts = new List <ChoreCount>();
while (reader.Read())
{
//int idColumnPosition = reader.GetOrdinal("Id");
//int IdValue = reader.GetInt32(idColumnPosition);
int nameColumnPosition = reader.GetOrdinal("FirstName");
string nameValue = reader.GetString(nameColumnPosition);
int countColumnPosition = reader.GetOrdinal("Count");
int CountValue = reader.GetInt32(countColumnPosition);
ChoreCount choreCount = new ChoreCount
{
//Id = IdValue,
Name = nameValue,
Count = CountValue,
};
counts.Add(choreCount);
}
reader.Close();
return(counts);
}
}
}