public static Student Login(int id, string pass)
{
Student student = null;
string sql = String.Format("SELECT * FROM Students Where StudentID='{0}' and Pass='******'", id, pass);
using (SqlConnection conn = UtilityDB.ConnDB())
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
student = new Student();
student.StudentNumber = Convert.ToInt32(reader["StudentID"]);
student.FirstName = reader["FirstName"].ToString();
student.LastName = reader["LastName"].ToString();
student.Email = reader["Email"].ToString();
student.Phone = reader["HomePhone"].ToString();
}
}
reader.Close();
}
}
}
return(student);
}
public static List <Course> GetStudentCourses(int id)
{
List <Course> listOfCourses = new List <Course>();
Course course;
string sql = String.Format("SELECT * FROM Courses WHERE CourseNumber IN (SELECT CourseNumber FROM StudentCourse WHERE StudentID = {0})", id);
using (SqlConnection conn = UtilityDB.ConnDB())
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
course = new Course();
course.CourseNumber = reader["CourseNumber"].ToString();
course.Title = reader["CourseTitle"].ToString();
course.Duration = reader["Duration"].ToString();
course.Prerequisite = reader["Prerequisite"].ToString();
listOfCourses.Add(course);
}
}
else
{
listOfCourses = null;
}
reader.Close();
}
}
conn.Close();
}
return(listOfCourses);
}
public static Course GetCourse(string id)
{
string sql = String.Format("Select * From Courses Where CourseNumber='{0}'", id);
Course course = null;
using (SqlConnection conn = UtilityDB.ConnDB())
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
course = new Course();
course.CourseNumber = reader["CourseNumber"].ToString();
course.Title = reader["CourseTitle"].ToString();
course.Duration = reader["Duration"].ToString();
course.Prerequisite = reader["Prerequisite"].ToString();
}
}
reader.Close();
}
}
conn.Close();
}
return(course);
}
public static void Register(Student st)
{
string sql = String.Format("insert into Students values({0},'{1}','{2}','{3}','{4}','{5}')", st.StudentNumber, st.FirstName, st.LastName, st.Email, st.Phone, st.Password);
using (SqlConnection conn = UtilityDB.ConnDB())
{
using (SqlCommand cmd2 = new SqlCommand(sql, conn))
{
cmd2.ExecuteNonQuery();
MessageBox.Show("Registered");
}
}
}
public static void SelectCourse(Course course, int stuID)
{
string sql = String.Format("INSERT INTO StudentCourse VALUES('{0}','{1}')", stuID, course.CourseNumber);
using (SqlConnection conn = UtilityDB.ConnDB())
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public static List <Student> GetAllStudents()
{
List <Student> listOfStudents = new List <Student>();
Student student;
string sql = String.Format("SELECT * FROM Students");
using (SqlConnection conn = UtilityDB.ConnDB())
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
student = new Student();
student.StudentNumber = Convert.ToInt32(reader["StudentNumber"]);
student.FirstName = reader["FirstName"].ToString();
student.LastName = reader["LastName"].ToString();
student.Email = reader["Email"].ToString();
student.Phone = reader["HomePhone"].ToString();
listOfStudents.Add(student);
}
}
else
{
listOfStudents = null;
}
reader.Close();
}
}
conn.Close();
}
return(listOfStudents);
}