public static void InsertStudentperCourse()
{
Select.selectCourses();
Console.WriteLine("Choose one course id: ");
int courseid = Convert.ToInt32(Console.ReadLine());
Select.selectStudents();
Console.WriteLine("Choose one student id: ");
int studentid = Convert.ToInt32(Console.ReadLine());
//INSERT STUDENT_COURSE
string connectionString = @"Data Source=DESKTOP-JBG6OHV\SQLEXPRESS;Initial Catalog=SCHOOL;Integrated Security=True";
string sqlqr = "INSERT INTO COURSE_STUDENT(CID,SID) VALUES(@cid,@sid)";
string sqlqrtest = "SELECT COUNT(*) FROM COURSE_STUDENT WHERE CID=@idc AND SID=@ids";
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
SqlCommand cmfinder = new SqlCommand(sqlqrtest, cn);
cmfinder.Parameters.Add(new SqlParameter("idc", courseid));
cmfinder.Parameters.Add(new SqlParameter("ids", studentid));
int count = (int)cmfinder.ExecuteScalar();
if (count >= 1)
{
throw new Exception("This id already exists.");
}
SqlCommand cm = new SqlCommand(sqlqr, cn);
cm.Parameters.Add(new SqlParameter("cid", courseid));
cm.Parameters.Add(new SqlParameter("sid", studentid));
cm.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
public static void InsertTrainer()
{
//INSERT TRAINER & INSERT TRAINERS PER COURSE
string connectionString = @"Data Source=DESKTOP-JBG6OHV\SQLEXPRESS;Initial Catalog=SCHOOL;Integrated Security=True";
string sqlqr = "INSERT INTO TRAINER (IDTRAINER,FIRSTNAME,LASTNAME,SUBJECT,COURSEID) VALUES(@id,@fname,@lname,@subject,@idc)";
string sqlqr1 = "INSERT INTO TRAINER (IDTRAINER,FIRSTNAME,LASTNAME,SUBJECT) VALUES(@id1,@fname1,@lname1,@subject1)";
string sqlqrtest = "SELECT COUNT(*) FROM TRAINER WHERE IDTRAINER= @id2";
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
Console.WriteLine("If you want to pair a trainer with a course press 1 else press 2:");
int option = Convert.ToInt32(Console.ReadLine());
if (option == 1)
{
Console.WriteLine("Enter Trainer ID: ");
int id = Convert.ToInt32(Console.ReadLine());
SqlCommand cmfinder = new SqlCommand(sqlqrtest, cn);
cmfinder.Parameters.Add(new SqlParameter("id2", id));
int count = (int)cmfinder.ExecuteScalar();
if (count >= 1)
{
throw new Exception("This id already exists.");
}
Console.WriteLine("Enter firstname: ");
string fname = Console.ReadLine();
Console.WriteLine("Enter lastname: ");
string lname = Console.ReadLine();
Console.WriteLine("Enter subject: ");
string subject = Console.ReadLine();
Select.selectCourses();
Console.WriteLine();
Console.WriteLine();
Console.WriteLine("Enter Course id: ");
int idC = Convert.ToInt32(Console.ReadLine());
SqlCommand cm = new SqlCommand(sqlqr, cn);
cm.Parameters.Add(new SqlParameter("@id", id));
cm.Parameters.Add(new SqlParameter("fname", fname));
cm.Parameters.Add(new SqlParameter("lname", lname));
cm.Parameters.Add(new SqlParameter("subject", subject));
cm.Parameters.Add(new SqlParameter("idc", idC));
cm.ExecuteNonQuery();
}
else if (option == 2)
{
Console.WriteLine("Enter Trainer ID: ");
int id1 = Convert.ToInt32(Console.ReadLine());
SqlCommand cmfinder = new SqlCommand(sqlqrtest, cn);
cmfinder.Parameters.Add(new SqlParameter("id2", id1));
int count = (int)cmfinder.ExecuteScalar();
if (count >= 1)
{
throw new Exception("This id already exists.");
}
Console.WriteLine("Enter firstname: ");
string fname1 = Console.ReadLine();
Console.WriteLine("Enter lastname: ");
string lname1 = Console.ReadLine();
Console.WriteLine("Enter subject: ");
string subject1 = Console.ReadLine();
SqlCommand cm = new SqlCommand(sqlqr1, cn);
cm.Parameters.Add(new SqlParameter("id1", id1));
cm.Parameters.Add(new SqlParameter("fname1", fname1));
cm.Parameters.Add(new SqlParameter("lname1", lname1));
cm.Parameters.Add(new SqlParameter("subject1", subject1));
cm.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
static void Main(string[] args)
{
int option = 0;
while (option != 3)
{
Console.WriteLine();
Console.WriteLine("-------SCHOOL MENU-------");
Console.WriteLine("Choose 1-3 for: ");
Console.WriteLine("1.View data");
Console.WriteLine("2.Insert data");
Console.WriteLine("3.Exit");
Console.WriteLine();
option = Convert.ToInt32(Console.ReadLine());
switch (option)
{
case 1:
option = 0;
Console.WriteLine();
Console.WriteLine("------------view data-------------");
Console.WriteLine("Choose 1-9 for: ");
Console.WriteLine("1.Student");
Console.WriteLine("2.Trainer");
Console.WriteLine("3.Assigment");
Console.WriteLine("4.Course");
Console.WriteLine("5.Students per course");
Console.WriteLine("6.Trainers per course");
Console.WriteLine("7.Assigments per course");
Console.WriteLine("8.Assigments per student");
Console.WriteLine("9.Students that belongs to more than one course");
Console.WriteLine();
option = Convert.ToInt32(Console.ReadLine());
switch (option)
{
case (1):
Select.selectStudents();
break;
case (2):
Select.selectTrainers();
break;
case (3):
option = 0;
Select.selectAssignments();
break;
case (4):
Select.selectCourses();
break;
case (5):
Select.selectStudentsperCourse();
break;
case (6):
Select.selectTrainersperCourse();
break;
case (7):
Select.selectAssignmentsperCourse();
break;
case (8):
Select.selectAssignmentsperCourseperStudent();
break;
case (9):
Select.selectStudentsBelongsToMoreThanOneCourse();
break;
default:
Console.WriteLine("Something went wrong");
break;
}
break;
case 2:
option = 0;
Console.WriteLine();
Console.WriteLine("------------insert data-------------");
Console.WriteLine("Choose 1-7 for: ");
Console.WriteLine("1.Student");
Console.WriteLine("2.Trainer");
Console.WriteLine("3.Assigment");
Console.WriteLine("4.Course");
Console.WriteLine("5.Students per course");
Console.WriteLine("6.Trainers per course");
Console.WriteLine("7.Assigments per student");
Console.WriteLine();
option = Convert.ToInt32(Console.ReadLine());
switch (option)
{
case (1):
Insert.InsertStudent();
break;
case (2):
Insert.InsertTrainer();
break;
case (3):
option = 0;
Insert.InsertAssignment();
break;
case (4):
Insert.InsertCourse();
break;
case (5):
Insert.InsertStudentperCourse();
break;
case (6):
Insert.InsertTrainer();
break;
case (7):
Insert.InsertAssignmentperStudentperCourse();
break;
default:
Console.WriteLine("Something went wrong");
break;
}
break;
}
}
}