public void AssCourseStudenttOutput()
{
string splGetAssPerCoursePerStudent = "SELECT Firstname, Lastname, Stream, CourseType, Description FROM Student " +
"INNER JOIN CourseStudent ON Student.Student_ID = CourseStudent.Student_ID " +
"INNER JOIN Course ON CourseStudent.CourseTitle = Course.CourseTitle " +
"INNER JOIN Assignment ON Course.CourseTitle = Assignment.CourseTitleA " +
"ORDER BY Firstname, Lastname;";
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString.connection))
{
conn.Open();
using (SqlCommand cmSelectAssCourseStudent = new SqlCommand(splGetAssPerCoursePerStudent, conn))
{
using (SqlDataReader drAssCourseStudent = cmSelectAssCourseStudent.ExecuteReader())
{
Console.ForegroundColor = ConsoleColor.DarkMagenta;
Console.WriteLine("----------------------------------------------------------------------------");
Console.WriteLine($"| {"FIRSTNAME",-12} | {"LASTNAME",-12} | {"COURSE PARTICIPATING",-23} | {"ASSIGNMENTS",-16} |");
Console.WriteLine("----------------------------------------------------------------------------");
Console.WriteLine("----------------------------------------------------------------------------");
Console.ForegroundColor = ConsoleColor.White;
while (drAssCourseStudent.Read())
{
FirstName = drAssCourseStudent["FirstName"].ToString();
LastName = drAssCourseStudent["LastName"].ToString();
Stream = drAssCourseStudent["Stream"].ToString();
CourseType = drAssCourseStudent["CourseType"].ToString();
Description = drAssCourseStudent["Description"].ToString();
// Remove the unessessary empty space characters from inserted strings
FirstName = FirstName.Replace(" ", string.Empty);
LastName = LastName.Replace(" ", string.Empty);
Stream = Stream.Replace(" ", string.Empty);
CourseType = CourseType.Replace(" ", string.Empty);
Description = Description.Replace(" ", string.Empty);
Console.ForegroundColor = ConsoleColor.Magenta;
Console.WriteLine($"| {FirstName,-12} | {LastName,-12} | {Stream,-10} | {CourseType,-10} |{Description,-17} |");
Console.WriteLine("----------------------------------------------------------------------------");
Console.ForegroundColor = ConsoleColor.White;
}
Console.ForegroundColor = ConsoleColor.DarkMagenta;
Console.WriteLine("----------------------------------------------------------------------------");
Console.WriteLine($"| {"FIRSTNAME",-12} | {"LASTNAME",-12} | {"COURSE PARTICIPATING",-23} | {"ASSIGNMENTS",-16} |");
Console.WriteLine("----------------------------------------------------------------------------");
Console.ForegroundColor = ConsoleColor.White;
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public void CourseSelectOutput()
{
string splGetCourse = "SELECT * FROM Course";
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString.connection))
{
conn.Open();
using (SqlCommand cmSelectCourse = new SqlCommand(splGetCourse, conn))
{
using (SqlDataReader drCourse = cmSelectCourse.ExecuteReader())
{
Console.ForegroundColor = ConsoleColor.DarkMagenta;
Console.WriteLine("----------------------------------------------------------------------------");
Console.WriteLine($"| {"TITLE",-8} | {"STREAM",-12} | {"COURSE",-14} | {"START DATE",-13} | {"END DATE",-13} |");
Console.WriteLine("----------------------------------------------------------------------------");
Console.WriteLine("----------------------------------------------------------------------------");
Console.ForegroundColor = ConsoleColor.White;
while (drCourse.Read())
{
CourseTitle = drCourse["CourseTitle"].ToString();
Stream = drCourse["Stream"].ToString();
CourseType = drCourse["CourseType"].ToString();
StartDate = drCourse["StartDate"].ToString();
EndDate = drCourse["EndDate"].ToString();
// Split the DATE string so as to get rid of the Time
string[] StartDateList = StartDate.Split(' ');
string[] EndDateList = EndDate.Split(' ');
// Remove the unessessary empty space characters from inserted strings
CourseTitle = CourseTitle.Replace(" ", string.Empty);
Stream = Stream.Replace(" ", string.Empty);
CourseType = CourseType.Replace(" ", string.Empty);
StartDate = StartDate.Replace(" ", string.Empty);
EndDate = EndDate.Replace(" ", string.Empty);
Console.ForegroundColor = ConsoleColor.Magenta;
Console.WriteLine($"| {CourseTitle,-8} | {Stream,-12} | {CourseType,-14} | {StartDateList[0],-13} | {EndDateList[0],-13} |");
Console.WriteLine("----------------------------------------------------------------------------");
Console.ForegroundColor = ConsoleColor.White;
}
Console.ForegroundColor = ConsoleColor.DarkMagenta;
Console.WriteLine("----------------------------------------------------------------------------");
Console.WriteLine($"| {"TITLE",-8} | {"STREAM",-12} | {"COURSE",-14} | {"START DATE",-13} | {"END DATE",-13} |");
Console.WriteLine("----------------------------------------------------------------------------");
Console.ForegroundColor = ConsoleColor.White;
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public void AssesPerSpecificCourse()
{
string sqlAssesInInputCourse = "SELECT Description, Stream, CourseType FROM Assignment " +
"INNER JOIN Course ON Assignment.CourseTitleA = Course.CourseTitle " +
"WHERE Course.CourseTitle = @inputcoursetitle";
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString.connection))
{
conn.Open();
using (SqlCommand cmAssCourseStudent = new SqlCommand(sqlAssesInInputCourse, 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($"| ASSIGNMENTS IN CHOSEN COURSE |");
Console.ForegroundColor = ConsoleColor.DarkRed;
Console.WriteLine("#==========================================================================#");
Console.ForegroundColor = ConsoleColor.White;
Console.ForegroundColor = ConsoleColor.DarkCyan;
Console.WriteLine("----------------------------------------------------------------------------");
Console.WriteLine($"| {"ASSIGNMENT TITLE",-39} | {"COURSE ",-30} | ");
Console.WriteLine("----------------------------------------------------------------------------");
Console.WriteLine("----------------------------------------------------------------------------");
Console.ForegroundColor = ConsoleColor.White;
while (drAssCourseStudent.Read())
{
Description = drAssCourseStudent["Description"].ToString();
Stream = drAssCourseStudent["Stream"].ToString();
CourseType = drAssCourseStudent["CourseType"].ToString();
// Remove the unessessary empty space characters from inserted strings
Description = Description.Replace(" ", string.Empty);
Stream = Stream.Replace(" ", string.Empty);
CourseType = CourseType.Replace(" ", string.Empty);
Console.ForegroundColor = ConsoleColor.Cyan;
Console.WriteLine($"| {Description,-39} | {Stream,-14} | {CourseType,-13} |");
Console.WriteLine("----------------------------------------------------------------------------");
Console.ForegroundColor = ConsoleColor.White;
}
Console.ForegroundColor = ConsoleColor.DarkCyan;
Console.WriteLine("----------------------------------------------------------------------------");
Console.WriteLine($"| {"ASSIGNMENT TITLE",-39} | {"COURSE ",-30} | ");
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);
}
}