/// <summary>
/// Removes a grade from the database
/// </summary>
/// <param name="connection"></param>
/// <param name="stgID"></param>
private static void clearGrade(SqlConnection connection, int stgID)
{
try
{
SqlCommand command = new SqlCommand(null, connection)
{
CommandText = "DELETE FROM STUDENT_ANSWERS WHERE SA_SCENARIO_TO_GRADE = @stgID"
};
command.Parameters.Add(new SqlParameter("@stgID", SqlDbType.VarChar, 15)
{
Value = stgID
});
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
Singleton.errorCode = "SEN14";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
public static void getUserRoles(SqlConnection connection)
{
Singleton.roles = new Dictionary <string, string>();
try
{
var getUserRolesCommand = new SqlCommand(null, connection)
{
CommandText = "SELECT * FROM AspNetRoles;"
};
connection.Close(); connection.Open();
getUserRolesCommand.Prepare();
using (var reader = getUserRolesCommand.ExecuteReader())
{
while (reader.Read())
{
Singleton.roles.Add($"{reader["Id"]}", $"{reader["Name"]}");
}
}
}
catch (Exception ex)
{
Singleton.errorCode = "USR06";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Activates a scenario in the database
/// </summary>
/// <param name="scenarioId"></param>
/// <param name="connection"></param>
public static void makeScenarioActive(int scenarioId, SqlConnection connection)
{
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "UPDATE SCENARIOS SET SCENARIO_IS_ACTIVE = 1 WHERE SCENARIO_ID = @scenarioID"
};
var param = new SqlParameter("@scenarioID", SqlDbType.Int)
{
Value = scenarioId
};
command.Parameters.Add(param);
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
Singleton.errorCode = "SEN06";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* This function takes in a questionID and makes that question active.
*/
public static void makeQuestionActive(int questionId, SqlConnection connection)
{
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "UPDATE QUESTIONS SET QUESTION_IS_ACTIVE = 1 WHERE QUESTION_ID = @questionId"
};
var param = new SqlParameter("@questionId", SqlDbType.Int)
{
Value = questionId
};
command.Parameters.Add(param);
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
Singleton.errorCode = "QUE06";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// make a answer inactive
/// This means it still exists but cannot be seen when playing a scenario
/// </summary>
/// <param name="answerID">the selected answer object in the UI to modify the answer based off the ID in the database </param>
/// <param name="connection">connection to the database</param>
public static void makeAnswerInactive(int answerID, SqlConnection connection)
{
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "UPDATE ANSWERS SET ANSWER_IS_ACTIVE = 0 WHERE ANSWER_ID = @answerID"
};
var param = new SqlParameter("@answerID", SqlDbType.Int)
{
Value = answerID
};
command.Parameters.Add(param);
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
Singleton.errorCode = "ANS04";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* On completion of a scenario, put an entry in the SCENARIOS_TO_GRADE table for teachers/admins to grade
*/
public static int insertScenarioToGrade(string studentId, int scenarioId, int?grade, SqlConnection connection)
{
int newScenarioToGrade;
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "INSERT INTO SCENARIOS_TO_GRADE " +
"(" +
"STG_STUDENT_ID, " +
"STG_SCENARIO_ID, " +
"STG_GRADE" +
") " +
"VALUES " +
"(" +
"@studentID, " +
"@scenarioID, " +
"@grade" +
"); " +
"" +
"SELECT SCOPE_IDENTITY();"
};
var param = new SqlParameter("@studentID", SqlDbType.NVarChar, 128)
{
Value = studentId
};
command.Parameters.Add(param);
var param2 = new SqlParameter("@scenarioID", SqlDbType.Int)
{
Value = scenarioId
};
command.Parameters.Add(param2);
var param3 = new SqlParameter("@grade", SqlDbType.Int)
{
Value = (object)grade ?? DBNull.Value
};
command.Parameters.Add(param3);
connection.Close(); connection.Open();
command.Prepare();
newScenarioToGrade = Convert.ToInt32(command.ExecuteScalar());
}
catch (Exception ex)
{
Singleton.errorCode = "SEN10";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(newScenarioToGrade);
}
/*
* This function approves the scenario that matches the scenario ID
*/
public static void approveScenario(int scenarioId, SqlConnection connection)
{
try
{
SqlCommand approveScenarioCommand = new SqlCommand(null, connection)
{
CommandText = "UPDATE SCENARIOS " +
"SET SCENARIO_APPROVED = 1 " +
"WHERE SCENARIO_ID = @id;"
};
// Create and prepare an SQL statement.
SqlParameter param1 = new SqlParameter("@id", SqlDbType.Int)
{
Value = scenarioId
};
approveScenarioCommand.Parameters.Add(param1);
// Call Prepare after setting the Commandtext and Parameters.
connection.Close(); connection.Open();
approveScenarioCommand.Prepare();
approveScenarioCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "SEN07";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* This function takes in a scenario ID and deletes that entry in the scenarios table in the database.
*/
public static void deleteScenario(int scenarioID, SqlConnection connection)
{
try
{
SqlCommand command = new SqlCommand(null, connection)
{
CommandText = "DELETE FROM SCENARIOS WHERE SCENARIO_ID = @scenarioID"
};
command.Parameters.Add(new SqlParameter("@scenarioID", SqlDbType.VarChar, 15)
{
Value = scenarioID
});
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
Singleton.errorCode = "SEN03";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// make a answer active
/// This means it can be seen while playing
/// </summary>
/// <param name="answerID">the selected answer object in the UI to modify the answer based off the ID in the database </param>
/// <param name="connection">connection to the database</param>
public static bool makeAnswerActive(int answerID, SqlConnection connection)
{
try
{
var checkForQuestionInactiveCommand = new SqlCommand(null, connection)
{
CommandText = "SELECT QUESTIONS.QUESTION_IS_ACTIVE FROM QUESTIONS " +
"WHERE QUESTION_ID IN (SELECT ANSWER_QUESTIONS.QUESTION_REFERENCE_ID FROM " +
"ANSWER_QUESTIONS WHERE ANSWER_QUESTIONS.ANSWER_ID = @answerID);"
};
var answerIDParam = new SqlParameter("@answerID", SqlDbType.Int)
{
Value = answerID
};
checkForQuestionInactiveCommand.Parameters.Add(answerIDParam);
connection.Close(); connection.Open();
checkForQuestionInactiveCommand.Prepare();
using (SqlDataReader reader = checkForQuestionInactiveCommand.ExecuteReader())
{
while (reader.Read())
{
if ($"{reader["QUESTION_IS_ACTIVE"]}" == "False")
{
Singleton.errorMsg = "Unable to activate this question. One or more questions this answer references are inactive. ";
Singleton.isError = true;
return(false);
}
}
}
checkForQuestionInactiveCommand.Parameters.Clear();
var command = new SqlCommand(null, connection)
{
CommandText = "UPDATE ANSWERS SET ANSWER_IS_ACTIVE = 1 WHERE ANSWER_ID = @answerID"
};
command.Parameters.Add(answerIDParam);
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
Singleton.errorCode = "ANS05";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(true);
}
/*
* This function will return a list of all the scenarios. Teachers and Admins can edit any scenario but
* students are restricted to editing their own scenarios.
*/
public static Scenarios getAdminScenariosToEdit(SqlConnection connection)
{
var scenarios = new Scenarios {
ScenarioList = new List <Scenario>()
};
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "SELECT * " +
"FROM SCENARIOS JOIN STUDENTS ON SCENARIOS.SCENARIO_MADE_BY = STUDENTS.STUDENT_ID " +
"WHERE SCENARIO_ID != 0"
};
connection.Close(); connection.Open();
command.Prepare();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var scenario = new Scenario();
scenario.scenarioName = $"{reader["SCENARIO_NAME"]}";
scenario.scenarioID = Convert.ToInt32($"{reader["SCENARIO_ID"]}");
scenario.description = $"{reader["SCENARIO_DESCRIPTION"]}";
scenario.isActive = Convert.ToBoolean($"{reader["SCENARIO_IS_ACTIVE"]}".ToLower());
scenario.madeBy = $"{reader["STUDENT_NAME"]}";
scenario.cellFunction = $"{reader["SCENARIO_CELL_FUNCTION"]}";
scenario.cellShapeAndFeatures = $"{reader["SCENARIO_CELL_SHAPE_AND_FEATURES"]}";
scenario.cellLifespan = $"{reader["SCENARIO_CELL_LIFESPAN"]}";
scenario.cellNutrition = $"{reader["SCENARIO_CELL_NUTRITION"]}";
if ($"{reader["SCENARIO_FIRST_QUESTION"]}".Equals(""))
{
scenario.currentQuestion = 0;
}
else
{
scenario.currentQuestion = Convert.ToInt32($"{reader["SCENARIO_FIRST_QUESTION"]}");
}
scenarios.ScenarioList.Add(scenario);
}
}
}
catch (Exception ex)
{
Singleton.errorCode = "USR04";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(scenarios);
}
/*
* This function takes in a question and adds its information to the database.
*/
public static int addQuestion(Question question, SqlConnection connection)
{
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "INSERT INTO QUESTIONS (QUESTION_TITLE, QUESTION_DESCRIPTION, QUESTION_SCENARIO, QUESTION_PICTURE_URL, QUESTION_IS_ACTIVE)" +
"VALUES (@questionTitle, @questionDescription, @scenarioNumber, @pictureURL, 1); SELECT SCOPE_IDENTITY();"
};
var param = new SqlParameter("@questionTitle", SqlDbType.VarChar, 140)
{
Value = question.questionTitle
};
command.Parameters.Add(param);
var param2 = new SqlParameter("@questionDescription", SqlDbType.VarChar, 1000)
{
Value = question.questionDescription
};
command.Parameters.Add(param2);
if (question.questionPicture == null)
{
question.questionPicture = "";
}
var param3 = new SqlParameter("@pictureURL", SqlDbType.VarChar, 500)
{
Value = question.questionPicture
};
command.Parameters.Add(param3);
var param4 = new SqlParameter("@scenarioNumber", SqlDbType.Int)
{
Value = question.questionScenario
};
command.Parameters.Add(param4);
connection.Close(); connection.Open();
command.Prepare();
question.questionId = Convert.ToInt32(command.ExecuteScalar());
}
catch (Exception ex)
{
Singleton.errorCode = "QUE02";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(question.questionId);
}
/// <summary>
/// Updates the answer from the EditScenario UI
/// </summary>
/// <param name="answer">the answer that they have modified in the UI</param>
/// <param name="connection">connection to the database</param>
public static void editAnswer(Answer answer, SqlConnection connection)
{
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "UPDATE ANSWERS SET " +
"ANSWER_FOR_QUESTION = @answerForQuestion, " +
"ANSWER_TEXT = @answerText, " +
"ANSWER_REQUIRES_COMMENT = @requiresComment " +
"WHERE ANSWER_ID = @answerID"
};
var param = new SqlParameter("@answerForQuestion", SqlDbType.Int)
{
Value = answer.answerForQuestion
};
command.Parameters.Add(param);
var param2 = new SqlParameter("@answerText", SqlDbType.VarChar, 500)
{
Value = answer.answerText
};
command.Parameters.Add(param2);
var param3 = new SqlParameter("@answerID", SqlDbType.Int)
{
Value = answer.answerID
};
command.Parameters.Add(param3);
var param4 = new SqlParameter("@requiresComment", SqlDbType.Bit)
{
Value = Convert.ToInt32(answer.requiresComment)
};
command.Parameters.Add(param4);
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "ANS02";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* This function takes in a Question and updates its values in the database.
*/
public static void editQuestion(Question question, SqlConnection connection)
{
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "UPDATE QUESTIONS SET " +
"QUESTION_TITLE = @questionTitle, " +
"QUESTION_DESCRIPTION = @questionDescription, " +
"QUESTION_PICTURE_URL = @pictureURL " +
"WHERE QUESTION_ID = @questionId"
};
var param = new SqlParameter("@questionTitle", SqlDbType.VarChar, 140)
{
Value = question.questionTitle
};
command.Parameters.Add(param);
var param2 = new SqlParameter("@questionDescription", SqlDbType.VarChar, 1000)
{
Value = question.questionDescription
};
command.Parameters.Add(param2);
var param3 = new SqlParameter("@pictureURL", SqlDbType.VarChar, 200)
{
Value = question.questionPicture
};
command.Parameters.Add(param3);
var param4 = new SqlParameter("@questionId", SqlDbType.Int)
{
Value = question.questionId
};
command.Parameters.Add(param4);
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "QUE03";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// This deletes an answer.
/// To delete the answer must not have been answered in a Play Scenario and inserted into a grade
/// </summary>
/// <param name="answerID">the selected answer object in the UI to modify the answer based off the ID in the database </param>
/// <param name="connection">connection to the database</param>
public static bool deleteAnswer(int answerID, SqlConnection connection)
{
try
{
var checkIfReferencesCommand = new SqlCommand(null, connection)
{
CommandText = "SELECT COUNT(*) FROM STUDENT_ANSWERS WHERE SA_ANSWER_ID = 2;"
};
var answerIDParam = new SqlParameter("@answerID", SqlDbType.Int)
{
Value = answerID
};
checkIfReferencesCommand.Parameters.Add(answerIDParam);
connection.Close(); connection.Open();
if ((Int32)checkIfReferencesCommand.ExecuteScalar() > 0)
{
Singleton.warningMsg =
"This answer is referenced in current answers that are being graded, so it has been inactivated";
return(false);
}
checkIfReferencesCommand.Parameters.Clear();
var deletAnswerCommand = new SqlCommand(null, connection)
{
CommandText = "DELETE FROM ANSWER_QUESTIONS WHERE ANSWER_ID = @answerID; " +
"DELETE FROM ANSWERS WHERE ANSWER_ID = @answerID;"
};
deletAnswerCommand.Parameters.Add(answerIDParam);
connection.Close(); connection.Open();
deletAnswerCommand.Prepare();
deletAnswerCommand.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
Singleton.errorCode = "ANS06";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(true);
}
/*
* This function will return a list of scenarios to grade.
*/
public static ScenariosToGradeList getScenariosToGrade(SqlConnection connection)
{
var scenariosToGradeList = new ScenariosToGradeList();
scenariosToGradeList.scenariosToGrade = new List <ScenariosToGrade>();
try
{
var questionCommand = new SqlCommand(null, connection);
questionCommand.CommandText = "SELECT SCENARIOS_TO_GRADE.STG_ID, " +
"SCENARIOS.SCENARIO_ID, " +
"SCENARIOS.SCENARIO_NAME, " +
"STUDENTS.STUDENT_ID, " +
"STUDENTS.STUDENT_NAME " +
"FROM SCENARIOS_TO_GRADE " +
"JOIN STUDENTS ON SCENARIOS_TO_GRADE.STG_STUDENT_ID = STUDENTS.STUDENT_ID " +
"JOIN SCENARIOS ON SCENARIOS_TO_GRADE.STG_SCENARIO_ID = SCENARIOS.SCENARIO_ID " +
"WHERE SCENARIOS_TO_GRADE.STG_GRADE IS NULL;";
connection.Close(); connection.Open();
questionCommand.Prepare();
using (var reader = questionCommand.ExecuteReader())
{
while (reader.Read())
{
var scenariosToGrade = new ScenariosToGrade();
scenariosToGrade.stgID = Convert.ToInt32($"{reader["STG_ID"]}");
scenariosToGrade.stgScenarioID = Convert.ToInt32($"{reader["SCENARIO_ID"]}");
scenariosToGrade.stgScenarioName = $"{reader["SCENARIO_NAME"]}";
scenariosToGrade.stgStudentID = $"{reader["STUDENT_ID"]}";
scenariosToGrade.stgStudentName = $"{reader["STUDENT_NAME"]}";
scenariosToGradeList.scenariosToGrade.Add(scenariosToGrade);
}
}
}
catch (Exception ex)
{
Singleton.errorCode = "SEN11";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(scenariosToGradeList);
}
/// <summary>
/// Edit a question reference
/// </summary>
/// <param name="answerID">answer where you want to update the referece</param>
/// <param name="newReference">the edited reference</param>
/// <param name="connection">database connection</param>
internal static void editAnswerReference(int answerID, QuestionReference newReference, SqlConnection connection)
{
try
{
SqlCommand updateQuestionReferenceCommand = new SqlCommand(null, connection)
{
CommandText = "UPDATE ANSWER_QUESTIONS SET QUESTION_REFERENCE_ID = @newReferenceID, " +
"PROBABILITY = @probability WHERE ANSWER_ID = @answerID " +
"AND QUESTION_REFERENCE_ID = @prevReferenceID;"
};
var paramAnswerID = new SqlParameter("@answerID", SqlDbType.Int)
{
Value = answerID
};
var paramPrevReferenceID = new SqlParameter("@prevReferenceID", SqlDbType.Int)
{
Value = newReference.prevQuestionReference
};
var paramNewReferenceID = new SqlParameter("@newReferenceID", SqlDbType.Int)
{
Value = newReference.questionReference
};
var paramProbability = new SqlParameter("@probability", SqlDbType.Int)
{
Value = newReference.questionReferenceProbability
};
updateQuestionReferenceCommand.Parameters.Add(paramAnswerID);
updateQuestionReferenceCommand.Parameters.Add(paramPrevReferenceID);
updateQuestionReferenceCommand.Parameters.Add(paramNewReferenceID);
updateQuestionReferenceCommand.Parameters.Add(paramProbability);
connection.Close(); connection.Open();
updateQuestionReferenceCommand.Prepare();
updateQuestionReferenceCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "ANS08";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* This function returns the number of times that a scenario is present in the grades table.
* It is used when an admin or teacher attempts to delete a scenario to let them know how
* many references there are to the scenario.
*/
public static int getScenarioUsage(int scenarioID, SqlConnection connection)
{
int count;
try
{
SqlCommand command = new SqlCommand(null, connection)
{
CommandText = "SELECT COUNT(*) FROM SCENARIOS_TO_GRADE WHERE STG_SCENARIO_ID = @scenarioID"
};
command.Parameters.Add(new SqlParameter("@scenarioID", SqlDbType.Int)
{
Value = scenarioID
});
connection.Close(); connection.Open();
command.Prepare();
count = (int)command.ExecuteScalar();
SqlCommand command2 = new SqlCommand(null, connection)
{
CommandText = "SELECT COUNT(*) FROM QUESTIONS WHERE QUESTION_SCENARIO = @scenarioID"
};
command2.Parameters.Add(new SqlParameter("@scenarioID", SqlDbType.Int)
{
Value = scenarioID
});
count += (int)command2.ExecuteScalar();
connection.Close();
}
catch (Exception ex)
{
Singleton.errorCode = "SEN05";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(count);
}
/// <summary>
/// adds an answer to the database when someone adds an answer to a question when editing a scenario
/// </summary>
/// <param name="answer">the answer that they have modified in the UI</param>
/// <param name="connection"></param>
/// <returns></returns>
public static int addAnswer(Answer answer, SqlConnection connection)
{
try
{
var addAnswerCommand = new SqlCommand(null, connection)
{
CommandText = "INSERT INTO ANSWERS (ANSWER_FOR_QUESTION, ANSWER_TEXT, ANSWER_IS_ACTIVE, ANSWER_REQUIRES_COMMENT) " +
"VALUES (@answerForQuestion, @answerText, 1, @requiresComment); SELECT SCOPE_IDENTITY();"
};
var param = new SqlParameter("@answerForQuestion", SqlDbType.Int)
{
Value = answer.answerForQuestion
};
addAnswerCommand.Parameters.Add(param);
var param2 = new SqlParameter("@answerText", SqlDbType.VarChar, 500)
{
Value = answer.answerText
};
addAnswerCommand.Parameters.Add(param2);
var param3 = new SqlParameter("@requiresComment", SqlDbType.Bit)
{
Value = answer.requiresComment
};
addAnswerCommand.Parameters.Add(param3);
connection.Close(); connection.Open();
addAnswerCommand.Prepare();
answer.answerID = Convert.ToInt32(addAnswerCommand.ExecuteScalar());
}
catch (Exception ex)
{
Singleton.errorCode = "ANS01";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(answer.answerID);
}
/*
* After a teacher/admin submits a grade for a student, set that entry in the table to whatever grade the
* scenario received and insert any comments the teacher had.
*/
public static void addGradeForScenario(int stgId, int grade, string gradeComments, SqlConnection connection)
{
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "UPDATE SCENARIOS_TO_GRADE " +
"SET STG_GRADE = @grade, STG_COMMENTS=@gradeComments" +
" WHERE STG_ID = @stgID"
};
var param = new SqlParameter("@stgId", SqlDbType.Int)
{
Value = stgId
};
command.Parameters.Add(param);
var param2 = new SqlParameter("@grade", SqlDbType.Int)
{
Value = grade
};
command.Parameters.Add(param2);
var param3 = new SqlParameter("@gradeComments", SqlDbType.VarChar, 500)
{
Value = gradeComments
};
command.Parameters.Add(param3);
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "SEN13";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// add reference to a question from an answer (an answer points to a question or multiple questions)
/// </summary>
/// <param name="answerID">answer you want to attach the reference to</param>
/// <param name="questionReference">reference you want to add to the answer</param>
/// <param name="connection">database connection</param>
public static void addAnswerReference(int answerID, QuestionReference questionReference, SqlConnection connection)
{
try
{
SqlCommand addQuestionReferenceCommand = new SqlCommand(null, connection)
{
CommandText = "INSERT INTO ANSWER_QUESTIONS (ANSWER_ID, QUESTION_REFERENCE_ID, PROBABILITY) " +
"VALUES (@answerID, @questionID, @probability);"
};
var paramID = new SqlParameter("@answerID", SqlDbType.Int)
{
Value = answerID
};
var paramQuestion = new SqlParameter("@questionID", SqlDbType.Int)
{
Value = questionReference.questionReference
};
var paramProbability = new SqlParameter("@probability", SqlDbType.Int)
{
Value = questionReference.questionReferenceProbability
};
addQuestionReferenceCommand.Parameters.Add(paramID);
addQuestionReferenceCommand.Parameters.Add(paramQuestion);
addQuestionReferenceCommand.Parameters.Add(paramProbability);
connection.Close(); connection.Open();
addQuestionReferenceCommand.Prepare();
addQuestionReferenceCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "ANS09";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// deleting answer references deletes the questions that an answer points to
/// </summary>
/// <param name="answerID">the answers whose references you want to delete</param>
/// <param name="questionReferenceID">the question reference you want to remove from an answer</param>
/// <param name="connection">database connection</param>
public static void deleteAnswerReference(int answerID, int questionReferenceID, SqlConnection connection)
{
try
{
var deleteQuestionReferencesCommand = new SqlCommand(null, connection)
{
CommandText = "DELETE FROM ANSWER_QUESTIONS WHERE ANSWER_ID = @answerID " +
"AND QUESTION_REFERENCE_ID = @questionID "
};
var paramAnswerID = new SqlParameter("@answerID", SqlDbType.Int)
{
Value = answerID
};
var paramQuestionID = new SqlParameter("@questionID", SqlDbType.Int)
{
Value = questionReferenceID
};
deleteQuestionReferencesCommand.Parameters.Add(paramAnswerID);
deleteQuestionReferencesCommand.Parameters.Add(paramQuestionID);
connection.Close(); connection.Open();
deleteQuestionReferencesCommand.Prepare();
deleteQuestionReferencesCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "ANS07";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* This function takes a Question as an input. This will set this question as the first question
* for the scenario it is contained in.
*/
public static void changeFirstQuestion(Question question, SqlConnection connection)
{
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "UPDATE SCENARIOS " +
"SET SCENARIO_FIRST_QUESTION = @questionId " +
"WHERE SCENARIO_ID = @questionScenario"
};
var param = new SqlParameter("@questionId", SqlDbType.Int)
{
Value = question.questionId
};
command.Parameters.Add(param);
var param2 = new SqlParameter("@questionScenario", SqlDbType.Int)
{
Value = question.questionScenario
};
command.Parameters.Add(param2);
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "QUE01";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* This function takes in a userID and roleID and gives the user that role.
*/
public static void changeUserRole(String roleId, String userId, SqlConnection connection)
{
try
{
var changeUserRoleCommand = new SqlCommand(null, connection)
{
CommandText = "UPDATE AspNetUserRoles " +
"SET RoleId = @roleId " +
"WHERE UserId = @userId;"
};
var parameterRole = new SqlParameter("@roleId", SqlDbType.NVarChar, 128)
{
Value = roleId
};
changeUserRoleCommand.Parameters.Add(parameterRole);
var parameterUserID = new SqlParameter("@userId", SqlDbType.VarChar, 40)
{
Value = userId
};
changeUserRoleCommand.Parameters.Add(parameterUserID);
connection.Close(); connection.Open();
changeUserRoleCommand.Prepare();
changeUserRoleCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "USR03";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// get the count of how many times an answer is used within a scenario. This is used to know if we can delete/make active/make inactive
/// we can't make an answer active if the question it is in is inactive
/// </summary>
/// <param name="answerID">the selected answer object in the UI to modify the answer based off the ID in the database </param>
/// <param name="connection">connection to the database</param>
/// <returns></returns>
public static int getAnswerUsage(int answerID, SqlConnection connection)
{
int count;
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "SELECT COUNT(*) FROM STUDENT_ANSWERS WHERE SA_ANSWER_ID = @answerID"
};
var param = new SqlParameter("@answerID", SqlDbType.Int)
{
Value = answerID
};
command.Parameters.Add(param);
connection.Close(); connection.Open();
command.Prepare();
count = (Int32)command.ExecuteScalar();
connection.Close();
}
catch (Exception ex)
{
Singleton.errorCode = "ANS03";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(count);
}
/*
* This will return a list of scenarios created by the student who's ID is stored in the Singleton
*/
public static Scenarios getStudentScenariosToEdit(SqlConnection connection)
{
var scenarios = new Scenarios {
ScenarioList = new List <Scenario>()
};
try
{
var scenariosToEditCommand = new SqlCommand(null, connection)
{
CommandText = "SELECT * " +
"FROM SCENARIOS " +
"WHERE SCENARIO_ID != 0 AND SCENARIO_APPROVED = 0 AND SCENARIO_MADE_BY = @id"
};
var studentIDParam = new SqlParameter("@id", SqlDbType.NVarChar, 128)
{
Value = (string)HttpContext.Current.Session["userId"]
};
scenariosToEditCommand.Parameters.Add(studentIDParam);
connection.Close(); connection.Open();
scenariosToEditCommand.Prepare();
using (var reader = scenariosToEditCommand.ExecuteReader())
{
while (reader.Read())
{
var scenario = new Scenario();
scenario.scenarioName = $"{reader["SCENARIO_NAME"]}";
scenario.scenarioID = Convert.ToInt32($"{reader["SCENARIO_ID"]}");
scenario.description = $"{reader["SCENARIO_DESCRIPTION"]}";
scenario.isActive = Convert.ToBoolean($"{reader["SCENARIO_IS_ACTIVE"]}".ToLower());
scenario.cellFunction = $"{reader["SCENARIO_CELL_FUNCTION"]}";
scenario.cellShapeAndFeatures = $"{reader["SCENARIO_CELL_SHAPE_AND_FEATURES"]}";
scenario.cellLifespan = $"{reader["SCENARIO_CELL_LIFESPAN"]}";
scenario.cellNutrition = $"{reader["SCENARIO_CELL_NUTRITION"]}";
if ($"{reader["SCENARIO_FIRST_QUESTION"]}".Equals(""))
{
scenario.currentQuestion = 0;
}
else
{
scenario.currentQuestion = Convert.ToInt32($"{reader["SCENARIO_FIRST_QUESTION"]}");
}
scenarios.ScenarioList.Add(scenario);
}
}
}
catch (Exception ex)
{
Singleton.errorCode = "USR05";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(scenarios);
}
/*
* This function takes in a questionID and deletes that question from the database.
*/
public static bool deleteQuestion(int questionId, SqlConnection connection)
{
((EditScenarioModel)HttpContext.Current.Session["editScenario"]).warningMsg = "";
bool returnValue = true;
try
{
var checkQuestionReferenceCommand = new SqlCommand(null, connection)
{
CommandText = "SELECT COUNT(*) FROM STUDENT_ANSWERS WHERE SA_QUESTION_ID = @questionId"
};
var questionIDParam = new SqlParameter("@questionId", SqlDbType.Int)
{
Value = questionId
};
checkQuestionReferenceCommand.Parameters.Add(questionIDParam);
connection.Close(); connection.Open();
checkQuestionReferenceCommand.Prepare();
if ((Int32)checkQuestionReferenceCommand.ExecuteScalar() > 0)
{
Singleton.warningMsg += "\nThis question is being used in scenarios that are being graded. ";
returnValue = false;
}
checkQuestionReferenceCommand.CommandText =
"SELECT COUNT(*) FROM ANSWER_QUESTIONS WHERE QUESTION_REFERENCE_ID = @questionId";
connection.Close(); connection.Open();
checkQuestionReferenceCommand.Prepare();
if ((Int32)checkQuestionReferenceCommand.ExecuteScalar() > 0)
{
Singleton.warningMsg += "\nThis question is referenced by one or more answers. ";
returnValue = false;
}
checkQuestionReferenceCommand.CommandText =
"SELECT COUNT(*) FROM ANSWERS WHERE ANSWER_FOR_QUESTION = @questionId";
connection.Close(); connection.Open();
checkQuestionReferenceCommand.Prepare();
if ((Int32)checkQuestionReferenceCommand.ExecuteScalar() > 0)
{
Singleton.warningMsg += "\nA question cannot be deleted when it contains answers. ";
returnValue = false;
}
checkQuestionReferenceCommand.Parameters.Clear();
if (!returnValue)
{
Singleton.warningMsg += "\nThis question has been deactivated. ";
Singleton.isWarning = true;
return(returnValue);
}
var deleteQuestionCommand = new SqlCommand(null, connection)
{
CommandText = "DELETE FROM QUESTIONS WHERE QUESTION_ID = @questionId"
};
deleteQuestionCommand.Parameters.Add(questionIDParam);
connection.Close(); connection.Open();
deleteQuestionCommand.Prepare();
deleteQuestionCommand.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
Singleton.errorCode = "QUE07";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
return(returnValue);
}
/*
* This function will set the list of students in the Singleton.
*/
public static void getStudents(SqlConnection connection)
{
try
{
var userRolesCommand = new SqlCommand(null, connection)
{
CommandText = "SELECT AspNetUserRoles.UserId, STUDENTS.STUDENT_NAME, AspNetRoles.Name " +
"FROM AspNetUserRoles " +
"JOIN STUDENTS ON AspNetUserRoles.UserId = STUDENTS.STUDENT_ID " +
"JOIN AspNetRoles ON AspNetUserRoles.RoleId = AspNetRoles.Id;"
};
connection.Close(); connection.Open();
userRolesCommand.Prepare();
var studentsList = new StudentsList();
studentsList.studentsList = new List <Student>();
using (var reader = userRolesCommand.ExecuteReader())
{
while (reader.Read())
{
Student student = new Student();
student.id = $"{reader["UserId"]}";
student.name = $"{reader["STUDENT_NAME"]}";
student.role = $"{reader["Name"]}";
studentsList.studentsList.Add(student);
}
}
var getRolesCommand = new SqlCommand(null, connection)
{
CommandText = "SELECT * " +
"FROM AspNetRoles;"
};
getRolesCommand.Prepare();
List <String> roleId = new List <String>();
List <String> roleName = new List <String>();
using (var reader = getRolesCommand.ExecuteReader())
{
while (reader.Read())
{
roleId.Add($"{reader["Id"]}");
roleName.Add($"{reader["Name"]}");
}
}
studentsList.roleKeys = roleId;
studentsList.roleNames = roleName;
HttpContext.Current.Session["studentList"] = studentsList;
}
catch (Exception ex)
{
Singleton.errorCode = "STU03";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* This function takes in all the info to identify a student, scenario, which grade it belongs to,
* the
* , answer, and any comments the student had. These are inserted into the STUDENT_ANSWERS table
* so teachers and admins can see them and enter a grade.
*/
public static void insertStudentAnswer(string studentId, int scenarioId, int scenarioToGrade, int scenarioQuestion,
int scenarioAnswer, string studentComment, SqlConnection connection)
{
try
{
var command = new SqlCommand(null, connection)
{
CommandText = "INSERT INTO STUDENT_ANSWERS " +
"(" +
"SA_STUDENT_ID, " +
"SA_SCENARIO_ID, " +
"SA_SCENARIO_TO_GRADE, " +
"SA_QUESTION_ID, " +
"SA_ANSWER_ID, " +
"SA_STUDENT_ANSWER_TEXT" +
") " +
"VALUES " +
"(" +
"@studentID, " +
"@scenarioID, " +
"@scenarioToGrade, " +
"@scenarioQuestion, " +
"@scenarioAnswer, " +
"@studentComment" +
")"
};
var param = new SqlParameter("@studentID", SqlDbType.NVarChar, 128)
{
Value = studentId
};
command.Parameters.Add(param);
var param2 = new SqlParameter("@scenarioID", SqlDbType.Int)
{
Value = scenarioId
};
command.Parameters.Add(param2);
var param3 = new SqlParameter("@scenarioToGrade", SqlDbType.Int)
{
Value = scenarioToGrade
};
command.Parameters.Add(param3);
var param4 = new SqlParameter("@scenarioQuestion", SqlDbType.Int)
{
Value = scenarioQuestion
};
command.Parameters.Add(param4);
var param5 = new SqlParameter("@scenarioAnswer", SqlDbType.Int)
{
Value = scenarioAnswer
};
command.Parameters.Add(param5);
var param6 = new SqlParameter("@studentComment", SqlDbType.VarChar, 500)
{
Value = studentComment
};
command.Parameters.Add(param6);
connection.Close(); connection.Open();
command.Prepare();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "STU01";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* When a user registers, split the email into strings for their first name and last name. Concatenate
* these and store them in the users table as the UserName.
*/
public static void setNewUserName(string email, SqlConnection connection)
{
try
{
connection.Close(); connection.Open();
string namePortion = email.Split(new[] { '@' })[0];
string[] nameSplit = namePortion.Split(new[] { '.' });
string firstName = nameSplit[0];
string lastName = "";
if (nameSplit.Length == 2)
{
lastName = nameSplit[1];
}
string fullName = firstName + " " + lastName;
fullName = fullName.Trim();
HttpContext.Current.Session["userName"] = fullName;
//we need to ensure that we're not writing in null values to the database.
var commandAddName = new SqlCommand(null, connection)
{
CommandText = "INSERT INTO STUDENTS " +
"(STUDENT_ID, STUDENT_NAME) " +
"VALUES (@studentId, @studentName);"
};
if (String.IsNullOrEmpty((string)HttpContext.Current.Session["userId"]))
{
//This seems to be causing issues with some foreign keys in the database.
//Figure out why it's attempting to write a NULL.
var studentId = new SqlParameter("@studentId", SqlDbType.NVarChar, 128)
{
Value = " ***ERROR*** "
};
commandAddName.Parameters.Add(studentId);
}
else
{
var studentId = new SqlParameter("@studentId", SqlDbType.NVarChar, 128)
{
Value = (string)HttpContext.Current.Session["userId"]
};
commandAddName.Parameters.Add(studentId);
}
if (String.IsNullOrEmpty((string)HttpContext.Current.Session["userName"]))
{
var studentName = new SqlParameter("@studentName", SqlDbType.VarChar, 40)
{
Value = " ***ERROR*** "
};
commandAddName.Parameters.Add(studentName);
}
else
{
var studentName = new SqlParameter("@studentName", SqlDbType.VarChar, 40)
{
Value = fullName
};
commandAddName.Parameters.Add(studentName);
}
commandAddName.Prepare();
commandAddName.ExecuteNonQuery();
}
catch (Exception ex)
{
Singleton.errorCode = "USR02";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
/*
* This function is called when a user logs in. It sets the username and ID in the Singleton.
*/
public static void setUserNameId(string email, SqlConnection connection)
{
try
{
string userIdString;
string userNameString;
var setUserNameCommand = new SqlCommand(null, connection)
{
CommandText = "SELECT AspNetUsers.Id, STUDENTS.STUDENT_NAME " +
"FROM AspNetUsers JOIN STUDENTS ON AspNetUsers.Id = STUDENTS.STUDENT_ID " +
"WHERE Email = @email"
};
var paramEmail = new SqlParameter("@email", SqlDbType.VarChar, 40)
{
Value = email
};
setUserNameCommand.Parameters.Add(paramEmail);
connection.Close(); connection.Open();
setUserNameCommand.Prepare();
bool found = false;
using (var reader = setUserNameCommand.ExecuteReader())
{
if (reader.Read())
{
found = true;
userIdString = $"{reader["Id"]}";
userNameString = $"{reader["STUDENT_NAME"]}";
HttpContext.Current.Session["userId"] = userIdString;
HttpContext.Current.Session["userName"] = userNameString;
}
}
if (!found)
{
var command1 = new SqlCommand(null, connection)
{
CommandText = "SELECT AspNetUsers.Id " +
"FROM AspNetUsers " +
"WHERE Email = @email"
};
var param2 = new SqlParameter("@email", SqlDbType.VarChar, 40)
{
Value = email
};
command1.Parameters.Add(param2);
command1.Prepare();
using (var reader = command1.ExecuteReader())
{
if (reader.Read())
{
HttpContext.Current.Session["userId"] = $"{reader["Id"]}";
}
}
setNewUserName(email, connection);
}
}
catch (Exception ex)
{
Singleton.errorCode = "USR01";
Singleton.writeErrorToFile(Singleton.errorCode, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
connection.Close();
}
}
