/// <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);
}
