public List <Task> GetTasks()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tasks WHERE category_id = @CategoryId ORDER BY duedate DESC;", conn);
SqlParameter categoryIdParameter = new SqlParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = this.GetId();
cmd.Parameters.Add(categoryIdParameter);
SqlDataReader rdr = cmd.ExecuteReader();
List <Task> tasks = new List <Task> {
};
while (rdr.Read())
{
int taskId = rdr.GetInt32(0);
string taskDescription = rdr.GetString(1);
string taskDueDate = rdr.GetString(2);
int taskCategoryId = rdr.GetInt32(3);
Task newTask = new Task(taskDescription, taskDueDate, taskCategoryId, taskId);
tasks.Add(newTask);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return(tasks);
}
public void Update(string newName)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("UPDATE categories SET name = @NewName OUTPUT INSERTED.name WHERE id = @CategoryId;", conn);
SqlParameter newNameParameter = new SqlParameter();
newNameParameter.ParameterName = "@NewName";
newNameParameter.Value = newName;
cmd.Parameters.Add(newNameParameter);
SqlParameter categoryIdParameter = new SqlParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = this.GetId();
cmd.Parameters.Add(categoryIdParameter);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
this._name = rdr.GetString(0);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
public void Save()
{
SqlConnection conn = DB.Connection();
SqlDataReader rdr;
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO tasks (description, categoryId) OUTPUT INSERTED.id VALUES (@TaskDescription, @TaskCategoryId)", conn);
SqlParameter descriptionParam = new SqlParameter();
descriptionParam.ParameterName = "@TaskDescription";
descriptionParam.Value = this.GetDescription();
SqlParameter categoryIdParam = new SqlParameter();
categoryIdParam.ParameterName = "@TaskCategoryId";
categoryIdParam.Value = this.GetCategoryId();
cmd.Parameters.Add(descriptionParam);
cmd.Parameters.Add(categoryIdParam);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
this.id = rdr.GetInt32(0);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
public static Task Find(int id)
{
SqlConnection conn = DB.Connection();
SqlDataReader rdr = null;
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tasks WHERE id = @TaskId", conn);
SqlParameter taskIdParameter = new SqlParameter();
taskIdParameter.ParameterName = "@TaskId";
taskIdParameter.Value = id.ToString();
cmd.Parameters.Add(taskIdParameter);
rdr = cmd.ExecuteReader();
int foundTaskId = 0;
string foundTaskDescription = null;
int foundTaskCategoryId = 0;
while (rdr.Read())
{
foundTaskId = rdr.GetInt32(0);
foundTaskDescription = rdr.GetString(1);
foundTaskCategoryId = rdr.GetInt32(2);
}
Task foundTask = new Task(foundTaskDescription, foundTaskCategoryId, foundTaskId);
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return(foundTask);
}
/////OVERLOAD TO RETRIEVE ONLY COMPLETED OR UNCOMPLETED
public static List <Task> GetAll(bool status)
{
///true status returns only completed tasks, false status returns incomplete tasks
List <Task> allTasks = new List <Task> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tasks WHERE completed = @Completed;", conn);
cmd.Parameters.AddWithValue("@Completed", status);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int taskId = rdr.GetInt32(0);
string taskDescription = rdr.GetString(1);
bool taskCompleted = rdr.GetBoolean(2);
DateTime taskDueDate = rdr.GetDateTime(3);
Task newTask = new Task(taskDescription, taskDueDate, taskCompleted, taskId);
allTasks.Add(newTask);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return(allTasks);
}
public static Task Find(int id)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM tasks WHERE id = @TaskId;", conn);
cmd.Parameters.AddWithValue("@TaskId", id.ToString());
SqlDataReader rdr = cmd.ExecuteReader();
int foundTaskId = 0;
string foundTaskDescription = null;
bool foundTaskCompleted = false;
DateTime foundTaskDueDate = new DateTime();
while (rdr.Read())
{
foundTaskId = rdr.GetInt32(0);
foundTaskDescription = rdr.GetString(1);
foundTaskCompleted = rdr.GetBoolean(2);
foundTaskDueDate = rdr.GetDateTime(3);
}
Task foundTask = new Task(foundTaskDescription, foundTaskDueDate, foundTaskCompleted, foundTaskId);
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return(foundTask);
}
public void AddCategory(Category newCategory)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO categories_tasks (category_id, task_id) VALUES (@CategoryId, @TaskId);", conn);
SqlParameter categoryIdParameter = new SqlParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = newCategory.GetId();
cmd.Parameters.Add(categoryIdParameter);
SqlParameter taskIdParameter = new SqlParameter();
taskIdParameter.ParameterName = "@TaskId";
taskIdParameter.Value = this.GetId();
cmd.Parameters.Add(taskIdParameter);
cmd.ExecuteNonQuery();
if (conn != null)
{
conn.Close();
}
}
public List <Task> GetTasks()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT task_id FROM categories_tasks WHERE category_id = @CategoryId;", conn);
SqlParameter categoryIdParameter = new SqlParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = this.GetId();
cmd.Parameters.Add(categoryIdParameter);
SqlDataReader rdr = cmd.ExecuteReader();
List <int> taskIds = new List <int> {
};
while (rdr.Read())
{
int taskId = rdr.GetInt32(0);
taskIds.Add(taskId);
}
if (rdr != null)
{
rdr.Close();
}
List <Task> tasks = new List <Task> {
};
foreach (int taskId in taskIds)
{
SqlCommand taskQuery = new SqlCommand("SELECT * FROM tasks WHERE id = @TaskId;", conn);
SqlParameter taskIdParameter = new SqlParameter();
taskIdParameter.ParameterName = "@TaskId";
taskIdParameter.Value = taskId;
taskQuery.Parameters.Add(taskIdParameter);
SqlDataReader newrdr = taskQuery.ExecuteReader();
while (newrdr.Read())
{
int thisTaskId = newrdr.GetInt32(0);
string taskDescription = newrdr.GetString(1);
bool taskComplete;
if (newrdr.GetByte(2) == 1)
{
taskComplete = true;
}
else
{
taskComplete = false;
}
Task foundTask = new Task(taskDescription, taskComplete, thisTaskId);
tasks.Add(foundTask);
}
if (newrdr != null)
{
newrdr.Close();
}
}
if (conn != null)
{
conn.Close();
}
return(tasks);
}
public List <Category> GetCategories()
{
SqlConnection conn = DB.Connection();
SqlDataReader rdr = null;
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT category_id FROM categories_tasks WHERE task_id = @TaskId;", conn);
SqlParameter taskIdParameter = new SqlParameter();
taskIdParameter.ParameterName = "@TaskId";
taskIdParameter.Value = this.GetId();
cmd.Parameters.Add(taskIdParameter);
rdr = cmd.ExecuteReader();
List <int> categoryIds = new List <int> {
};
while (rdr.Read())
{
int categoryId = rdr.GetInt32(0);
categoryIds.Add(categoryId);
}
if (rdr != null)
{
rdr.Close();
}
List <Category> categories = new List <Category> {
};
foreach (int categoryId in categoryIds)
{
SqlDataReader queryReader = null;
SqlCommand categoryQuery = new SqlCommand("SELECT * FROM categories WHERE id = @CategoryId;", conn);
SqlParameter categoryIdParameter = new SqlParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = categoryId;
categoryQuery.Parameters.Add(categoryIdParameter);
queryReader = categoryQuery.ExecuteReader();
while (queryReader.Read())
{
int thisCategoryId = queryReader.GetInt32(0);
string categoryName = queryReader.GetString(1);
Category foundCategory = new Category(categoryName, thisCategoryId);
categories.Add(foundCategory);
}
if (queryReader != null)
{
queryReader.Close();
}
}
if (conn != null)
{
conn.Close();
}
return(categories);
}
public List <Task> GetTasks()
{
List <Task> allTasks = new List <Task> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT task_id FROM categories_tasks WHERE category_id = @CategoryId;", conn);
SqlParameter categoryIdParameter = new SqlParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = this.GetId();
cmd.Parameters.Add(categoryIdParameter);
SqlDataReader rdr = cmd.ExecuteReader();
List <int> taskIds = new List <int> {
};
while (rdr.Read())
{
int taskId = rdr.GetInt32(0);
taskIds.Add(taskId);
}
if (rdr != null)
{
rdr.Close();
}
List <Task> tasks = new List <Task> {
};
foreach (int taskId in taskIds)
{
SqlCommand taskQuery = new SqlCommand("SELECT * FROM tasks WHERE id = @TaskId;", conn);
SqlParameter taskIdParameter = new SqlParameter();
taskIdParameter.ParameterName = "@TaskId";
taskIdParameter.Value = taskId;
taskQuery.Parameters.Add(taskIdParameter);
SqlDataReader queryReader = taskQuery.ExecuteReader();
while (queryReader.Read())
{
int thisTaskId = queryReader.GetInt32(0);
string thisTaskName = queryReader.GetString(1);
DateTime thisTaskDueDate = queryReader.GetDateTime(2);
bool thisTaskStatus = queryReader.GetBoolean(3);
Task foundTask = new Task(thisTaskName, thisTaskDueDate, thisTaskStatus, thisTaskId);
tasks.Add(foundTask);
}
if (queryReader != null)
{
queryReader.Close();
}
}
if (conn != null)
{
conn.Close();
}
return(tasks);
}
