private bool CourseAlreadyTaken(int courseId, CourseEnrollment aCourseEnrollment) { string query = "SELECT * FROM IsCourseAlreadyTakenView WHERE Student_RegNo='" + aCourseEnrollment.AStudent.RegNo + "'"; aConnection = new SqlConnection(connection); aConnection.Open(); aCommand = new SqlCommand(query, aConnection); aReader = aCommand.ExecuteReader(); if (aReader.HasRows) { while (aReader.Read()) { int tempCourseId = (int)aReader[0]; if (tempCourseId == courseId) { return(false); } } } aConnection.Close(); return(true); }
public CourseEnrollment CheckRegNo(string regNo) { CourseEnrollment aCourseEnrollment = new CourseEnrollment(); aConnection = new SqlConnection(connection); string registrationNumber = regNo; string query1 = "SELECT * FROM CourseEnrollmentView1 WHERE Student_RegNo='" + registrationNumber + "'"; string query2 = "SELECT * FROM CourseEnrollmentView2"; aConnection.Open(); aCommand = new SqlCommand(query1, aConnection); aReader = aCommand.ExecuteReader(); if (aReader.HasRows) { while (aReader.Read()) { aCourseEnrollment.AStudent.RegNo = aReader[0].ToString(); aCourseEnrollment.AStudent.Name = aReader[1].ToString(); aCourseEnrollment.AStudent.Email = aReader[2].ToString(); } } aConnection.Close(); aConnection.Open(); aCommand = new SqlCommand(query2, aConnection); aReader = aCommand.ExecuteReader(); if (aReader.HasRows) { while (aReader.Read()) { Course aCourse = new Course(); aCourse.CourseId = (int)aReader[0]; aCourse.CourseTitle = aReader[1].ToString(); aCourse.CourseName = aReader[2].ToString(); aCourseEnrollment.Courses.Add(aCourse); } } aConnection.Close(); return(aCourseEnrollment); }
public string InsertIntoDatabase(int courseId, DateTime aDateTime, CourseEnrollment aCourseEnrollment) { if (CourseAlreadyTaken(courseId, aCourseEnrollment)) { string studentRegNo = aCourseEnrollment.AStudent.RegNo; string query = string.Format("INSERT INTO t_StudentEnroll VALUES ('{0}','{1}','{2}')", studentRegNo, courseId, aDateTime); aConnection = new SqlConnection(connection); aConnection.Open(); SqlCommand aCommand = new SqlCommand(query, aConnection); int isAffected = aCommand.ExecuteNonQuery(); aConnection.Close(); if (isAffected > 0) { return("Successfully Entered"); } //return isAffected; return("Failed to Insert"); } return("This Course is Already Taken"); }