public bool DeleteClass(int studyClassID, SqlConnection conn = null)
{
bool succes = true;
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_deleteClass", conn))
{
cmd.Parameters.AddWithValue("@CLASS_ID", studyClassID);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
cmd.ExecuteNonQuery();
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("DeleteClass() error. ClassId: " + studyClassID, e);
}
return(succes);
}
public void AddOrUpdateQuestionAnswer(int questionID, int answerID, bool correct, SqlConnection conn = null)
{
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_insertQuestionAnswers", conn))
{
cmd.Parameters.AddWithValue("@QUESTION_ID", questionID);
cmd.Parameters.AddWithValue("@ANSWER_ID", answerID);
cmd.Parameters.AddWithValue("@CORRECT", correct);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
cmd.ExecuteNonQuery();
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("AddOrUpdateQuestionAnswer() error. QuestionId: " + questionID, e);
}
}
public void DeleteTeacherLecturesForTeacher(int teacherID, SqlConnection conn = null)
{
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_deleteTeacherLecturesForTeacher", conn))
{
cmd.Parameters.AddWithValue("@TEACHER_ID", teacherID);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
cmd.ExecuteNonQuery();
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("DeleteTeacherLecturesForTeacher() error. TeacherId: " + teacherID, e);
}
}
public List <TestResults> GetTestsResults(SqlConnection conn = null)
{
List <TestResults> testsResults = new List <TestResults>();
try
{
bool nullConnection = false;
TestResults testResults = null;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getTestResults", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
testResults = new TestResults
{
TestID = DataUtil.GetDataReaderValue <int>("TestID", reader),
StudentID = DataUtil.GetDataReaderValue <int>("StudentID", reader),
AnswersResult = DataUtil.GetDataReaderValue <string>("AnswersResult", reader),
Mark = DataUtil.GetDataReaderValue <float>("Mark", reader),
Points = DataUtil.GetDataReaderValue <float>("Points", reader),
TestResultDate = DataUtil.GetDataReaderValue <DateTime>("TestResultDate", reader),
NrOfCorrectAnswers = DataUtil.GetDataReaderValue <int>("NrOfCorrectAnswers", reader),
NrOfWrongAnswers = DataUtil.GetDataReaderValue <int>("NrOfWrongAnswers", reader),
NrOfUnfilledAnswers = DataUtil.GetDataReaderValue <int>("NrOfUnfilledAnswers", reader)
};
testsResults.Add(testResults);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetTestsResults() error.", e);
}
return(testsResults);
}
public List <TestParameters> GetTestParameters(SqlConnection conn = null)
{
List <TestParameters> testsParams = new List <TestParameters>();
try
{
bool nullConnection = false;
TestParameters testParams = null;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getTestParameters", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
testParams = new TestParameters
{
TestID = DataUtil.GetDataReaderValue <int>("TestID", reader),
TeacherID = DataUtil.GetDataReaderValue <int>("TeacherID", reader),
ClassID = DataUtil.GetDataReaderValue <int>("ClassID", reader),
Duration = DataUtil.GetDataReaderValue <int>("Duration", reader),
Penalty = DataUtil.GetDataReaderValue <float>("Penalty", reader),
StartTest = DataUtil.GetDataReaderValue <DateTime>("StartTest", reader),
FinishTest = DataUtil.GetDataReaderValue <DateTime>("FinishTest", reader)
};
testsParams.Add(testParams);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetTestParameters() error. ", e);
}
return(testsParams);
}
public List <Student> GetStudents(SqlConnection conn = null)
{
List <Student> students = new List <Student>();
try
{
bool nullConnection = false;
Student student = null;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getStudents", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
student = new Student
{
StudentID = DataUtil.GetDataReaderValue <int>("StudentID", reader),
FirstName = DataUtil.GetDataReaderValue <string>("FirstName", reader),
LastName = DataUtil.GetDataReaderValue <string>("LastName", reader),
Email = DataUtil.GetDataReaderValue <string>("Email", reader),
ClassID = DataUtil.GetDataReaderValue <int>("ClassID", reader),
UserID = DataUtil.GetDataReaderValue <int>("UserID", reader)
};
students.Add(student);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetStudents() error.", e);
}
return(students);
}
public User GetUserByUsername(string username, SqlConnection conn = null)
{
User user = null;
try
{
bool nullConnection = false;
List <User> studyClasses = new List <User>();
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getUserByEmail", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@USERNAME", username);
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
user = new User
{
UserID = DataUtil.GetDataReaderValue <int>("UserID", reader),
Username = DataUtil.GetDataReaderValue <string>("Username", reader),
Password = DataUtil.GetDataReaderValue <string>("Password", reader),
IsActive = DataUtil.GetDataReaderValue <bool>("IsActive", reader),
Role = DataUtil.GetDataReaderValue <string>("Role", reader)
};
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetUserByUsername() error. Username: " + user.Username, e);
}
return(user);
}
public List <Question> GetQuestionsByTestID(int testID, SqlConnection conn = null)
{
List <Question> questions = new List <Question>();
try
{
bool nullConnection = false;
Question question = null;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getQuestionsByTestID", conn))
{
cmd.Parameters.AddWithValue("@TEST_ID", testID);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
question = new Question
{
QuestionID = DataUtil.GetDataReaderValue <int>("QuestionID", reader),
Content = DataUtil.GetDataReaderValue <string>("Question", reader),
Points = DataUtil.GetDataReaderValue <int>("Points", reader),
TestID = DataUtil.GetDataReaderValue <int>("TestID", reader)
};
questions.Add(question);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetQuestionsByTestID() error. TestId: " + testID, e);
}
return(questions);
}
public IEnumerable <Test> GetTests(SqlConnection conn = null)
{
List <Test> tests = new List <Test>();
try
{
bool nullConnection = false;
Test test = null;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getTests", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
test = new Test
{
TestID = DataUtil.GetDataReaderValue <int>("TestID", reader),
Naming = DataUtil.GetDataReaderValue <string>("Name", reader),
TeacherID = DataUtil.GetDataReaderValue <int>("TeacherID", reader),
LectureID = DataUtil.GetDataReaderValue <int>("LectureID", reader)
};
tests.Add(test);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetTests() error.", e);
}
return(tests);
}
public List <Lecture> GetLectures(SqlConnection conn = null)
{
List <Lecture> lectures = new List <Lecture>();
try
{
bool nullConnection = false;
Lecture lecture = null;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getLectures", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
lecture = new Lecture
{
LectureID = DataUtil.GetDataReaderValue <int>("LectureID", reader),
Name = DataUtil.GetDataReaderValue <string>("Name", reader),
YearOfStudy = DataUtil.GetDataReaderValue <int>("YearOfStudy", reader)
};
lectures.Add(lecture);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetLectures() error.", e);
}
return(lectures);
}
public Teacher GetTeacherUserAuth(string email, SqlConnection conn = null)
{
Teacher teacher = null;
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getTeacherUserByEmail", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EMAIL", email);
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
teacher = new Teacher()
{
TeacherID = DataUtil.GetDataReaderValue <int>("TeacherID", reader),
FirstName = DataUtil.GetDataReaderValue <string>("FirstName", reader),
LastName = DataUtil.GetDataReaderValue <string>("LastName", reader),
Email = DataUtil.GetDataReaderValue <string>("Email", reader),
UserID = DataUtil.GetDataReaderValue <int>("UserID", reader)
};
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetTeacherUserAuth() error. Teacher: " + teacher.FirstName + " " + teacher.LastName, e);
}
return(teacher);
}
public List <QuestionWithAnswers> GetQuestionAnswers(SqlConnection conn = null)
{
List <QuestionWithAnswers> questionsWithAnswers = new List <QuestionWithAnswers>();
try
{
bool nullConnection = false;
QuestionWithAnswers questionAnswer = null;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getQuestionAnswers", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
questionAnswer = new QuestionWithAnswers
{
QuestionID = DataUtil.GetDataReaderValue <int>("QuestionID", reader),
AnswerID = DataUtil.GetDataReaderValue <int>("AnswerID", reader),
Correct = DataUtil.GetDataReaderValue <bool>("Correct", reader)
};
questionsWithAnswers.Add(questionAnswer);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetQuestionAnswers() error.", e);
}
return(questionsWithAnswers);
}
public List <Answer> GetAnswers(SqlConnection conn = null)
{
List <Answer> answers = new List <Answer>();
try
{
bool nullConnection = false;
Answer answer = null;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getAnswers", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
answer = new Answer
{
AnswerID = DataUtil.GetDataReaderValue <int>("AnswerID", reader),
Content = DataUtil.GetDataReaderValue <string>("Answer", reader)
};
answers.Add(answer);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetAnswers() error.", e);
}
return(answers);
}
public int AddOrUpdateStudent(Student student, SqlConnection conn = null, int studentID = -1)
{
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_insertOrUpdateStudent", conn))
{
cmd.Parameters.AddWithValue("@FIRSTNAME", student.FirstName);
cmd.Parameters.AddWithValue("@LASTNAME", student.LastName);
cmd.Parameters.AddWithValue("@EMAIL", student.Email);
cmd.Parameters.AddWithValue("@CLASS_ID", student.ClassID);
cmd.Parameters.AddWithValue("@STUDENT_ID", studentID);
cmd.Parameters.AddWithValue("@USER_ID", student.UserID);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
studentID = DataUtil.GetDataReaderValue <int>("StudentID", reader);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("AddOrUpdateStudent() error. Student: " + student.FirstName + " " + student.LastName, e);
}
return(studentID);
}
public int AddTeacher(Teacher teacher, SqlConnection conn = null, int teacherId = -1)
{
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_insertOrUpdateTeacher", conn))
{
cmd.Parameters.AddWithValue("@FIRSTNAME", teacher.FirstName);
cmd.Parameters.AddWithValue("@LASTNAME", teacher.LastName);
cmd.Parameters.AddWithValue("@EMAIL", teacher.Email);
cmd.Parameters.AddWithValue("@TEACHER_ID", teacherId);
cmd.Parameters.AddWithValue("@USER_ID", teacher.UserID);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
teacherId = DataUtil.GetDataReaderValue <int>("TeacherID", reader);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("AddTeacher() error. Teacher: " + teacher.FirstName + " " + teacher.LastName, e);
}
return(teacherId);
}
public int AddTest(Test test, SqlConnection conn = null)
{
int testID = -1;
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_insertTest", conn))
{
cmd.Parameters.AddWithValue("@NAME", test.Naming);
cmd.Parameters.AddWithValue("@TEACHER_ID", test.TeacherID);
cmd.Parameters.AddWithValue("@LECTURE_ID", test.LectureID);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
testID = DataUtil.GetDataReaderValue <int>("TestID", reader);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("AddTest() error. Test: " + test.Naming, e);
}
return(testID);
}
public int UpdateQuestion(Question question, SqlConnection conn = null)
{
int questionID = -1;
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_updateQuestion", conn))
{
cmd.Parameters.AddWithValue("@QUESTION", question.Content);
cmd.Parameters.AddWithValue("@POINTS", question.Points);
cmd.Parameters.AddWithValue("@TEST_ID", question.TestID);
cmd.Parameters.AddWithValue("@QUESTION_ID", question.QuestionID);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
questionID = DataUtil.GetDataReaderValue <int>("QuestionID", reader);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("UpdateQuestion() error. QuestionId: " + question.QuestionID, e);
}
return(questionID);
}
public bool AddTestResults(TestResults testResults, SqlConnection conn = null)
{
bool succes = true;
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_insertTestResults", conn))
{
cmd.Parameters.AddWithValue("@STUDENT_ID", testResults.StudentID);
cmd.Parameters.AddWithValue("@TEST_ID", testResults.TestID);
cmd.Parameters.AddWithValue("@MARK", testResults.Mark);
cmd.Parameters.AddWithValue("@POINTS", testResults.Points);
cmd.Parameters.AddWithValue("@ANSWERS_RESULT", testResults.AnswersResult);
cmd.Parameters.AddWithValue("@TEST_RESULT_DATE", testResults.TestResultDate);
cmd.Parameters.AddWithValue("@NR_OF_CORRECT_ANSWERS", testResults.NrOfCorrectAnswers);
cmd.Parameters.AddWithValue("@NR_OF_WRONG_ANSWERS", testResults.NrOfWrongAnswers);
cmd.Parameters.AddWithValue("@NR_OF_UNFILLED_ANSWERS", testResults.NrOfUnfilledAnswers);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
cmd.ExecuteNonQuery();
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("AddTestResults() error. TestId: " + testResults.TestID, e);
}
return(succes);
}
public int AddOrUpdateUser(User user, SqlConnection conn = null, int userID = -1)
{
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_insertOrUpdateUser", conn))
{
cmd.Parameters.AddWithValue("@USERNAME", user.Username);
cmd.Parameters.AddWithValue("@PASSWORD", user.Password);
cmd.Parameters.AddWithValue("@ROLE", user.Role);
cmd.Parameters.AddWithValue("@USER_ID", userID);
cmd.Parameters.AddWithValue("@IS_ACTIVE", user.IsActive);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
userID = DataUtil.GetDataReaderValue <int>("UserID", reader);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("AddOrUpdateUser() error. UserName: " + user.Username, e);
}
return(userID);
}
public int AddAnswer(Answer answer, SqlConnection conn = null)
{
int answerID = -1;
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_insertAnswer", conn))
{
cmd.Parameters.AddWithValue("@ANSWER", answer.Content);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
answerID = DataUtil.GetDataReaderValue <int>("AnswerID", reader);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("AddAnswer() error. Answer: " + answer.AnswerID, e);
}
return(answerID);
}
public int AddOrUpdate(Lecture lecture, SqlConnection conn = null, int lectureID = -1)
{
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_insertOrUpdateLecture", conn))
{
cmd.Parameters.AddWithValue("@NAME", lecture.Name);
cmd.Parameters.AddWithValue("@YEAR_OF_STUDY", lecture.YearOfStudy);
cmd.Parameters.AddWithValue("@LECTURE_ID", lectureID);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
lectureID = DataUtil.GetDataReaderValue <int>("LectureID", reader);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("AddOrUpdate() error. Lecture: " + lecture.Name, e);
}
return(lectureID);
}
public int AddOrUpdateClass(StudyClass studyClass, SqlConnection conn = null, int classID = -1)
{
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_insertOrUpdateClass", conn))
{
cmd.Parameters.AddWithValue("@NAME", studyClass.Name);
cmd.Parameters.AddWithValue("@CLASS_ID", classID);
cmd.Parameters.AddWithValue("@IS_ACTIVE", studyClass.IsValid);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
classID = DataUtil.GetDataReaderValue <int>("ClassID", reader);
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("AddOrUpdateClass() error. Class: " + studyClass.Name, e);
}
return(classID);
}
public bool AddTestParameters(TestParameters testParam, SqlConnection conn = null)
{
bool succes = true;
try
{
bool nullConnection = false;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_insertTestParams", conn))
{
cmd.Parameters.AddWithValue("@TEST_ID", testParam.TestID);
cmd.Parameters.AddWithValue("@TEACHER_ID", testParam.TeacherID);
cmd.Parameters.AddWithValue("@CLASSID", testParam.ClassID);
cmd.Parameters.AddWithValue("@DURATION", testParam.Duration);
cmd.Parameters.AddWithValue("@PENALTY", testParam.Penalty);
cmd.Parameters.AddWithValue("@START_TEST", testParam.StartTest);
cmd.Parameters.AddWithValue("@FINISH_TEST", testParam.FinishTest);
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
cmd.ExecuteNonQuery();
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("AddTestParameters() error. TestId: " + testParam.TestID, e);
}
return(succes);
}
public List <TeacherWithLecturesDto> GetTeachersWithLectures(SqlConnection conn = null)
{
List <TeacherWithLecturesDto> teachers = new List <TeacherWithLecturesDto>();
try
{
bool nullConnection = false;
TeacherWithLecturesDto teacher = null;
Lecture lecture = null;
int teacherIndex = -1;
UtilitiesClass.CreateConnection(ref nullConnection, ref conn, base.GetConnectionString());
using (var cmd = new SqlCommand("sp_getTeachersWithLectures", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (nullConnection)
{
conn.Open();
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
teacher = new TeacherWithLecturesDto
{
TeacherID = DataUtil.GetDataReaderValue <int>("TeacherID", reader),
FirstName = DataUtil.GetDataReaderValue <string>("FirstName", reader),
LastName = DataUtil.GetDataReaderValue <string>("LastName", reader),
Email = DataUtil.GetDataReaderValue <string>("Email", reader),
UserID = DataUtil.GetDataReaderValue <int>("UserID", reader),
Lectures = new List <Lecture>()
};
lecture = new Lecture
{
LectureID = DataUtil.GetDataReaderValue <int>("LectureID", reader),
Name = DataUtil.GetDataReaderValue <string>("Name", reader),
YearOfStudy = DataUtil.GetDataReaderValue <int>("YearOfStudy", reader)
};
teacherIndex = teachers.FindIndex(teacherObj => teacherObj.TeacherID == teacher.TeacherID);
if (teacherIndex != -1)
{
teachers[teacherIndex].Lectures.Add(lecture);
}
else
{
teacher.Lectures.Add(lecture);
teachers.Add(teacher);
}
}
}
if (conn.State == ConnectionState.Open && nullConnection)
{
conn.Close();
}
}
}
catch (Exception e)
{
_log.Error("GetTeachersWithLectures() error.", e);
}
return(teachers);
}