public static List <Students> SearchStudentByNum(int stuNum)
{
List <Students> stuList = new List <Students>();
Students student = new Students();
SqlConnection conn = UtilityDB.GetConn();
conn.Open();
string sqlCommandSearchStudentByNum = "select * from Students where studentNumber=" + stuNum + ";";
SqlCommand sqlcommand = new SqlCommand(sqlCommandSearchStudentByNum, conn);
SqlDataReader sqlReader = sqlcommand.ExecuteReader();
if (sqlReader.Read())
{
student.FirstName = sqlReader["FirstName"].ToString();
student.LastName = sqlReader["LastName"].ToString();
student.StudentNumber = Convert.ToInt32(sqlReader["StudentNumber"].ToString());
student.Email = sqlReader["Email"].ToString();
}
else
{
student = null;
}
stuList.Add(student);
return(stuList);
}
public static List <Students> GetStudentList()
{
List <Students> userList = new List <Students>();
Students student = new Students();
//connect to db
SqlConnection conn = UtilityDB.GetConn();
conn.Open();
//selected query
string selectedStudent = "SELECT * From Students";
SqlCommand sqlcommandStudent = new SqlCommand(selectedStudent, conn);
SqlDataReader sqlreaderStudent = sqlcommandStudent.ExecuteReader();
while (sqlreaderStudent.Read())
{
student = new Students();
student.StudentNumber = Convert.ToInt32(sqlreaderStudent["StudentNumber"]);
student.FirstName = sqlreaderStudent["FirstName"].ToString();
student.LastName = sqlreaderStudent["LastName"].ToString();
student.Email = (sqlreaderStudent["Email"]).ToString();
userList.Add(student);
//MessageBox.Show(student.FirstName);
}
conn.Close();
return(userList);
}
//list all projects and return to dropdown list
public static List <Projects> SearchAllProject()
{
List <Projects> projList = new List <Projects>();
SqlConnection conn = UtilityDB.GetConn();
conn.Open();
string sqlCommandSearchAllProject = "select * from Projects;";
SqlCommand sqlcommand = new SqlCommand(sqlCommandSearchAllProject, conn);
SqlDataReader sqlReader = sqlcommand.ExecuteReader();
while (sqlReader.Read())
{
Projects project = new Projects();
project.ProjectCode = sqlReader["ProjectCode"].ToString();
project.ProjectTitle = sqlReader["ProjectTitle"].ToString();
project.DueDate = Convert.ToDateTime(sqlReader["DueDate"]);
projList.Add(project);
}
return(projList);
}
public static List <Projects> SearchProjectByCode(string projCode)
{
List <Projects> projList = new List <Projects>();
Projects project = new Projects();
SqlConnection conn = UtilityDB.GetConn();
conn.Open();
string sqlCommandSearchProjectByCode = "select * from Projects where ProjectCode='" + projCode + "'";
SqlCommand sqlcommand = new SqlCommand(sqlCommandSearchProjectByCode, conn);
SqlDataReader sqlReader = sqlcommand.ExecuteReader();
if (sqlReader.Read())
{
project.ProjectCode = sqlReader["ProjectCode"].ToString();
project.ProjectTitle = sqlReader["ProjectTitle"].ToString();
project.DueDate = Convert.ToDateTime(sqlReader["DueDate"]);
projList.Add(project);
}
return(projList);
}
public static void AssignProject(int stuId, string projCode)
{
try
{
if (ListAssignmentByStudents(stuId).Count < 4)
{
//Students student = new Students();
//Projects project = new Projects();
SqlConnection conn = UtilityDB.GetConn();
conn.Open();
string sqlcmdAssignProject = "Insert into Assignments Values (@studentid,@projectcode,@grade,@date);";
SqlCommand sqlcmd = new SqlCommand(sqlcmdAssignProject, conn);
//sqlcmd.Parameters.AddWithValue("assid", RandomNumber(0,1000));
sqlcmd.Parameters.AddWithValue("studentid", stuId);
sqlcmd.Parameters.AddWithValue("projectcode", projCode);
sqlcmd.Parameters.AddWithValue("grade", RandomNumber(0, 100));
sqlcmd.Parameters.AddWithValue("date", DateTime.Today);
int success = sqlcmd.ExecuteNonQuery();
if (success >= 1)
{
MessageBox.Show("insert successfully");
}
else
{
MessageBox.Show("insert fail");
}
conn.Close();
}
else
{
MessageBox.Show("one student can only have up to 3 projects!");
}
}
catch
{
MessageBox.Show("this student has already been assigned to this project");
}
}
public static List <Assignments> ListAssignmentByStudents(int stuId)
{
List <Assignments> assignmentList = new List <Assignments>();
SqlConnection conn = UtilityDB.GetConn();
conn.Open();
String query = "select * from assignments where StudentId=@stuid";
SqlCommand sqlcommand = new SqlCommand(query, conn);
sqlcommand.Parameters.AddWithValue("stuid", stuId);
SqlDataReader reader = sqlcommand.ExecuteReader();
while (reader.Read())
{
Assignments ass = new Assignments();
ass.AssignmentId = Convert.ToInt32(reader["AssignmentId"]);
ass.StudentId = Convert.ToInt32(reader["StudentId"]);
ass.ProjectId = reader["ProjectId"].ToString();
ass.AssignmentDate = Convert.ToDateTime(reader["AssignmentDate"]);
ass.Grade = Convert.ToInt32(reader["Grade"]);
assignmentList.Add(ass);
}
return(assignmentList);
}