public void Update(Teacher teacher, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"UPDATE {TeachersContract.TABLE_NAME}" +
$"SET [{TeachersContract.COURSE_ID}] = @COURSE_ID, [{TeachersContract.RESOURCE_ID}] = @RES_ID," +
$"[{TeachersContract.NOTES}] = @NOTES " +
$"WHERE [{TeachersContract.ID}] = @ID";
command.AddParameter("ID", teacher.Id);
command.AddParameter("RES_ID", teacher.ResourceId);
command.AddParameter("COURSE_ID", teacher.CourseId);
command.AddParameter("NOTES", teacher.Notes ?? (object)DBNull.Value);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
DbLog.LogError($"Error updating teacher: {teacher.ToString()}", ex);
throw ex;
}
}
public void Create(Enrollment enroll, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"INSERT INTO {EnrollmentsContract.TABLE_NAME} (" +
$"[{EnrollmentsContract.RESOURCE_ID}], [{EnrollmentsContract.PROJECT_LEADER_ID}], " +
$"[{EnrollmentsContract.COURSE_ID}], [{EnrollmentsContract.START_DATE}], [{EnrollmentsContract.IS_ADMITTED}], [{EnrollmentsContract.NOTES}])" +
"VALUES(@RES_ID, @PROJLD_ID, @COURSE_ID, @START_DATE, @IS_ADMIT, @NOTES)";
command.AddParameter("RES_ID", enroll.ResourceId);
command.AddParameter("COURSE_ID", enroll.CourseId);
command.AddParameter("NOTES", enroll.Notes ?? (object)DBNull.Value);
command.AddParameter("PROJLD_ID", enroll.ProjectLeaderId);
command.AddParameter("START_DATE", enroll.StartDate ?? (object)DBNull.Value);
command.AddParameter("IS_ADMIT", enroll.IsAdmitted);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
DbLog.LogError($"Error inserting the new Enrollment: {enroll.ToString()}", ex);
throw ex;
}
}
public void Update(Resource resource, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"UPDATE {ResourcesContract.TABLE_NAME}" +
$"SET [{ResourcesContract.USERNAME}] = @USERNAME, [{ResourcesContract.SURNAME}] = @SURNAME," +
$"[{ResourcesContract.NAME}] = @NAME, [{ResourcesContract.STATUS}] = @STATUS, [{ResourcesContract.ID}] = @ID " +
$"WHERE [{ResourcesContract.ID}] = @ID";
command.AddParameter("ID", resource.Id);
command.AddParameter("USERNAME", resource.Username);
command.AddParameter("NAME", resource.Name);
command.AddParameter("SURNAME", resource.Surname);
command.AddParameter("STATUS", resource.Status);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
DbLog.LogError($"Error updating Resource: {resource.ToString()}", ex);
throw ex;
}
}
public void Update(Course course, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"UPDATE {CoursesContract.TABLE_NAME}" +
$"SET [{CoursesContract.DESCRIPTION}] = @DESCRIPTION, [{CoursesContract.REF_YEAR}] = @REF_YEAR," +
$"[{CoursesContract.START_DATE}] = @START_DATE, [{CoursesContract.END_DATE}] = @END_DATE, " +
$"[{CoursesContract.IS_PERIODIC}] = @IS_PERIODIC, [{CoursesContract.COORDINATOR_ID}] = @COORDINATOR_ID " +
$"WHERE [{CoursesContract.ID}] = @ID";
command.AddParameter("DESCRIPTION", course.Description ?? (object)DBNull.Value);
command.AddParameter("REF_YEAR", course.RefYear);
command.AddParameter("START_DATE", course.StartDate ?? (object)DBNull.Value);
command.AddParameter("END_DATE", course.EndDate ?? (object)DBNull.Value);
command.AddParameter("IS_PERIODIC", course.IsPeriodic);
command.AddParameter("COORDINATOR_ID", course.CoordinatorId);
command.AddParameter("ID", course.Id);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
DbLog.LogError($"Error updating course! {course.ToString()}", ex);
throw ex;
}
}
public void Create(Course course, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"INSERT INTO {CoursesContract.TABLE_NAME} ([{CoursesContract.DESCRIPTION}], [{CoursesContract.REF_YEAR}]," +
$"[{CoursesContract.START_DATE}],[{CoursesContract.END_DATE}]," +
$"[{CoursesContract.IS_PERIODIC}],[{CoursesContract.COORDINATOR_ID}])" +
$"VALUES(@DESCRIPTION, @REF_YEAR, @START_DATE, @END_DATE, @IS_PERIODIC, @COORDINATOR_ID)";
command.AddParameter("DESCRIPTION", course.Description ?? (object)DBNull.Value);
command.AddParameter("REF_YEAR", course.RefYear);
command.AddParameter("START_DATE", course.StartDate ?? (object)DBNull.Value);
command.AddParameter("END_DATE", course.EndDate ?? (object)DBNull.Value);
command.AddParameter("IS_PERIODIC", course.IsPeriodic);
command.AddParameter("COORDINATOR_ID", course.CoordinatorId);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
DbLog.LogError($"Error inserting the new course: {course.ToString()}", ex);
throw ex;
}
}
public void Update(Enrollment enroll, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"UPDATE {EnrollmentsContract.TABLE_NAME}" +
$"SET [{EnrollmentsContract.COURSE_ID}] = @COURSE_ID, [{EnrollmentsContract.RESOURCE_ID}] = @RES_ID," +
$"[{EnrollmentsContract.NOTES}] = @NOTES, [{EnrollmentsContract.START_DATE}] = @START_DATE," +
$"[{EnrollmentsContract.IS_ADMITTED}] = @IS_ADMIT, [{EnrollmentsContract.PROJECT_LEADER_ID}] = @PROJLD_ID " +
$"WHERE [{EnrollmentsContract.ID}] = @ID";
command.AddParameter("ID", enroll.Id);
command.AddParameter("RES_ID", enroll.ResourceId);
command.AddParameter("COURSE_ID", enroll.CourseId);
command.AddParameter("NOTES", enroll.Notes ?? (object)DBNull.Value);
command.AddParameter("PROJLD_ID", enroll.ProjectLeaderId);
command.AddParameter("START_DATE", enroll.StartDate ?? (object)DBNull.Value);
command.AddParameter("IS_ADMIT", enroll.IsAdmitted);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
DbLog.LogError($"Error updating Enrollment: {enroll.ToString()}", ex);
throw ex;
}
}
public void Create(Resource resource, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"INSERT INTO {ResourcesContract.TABLE_NAME} (" +
$"[{ResourcesContract.ID}]," +
$"[{ResourcesContract.USERNAME}], [{ResourcesContract.NAME}], " +
$"[{ResourcesContract.SURNAME}], [{ResourcesContract.STATUS}])" +
"VALUES(@ID, @USERNAME, @NAME, @SURNAME, @STATUS)";
command.AddParameter("ID", resource.Id);
command.AddParameter("USERNAME", resource.Username);
command.AddParameter("NAME", resource.Name);
command.AddParameter("SURNAME", resource.Surname);
command.AddParameter("STATUS", resource.Status);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
DbLog.LogError($"Error inserting the new Resource: {resource.ToString()}", ex);
throw ex;
}
}
/// <summary>
/// Return null if not found
/// </summary>
/// <param name="id"></param>
/// <param name="uow"></param>
/// <returns></returns>
public Enrollment GetEnrollmentByID(int id, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"SELECT * FROM {EnrollmentsContract.TABLE_NAME} WHERE [{EnrollmentsContract.ID}] = @ID";
command.AddParameter("ID", id);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
var result = Map(reader);
return(result);
}
return(null);
}
}
}
catch (Exception ex)
{
DbLog.LogError($"Error retriving Enrollment by id {id}", ex);
throw ex;
}
}
public Attachment SwitchBaseAttachment(int attachmentIndex)
{
if (attachmentIndex < 0 || attachmentIndex > (attachments.Count - 1))
{
DbLog.LogError(string.Format("{0} attachmentIndex {1} is out of attachments range", gameObject.name, attachmentIndex), this);
}
else
{
currentBaseAttachment = attachments[attachmentIndex];
}
return(currentBaseAttachment);
}
public HttpResponseMessage InsertEnrollment([FromBody] EnrollmentEntity enrollment)
{
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.InternalServerError);
try
{
EnrollmentsManager enrollManager = new EnrollmentsManager();
enrollManager.InsertEnrollment(enrollment);
response = Request.CreateResponse(HttpStatusCode.OK);
}
catch (Exception ex)
{
response.Content = new StringContent($"{GENERIC_ERROR} [{ex.Message}]");
DbLog.LogError("Error in EnrollmentsController", ex);
}
return response;
}
public List <Enrollment> GetAllEnrollments(UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"SELECT * FROM {EnrollmentsContract.TABLE_NAME}";
return(ToList(command));
}
}
catch (Exception ex)
{
DbLog.LogError($"Error retrieving all Enrollments", ex);
throw ex;
}
}
public HttpResponseMessage GetAllEnrollments()
{
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.InternalServerError);
try
{
EnrollmentsManager enrollManager = new EnrollmentsManager();
List<EnrollmentEntity> enrollments = enrollManager.GetAllEnrollments();
response = Request.CreateResponse(HttpStatusCode.OK, enrollments);
}
catch (Exception ex)
{
response.Content = new StringContent($"{GENERIC_ERROR} [{ex.Message}]");
DbLog.LogError("Error in EnrollmentsController", ex);
}
return response;
}
public HttpResponseMessage DeleteCourse([FromBody] CourseEntity Course)
{
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.InternalServerError);
try
{
CoursesManager courseManager = new CoursesManager();
courseManager.DeleteCourse(Course);
response = Request.CreateResponse(HttpStatusCode.OK);
}
catch (Exception ex)
{
response.Content = new StringContent($"{GENERIC_ERROR} [{ex.Message}]");
DbLog.LogError("Error in CoursesController", ex);
}
return(response);
}
public HttpResponseMessage GetCourseById(int id)
{
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.InternalServerError);
try
{
CoursesManager courseManager = new CoursesManager();
CourseEntity course = courseManager.GetCourse(id);
response = Request.CreateResponse(HttpStatusCode.OK, course);
}
catch (Exception ex)
{
response.Content = new StringContent($"{GENERIC_ERROR} [{ex.Message}]");
DbLog.LogError("Error in CoursesController", ex);
}
return(response);
}
public HttpResponseMessage GetAllResources()
{
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.InternalServerError);
try
{
ResourcesManager resManager = new ResourcesManager();
List <ResourceEntity> users = resManager.GetAllResources();
response = Request.CreateResponse(HttpStatusCode.OK, users);
}
catch (Exception ex)
{
response.Content = new StringContent($"{GENERIC_ERROR} [{ex.Message}]");
DbLog.LogError("Error in ResourcesController", ex);
}
return(response);
}
public HttpResponseMessage InsertResource([FromBody] ResourceEntity resource)
{
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.InternalServerError);
try
{
ResourcesManager resManager = new ResourcesManager();
resManager.InsertResource(resource);
response = Request.CreateResponse(HttpStatusCode.OK);
}
catch (Exception ex)
{
response.Content = new StringContent($"{GENERIC_ERROR} [{ex.Message}]");
DbLog.LogError("Error in ResourcesController", ex);
}
return(response);
}
public static string Serialize(T objectToSerialize)
{
try
{
objectToSerialize.OnBeforeSerialize();
return(JsonUtility.ToJson(objectToSerialize));
}
catch (Exception ex)
{
DbLog.LogError(ex.Message);
if (ErrorOccurred != null)
{
ErrorOccurred(new GameErrorEventArgs(ex.Message));
}
return(null);
}
}
public HttpResponseMessage GetTeacherById(int id)
{
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.InternalServerError);
try
{
TeachersManager teachManager = new TeachersManager();
TeacherEntity teacher = teachManager.GetTeacher(id);
response = Request.CreateResponse(HttpStatusCode.OK, teacher);
}
catch (Exception ex)
{
response.Content = new StringContent($"{GENERIC_ERROR} [{ex.Message}]");
DbLog.LogError("Error in TeachersController", ex);
}
return(response);
}
public HttpResponseMessage DeleteTeachers([FromBody] TeacherEntity teacher)
{
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.InternalServerError);
try
{
TeachersManager teachersManager = new TeachersManager();
teachersManager.DeleteTeacher(teacher);
response = Request.CreateResponse(HttpStatusCode.OK);
}
catch (Exception ex)
{
response.Content = new StringContent($"{GENERIC_ERROR} [{ex.Message}]");
DbLog.LogError("Error in TeachersController", ex);
}
return(response);
}
public void DeleteResource(ResourceEntity resource)
{
ResourcesRepository resRepo = new ResourcesRepository();
using (var uow = UnitOfWork.CreateUoW())
{
try
{
resRepo.Delete(resource.Id, uow);
uow.ApplyChanges();
}
catch (Exception ex)
{
DbLog.LogError("Error deleting resource " + resource.Id, ex);
throw ex;
}
}
}
public void EditTeacher(TeacherEntity teacher)
{
TeachersRepository teachRepo = new TeachersRepository();
using (var uow = UnitOfWork.CreateUoW())
{
try
{
teachRepo.Update(EntitiesMapper.ToDbModel(teacher), uow);
uow.ApplyChanges();
}
catch (Exception ex)
{
DbLog.LogError("Error editing teacher " + teacher, ex);
throw ex;
}
}
}
public List <Resource> GetResourcesByPartialUsername(string partialUsername, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"SELECT * FROM {ResourcesContract.TABLE_NAME} " +
$"WHERE [{ResourcesContract.USERNAME}] LIKE @USERNAME";
command.AddParameter("USERNAME", partialUsername + "%");
return(ToList(command));
}
}
catch (Exception ex)
{
DbLog.LogError($"Error retriving Resources by partial username {partialUsername}", ex);
throw ex;
}
}
public void EditEnrollment(EnrollmentEntity enroll)
{
EnrollmentsRepository enrollRepo = new EnrollmentsRepository();
using (var uow = UnitOfWork.CreateUoW())
{
try
{
enrollRepo.Update(EntitiesMapper.ToDbModel(enroll), uow);
uow.ApplyChanges();
}
catch (Exception ex)
{
DbLog.LogError("Error editing enrollment " + enroll, ex);
throw ex;
}
}
}
public void DeleteEnrollment(EnrollmentEntity enroll)
{
EnrollmentsRepository enrollRepo = new EnrollmentsRepository();
using (var uow = UnitOfWork.CreateUoW())
{
try
{
enrollRepo.Delete(enroll.Id, uow);
uow.ApplyChanges();
}
catch (Exception ex)
{
DbLog.LogError("Error deleting enrollment " + enroll.Id, ex);
throw ex;
}
}
}
public static T Deserialize(string serializedText)
{
try
{
T deserializedObject = JsonUtility.FromJson <T>(serializedText);
deserializedObject.OnAfterDeserialize();
return(deserializedObject);
}
catch (Exception ex)
{
DbLog.LogError(ex.Message);
if (ErrorOccurred != null)
{
ErrorOccurred(new GameErrorEventArgs(ex.Message));
}
return(default(T));
}
}
public void EditResource(ResourceEntity resource)
{
ResourcesRepository resRepo = new ResourcesRepository();
using (var uow = UnitOfWork.CreateUoW())
{
try
{
resRepo.Update(EntitiesMapper.ToDbModel(resource), uow);
uow.ApplyChanges();
}
catch (Exception ex)
{
DbLog.LogError("Error editing resource " + resource, ex);
throw ex;
}
}
}
public void DeleteTeacher(TeacherEntity teacher)
{
TeachersRepository teachRepo = new TeachersRepository();
using (var uow = UnitOfWork.CreateUoW())
{
try
{
teachRepo.Delete(teacher.Id, uow);
uow.ApplyChanges();
}
catch (Exception ex)
{
DbLog.LogError("Error deleting teacher " + teacher.Id, ex);
throw ex;
}
}
}
public void Delete(int id, UnitOfWork uow)
{
try
{
using (var command = uow.CreateCommand())
{
command.CommandText = $"DELETE FROM {EnrollmentsContract.TABLE_NAME}" +
$"WHERE [{EnrollmentsContract.ID}] = @ID";
command.AddParameter("ID", id);
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
DbLog.LogError($"Error deleting Enrollment of id {id}", ex);
throw ex;
}
}
public void EditCourse(CourseEntity course)
{
CoursesRepository courseRepo = new CoursesRepository();
using (var uow = UnitOfWork.CreateUoW())
{
try
{
courseRepo.Update(EntitiesMapper.ToDbModel(course), uow);
uow.ApplyChanges();
}
catch (Exception ex)
{
uow.Rollback();
DbLog.LogError("Error editing course " + course, ex);
throw ex;
}
}
}
public void InsertResource(ResourceEntity res)
{
ResourcesRepository resRepo = new ResourcesRepository();
using (var uow = UnitOfWork.CreateUoW())
{
try
{
resRepo.Create(EntitiesMapper.ToDbModel(res), uow);
uow.ApplyChanges();
}
catch (Exception ex)
{
uow.Rollback();
DbLog.LogError("Error inserting resource " + res, ex);
throw ex;
}
}
}