public static void InsertProject(string name, string description, int max_grade, string course_id, DateTime due_date)
{
using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
{
try
{
con.Open();
string project_id = RegNum.getNextValue("Project");
string sql = "INSERT INTO Projects VALUES(@id, @name, @description, @max_grade, @course_id, @due_date)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("id", project_id);
cmd.Parameters.AddWithValue("name", name);
cmd.Parameters.AddWithValue("description", description);
cmd.Parameters.AddWithValue("max_grade", max_grade);
cmd.Parameters.AddWithValue("course_id", course_id);
cmd.Parameters.AddWithValue("due_date", due_date);
cmd.ExecuteNonQuery();
foreach (Team team in GetTeams(course_id))
{
string sql_temp = "INSERT INTO ProjectsOfTeam VALUES(@project_id, null, @team_id, null)";
NpgsqlCommand cmd_temp = new NpgsqlCommand(sql_temp, con);
cmd_temp.Parameters.AddWithValue("project_id", project_id);
cmd_temp.Parameters.AddWithValue("team_id", team.getTeamID());
cmd_temp.ExecuteNonQuery();
}
}
catch (Exception msg)
{
MessageBox.Show("There was a problem while executing this action. Please contact the developers.");
}
}
}
public static void InsertUser(string userType, string name, string password, string surname, Email email)
{
using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
{
try
{
con.Open();
string sql = "INSERT INTO Users VALUES(@reg_num, @name, @password, @surname, @email)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, con);
RegNum regNum = new RegNum(RegNum.getNextValue(userType));
cmd.Parameters.AddWithValue("reg_num", regNum.getRegNumString());
cmd.Parameters.AddWithValue("name", name);
cmd.Parameters.AddWithValue("password", password);
cmd.Parameters.AddWithValue("surname", surname);
cmd.Parameters.AddWithValue("email", email.getEmailAddress());
cmd.ExecuteNonQuery();
sql = "INSERT INTO " + userType + "s VALUES(@reg_num)";
cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("reg_num", regNum.getRegNumString());
cmd.ExecuteNonQuery();
}
catch (Exception msg)
{
MessageBox.Show("There was a problem while executing this action. Please contact the developers.");
return;
}
}
}
public static void CreateTeam(List <string> students, string course_id)
{
using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
{
try
{
con.Open();
string team_id = RegNum.getNextValue("Team");
string sql = "INSERT INTO teams VALUES(@team_id)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("team_id", team_id);
cmd.ExecuteNonQuery();
foreach (string stu_reg_num in students)
{
sql = "INSERT INTO StudentsTeams VALUES(@stu_reg_num, @team_id, @course_id)";
cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("stu_reg_num", stu_reg_num);
cmd.Parameters.AddWithValue("team_id", team_id);
cmd.Parameters.AddWithValue("course_id", course_id);
cmd.ExecuteNonQuery();
}
foreach (Project proj in GetProjectsForCourse(course_id))
{
sql = "INSERT INTO ProjectsOfTeam VALUES(@project_id, null, @team_id, null)";
cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("project_id", proj.getProjectID());
cmd.Parameters.AddWithValue("team_id", team_id);
cmd.ExecuteNonQuery();
}
}
catch (Exception msg)
{
MessageBox.Show("There was a problem connecting to the server.");
}
}
}
public static void UploadProject(byte[] file, string name, DateTime date, string team_id, string project_id)
{
using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
{
try
{
con.Open();
string sql = "SELECT project_file_id FROM Projectsofteam WHERE team_id=@team_id AND project_id=@project_id";
NpgsqlCommand cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("team_id", team_id);
cmd.Parameters.AddWithValue("project_id", project_id);
NpgsqlDataReader results = cmd.ExecuteReader();
string file_id = null;
if (results.Read())
{
file_id = results[0].ToString();
}
else
{
MessageBox.Show("Could not upload file");
return;
}
results.Close();
if (string.IsNullOrEmpty(file_id))
{
string id = RegNum.getNextValue("projectfile");
sql = "INSERT INTO projectfiles VALUES(@id, @file, @name, @date)";
NpgsqlCommand cmd2 = new NpgsqlCommand(sql, con);
cmd2.Parameters.AddWithValue("id", id);
cmd2.Parameters.AddWithValue("file", file);
cmd2.Parameters.AddWithValue("name", name);
cmd2.Parameters.AddWithValue("date", date);
cmd2.ExecuteNonQuery();
sql = "UPDATE projectsofteam SET project_file_id=@project_file_id WHERE team_id=@team_id AND project_id=@project_id";
NpgsqlCommand cmd3 = new NpgsqlCommand(sql, con);
cmd3.Parameters.AddWithValue("project_file_id", id);
cmd3.Parameters.AddWithValue("team_id", team_id);
cmd3.Parameters.AddWithValue("project_id", project_id);
cmd3.ExecuteNonQuery();
}
else
{
sql = "UPDATE projectfiles SET (file, name, date)=(@file, @name, @date) WHERE id=@id";
NpgsqlCommand cmd4 = new NpgsqlCommand(sql, con);
cmd4.Parameters.AddWithValue("file", file);
cmd4.Parameters.AddWithValue("name", name);
cmd4.Parameters.AddWithValue("id", file_id);
cmd4.Parameters.AddWithValue("date", date);
cmd4.ExecuteNonQuery();
}
}
catch (Exception msg)
{
MessageBox.Show("There was a problem while executing this action. Please contact the developers.");
}
}
}