// Assign an existing exercise to an existing student.
public void AddExerciseToStudent(StudentExercise exercise)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
///cmd.CommandText = $@"INSERT INTO StudentExercise (StudentId, ExerciseId)
/// VALUES ({exercise.StudentId}, {exercise.ExerciseId})";
cmd.CommandText = $@"INSERT INTO StudentExercise (StudentId, ExerciseId)
VALUES (@studentId, @exerciseId)";
cmd.Parameters.Add(new SqlParameter("@studentId", exercise.StudentId));
cmd.Parameters.Add(new SqlParameter("@exerciseId", exercise.ExerciseId));
cmd.ExecuteNonQuery();
}
}
}
public async Task <IActionResult> Create(CreateExerciseViewModel model)
{
if (ModelState.IsValid)
{
_context.Add(model.Exercise);
foreach (int StudentId in model.SelectedStudents)
{
StudentExercise newSE = new StudentExercise()
{
ExerciseId = model.Exercise.ExerciseId,
StudentId = StudentId
};
_context.Add(newSE);
}
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
return(View(model));
}
public async Task <IActionResult> Create(CreateStudentViewModel model)
{
if (ModelState.IsValid)
{
_context.Add(model.Student);
foreach (int exerciseId in model.SelectedExercises)
{
StudentExercise newSE = new StudentExercise()
{
StudentId = model.Student.StudentId,
ExerciseId = exerciseId
};
_context.Add(newSE);
}
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
ViewData["CohortId"] = new SelectList(_context.Cohorts, "CohortId", "Name", model.Student.CohortId);
return(View(model));
}
public async Task <IActionResult> Put([FromRoute] int id, [FromBody] StudentExercise studentexercise)
{
try
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"UPDATE StudentExercise
SET StudentId = @studentId,
ExerciseId = @exerciseId
WHERE Id = @id";
cmd.Parameters.Add(new SqlParameter("@studentId", studentexercise.StudentId));
cmd.Parameters.Add(new SqlParameter("@exerciseId", studentexercise.ExerciseId));
cmd.Parameters.Add(new SqlParameter("@id", id));
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
return(new StatusCodeResult(StatusCodes.Status204NoContent));
}
throw new Exception("No rows affected");
}
}
}
catch (Exception)
{
if (!StudentExerciseExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
}
public ActionResult AddExercise(int id, StudentExercise table)
{
try
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
INSERT INTO StudentExercise (
ExerciseId,
StudentId,
InstructorId
) Values (
@studentId,
@exerciseId,
@instructorId
)
";
cmd.Parameters.AddWithValue("@studentId", id);
cmd.Parameters.AddWithValue("@exerciseId", table.ExerciseId);
cmd.Parameters.AddWithValue("@instructorId", table.InstructorId);
cmd.ExecuteNonQuery();
}
}
return(RedirectToAction(nameof(Index)));
}
catch (Exception ex)
{
return(View());
}
}
static void CreateHelpRequestExercise()
{
StudentExercise newStudentExercise = new StudentExercise();
using (var _UnitOfWork = new UnitOfWork(new StudentHelperContext()))
{
Console.WriteLine("Current available student ID's:");
foreach (Student element in _UnitOfWork.Students.GetAll())
{
Console.WriteLine(element.AUID);
}
}
Console.WriteLine("Enter students AUID");
newStudentExercise.StudentAUID = System.Console.ReadLine();
using (var _UnitOfWork = new UnitOfWork(new StudentHelperContext()))
{
Console.WriteLine("Current exercises (number, lectures, courseID):");
foreach (Exercise element in _UnitOfWork.Exercises.GetAll())
{
Console.WriteLine(element.Number + ", " + element.Lecture + ", " + element.CourseID);
}
}
Console.WriteLine("Enter exercise number");
newStudentExercise.ExerciseNumber = int.Parse(System.Console.ReadLine());
Console.WriteLine("Enter lecture");
newStudentExercise.ExerciseLecture = int.Parse(System.Console.ReadLine());
Console.WriteLine("Enter course ID");
newStudentExercise.CourseId = int.Parse(System.Console.ReadLine());
using (var _UnitOfWork = new UnitOfWork(new StudentHelperContext()))
{
_UnitOfWork.HelpRequests.AddHelpRequestExercise(newStudentExercise);
_UnitOfWork.Complete();
}
}
public async Task <IActionResult> Create(CreateExerciseViewModel viewmodel)
{
if (ModelState.IsValid)
{
_context.Add(viewmodel.Exercise);
if (viewmodel.SelectedStudents != null)
{
foreach (int assignedStudentId in viewmodel.SelectedStudents)
{
StudentExercise newSE = new StudentExercise()
{
StudentId = assignedStudentId,
ExerciseId = viewmodel.Exercise.ExerciseId
};
_context.Add(newSE);
}
}
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
return(View(viewmodel));
}
public async Task <IActionResult> Create(CreateExerciseViewModel model)
{
if (ModelState.IsValid)
{
_context.Add(model.Exercise);
foreach (int studentId in model.SelectedStudents)
{
// create a new instance of StudentExercise for each selected student
StudentExercise newSE = new StudentExercise()
{
ExerciseId = model.Exercise.ExerciseId,
StudentId = studentId
};
// insert each selected exercise to the StudentExercise join table in db
_context.Add(newSE);
}
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Index)));
}
return(View(model));
}
public List <StudentExercise> GetStudentExercises()
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
@"SELECT se.Id as StudentExerciseId, StudentId, ExerciseId, s.FirstName, s.LastName,
s.SlackHandle, s.CohortId, e.ExerciseName as ExerciseName, e.ExerciseLanguage FROM StudentExercise as se
LEFT JOIN Student as s ON se.StudentId = s.Id
JOIN Exercise as e ON se.ExerciseId = e.Id;";
SqlDataReader reader = cmd.ExecuteReader();
List <StudentExercise> StudentExercises = new List <StudentExercise>();
while (reader.Read())
{
int StudentExerciseIdPosition = reader.GetOrdinal("StudentExerciseId");
int StudentExerciseIdValue = reader.GetInt32(StudentExerciseIdPosition);
int StudentIdPosition = reader.GetOrdinal("StudentId");
int StudentIdValue = reader.GetInt32(StudentIdPosition);
int ExerciseIdPosition = reader.GetOrdinal("ExerciseId");
int ExerciseIdValue = reader.GetInt32(ExerciseIdPosition);
int StudentFirstNamePosition = reader.GetOrdinal("FirstName");
string StudentFirstNameValue = reader.GetString(StudentFirstNamePosition);
int StudentLastNamePosition = reader.GetOrdinal("LastName");
string StudentLastNameValue = reader.GetString(StudentLastNamePosition);
int SlackHandlePosition = reader.GetOrdinal("SlackHandle");
string SlackHandleValue = reader.GetString(SlackHandlePosition);
int CohortIdPosition = reader.GetOrdinal("CohortId");
int CohortIdValue = reader.GetInt32(CohortIdPosition);
int ExerciseNamePosition = reader.GetOrdinal("ExerciseName");
string ExerciseNameValue = reader.GetString(ExerciseNamePosition);
int ExerciseLanguagePosition = reader.GetOrdinal("ExerciseLanguage");
string ExerciseLanguageValue = reader.GetString(ExerciseLanguagePosition);
Exercise exercise = new Exercise
{
Id = ExerciseIdValue,
ExerciseName = ExerciseNameValue,
ExerciseLanguage = ExerciseLanguageValue
};
Student student = new Student
{
Id = StudentIdValue,
FirstName = StudentFirstNameValue,
LastName = StudentLastNameValue,
SlackHandle = SlackHandleValue,
CohortId = CohortIdValue
};
StudentExercise studentExercise = new StudentExercise
{
Id = StudentExerciseIdValue,
StudentId = StudentIdValue,
Student = student,
ExerciseId = ExerciseIdValue,
Exercise = exercise
};
StudentExercises.Add(studentExercise);
}
reader.Close();
return(StudentExercises);
}
}
}
/// The Main method is the starting point for a .net application.
static void Main(string[] args)
{
/// Create an instance of the Repository class in order to use its methods to interact with the database.
Repository repository = new Repository();
List <Exercise> exercises = repository.GetAllExercises();
List <Student> students = repository.GetAllStudentsWithCohorts();
List <StudentExercise> studentExercises = repository.GetAllStudentExercises();
List <Instructor> instructors = repository.GetAllInstructorsWithCohorts();
PrintExerciseReport("All Exercises", exercises);
Pause();
// Find all the exercises in the database where the language is JavaScript.
IEnumerable <Exercise> JsExercises = exercises.Where(e => e.ExerciseLanguage == "Javascript");
PrintExerciseReport("Javascript Exercises", JsExercises.ToList());
Pause();
// Insert a new exercise into the database.
Exercise adoNetSql = new Exercise {
ExerciseName = "Using ADO.NET and SQL", ExerciseLanguage = "C#"
};
// Pass the exercise object as an argument to the repository's AddExercise() method.
repository.AddExercise(adoNetSql);
exercises = repository.GetAllExercises();
PrintExerciseReport("All Departments after adding new C# exercise", exercises);
Pause();
// Find all instructors in the database. Include each instructor's cohort.
PrintInstructorReport("All Instructors", instructors);
Pause();
// Insert a new instructor into the database. Assign the instructor to an existing cohort.
Instructor brenda = new Instructor {
FirstName = "Brenda", LastName = "Long", Slack = "brenda", CohortId = 1
};
// Pass the exercise object as an argument to the repository's AddExercise() method.
repository.AddInstructor(brenda);
instructors = repository.GetAllInstructorsWithCohorts();
PrintInstructorReport("All Instructors after adding new instructor", instructors);
Pause();
PrintStudentExerciseReport("All Student Exercises", studentExercises);
Pause();
// Assign an existing exercise to an existing student.
StudentExercise newAssignment = new StudentExercise {
StudentId = 9, ExerciseId = 1
};
repository.AddExerciseToStudent(newAssignment);
studentExercises = repository.GetAllStudentExercises();
PrintStudentExerciseReport("All Student Exercises after assigning a new one", studentExercises);
Pause();
}
public void AddHelpRequestExercise(StudentExercise studentExercise)
{
Context.Set <StudentExercise>().Add(studentExercise);
}
public ExerciseReview(Teacher teacher, StudentExercise studentExercise, ExerciseReviewStatus exerciseReviewStatus)
{
Teacher = teacher;
StudentExercise = studentExercise;
ExerciseReviewStatus = exerciseReviewStatus;
}
