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;
}