public List <DistanceEdClass> GetAllClasses() { List <DistanceEdClass> returnMe = new List <DistanceEdClass>(); using (SqlConnection connection = new SqlConnection(_connStr)) { using (SqlCommand sqlCommand = new SqlCommand { Connection = connection, CommandType = CommandType.Text, CommandText = "SELECT * FROM DistanceEdClass;" }) { sqlCommand.Connection.Open(); SqlDataReader dbDataReader = sqlCommand.ExecuteReader(); if (dbDataReader.HasRows) { while (dbDataReader.Read()) { DistanceEdClass obj = dataReaderToDistanceEdClass(dbDataReader); if (obj != null) { returnMe.Add(obj); } } } sqlCommand.Connection.Close(); } } return(returnMe); }
public void Update(DistanceEdClass DEClass) { sanitize(DEClass); using (SqlConnection connection = new SqlConnection(_connStr)) { using (SqlCommand sqlCommand = new SqlCommand { Connection = connection, CommandType = CommandType.Text, CommandText = "UPDATE DistanceEdClass SET BlackboardID=@BBID, Name=@CNAME, RegistrationAvailableFrom=@AVAILFROM, RegistrationAvailableTo=@AVAILTO, InfoURL=@INFOURL, Description=@CDESC, IsRequestable=@ISREQUESTABLE, AreMaterialsAvailable=@MATERIALSAVAIL, RequiresMentor=@REQUIRESMENTOR, DeliveryMethod=@DELIVERYMETHOD, PreRequisites=@PREREQS, RequiredMaterials=@REQMATS, StartDate=@STARTDATE, EndDate=@ENDDATE WHERE id=@CID ;" }) { sqlCommand.Parameters.AddWithValue("@CID", DEClass.ID); sqlCommand.Parameters.AddWithValue("@BBID", !string.IsNullOrEmpty(DEClass.BlackboardID) ? DEClass.BlackboardID : ""); sqlCommand.Parameters.AddWithValue("@CNAME", !string.IsNullOrEmpty(DEClass.Name) ? DEClass.Name : ""); sqlCommand.Parameters.AddWithValue("@AVAILFROM", DEClass.RegistrationAvailableFrom); sqlCommand.Parameters.AddWithValue("@AVAILTO", DEClass.RegistrationAvailableTo); sqlCommand.Parameters.AddWithValue("@INFOURL", !string.IsNullOrEmpty(DEClass.MoreInfoURL) ? DEClass.MoreInfoURL : ""); sqlCommand.Parameters.AddWithValue("@CDESC", !string.IsNullOrEmpty(DEClass.Description) ? DEClass.Description : ""); sqlCommand.Parameters.AddWithValue("@ISREQUESTABLE", DEClass.IsRequestable); sqlCommand.Parameters.AddWithValue("@MATERIALSAVAIL", DEClass.MaterialsAvailableToTeachers); sqlCommand.Parameters.AddWithValue("@REQUIRESMENTOR", DEClass.MentorTeacherRequired); sqlCommand.Parameters.AddWithValue("@DELIVERYMETHOD", !string.IsNullOrEmpty(DEClass.DeliveryMethod) ? DEClass.DeliveryMethod : ""); sqlCommand.Parameters.AddWithValue("@PREREQS", !string.IsNullOrEmpty(DEClass.PreRequisites) ? DEClass.PreRequisites : ""); sqlCommand.Parameters.AddWithValue("@REQMATS", !string.IsNullOrEmpty(DEClass.RequiredMaterials) ? DEClass.RequiredMaterials : ""); sqlCommand.Parameters.AddWithValue("@STARTDATE", DEClass.Starts); sqlCommand.Parameters.AddWithValue("@ENDDATE", DEClass.Ends); sqlCommand.Connection.Open(); sqlCommand.ExecuteNonQuery(); sqlCommand.Connection.Close(); } } }
public List <DistanceEdClass> GetAvailableClasses(DateTime ReferenceDate) { List <DistanceEdClass> returnMe = new List <DistanceEdClass>(); using (SqlConnection connection = new SqlConnection(_connStr)) { using (SqlCommand sqlCommand = new SqlCommand { Connection = connection, CommandType = CommandType.Text, CommandText = "SELECT * FROM DistanceEdClass WHERE RegistrationAvailableFrom<=GETDATE() AND RegistrationAvailableTo>=GETDATE();" }) { sqlCommand.Connection.Open(); SqlDataReader dbDataReader = sqlCommand.ExecuteReader(); if (dbDataReader.HasRows) { while (dbDataReader.Read()) { DistanceEdClass obj = dataReaderToDistanceEdClass(dbDataReader); if (obj != null) { returnMe.Add(obj); } } } sqlCommand.Connection.Close(); } } return(returnMe); }
public void Add(DistanceEdClass DEClass) { sanitize(DEClass); using (SqlConnection connection = new SqlConnection(_connStr)) { using (SqlCommand sqlCommand = new SqlCommand { Connection = connection, CommandType = CommandType.Text, CommandText = "INSERT INTO DistanceEdClass(BlackboardID,Name,RegistrationAvailableFrom,RegistrationAvailableTo,InfoURL,Description,IsRequestable,AreMaterialsAvailable,RequiresMentor,DeliveryMethod,PreRequisites,RequiredMaterials,StartDate,EndDate) VALUES(@BBID,@CNAME,@AVAILFROM,@AVAILTO,@INFOURL,@CDESC,@ISREQUESTABLE,@MATERIALSAVAIL,@REQUIRESMENTOR,@DELIVERYMETHOD,@PREREQS,@REQMATS,@STARTDATE,@ENDDATE);" }) { sqlCommand.Parameters.AddWithValue("@BBID", !string.IsNullOrEmpty(DEClass.BlackboardID) ? DEClass.BlackboardID : ""); sqlCommand.Parameters.AddWithValue("@CNAME", !string.IsNullOrEmpty(DEClass.Name) ? DEClass.Name : ""); sqlCommand.Parameters.AddWithValue("@AVAILFROM", DEClass.RegistrationAvailableFrom); sqlCommand.Parameters.AddWithValue("@AVAILTO", DEClass.RegistrationAvailableTo); sqlCommand.Parameters.AddWithValue("@INFOURL", !string.IsNullOrEmpty(DEClass.MoreInfoURL) ? DEClass.MoreInfoURL : ""); sqlCommand.Parameters.AddWithValue("@CDESC", !string.IsNullOrEmpty(DEClass.Description) ? DEClass.Description : ""); sqlCommand.Parameters.AddWithValue("@ISREQUESTABLE", DEClass.IsRequestable); sqlCommand.Parameters.AddWithValue("@MATERIALSAVAIL", DEClass.MaterialsAvailableToTeachers); sqlCommand.Parameters.AddWithValue("@REQUIRESMENTOR", DEClass.MentorTeacherRequired); sqlCommand.Parameters.AddWithValue("@DELIVERYMETHOD", !string.IsNullOrEmpty(DEClass.DeliveryMethod) ? DEClass.DeliveryMethod : ""); sqlCommand.Parameters.AddWithValue("@PREREQS", !string.IsNullOrEmpty(DEClass.PreRequisites) ? DEClass.PreRequisites : ""); sqlCommand.Parameters.AddWithValue("@REQMATS", !string.IsNullOrEmpty(DEClass.RequiredMaterials) ? DEClass.RequiredMaterials : ""); sqlCommand.Parameters.AddWithValue("@STARTDATE", DEClass.Starts); sqlCommand.Parameters.AddWithValue("@ENDDATE", DEClass.Ends); sqlCommand.Connection.Open(); sqlCommand.ExecuteNonQuery(); sqlCommand.Connection.Close(); } } }
private void sanitize(DistanceEdClass DEClass) { // Validate dates, because C# and SQL server have different date constraints if (DEClass.Starts < DataSettings.MinSQLDate) { DEClass.Starts = DataSettings.MinSQLDate; } if (DEClass.Ends < DataSettings.MinSQLDate) { DEClass.Ends = DataSettings.MinSQLDate; } if (DEClass.RegistrationAvailableFrom < DataSettings.MinSQLDate) { DEClass.RegistrationAvailableFrom = DataSettings.MinSQLDate; } if (DEClass.RegistrationAvailableTo < DataSettings.MinSQLDate) { DEClass.RegistrationAvailableTo = DataSettings.MinSQLDate; } }
public DistanceEdClass Get(int ID) { DistanceEdClass returnMe = null; using (SqlConnection connection = new SqlConnection(_connStr)) { using (SqlCommand sqlCommand = new SqlCommand { Connection = connection, CommandType = CommandType.Text, CommandText = "SELECT * FROM DistanceEdClass WHERE ID=@CLASSID;" }) { sqlCommand.Parameters.AddWithValue("CLASSID", ID); sqlCommand.Connection.Open(); SqlDataReader dbDataReader = sqlCommand.ExecuteReader(); if (dbDataReader.HasRows) { while (dbDataReader.Read()) { DistanceEdClass obj = dataReaderToDistanceEdClass(dbDataReader); if (obj != null) { returnMe = obj; } } } sqlCommand.Connection.Close(); } } return(returnMe); }
public void Update(DistanceEdClass DEClass) { _classRepo.Update(DEClass); }
public void Add(DistanceEdClass DEClass) { _classRepo.Add(DEClass); }
public IActionResult OnPostFormSubmit() { if (ModelState.IsValid) { regErrors.Clear(); string studentName = Request.Form["txtStudentName"].ToString(); string studentNumber = Request.Form["txtStudentNumber"].ToString(); string studentSchool = Request.Form["txtStudentSchool"].ToString(); string comments = Request.Form["txtNotes"].ToString(); string requestor = Request.Form["txtRequestor"].ToString(); string mentorName = Request.Form["txtMentor"].ToString() ?? string.Empty; int courseID = Parsers.ToInt(Request.Form["txtCourseID"].ToString() ?? string.Empty); // Find checked classes // The easy way to do this would be check all submitted form elements for anything that starts with "chkClass_" // The safe way to do this would be to load all valid classes, and compare to that list instead Dictionary <int, DistanceEdClass> availableClasses = _classService.GetAllAvailable(DateTime.Now).ToDictionary(x => x.ID); DistanceEdRequest newRequest = new DistanceEdRequest() { StudentName = studentName, StudentNumber = studentNumber, StudentBaseSchool = studentSchool, Comments = comments, CourseID = courseID, MentorTeacherName = mentorName, Requestor = requestor }; // Validate if (courseID > 0) { DistanceEdClass selectedClass = availableClasses[courseID] ?? null; if (selectedClass == null) { regErrors.Add("Course with ID \"" + courseID + "\" was not found."); } else { if (selectedClass.MentorTeacherRequired) { if (string.IsNullOrEmpty(mentorName)) { regErrors.Add("The class \"" + selectedClass.Name + "\" requires a mentor teacher, but no name was provided."); } } } } else { regErrors.Add("No course selected"); } if (string.IsNullOrEmpty(studentName)) { regErrors.Add("Student name is required"); } if (string.IsNullOrEmpty(studentNumber)) { regErrors.Add("Student number is required"); } if (string.IsNullOrEmpty(studentSchool)) { regErrors.Add("Student base school is required"); } if (string.IsNullOrEmpty(requestor)) { regErrors.Add("Requestor name is required"); } // Submit to DB if (regErrors.Count == 0) { try { _requestService.Add(newRequest); } catch (Exception ex) { regErrors.Add(ex.Message); } } if (regErrors.Count == 0) { return(RedirectToPage("Thanks")); } else { return(Page()); } } else { return(Page()); } }
public List <DistanceEdRequest> GetForCourse(DistanceEdClass Course) { return(_repository.GetForCourse(Course)); }
public List <DistanceEdRequest> GetForCourse(DistanceEdClass course) { return(GetForCourse(course.ID)); }