예제 #1
0
        public List <ExerciseModel> GetAll(string sortField, int userId)
        {
            List <ExerciseModel> tempExercises = new List <ExerciseModel>();

            using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
            {
                connection.Open();
                SqlCommand dataCommand = new SqlCommand()
                {
                    CommandText = "SELECT * FROM Exercises Inner join User_Exercise on [Exercises].ExerciseId = [User_Exercise].ExerciseId where UserId = '" + userId + "' AND InWorkout = 0 ORDER BY CASE WHEN @sortField = 'Name' Then Name WHEN @sortField = 'Weight' Then Weight WHEN @sortField = 'Repetitions' Then Repetitions WHEN @sortField = 'Date' Then Date END DESC",
                    Connection  = connection
                };
                SqlParameter sqlParameter = new SqlParameter();
                sqlParameter.ParameterName = "@sortfield";
                sqlParameter.Value         = sortField;
                dataCommand.Parameters.Add(sqlParameter);
                using (SqlDataReader exerciseReader = dataCommand.ExecuteReader())
                {
                    while (exerciseReader.Read())
                    {
                        ExerciseModel tempExercise = new ExerciseModel(Convert.ToInt32(exerciseReader["ExerciseId"]), exerciseReader["Name"].ToString(), Convert.ToInt32(exerciseReader["Weight"]), Convert.ToInt32(exerciseReader["Repetitions"]), ((DateTime)exerciseReader["Date"]).ToString("d/M/yyyy HH:mm:ss"), exerciseReader["Skillevel"].ToString(), (bool)exerciseReader["InWorkout"]);
                        tempExercises.Add(tempExercise);
                    }
                    exerciseReader.Close();
                    return(tempExercises);
                }
            }
        }
예제 #2
0
        public List <ExerciseModel> GetAllExercises(int workoutId)
        {
            List <ExerciseModel> tempExercises = new List <ExerciseModel>();

            using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
            {
                connection.Open();
                SqlCommand dataCommand = new SqlCommand()
                {
                    CommandText = "Select* FROM [Exercises] Inner join [Exercise_Workout] on [Exercises].ExerciseId = [Exercise_Workout].ExerciseId where [Exercise_Workout].WorkoutId = '" + workoutId + "'",
                    Connection  = connection
                };

                using (SqlDataReader exerciseReader = dataCommand.ExecuteReader())
                {
                    while (exerciseReader.Read())
                    {
                        ExerciseModel tempExercise = new ExerciseModel(exerciseReader["Name"].ToString(), Convert.ToInt32(exerciseReader["Repetitions"]), exerciseReader["Skillevel"].ToString());;
                        tempExercises.Add(tempExercise);
                    }
                    exerciseReader.Close();
                    return(tempExercises);
                }
            }
        }
예제 #3
0
 public void Delete(int id)
 {
     using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
     {
         connection.Open();
         string     query = @"Delete from [Workouts] where WorkoutId = '" + id + "'";
         SqlCommand qry   = new SqlCommand(query, connection);
         qry.ExecuteNonQuery();
     }
 }
예제 #4
0
 public void DeleteAll()
 {
     using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
     {
         connection.Open();
         string     query = @"Delete from [Exercises] where inWorkout = 0";
         SqlCommand qry   = new SqlCommand(query, connection);
         qry.ExecuteNonQuery();
     }
 }
예제 #5
0
 public void UpdateExercise(int id, string name, int weight, int repetitions, string skillevel)
 {
     using (SqlConnection conn = new SqlConnection(AppSettingsJson.GetConnectionstring()))
     {
         conn.Open();
         var query = @"update Exercises set Name ='" + name + "', Weight ='" + weight + "', Repetitions ='" + repetitions + "', Skillevel ='" + skillevel + "' where ExerciseId = '" + id + "'";
         using (SqlCommand command = new SqlCommand(query, conn))
         {
             command.ExecuteNonQuery();
         }
     }
 }
예제 #6
0
        public void Add(WorkoutModel workout, int userId)
        {
            using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
            {
                connection.Open();
                string query = @"insert into [Workouts] (Name, Skillevel, Category, Time, CaloriesBurned) values ('" + workout.Name + "','" +
                               workout.Skillevel + "','" + workout.Category + "','" + workout.Time + "', '" + workout.Time * 4 + "')";
                SqlCommand qry = new SqlCommand(query, connection);
                qry.ExecuteNonQuery();

                string     userExerciseQuery = @"insert into [User_Workout] (UserId, WorkoutId) values ('" + userId + "','" + workout.Id + "')";
                SqlCommand userExerciseQry   = new SqlCommand(userExerciseQuery, connection);
                userExerciseQry.ExecuteNonQuery();
            }
        }
예제 #7
0
 public int GetAmountOfExercises(int id)
 {
     using (SqlConnection conn = new SqlConnection(AppSettingsJson.GetConnectionstring()))
     {
         conn.Open();
         SqlCommand dataCommand = new SqlCommand()
         {
             CommandText = "SELECT COUNT(Exercise_Workout.ExerciseId) AS NumberOfExercises FROM Exercise_Workout LEFT JOIN Workouts ON Exercise_Workout.WorkoutId = Workouts.WorkoutId where workouts.workoutId ='" + id + "' GROUP BY Workouts.name ",
             Connection  = conn
         };
         using (SqlDataReader workoutReader = dataCommand.ExecuteReader())
         {
             workoutReader.Read();
             return(Convert.ToInt32(workoutReader["NumberOfExercises"]));
         }
     }
 }
예제 #8
0
 public int GetIdFromLastExercise()
 {
     using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
     {
         connection.Open();
         SqlCommand dataCommand = new SqlCommand()
         {
             CommandText = "Select Top 1 ExerciseId From [Exercises] Order By ExerciseId Desc",
             Connection  = connection
         };
         using (SqlDataReader exerciseReader = dataCommand.ExecuteReader())
         {
             exerciseReader.Read();
             return(Convert.ToInt32(exerciseReader["ExerciseId"]));
         }
     }
 }
예제 #9
0
        public void Add(ExerciseModel exercise, int workoutId, int userId)
        {
            using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
            {
                connection.Open();
                string query = @"insert into [Exercises] (Name,Weight,Repetitions,Skillevel, InWorkout) values ('" + exercise.Name + "','" +
                               exercise.Weight + "','" + exercise.Repetitions + "','" + exercise.Level + "','" + exercise.InWorkout + "')";
                SqlCommand qry = new SqlCommand(query, connection);
                qry.ExecuteNonQuery();

                string     userExerciseQuery = @"insert into [User_Exercise] (UserId, ExerciseId) values ('" + userId + "','" + GetIdFromLastExercise() + "')";
                SqlCommand userExerciseQry   = new SqlCommand(userExerciseQuery, connection);
                userExerciseQry.ExecuteNonQuery();

                if (workoutId != 0)
                {
                    string     exerciseWorkoutQuery = @"insert into [Exercise_Workout](ExerciseId, WorkoutId) values ('" + GetIdFromLastExercise() + "' , '" + workoutId + "')";
                    SqlCommand exerciseWorkoutQry   = new SqlCommand(exerciseWorkoutQuery, connection);
                    exerciseWorkoutQry.ExecuteNonQuery();
                }
            }
        }
예제 #10
0
        public List <WorkoutModel> GetAll(int userId)
        {
            List <WorkoutModel> tempWorkouts = new List <WorkoutModel>();

            using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
            {
                connection.Open();
                SqlCommand dataCommand = new SqlCommand()
                {
                    CommandText = "SELECT * FROM [dbo].[Workouts] Inner join User_Workout on [Workouts].WorkoutId = User_Workout.WorkoutId where UserId = '" + userId + "' SELECT COUNT(Exercise_Workout.ExerciseId) AS NumberOfExercises FROM Exercise_Workout LEFT JOIN Workouts ON Exercise_Workout.WorkoutId = Workouts.WorkoutId GROUP BY Workouts.name ",
                    Connection  = connection
                };
                using (SqlDataReader workoutReader = dataCommand.ExecuteReader())
                {
                    while (workoutReader.Read())
                    {
                        WorkoutModel tempWorkout = new WorkoutModel(Convert.ToInt32(workoutReader["WorkoutId"]), workoutReader["Name"].ToString(), workoutReader["Skillevel"].ToString(), Convert.ToInt32(workoutReader["Time"]), Convert.ToInt32(workoutReader["CaloriesBurned"]), workoutReader["Category"].ToString(), GetAmountOfExercises(Convert.ToInt32(workoutReader["WorkoutId"])));
                        tempWorkouts.Add(tempWorkout);
                    }
                    workoutReader.Close();
                    return(tempWorkouts);
                }
            }
        }