/// <summary> /// Returns a full list of exercises in an individual workout plan, with all information returned from Workout_Exercise, Exercise and Set tables for the exercise instance. /// </summary> /// <param name="WorkoutId">The WorkoutId of the workout from the Workout table</param> /// <returns>A list of ExerciseObject objects, which contains all information from the Workout_Exercise, Exercise and Set tables for one exercise instance</returns> public List <ExerciseObject> GetCompleteWorkout(int WorkoutId) { List <ExerciseObject> Workout = new List <ExerciseObject>(); const string WE_Id_sql = " SELECT * from Workout_Exercise where Workout_Exercise.WorkoutId = @WorkoutId ORDER BY [Order]"; List <int> intWork = new List <int>(); using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(WE_Id_sql, conn); cmd.Parameters.AddWithValue("@WorkoutId", WorkoutId); var reader = cmd.ExecuteReader(); while (reader.Read()) { int id = Convert.ToInt32(reader["WE_Id"]); intWork.Add(id); } } foreach (var id in intWork) { ExerciseObject item = GetCompleteExercise(id); Workout.Add(item); } return(Workout); }
/// <summary> /// Returns all information for an exercise on the Workout_Exercise, Exercise and Set tables /// </summary> /// <param name="WE_Id">The ID for the exercise on the Workout_Exercise table</param> /// <returns>An ExerciseObject, which contains all information from the Workout_Exercise, Exercise and Set tables for one exercise instance</returns> public ExerciseObject GetCompleteExercise(int WE_Id) { ExerciseObject ExObject = new ExerciseObject(); List <Set> List = new List <Set>(); const string sql = "SELECT Workout_Exercise.WE_Id as 'Id', Workout_Exercise.WorkoutId as 'WorkoutId', Workout_Exercise.[Order] as 'Exercise Order', [Set].[Order] as 'Set Order', (Select [Name] from [Type] where [Type].TypeId = Exercise.TypeID ) as 'Type', * from [Set]" + " Full outer Join Workout_Exercise on Workout_Exercise.WE_Id = [Set].WE_Id" + " Full outer Join Exercise on Exercise.ExerciseId = Workout_Exercise.ExerciseId" + " where Workout_Exercise.WE_Id = @WE_Id Order by [Set].[Order]"; using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@WE_Id", WE_Id); var reader = cmd.ExecuteReader(); while (reader.Read()) { ExObject.Id = Convert.ToInt32(reader["Id"]); ExObject.WorkoutID = Convert.ToInt32(reader["WorkoutId"]); ExObject.Order = Convert.ToInt32(reader["Exercise Order"]); ExObject.Exercise.Id = Convert.ToInt32(reader["ExerciseId"]); ExObject.Exercise.Name = Convert.ToString(reader["Name"]); ExObject.Exercise.Type = Convert.ToString(reader["Type"]); ExObject.Exercise.VideoLink = Convert.ToString(reader["VideoLink"]); ExObject.Exercise.Description = Convert.ToString(reader["Description"]); if (!DBNull.Value.Equals(reader["Set_Id"])) { Set set = new Set(); set.Id = Convert.ToInt32(reader["Set_Id"]); set.WorkoutExerciseId = Convert.ToInt32(reader["WE_Id"]); set.Reps = Convert.ToInt32(reader["Reps"]); set.Weight = Convert.ToInt32(reader["Weight"]); set.Order = Convert.ToInt32(reader["Set Order"]); set.Time = Convert.ToDecimal(reader["Time"]); set.Distance = Convert.ToDecimal(reader["Distance"]); set.Intensity = Convert.ToInt32(reader["Intensity"]); List.Add(set); } } } ExObject.SetList = List; return(ExObject); }