public static void InsertAssignmentperStudentperCourse()
{
Select.selectAssignments();
Console.WriteLine("Choose one assignment id: ");
int assignmentid = Convert.ToInt32(Console.ReadLine());
Select.selectCourses();
Console.WriteLine("Choose one course id: ");
int courseid = Convert.ToInt32(Console.ReadLine());
string connectionString = @"Data Source=DESKTOP-JBG6OHV\SQLEXPRESS;Initial Catalog=SCHOOL;Integrated Security=True";
//INSERT ASSIGNMENT_COURSE
string sqlqrCA = "INSERT INTO COURSE_ASSIGNMENT (IDC,IDA) VALUES(@courid,@assid)";
string sqlqrtest1 = "SELECT COUNT(*) FROM COURSE_ASSIGNMENT WHERE IDC=@idc1 AND IDA=@ida1";
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
SqlCommand cmfinder = new SqlCommand(sqlqrtest1, cn);
cmfinder.Parameters.Add(new SqlParameter("idc1", courseid));
cmfinder.Parameters.Add(new SqlParameter("ida1", assignmentid));
int count = (int)cmfinder.ExecuteScalar();
if (count >= 1)
{
throw new Exception("This assignment per course already exists.");
}
SqlCommand cm = new SqlCommand(sqlqrCA, cn);
cm.Parameters.Add(new SqlParameter("courid", courseid));
cm.Parameters.Add(new SqlParameter("assid", assignmentid));
cm.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Select.selectStudents();
Console.WriteLine("Choose one student id: ");
int studentid = Convert.ToInt32(Console.ReadLine());
//INSERT STUDENT_COURSE
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 student per course 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);
}
}
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;
}
}
}
