//returns all subtasks with the subtaskFKey to match the parameter taskId public Dictionary <int, SubTask> FetchAllSubTasks(int taskId) { Dictionary <int, SubTask> returnedSubTasks = new Dictionary <int, SubTask>(); SubTask tempSubTask; MySqlConnection conn = null; try { conn = new MySqlConnection(connectionString); conn.Open(); MySqlCommand cmd = new MySqlCommand("SELECT * FROM `Subtask` WHERE `subtaskFKey` = @taskId", conn); cmd.Parameters.Add(new MySqlParameter("subtaskFKey", taskId)); MySqlDataReader reader = cmd.ExecuteReader(); int count = reader.FieldCount; while (reader.Read()) { for (int i = 0; i < count; i++) { tempSubTask = new SubTask(); SubTask temp = FetchSubTask(((int)reader.GetValue(i))); temp.setId((int)reader.GetValue(0)); //subtaskId temp.setDueDate((DateTime)reader.GetValue(1)); //dueDate temp.setTitle((String)reader.GetValue(2)); //title temp.setNotes((String)reader.GetValue(3)); //description //TODO THIS WON'T WORK //TODO THIS WON'T WORK temp.setRepeatFrom((DateTime)reader.GetValue(5)); //repeatFrom temp.setTaskComplete((Boolean)reader.GetValue(6)); //isComplete temp.setSubtaskFKey((int)reader.GetValue(7)); //subTaskFKey returnedSubTasks.Add(((int)reader.GetValue(i)), temp); } } //Console.WriteLine("Result: Success!"); } catch (Exception ex) { Console.WriteLine("fetchAllSubTasks: Failure!" + ex); } finally { if (conn != null) { conn.Close(); } } return(returnedSubTasks); }
//Returns a Subtask object for the passed in id public SubTask FetchSubTask(int SubtaskId) { MySqlConnection conn = null; SubTask mySubTask = new SubTask(); if (CheckSubTaskExistsInDB(SubtaskId)) { try { conn = new MySqlConnection(connectionString); conn.Open(); MySqlCommand cmd = new MySqlCommand("SELECT* from `Subtask` WHERE `subtaskId` = @SubtaskId", conn); cmd.Parameters.Add(new MySqlParameter("SubtaskId", SubtaskId)); MySqlDataReader reader = cmd.ExecuteReader(); int count = reader.FieldCount; //Database columns: //subtaskId dueDate title description filePath repeatFrom isComplete subtaskFKey mySubTask.setId((int)reader.GetValue(0)); //subtaskId mySubTask.setDueDate((DateTime)reader.GetValue(1)); //dueDate mySubTask.setTitle((String)reader.GetValue(2)); //title mySubTask.setNotes((String)reader.GetValue(3)); //description //TODO THIS WON'T WORK //TODO THIS WON'T WORK mySubTask.setRepeatFrom((DateTime)reader.GetValue(5)); //repeatFrom mySubTask.setTaskComplete((Boolean)reader.GetValue(6)); //isComplete mySubTask.setSubtaskFKey((int)reader.GetValue(7)); //subTaskFKey //Console.WriteLine("Result: Success!"); } catch (Exception ex) { Console.WriteLine("fetchSubTask: Failure!" + ex); } finally { if (conn != null) { conn.Close(); } } } return(mySubTask); }