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);
}
}
private void Click_check(PictureBox p, TextBox t, ref bool chk)
{
if (t.Text != "" && chk == false)
{
//해당 버튼 옆에 텍스트 박스에 담긴 학번 정보 서버에 보내기
this.m_Student_ID = new Student_ID();
this.m_Student_ID.Type = (int)PacketType.학번확인;
this.m_Student_ID.s_id = t.Text;
Packet.Serialize(this.m_Student_ID).CopyTo(this.sendBuf, 0);
this.m_networkstream = m_client.GetStream();
this.Send();
//(정상인지 오류인지) 해당 학번이 이미 사용중인지 아닌지 정보 받기
this.Recv();
Packet packet = (Packet)Packet.Deserialize(this.readBuf);
if ((int)packet.Type == (int)PacketType.학번확인)
{
this.m_Student_ID = (Student_ID)Packet.Deserialize(this.readBuf);
if ((int)m_Student_ID.Error == (int)PacketSendERROR.에러) //이미 존재하는 학번
{
MessageBox.Show("Already Using Student ID : " + m_Student_ID.s_id + "!", "Failed",
MessageBoxButtons.OK, MessageBoxIcon.Error); //에러 메시지 띄우기
}
else if ((int)m_Student_ID.Error == (int)PacketSendERROR.정상) //존재하지 않음
{
p.Image = soccerForm.Properties.Resources.green_check; //체크이미지 바꾸기
t.Enabled = false;
chk = true;
MessageBox.Show("Valid Team Student ID : " + m_Student_ID.s_id + "!", "Success",
MessageBoxButtons.OK, MessageBoxIcon.None); //확인 메시지 띄우기
if (plus_check)
{
++st_count;
plus_check = false;
}
}
}
}
else if (t.Text == "")
{
MessageBox.Show("Input Student ID!", "Empty",
MessageBoxButtons.OK, MessageBoxIcon.Error); //에러 메시지 띄우기
}
}
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);
}
}
private void register_btn_Click(object sender, EventArgs e)
{
if (team_check)
{
if (id_check1 || id_check2 || id_check3 || id_check4 || id_check5)
{
if (PW_txtBox.Text.Equals(Confirm_txtBox.Text))
{
//학번 보내기
foreach (string s_id in si)
{
//저장한 학번들 정보 서버에 보내기
this.m_Student_ID = new Student_ID();
this.m_Student_ID.Type = (int)PacketType.학번저장;
this.m_Student_ID.s_id = s_id;
this.m_Student_ID.t_name = team_name;
Packet.Serialize(this.m_Student_ID).CopyTo(this.sendBuf, 0);
this.m_networkstream = m_client.GetStream();
this.Send();
//(정상인지 오류인지) 학번저장이 안되었다는 메시지오면 오류 메시지!
this.Recv();
Packet pack = (Packet)Packet.Deserialize(this.readBuf);
if ((int)pack.Type == (int)PacketType.학번저장)
{
this.m_Student_ID = (Student_ID)Packet.Deserialize(this.readBuf);
if ((int)m_Student_ID.Error == (int)PacketSendERROR.에러)
{
MessageBox.Show("Student ID can Not be Saved!", "Failed",
MessageBoxButtons.OK, MessageBoxIcon.Error); //에러 메시지 띄우기
}
}
}
//팀이름, 패스워드, 팀 인원수 보내기
this.m_Team_Info = new Team_Info();
this.m_Team_Info.Type = (int)PacketType.로그인정보저장;
this.m_Team_Info.t_name = team_name;
this.m_Team_Info.pw = PW_txtBox.Text;
this.m_Team_Info.stCount = si.Count;
Packet.Serialize(this.m_Team_Info).CopyTo(this.sendBuf, 0);
this.m_networkstream = m_client.GetStream();
this.Send();
//(정상인지 오류인지) 팀이름, 패스워드 저장이 안되었다는 메시지오면 오류 메시지!
this.Recv();
Packet packet = (Packet)Packet.Deserialize(this.readBuf);
if ((int)packet.Type == (int)PacketType.로그인정보저장)
{
this.m_Team_Info = (Team_Info)Packet.Deserialize(this.readBuf);
if ((int)m_Team_Info.Error == (int)PacketSendERROR.에러)
{
MessageBox.Show("User Information can Not be Saved!", "Failed",
MessageBoxButtons.OK, MessageBoxIcon.Error); //에러 메시지 띄우기
}
else if ((int)m_Team_Info.Error == (int)PacketSendERROR.정상)
{
this.DialogResult = DialogResult.OK; //완료 결과
//this.Close();
}
}
}
else
{
MessageBox.Show("Password Incorrect!", "Failed",
MessageBoxButtons.OK, MessageBoxIcon.Error); //에러 메시지 띄우기
}
}
else
{
MessageBox.Show("Input Student ID!", "Empty",
MessageBoxButtons.OK, MessageBoxIcon.Error); //에러 메시지 띄우기
}
}
else
{
MessageBox.Show("Input your Team Name!", "Empty",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Client_Setting_Load(object sender, EventArgs e)
{
//정보 텍스트 박스 비활
SI_txtBox1.Enabled = false;
SI_txtBox2.Enabled = false;
SI_txtBox3.Enabled = false;
SI_txtBox4.Enabled = false;
SI_txtBox5.Enabled = false;
//해당 팀 정보 보여주기
ID_txtBox.Text = team_name;
//해당 팀 정보 불러오기
this.m_Team_Info = new Team_Info();
this.m_Team_Info.Type = (int)PacketType.로그인정보확인;
this.m_Team_Info.t_name = team_name;
Packet.Serialize(this.m_Team_Info).CopyTo(this.sendBuf, 0);
this.m_networkstream = m_client.GetStream();
this.Send();
this.Recv();
Packet pack = (Packet)Packet.Deserialize(this.readBuf);
if ((int)pack.Type == (int)PacketType.로그인정보확인)
{
this.m_Team_Info = (Team_Info)Packet.Deserialize(this.readBuf);
this.team_pw = this.m_Team_Info.pw;
this.st_count = this.m_Team_Info.stCount;
}
//서버한테 해당 팀이름 정보 보내주기
this.m_Student_ID = new Student_ID();
this.m_Student_ID.Type = (int)PacketType.학번정보;
this.m_Student_ID.t_name = team_name;
Packet.Serialize(this.m_Student_ID).CopyTo(this.sendBuf, 0);
this.m_networkstream = m_client.GetStream();
this.Send();
for (int i = 0; i < st_count; i++)
{
//해당 팀의 팀원수 만큼 학번 정보 불러오기
this.Recv();
pack = (Packet)Packet.Deserialize(this.readBuf);
if ((int)pack.Type == (int)PacketType.학번정보)
{
this.m_Student_ID = (Student_ID)Packet.Deserialize(this.readBuf);
this.student_List.Add(this.m_Student_ID.s_id);
switch (i)
{
case 0:
SI_txtBox1.Text = student_List[i];
id_chk1.Image = soccerForm.Properties.Resources.change;
SI_txtBox1.Enabled = true;
break;
case 1:
SI_txtBox2.Text = student_List[i];
id_chk2.Image = soccerForm.Properties.Resources.change;
SI_txtBox2.Enabled = true;
break;
case 2:
SI_txtBox3.Text = student_List[i];
id_chk3.Image = soccerForm.Properties.Resources.change;
SI_txtBox3.Enabled = true;
break;
case 3:
SI_txtBox4.Text = student_List[i];
id_chk4.Image = soccerForm.Properties.Resources.change;
SI_txtBox4.Enabled = true;
break;
case 4:
SI_txtBox5.Text = student_List[i];
id_chk5.Image = soccerForm.Properties.Resources.change;
SI_txtBox5.Enabled = true;
break;
}
}
}
}
