public void Update(string newCourseName, string newCourseNumber)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"UPDATE courses SET course_name = @newCourseName, course_number = @newCourseNumber WHERE id = @searchId;";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = _id;
cmd.Parameters.Add(searchId);
MySqlParameter courseName = new MySqlParameter();
courseName.ParameterName = "@newCourseName";
courseName.Value = newCourseName;
cmd.Parameters.Add(courseName);
MySqlParameter courseNumber = new MySqlParameter();
courseNumber.ParameterName = "@newCourseNumber";
courseNumber.Value = newCourseNumber;
cmd.Parameters.Add(courseNumber);
cmd.ExecuteNonQuery();
_courseName = newCourseName;
_courseNumber = newCourseNumber;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public List <Student> GetStudents()
{
List <Student> newStudentList = new List <Student> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT students.* FROM courses
JOIN schedule ON (courses.id = schedule.course_id)
JOIN students ON (schedule.student_id = students.id)
WHERE courses.id = @CourseId;";
MySqlParameter course_id = new MySqlParameter();
course_id.ParameterName = "@CourseId";
course_id.Value = _id;
cmd.Parameters.Add(course_id);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
string name = rdr.GetString(1);
string grade = rdr.GetString(2);
DateTime date = rdr.GetDateTime(3);
Student newStudent = new Student(name, grade, date);
newStudentList.Add(newStudent);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(newStudentList);
}
public void Save()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO students (name, grade, enrollment_date) VALUES (@name, @grade, @enrollment_date);";
MySqlParameter name = new MySqlParameter();
name.ParameterName = "@name";
name.Value = this._name;
cmd.Parameters.Add(name);
MySqlParameter grade = new MySqlParameter();
grade.ParameterName = "@grade";
grade.Value = this._grade;
cmd.Parameters.Add(grade);
MySqlParameter enrollment_date = new MySqlParameter();
enrollment_date.ParameterName = "@enrollment_date";
enrollment_date.Value = this._enrollment_date;
cmd.Parameters.Add(enrollment_date);
cmd.ExecuteNonQuery();
_id = (int)cmd.LastInsertedId;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public static void Delete(int searchId = 0)
{
if (searchId > 0)
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = @"DELETE FROM courses WHERE id = @thisId; DELETE FROM students_courses WHERE course_id = @thisId;";
MySqlParameter thisId = new MySqlParameter();
thisId.ParameterName = "@thisId";
thisId.Value = searchId;
cmd.Parameters.Add(thisId);
cmd.ExecuteNonQuery();
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
}
public List <Student> GetAllStudents()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT students.* FROM courses
JOIN students_courses ON (courses.id = students_courses.course_id)
JOIN students ON (students_courses.student_id = students.id)
WHERE courses.id = @CourseId;";
cmd.Parameters.Add(new MySqlParameter("@CourseId", _id));
var rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Student> allStudents = new List <Student> {
};
while (rdr.Read())
{
int StudentId = rdr.GetInt32(0);
string StudentName = rdr.GetString(1);
string StudentEnrollment = rdr.GetString(2);
Student newStudent = new Student(StudentName, StudentEnrollment, StudentId);
allStudents.Add(newStudent);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allStudents);
}
public void AddDepartment(Department newDepartment)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO courses_departments (course_id, department_id) VALUES (@courseId, @departmentId);";
MySqlParameter course_id = new MySqlParameter();
course_id.ParameterName = "@courseId";
course_id.Value = _id;
cmd.Parameters.Add(course_id);
MySqlParameter department_id = new MySqlParameter();
department_id.ParameterName = "@departmentId";
department_id.Value = newDepartment.GetId();
cmd.Parameters.Add(department_id);
cmd.ExecuteNonQuery();
conn.Close();
}
public void Update(string newName, string newEnrollment)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"UPDATE students SET name = @StudentName, enrollment = @StudentEnrollment WHERE id = @StudentId;";
cmd.Parameters.Add(new MySqlParameter("@StudentId", _id));
cmd.Parameters.Add(new MySqlParameter("@StudentName", newName));
cmd.Parameters.Add(new MySqlParameter("@StudentEnrollment", newEnrollment));
cmd.ExecuteNonQuery();
_name = newName;
_enrollment = newEnrollment;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public List <Course> GetAllCourses()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT courses.* FROM students
JOIN students_courses ON (students.id = students_courses.student_id)
JOIN courses ON (students_courses.course_id = courses.id)
WHERE students.id = @StudentId;";
cmd.Parameters.Add(new MySqlParameter("@StudentId", _id));
var rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Course> allCourses = new List <Course> {
};
while (rdr.Read())
{
int CourseId = rdr.GetInt32(0);
string CourseName = rdr.GetString(1);
string CourseCode = rdr.GetString(2);
Course newCourse = new Course(CourseName, CourseCode, CourseId);
allCourses.Add(newCourse);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allCourses);
}
public static Student Find(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM students WHERE id = (@searchId);";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = id;
cmd.Parameters.Add(searchId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
int studentId = 0;
string studentName = "";
while (rdr.Read())
{
studentId = rdr.GetInt32(0);
studentName = rdr.GetString(1);
}
// Constructor below no longer includes a itemCategoryId parameter:
Student newStudent = new Student(studentName, studentId);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(newStudent);
}
public List <Student> GetStudents()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT students.* FROM courses
JOIN course_info ON (courses.course_id = course_info.course_id)
JOIN students ON (course_info.student_id = students.student_id)
WHERE courses.course_id = @CourseId;";
MySqlParameter courseIdParameter = new MySqlParameter();
courseIdParameter.ParameterName = "@CourseId";
courseIdParameter.Value = _course_id;
cmd.Parameters.Add(courseIdParameter);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
List <int> studentIds = new List <int> {
};
while (rdr.Read())
{
int studentId = rdr.GetInt32(0);
studentIds.Add(studentId);
}
rdr.Dispose();
List <Student> students = new List <Student> {
};
foreach (int studentId in studentIds)
{
var studentQuery = conn.CreateCommand() as MySqlCommand;
studentQuery.CommandText = @"SELECT * FROM students WHERE student_id = @StudentId;";
MySqlParameter studentIdParameter = new MySqlParameter();
studentIdParameter.ParameterName = "@StudentId";
studentIdParameter.Value = studentId;
studentQuery.Parameters.Add(studentIdParameter);
var studentQueryRdr = studentQuery.ExecuteReader() as MySqlDataReader;
while (studentQueryRdr.Read())
{
int thisStudentId = studentQueryRdr.GetInt32(0);
string studentName = studentQueryRdr.GetString(1);
string studentDate = studentQueryRdr.GetString(2);
Student foundStudent = new Student(studentName, studentDate, thisStudentId);
students.Add(foundStudent);
}
studentQueryRdr.Dispose();
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(students);
}