public IEnumerable <CourseInstance> GetCurrent([FromBody] RequestObject req) { var courses = new List <CourseInstance>(); var query = @"DECLARE @ProfessorId INT; SET @ProfessorId = @Set_ProfessorId; SELECT c.ID ,c.Title ,c.Descr ,c.IsActive ,ca.ID AS CourseAssignmentId FROM Courses c JOIN CoursesAssignments ca ON c.ID = ca.CourseID JOIN AcademicTerms at ON at.ID = ca.AcademicTermID WHERE ca.ProfessorID = @ProfessorId AND c.IsActive = 1 AND GETDATE() BETWEEN at.StartDate AND at.EndDate"; var cmd = new SqlCommand(query); cmd.Parameters.AddWithValue("@Set_ProfessorId", req.Id); var sdt = AdoHelper.GetDataTable(cmd, _connString); if (!sdt.ErrorFound) { foreach (DataRow dr in sdt.DataTable.Rows) { var c = new CourseInstance(); int id; if (int.TryParse(dr["ID"].ToString(), out id)) { c.Id = id; } c.Title = dr["Title"].ToString(); c.Descr = dr["Descr"].ToString(); c.IsActive = dr["IsActive"].ToString() == "1"; int caid; if (int.TryParse(dr["CourseAssignmentId"].ToString(), out caid)) { c.CourseAssignmentId = caid; } courses.Add(c); } } return(courses); }
/// <summary> /// Get the current academic term id /// </summary> /// <returns></returns> private int?GetCurrentAcademicTermId() { var query = @"select a.ID from AcademicTerms a where GETDATE() between a.StartDate and a.EndDate"; var cmd = new SqlCommand(query); var sdt = AdoHelper.GetDataTable(cmd, _connString); if (!sdt.ErrorFound && sdt.DataTable.Rows.Count > 0) { var termId = sdt.DataTable.Rows[0]["ID"].ToString(); int id; if (int.TryParse(termId, out id)) { return(id); } return(null); } return(null); }