public int AddStudentSubject(StudentSubject studentSubject) { int returnValue = 0; //create query here //string query = string.Format("insert into WAE_STUDENT_SUBJECT (subject_code,assignment_name, subject_name, description, user_id, assignment) values('{0}','{1}','{2}','{3}',{4},{5})", studentSubject.SubjectCode, studentSubject.AssignmentName, studentSubject.SubjectName, studentSubject.SubjectDescription, studentSubject.UserId, studentSubject.Assignment); try { Connection.Open(); MySqlCommand command = new MySqlCommand("insert into WAE_STUDENT_SUBJECT (subject_code,assignment_name, subject_name, description, user_id, assignment, assignment_size) values(@subject_code,@assignment_name,@subject_name,@description,@user_id,@assignment,@assignment_size)", Connection); command.Parameters.Add("@subject_code", MySqlDbType.VarChar).Value = studentSubject.SubjectCode; command.Parameters.Add("@assignment_name", MySqlDbType.VarChar).Value = studentSubject.AssignmentName; command.Parameters.Add("@subject_name", MySqlDbType.VarChar).Value = studentSubject.SubjectName; command.Parameters.Add("@description", MySqlDbType.VarChar).Value = studentSubject.SubjectDescription; command.Parameters.Add("@user_id", MySqlDbType.Int32).Value = studentSubject.UserId; command.Parameters.Add("@assignment", MySqlDbType.Blob).Value = studentSubject.Assignment; command.Parameters.Add("@assignment_size", MySqlDbType.Int32).Value = studentSubject.AssignmentSize; returnValue = command.ExecuteNonQuery(); } catch (Exception ex) { //throw ex; //log it the ex } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } return returnValue; }
public List<StudentSubject> GetStudentSubjects(User user) { List<StudentSubject> studentSubjects = new List<StudentSubject>(); try { Connection.Open(); MySqlCommand command = new MySqlCommand("select student_subject_id,subject_code,assignment_name, subject_name, description, user_id from WAE_STUDENT_SUBJECT where user_id=@user_id", Connection); command.Parameters.Add("@user_id", MySqlDbType.Int32).Value = user.UserId; MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { StudentSubject record = new StudentSubject { UserId = reader.GetInt32("user_id"), SubjectName = reader.GetString("subject_name"), SubjectDescription = reader.GetString("description"), SubjectCode = reader.GetString("subject_code"), AssignmentName = reader.GetString("assignment_name"), StudentSubjectId = reader.GetInt32("student_subject_id") }; studentSubjects.Add(record); } } catch (Exception ex) { //throw ex; //log it the ex } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } return studentSubjects; }
public StudentSubject GetStudentSubject(StudentSubject studentSubjectData) { StudentSubject studentSubject = null; try { Connection.Open(); // assignment,student_subject_id,subject_code,assignment_name, subject_name, description, user_id MySqlCommand command = new MySqlCommand("select student_subject_id, assignment_size, assignment from WAE_STUDENT_SUBJECT where student_subject_id=@student_subject_id", Connection); command.Parameters.Add("@student_subject_id", MySqlDbType.Int32).Value = studentSubjectData.StudentSubjectId; MySqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess); while (reader.Read()) { //studentSubject = new StudentSubject { UserId = reader.GetInt32("user_id"), SubjectName = reader.GetString("subject_name"), SubjectDescription = reader.GetString("description"), SubjectCode = reader.GetString("subject_code"), AssignmentName = reader.GetString("assignment_name"), StudentSubjectId = reader.GetInt32("student_subject_id") }; studentSubject = new StudentSubject { StudentSubjectId = reader.GetInt32("student_subject_id"), AssignmentSize = reader.GetInt32("assignment_size") }; //studentSubject.Assignment = new byte[studentSubject.AssignmentSize]; //studentSubject.Assignment = (byte[])reader.GetValue(2); int fileSize = studentSubject.AssignmentSize; // read the blob field byte[] tempArray = new byte[fileSize]; MemoryStream stream = new MemoryStream(); BinaryWriter writer = new BinaryWriter(stream); reader.GetBytes(2, 0, tempArray, 0, (int)fileSize); writer.Write(tempArray, 0, (int)fileSize); studentSubject.Assignment = stream.ToArray(); stream.Close(); reader.Close(); } } catch (Exception ex) { //throw ex; //log it the ex } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } return studentSubject; }