public void StudentSelectOutput() { string splGetStudent = "SELECT * FROM Student"; try { using (SqlConnection conn = new SqlConnection(ConnectionString.connection)) { conn.Open(); using (SqlCommand cmSelectStudent = new SqlCommand(splGetStudent, conn)) { using (SqlDataReader drStudents = cmSelectStudent.ExecuteReader()) { Console.ForegroundColor = ConsoleColor.DarkYellow; Console.WriteLine("----------------------------------------------------------------------------"); Console.WriteLine($"| {"STUDENT ID",-12} | {"FIRSTNAME",-12} | {"LASTNAME",-12} | {"BIRTHDATE",-12} | {"TUITION FEES", -12} |"); Console.WriteLine("----------------------------------------------------------------------------"); Console.WriteLine("----------------------------------------------------------------------------"); Console.ForegroundColor = ConsoleColor.White; while (drStudents.Read()) { Student_ID = drStudents["Student_ID"].ToString(); FirstName = drStudents["FirstName"].ToString(); LastName = drStudents["LastName"].ToString(); TuitionFees = drStudents["TuitionFees"].ToString(); BirthDate = drStudents["BirthDate"].ToString(); // Seperate the Date from Time (I will not show time) string[] BirthDateList = BirthDate.Split(' '); // Remove the unessessary empty space characters from inserted strings Student_ID = Student_ID.Replace(" ", string.Empty); FirstName = FirstName.Replace(" ", string.Empty); LastName = LastName.Replace(" ", string.Empty); TuitionFees = TuitionFees.Replace(" ", string.Empty); BirthDate = BirthDate.Replace(" ", string.Empty); Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine($"| {Student_ID,-12} | {FirstName, -12} | {LastName, -12} | {BirthDateList[0],-12} | {TuitionFees + " euro", -13}|"); Console.WriteLine("----------------------------------------------------------------------------"); Console.ForegroundColor = ConsoleColor.White; } Console.ForegroundColor = ConsoleColor.DarkYellow; Console.WriteLine("----------------------------------------------------------------------------"); Console.WriteLine($"| {"STUDENT ID",-12} | {"FIRSTNAME",-12} | {"LASTNAME",-12} | {"BIRTHDATE",-12} | {"TUITION FEES", -12} |"); Console.WriteLine("----------------------------------------------------------------------------"); Console.ForegroundColor = ConsoleColor.White; } } } } catch (Exception ex) { Console.WriteLine(ex.Message); } }
public void Output() { string splGetStudents = "SELECT Student.Student_ID, Student.LastName, Student.FirstName, COUNT(Course.CourseType) " + "AS 'NumberOfCourses' FROM Student " + "INNER JOIN CourseStudent ON Student.Student_ID = CourseStudent.Student_ID " + "INNER JOIN Course ON CourseStudent.CourseTitle = Course.CourseTitle " + "GROUP BY Student.Student_ID, Student.LastName, Student.FirstName " + "HAVING COUNT(Course.CourseType) > 1;"; try { using (SqlConnection conn = new SqlConnection(ConnectionString.connection)) { conn.Open(); using (SqlCommand cmSelectStudents = new SqlCommand(splGetStudents, conn)) { using (SqlDataReader drStudents = cmSelectStudents.ExecuteReader()) { Console.ForegroundColor = ConsoleColor.DarkGray; Console.WriteLine("----------------------------------------------------------------------------"); Console.WriteLine($"| {"STUDENT ID",-11} | {"FIRSTNAME",-15} | {"LASTNAME",-15} | {"NUMBER OF COURSES",-22} | "); Console.WriteLine("----------------------------------------------------------------------------"); Console.WriteLine("----------------------------------------------------------------------------"); while (drStudents.Read()) { Student_ID = drStudents["Student_ID"].ToString(); FirstName = drStudents["FirstName"].ToString(); LastName = drStudents["LastName"].ToString(); NumberOfCourses = drStudents["NumberOfCourses"].ToString(); // Remove the unessessary empty space characters from inserted strings Student_ID = Student_ID.Replace(" ", string.Empty); FirstName = FirstName.Replace(" ", string.Empty); LastName = LastName.Replace(" ", string.Empty); NumberOfCourses = NumberOfCourses.Replace(" ", string.Empty); Console.ForegroundColor = ConsoleColor.Gray; Console.WriteLine($"| {Student_ID,-11} | {FirstName,-15} | {LastName,-15} | {NumberOfCourses,-12} |"); Console.WriteLine("----------------------------------------------------------------------------"); } Console.ForegroundColor = ConsoleColor.DarkGray; Console.WriteLine("----------------------------------------------------------------------------"); Console.WriteLine($"| {"STUDENT ID",-11} | {"FIRSTNAME",-15} | {"LASTNAME",-15} | {"NUMBER OF COURSES",-22} | "); Console.WriteLine("----------------------------------------------------------------------------"); Console.ForegroundColor = ConsoleColor.White; } } } } catch (Exception ex) { Console.WriteLine(ex.Message); } }
public void StudentPerSpecificCourse() { string sqlStudentsInInputCourse = "SELECT Student.Student_ID, Firstname, Lastname, Stream, CourseType FROM Student " + "INNER JOIN CourseStudent ON Student.Student_ID = CourseStudent.Student_ID " + "INNER JOIN Course ON CourseStudent.CourseTitle = Course.CourseTitle " + "WHERE Course.CourseTitle = @inputcoursetitle"; try { using (SqlConnection conn = new SqlConnection(ConnectionString.connection)) { conn.Open(); using (SqlCommand cmAssCourseStudent = new SqlCommand(sqlStudentsInInputCourse, conn)) { // Add Parameter 1 SqlParameter parameter = new SqlParameter("@inputcoursetitle", CourseTitle); cmAssCourseStudent.Parameters.Add(parameter); using (SqlDataReader drAssCourseStudent = cmAssCourseStudent.ExecuteReader()) { // Display Title Console.ForegroundColor = ConsoleColor.DarkRed; Console.WriteLine("#==========================================================================#"); Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine($"| STUDENTS IN CHOSEN COURSE |"); Console.ForegroundColor = ConsoleColor.DarkRed; Console.WriteLine("#==========================================================================#"); Console.ForegroundColor = ConsoleColor.White; Console.ForegroundColor = ConsoleColor.DarkGreen; Console.WriteLine("----------------------------------------------------------------------------"); Console.WriteLine($"| {"FIRSTNAME",-18} | {"LASTNAME",-18} | {"COURSE PARTICIPATING",-30} | <{"CHOOSE ID",-8}>"); Console.WriteLine("----------------------------------------------------------------------------"); Console.WriteLine("----------------------------------------------------------------------------"); Console.ForegroundColor = ConsoleColor.White; while (drAssCourseStudent.Read()) { Student_ID = drAssCourseStudent["Student_ID"].ToString(); FirstName = drAssCourseStudent["FirstName"].ToString(); LastName = drAssCourseStudent["LastName"].ToString(); Stream = drAssCourseStudent["Stream"].ToString(); CourseType = drAssCourseStudent["CourseType"].ToString(); // Remove the unessessary empty space characters from inserted strings Student_ID = Student_ID.Replace(" ", string.Empty); FirstName = FirstName.Replace(" ", string.Empty); LastName = LastName.Replace(" ", string.Empty); Stream = Stream.Replace(" ", string.Empty); CourseType = CourseType.Replace(" ", string.Empty); Console.ForegroundColor = ConsoleColor.Green; Console.WriteLine($"| {FirstName,-18} | {LastName,-18} | {Stream,-14} | {CourseType,-13} | < {Student_ID,-6}>"); Console.WriteLine("----------------------------------------------------------------------------"); Console.ForegroundColor = ConsoleColor.White; } Console.ForegroundColor = ConsoleColor.DarkGreen; Console.WriteLine("----------------------------------------------------------------------------"); Console.WriteLine($"| {"FIRSTNAME",-18} | {"LASTNAME",-18} | {"COURSE PARTICIPATING",-30} | <{"CHOOSE ID",-8}> "); Console.WriteLine("----------------------------------------------------------------------------"); Console.ForegroundColor = ConsoleColor.White; } } } } catch (Exception ex) { Console.WriteLine(ex.Message); } }