public SubTask getSubTask(int inId) { SubTask task = new SubTask(); task.setId(inId); MySqlConnection conn = null; MySqlCommand command = null; MySqlDataReader reader = null; try{ conn = new MySqlConnection(connectionString); conn.Open(); command = new MySqlCommand( "SELECT * FROM `SubTask` WHERE `SubTaskId` = @inId;", conn); command.Parameters.AddWithValue("@inId", inId); reader = command.ExecuteReader(); reader.Read(); task.setDueDate((DateTime)reader.GetValue(1)); task.setTitle((String)reader.GetValue(4)); task.setNotes((String)reader.GetValue(5)); return(task); }catch (Exception ex) { Console.WriteLine("this is not supposed to happen: " + ex); return(null); } finally{ if (conn != null) { conn.Close(); } } }
//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); }