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);
}
}