public static double getMonthlyMeetingTime(User currentUser, string date)//, int personal, int professional, int other) { double timeSpent = 0; SqlConnection connection = DBConnection.GetConnection(); string selectStatement = "SELECT sum(a.seconds) " + "FROM activities a " + "left join tasks t " + "on t.taskID = a.taskID " + "WHERE " + "t.task_owner = @user and " + "t.isMeeting = 1 and " + "cast(t.currentDate as date) >= dateadd(month, -1, @date) and " + "a.startTime >= dateadd(month, -1, @date)"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@user", currentUser.userID); selectCommand.Parameters.AddWithValue("@date", date); try { connection.Open(); if (selectCommand.ExecuteScalar() != DBNull.Value) { timeSpent = (Convert.ToDouble(selectCommand.ExecuteScalar()) / 60.0) / 60.0; } else { timeSpent = 0.0; } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(timeSpent); }
//Returns the password for the passed user public static String getPassword(string username) { string password = ""; SqlConnection connection = DBConnection.GetConnection(); string selectStatement = "SELECT enc_password " + "FROM users " + "WHERE username = @Username"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@Username", username); SqlDataReader reader = null; try { connection.Open(); reader = selectCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow); while (reader.Read()) { password = reader["enc_password"].ToString(); } } catch (SqlException) { throw; } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } if (reader != null) { reader.Close(); } } return(password); }
public static DataSet timeSpentReport(int id, DateTime start, DateTime end) { SqlConnection connection = DBConnection.GetConnection(); string selectStatement = "SELECT t.title, t.createdDate, SUM(a.seconds) AS Expr1, t.completed " + "FROM tasks t " + "JOIN activities a ON t.taskID = a.taskID " + "WHERE t.task_owner = @id AND a.startTime >= @start AND a.startTime <= @end " + "GROUP BY t.title, t.createdDate, t.completed"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@start", start); selectCommand.Parameters.AddWithValue("@end", end); selectCommand.Parameters.AddWithValue("@id", id); // Create a new data adapter based on the specified query. SqlDataAdapter dataAdapter = new SqlDataAdapter(selectStatement, connection); // Populate a new data table DataSet dataSet = new DataSet(); try { connection.Open(); SqlDataReader sdr = selectCommand.ExecuteReader(); dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture; dataSet.Tables.Add("tasks"); dataSet.Tables.Add("activities"); //Load DataReader into the DataTable. dataSet.Tables[0].Load(sdr); dataSet.Tables[0].Columns.Add(new DataColumn("Task_Type", typeof(string))); dataSet.Tables[0].Columns.Add(new DataColumn("TaskPriority", typeof(string))); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(dataSet); }
public static int updateTaskIncomplete(int taskID) { SqlConnection connection = DBConnection.GetConnection(); string updateStatement = "UPDATE tasks " + "set completed = 0, " + "currentDate = GETDATE() " + "WHERE taskID = @taskID"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@taskID", taskID); SqlDataReader reader = null; try { connection.Open(); // Returns the number of rows affected by this update return(updateCommand.ExecuteNonQuery()); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { if (connection != null) { connection.Close(); } if (reader != null) { reader.Close(); } } }
public static int updateSubtaskIncomplete(int subtaskID) { SqlConnection connection = DBConnection.GetConnection(); string updateStatement = "UPDATE subtask " + "set st_CompleteDate = @null " + "WHERE subtaskID = @subtaskID"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@subtaskID", subtaskID); updateCommand.Parameters.AddWithValue("@null", DBNull.Value); SqlDataReader reader = null; try { connection.Open(); // Returns the number of rows affected by this update return(updateCommand.ExecuteNonQuery()); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { if (connection != null) { connection.Close(); } if (reader != null) { reader.Close(); } } }
//Creates a new user in the database public static void createUser(string username, string fName, string lName, string email, string phone, string password, string hint) { SqlConnection connection = DBConnection.GetConnection(); string insertStatement = "INSERT users " + "(username, fName, lName, email, phone, enc_password, hint) " + "VALUES " + "(@username, @fName, @lName, @email, @phone, @password, @hint)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@username", username); insertCommand.Parameters.AddWithValue("@fName", fName); insertCommand.Parameters.AddWithValue("@lName", lName); insertCommand.Parameters.AddWithValue("@email", email); insertCommand.Parameters.AddWithValue("@phone", phone); insertCommand.Parameters.AddWithValue("@password", password); insertCommand.Parameters.AddWithValue("@hint", hint); try { connection.Open(); insertCommand.ExecuteNonQuery(); } catch (SqlException) { throw; } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } } }
public static int updateTaskAlreadyInActivities(int taskID, int timerSecs) { SqlConnection connection = DBConnection.GetConnection(); string updateStatement = "UPDATE activities set " + "seconds = seconds + @seconds " + "WHERE taskID = @taskID"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@seconds", timerSecs); updateCommand.Parameters.AddWithValue("@taskID", taskID); SqlDataReader reader = null; try { connection.Open(); return(updateCommand.ExecuteNonQuery()); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { if (connection != null) { connection.Close(); } if (reader != null) { reader.Close(); } } }
//Updates the password for the passed user to the passed password public static void updatePassword(string username, string password) { SqlConnection connection = DBConnection.GetConnection(); string updateStatement = "UPDATE users " + "SET enc_password = @Password " + "WHERE username = @Username"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@Username", username); updateCommand.Parameters.AddWithValue("@Password", password); SqlDataReader reader = null; try { connection.Open(); updateCommand.ExecuteNonQuery(); } catch (SqlException) { throw; } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } if (reader != null) { reader.Close(); } } }
//This will return the total time for tasks public static int getTotalTime(int userID) { int time; SqlConnection connection = DBConnection.GetConnection(); string selectStatement = "SELECT SUM(a.seconds) " + "FROM tasks t " + "JOIN activities a ON t.taskID = a.taskID " + "WHERE t.task_owner = @userID and " + "startTime > dateadd(day, -30, getdate()) " + "GROUP BY t.task_owner"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@userID", userID); try { connection.Open(); selectCommand.ExecuteNonQuery(); time = (int)selectCommand.ExecuteScalar(); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { if (connection != null) { connection.Close(); } } return(time); }
public static double getTimeSpentOnTask(int taskID) { int taskTime; SqlConnection connection = DBConnection.GetConnection(); string selectStatement = "SELECT sum(a.seconds) " + "FROM tasks t " + "JOIN activities a ON t.taskID = a.taskID " + "WHERE a.taskID = @taskID and " + "startTime > dateadd(day, -30, getdate()) " + "group by a.taskID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@taskID", taskID); try { connection.Open(); //selectCommand.ExecuteNonQuery(); taskTime = (int)selectCommand.ExecuteScalar(); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { if (connection != null) { connection.Close(); } } return(taskTime); }
/*Delete subtask by subtask id*/ public static void deleteSubTask(int subtaskID) { SqlConnection connection = DBConnection.GetConnection(); string deleteStatement = "DELETE FROM subtask " + "WHERE subtaskID = @subtaskID"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue("@subtaskID", subtaskID); SqlDataReader reader = null; try { connection.Open(); deleteCommand.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { if (connection != null) { connection.Close(); } if (reader != null) { reader.Close(); } } }
// Only update the task to the currentDate if it is not complete public static int updateIncompleteTasksToCurrentDate() { SqlConnection connection = DBConnection.GetConnection(); string updateStatement = "UPDATE tasks set " + "currentDate = GETDATE() " + "WHERE completed = 0 and " + "isMeeting = 0"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); SqlDataReader reader = null; try { connection.Open(); return(updateCommand.ExecuteNonQuery()); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { if (connection != null) { connection.Close(); } if (reader != null) { reader.Close(); } } }
public static List <Subtask> getSubtasksForTask(User currentUser, int taskID) { List <Subtask> subtasks = new List <Subtask>(); SqlConnection connection = DBConnection.GetConnection(); string selectStatement = "SELECT * FROM subtask " + "WHERE " + "taskID = @task " + "order by st_Deadline, st_Priority"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@user", currentUser.userID); selectCommand.Parameters.AddWithValue("@task", taskID); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { Subtask subtask = new Subtask(); subtask.taskID = Convert.ToInt32(reader["taskID"]); subtask.subtaskID = Convert.ToInt32(reader["subtaskID"]); subtask.st_Description = reader["st_Description"].ToString(); //task.completed = Convert.ToInt32(reader["completed"]); subtask.st_CreatedDate = (DateTime)reader["st_CreatedDate"]; if (reader["st_CompleteDate"] != DBNull.Value) { subtask.st_CompleteDate = (DateTime)reader["st_CompleteDate"]; } else { subtask.st_CompleteDate = DateTime.MaxValue; } if (reader["st_Deadline"] != DBNull.Value) { subtask.st_Deadline = (DateTime)reader["st_Deadline"]; } else { subtask.st_Deadline = DateTime.MaxValue; } if (reader["st_Priority"] != DBNull.Value) { subtask.st_Priority = Convert.ToInt32(reader["st_Priority"]); if (subtask.st_Priority == 1) { subtask.Priority = "High"; } else if (subtask.st_Priority == 2) { subtask.Priority = "Medium"; } else { subtask.Priority = "Low"; } } else { subtask.st_Priority = -1; } subtasks.Add(subtask); } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(subtasks); }
/*Delete task by task id*/ public static void deleteTask(int taskID) { //delete list subtasks of this task id SqlConnection connection = DBConnection.GetConnection(); connection.Open(); SqlTransaction sqlTransaction = connection.BeginTransaction(); string selectStatement = "SELECT * " + "FROM subtask WHERE taskID = @taskID "; SqlCommand selectCommand = new SqlCommand(selectStatement, connection, sqlTransaction); selectCommand.Parameters.AddWithValue("@taskID", taskID); SqlDataReader reader = null; try { List <int> listSubtasks = new List <int>(); reader = selectCommand.ExecuteReader(); while (reader.Read()) { int subtaskID = Convert.ToInt32(reader["subtaskID"]); listSubtasks.Add(subtaskID); } reader.Close(); //delete list of subtask of this task id for (int i = 0; i < listSubtasks.Count; i++) { //delete this subtask string deleteSubtaskStatement = "DELETE FROM subtask " + "WHERE subtaskID = @subtaskID"; SqlCommand deleteSubtaskCommand = new SqlCommand(deleteSubtaskStatement, connection, sqlTransaction); deleteSubtaskCommand.Parameters.AddWithValue("@subtaskID", listSubtasks[i]); deleteSubtaskCommand.ExecuteNonQuery(); } //final, delete this task string deleteStatement = "DELETE FROM tasks " + "WHERE taskID = @taskID"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection, sqlTransaction); deleteCommand.Parameters.AddWithValue("@taskID", taskID); deleteCommand.ExecuteNonQuery(); sqlTransaction.Commit(); } catch (SqlException ex) { sqlTransaction.Rollback(); throw ex; } catch (Exception ex) { sqlTransaction.Rollback(); throw ex; } finally { if (connection != null) { connection.Close(); } if (reader != null) { reader.Close(); } } }
/* Add this method to get the list of tasks have created date between fromDate and toDate with completed or not*/ public static DataSet GetListTaskByCreatedDate(int id, DateTime fromDate, DateTime toDate, bool isCompleted) { SqlConnection connection = DBConnection.GetConnection(); string selectStatement = "SELECT * FROM tasks where task_owner = @id AND (createdDate between @fromDate and @toDate)"; if (isCompleted) { selectStatement = selectStatement + " and completed = 1"; } SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@fromDate", fromDate); selectCommand.Parameters.AddWithValue("@toDate", toDate); selectCommand.Parameters.AddWithValue("@id", id); // Create a new data adapter based on the specified query. SqlDataAdapter dataAdapter = new SqlDataAdapter(selectStatement, connection); // Populate a new data table DataSet dataSet = new DataSet(); try { connection.Open(); SqlDataReader sdr = selectCommand.ExecuteReader(); dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture; dataSet.Tables.Add("tasks"); //Load DataReader into the DataTable. dataSet.Tables[0].Load(sdr); dataSet.Tables[0].Columns.Add(new DataColumn("Task_Type", typeof(string))); dataSet.Tables[0].Columns.Add(new DataColumn("TaskPriority", typeof(string))); for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++) { if (dataSet.Tables[0].Rows[i]["taskType"].ToString() == "1") { dataSet.Tables[0].Rows[i]["Task_Type"] = "Professional"; } else if (dataSet.Tables[0].Rows[i]["taskType"].ToString() == "2") { dataSet.Tables[0].Rows[i]["Task_Type"] = "Personal"; } else if (dataSet.Tables[0].Rows[i]["taskType"].ToString() == "3") { dataSet.Tables[0].Rows[i]["Task_Type"] = "Other"; } else { dataSet.Tables[0].Rows[i]["Task_Type"] = "Meeting"; } if (dataSet.Tables[0].Rows[i]["task_priority"].ToString() == "1") { dataSet.Tables[0].Rows[i]["TaskPriority"] = "High"; } else if (dataSet.Tables[0].Rows[i]["task_priority"].ToString() == "2") { dataSet.Tables[0].Rows[i]["TaskPriority"] = "Medium"; } else { dataSet.Tables[0].Rows[i]["TaskPriority"] = "Low"; } } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(dataSet); }
/*Returns specified task based on taskID */ public static Models.Task getATask(int taskID) { Models.Task task = new Models.Task(); SqlConnection connection = DBConnection.GetConnection(); string selectStatement = "SELECT * FROM tasks WHERE taskID = @taskID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@taskID", taskID); SqlDataReader reader = null; try { connection.Open(); reader = selectCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow); if (reader.Read()) { task.task_owner = Convert.ToInt32(reader["task_owner"]); task.taskID = Convert.ToInt32(reader["taskID"]); task.taskType = Convert.ToInt32(reader["taskType"]); if (reader["task_priority"] == DBNull.Value) { task.task_priority = -1; } else { task.task_priority = Convert.ToInt32(reader["task_priority"]); } //task.completed = Convert.ToInt32(reader["completed"]); task.completed = Convert.ToBoolean(reader["completed"]); task.title = reader["title"].ToString(); task.createdDate = (DateTime)reader["createdDate"]; task.currentDate = (DateTime)reader["currentDate"]; if (reader["deadline"] == DBNull.Value) { task.deadline = DateTime.MaxValue; } else { task.deadline = (DateTime)reader["deadline"]; } task.isMeeting = Convert.ToInt16(reader["isMeeting"]); } else { task = null; } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { if (connection != null) { connection.Close(); } if (reader != null) { reader.Close(); } } return(task); }
/* * This method use for add subtask into database. * It return the subtask id of new subtask. */ public static int AddSubTask(YouCompleteMe.Models.Subtask subtask) { SqlConnection connection = DBConnection.GetConnection(); connection.Open(); SqlTransaction sqlTransaction = connection.BeginTransaction(); string insertStatement = "INSERT subtask " + "(taskID, st_Description, st_CreatedDate, st_CompleteDate, st_Deadline, st_Priority) " + "VALUES (@taskID, @st_Description, @st_CreatedDate, @st_CompleteDate, @st_Deadline, @st_Priority)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection, sqlTransaction); insertCommand.Parameters.AddWithValue("@taskID", subtask.taskID); insertCommand.Parameters.AddWithValue("@st_Description", subtask.st_Description); insertCommand.Parameters.AddWithValue("@st_CreatedDate", subtask.st_CreatedDate); if (subtask.st_CompleteDate == DateTime.MaxValue) { insertCommand.Parameters.AddWithValue("@st_CompleteDate", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@st_CompleteDate", subtask.st_CompleteDate); } if (subtask.st_Deadline == DateTime.MaxValue) { insertCommand.Parameters.AddWithValue("@st_Deadline", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@st_Deadline", subtask.st_Deadline); } if (subtask.st_Priority == -1) { insertCommand.Parameters.AddWithValue("@st_Priority", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@st_Priority", subtask.st_Priority); } try { insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('subtask') FROM subtask"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection, sqlTransaction); int subtaskID = Convert.ToInt32(selectCommand.ExecuteScalar()); //add notes to notes table if (subtask.note != "") { string insertNoteStatement = "INSERT note " + "(taskID, subtaskID, note_message) " + "VALUES (@taskID, @subtaskID, @note_message)"; SqlCommand insertNoteCommand = new SqlCommand(insertNoteStatement, connection, sqlTransaction); insertNoteCommand.Parameters.AddWithValue("@taskID", subtask.taskID); insertNoteCommand.Parameters.AddWithValue("@note_message", subtask.note); insertNoteCommand.Parameters.AddWithValue("@subtaskID", subtaskID); insertNoteCommand.ExecuteNonQuery(); } sqlTransaction.Commit(); return(subtaskID); } catch (SqlException ex) { sqlTransaction.Rollback(); throw ex; } finally { connection.Close(); } }
public static List <Models.Task> getMonthlyUsersTasks(User currentUser, string date)//, int personal, int professional, int other) { List <Models.Task> tasks = new List <Models.Task>(); SqlConnection connection = DBConnection.GetConnection(); string selectStatement = "SELECT distinct task_owner, t.taskID, t.title FROM tasks t " + "left join activities a " + "on t.taskID = a.taskID " + "WHERE " + "task_owner = @user and " + "isMeeting = 0 and " + "cast(currentDate as date) >= dateadd(month, -1, @date) and " + "startTime >= dateadd(day, -30, getdate())"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@user", currentUser.userID); selectCommand.Parameters.AddWithValue("@date", date); //selectCommand.Parameters.AddWithValue("@type", "("+personal + ", " + professional + ", " + other+")"); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { Models.Task task = new Models.Task(); task.task_owner = Convert.ToInt32(reader["task_owner"]); task.taskID = Convert.ToInt32(reader["taskID"]); //task.taskType = Convert.ToInt32(reader["taskType"]); //if (reader["task_priority"] == DBNull.Value) // task.task_priority = -1; //else // task.task_priority = Convert.ToInt32(reader["task_priority"]); ////task.completed = Convert.ToInt32(reader["completed"]); //task.completed = Convert.ToBoolean(reader["completed"]); task.title = reader["title"].ToString(); //task.createdDate = (DateTime)reader["createdDate"]; //task.currentDate = (DateTime)reader["currentDate"]; //if (reader["deadline"] == DBNull.Value) // task.deadline = DateTime.MaxValue; //else // task.deadline = (DateTime)reader["deadline"]; tasks.Add(task); } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(tasks); }
/* * This method use for add task into database. * It return the task id of new task. */ public static int AddTask(YouCompleteMe.Models.Task task) { SqlConnection connection = DBConnection.GetConnection(); connection.Open(); //SqlTransaction sqlTransaction = connection.BeginTransaction(); string insertStatement = "INSERT tasks " + "(task_owner, taskType, title, createdDate, currentDate, deadline, task_priority, completed, isMeeting) " + "VALUES (@task_owner, @taskType, @title, @createdDate, @currentDate, @deadline, @task_priority, @completed, @meeting)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@task_owner", task.task_owner); insertCommand.Parameters.AddWithValue("@title", task.title); insertCommand.Parameters.AddWithValue("@createdDate", task.createdDate); insertCommand.Parameters.AddWithValue("@currentDate", task.currentDate); if (task.deadline == DateTime.MaxValue) { insertCommand.Parameters.AddWithValue("@deadline", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@deadline", task.deadline); } if (task.task_priority == -1) { insertCommand.Parameters.AddWithValue("@task_priority", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@task_priority", task.task_priority); } insertCommand.Parameters.AddWithValue("@completed", task.completed); insertCommand.Parameters.AddWithValue("@taskType", task.taskType); insertCommand.Parameters.AddWithValue("@meeting", task.isMeeting); try { insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('tasks') FROM tasks"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection);//, sqlTransaction); int taskID = Convert.ToInt32(selectCommand.ExecuteScalar()); if (task.note != "") { string insertNoteStatement = "INSERT note " + "(taskID, subtaskID, note_message) " + "VALUES (@taskID, @subtaskID, @note_message)"; SqlCommand insertNoteCommand = new SqlCommand(insertNoteStatement, connection);//, sqlTransaction); insertNoteCommand.Parameters.AddWithValue("@taskID", taskID); insertNoteCommand.Parameters.AddWithValue("@note_message", task.note); insertNoteCommand.Parameters.AddWithValue("@subtaskID", DBNull.Value); insertNoteCommand.ExecuteNonQuery(); } //sqlTransaction.Commit(); return(taskID); } catch (SqlException ex) { //sqlTransaction.Rollback(); throw ex; } finally { connection.Close(); } }
public static List <Models.Task> getCurrentDeadlines(User currentUser, string date) { List <Models.Task> tasks = new List <Models.Task>(); SqlConnection connection = DBConnection.GetConnection(); string selectStatement = "SELECT * FROM tasks " + "WHERE " + "task_owner = @user and " + "cast(deadline as date) = @date " + "order by deadline"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@user", currentUser.userID); selectCommand.Parameters.AddWithValue("@date", date); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { Models.Task task = new Models.Task(); task.task_owner = Convert.ToInt32(reader["task_owner"]); task.taskID = Convert.ToInt32(reader["taskID"]); task.taskType = Convert.ToInt32(reader["taskType"]); if (reader["task_priority"] == DBNull.Value) { task.task_priority = -1; } else { task.task_priority = Convert.ToInt32(reader["task_priority"]); } //task.completed = Convert.ToInt32(reader["completed"]); task.completed = Convert.ToBoolean(reader["completed"]); task.title = reader["title"].ToString(); task.createdDate = (DateTime)reader["createdDate"]; task.currentDate = (DateTime)reader["currentDate"]; if (reader["deadline"] == DBNull.Value) { task.deadline = DateTime.MaxValue; } else { task.deadline = (DateTime)reader["deadline"]; } tasks.Add(task); } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(tasks); }
public static List <Note> getNotes(int taskID, int subtaskID) { List <Note> notes = new List <Note>(); string selectStatement = ""; SqlConnection connection = DBConnection.GetConnection(); if (subtaskID == -1) { selectStatement = "SELECT * FROM note " + "WHERE " + "taskID = @taskID and " + "subtaskID is null"; } else { selectStatement = "SELECT * FROM note " + "WHERE " + "taskID = @taskID and " + "subtaskID = @stID"; } SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@taskID", taskID); selectCommand.Parameters.AddWithValue("@stID", subtaskID); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { Note note = new Note(); note.taskID = Convert.ToInt32(reader["taskID"]); if (reader["subtaskID"] == DBNull.Value) { note.subtaskID = 0; } else { note.subtaskID = Convert.ToInt32(reader["subtaskID"]); } note.noteID = Convert.ToInt32(reader["noteID"]); note.note_message = reader["note_message"].ToString(); notes.Add(note); } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(notes); }