Exemplo n.º 1
0
        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);
                }
            }
        }
Exemplo n.º 2
0
        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);
                }
            }
        }
Exemplo n.º 3
0
        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);
                }
            }
        }