public static List<Category> GetAll()
{
List<Category> allCategories = new List<Category>{};
SqlConnection conn = DB.Connection();
SqlDataReader rdr = null;
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM categories;", conn);
rdr = cmd.ExecuteReader();
while(rdr.Read())
{
int categoryId = rdr.GetInt32(0);
string categoryName = rdr.GetString(1);
Category newCategory = new Category(categoryName, categoryId);
allCategories.Add(newCategory);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return allCategories;
}
public static Category Find(int id)
{
SqlConnection conn = DB.Connection();
SqlDataReader rdr = null;
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM categories WHERE id = @CategoryId;", conn);
SqlParameter categoryIdParameter = new SqlParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = id.ToString();
cmd.Parameters.Add(categoryIdParameter);
rdr = cmd.ExecuteReader();
int foundCategoryId = 0;
string foundCategoryDescription = null;
while(rdr.Read())
{
foundCategoryId = rdr.GetInt32(0);
foundCategoryDescription = rdr.GetString(1);
}
Category foundCategory = new Category(foundCategoryDescription, foundCategoryId);
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return foundCategory;
}
public void Test_Equal_ReturnsTrueForSameName()
{
//Arrange, Act
Category firstCategory = new Category("Household chores");
Category secondCategory = new Category("Household chores");
//Assert
Assert.Equal(firstCategory, secondCategory);
}
public void Test_Find_FindsCategoryInDatabase()
{
//Arrange
Category testCategory = new Category("Household chores");
testCategory.Save();
//Act
Category foundCategory = Category.Find(testCategory.GetId());
//Assert
Assert.Equal(testCategory, foundCategory);
}
public void Test_Save_SavesCategoryToDatabase()
{
//Arrange
Category testCategory = new Category("Household chores");
testCategory.Save();
//Act
List<Category> result = Category.GetAll();
List<Category> testList = new List<Category>{testCategory};
//Assert
Assert.Equal(testList, result);
}
public void Test_Save_AssignsIdToCategoryObject()
{
//Arrange
Category testCategory = new Category("Household chores");
testCategory.Save();
//Act
Category savedCategory = Category.GetAll()[0];
int result = savedCategory.GetId();
int testId = testCategory.GetId();
//Assert
Assert.Equal(testId, result);
}
public void Test_AddCategory_AddsCategoryToTask()
{
//Arrange
Task testTask = new Task("Mow the lawn", new DateTime(2015, 1, 18));
testTask.Save();
Category testCategory = new Category("Home stuff");
testCategory.Save();
//Act
testTask.AddCategory(testCategory);
List<Category> result = testTask.GetCategories();
List<Category> testList = new List<Category>{testCategory};
//Assert
Assert.Equal(testList, result);
}
public void Test_AddTask_AddsTaskToCategory()
{
Category testCategory = new Category("Household chores");
testCategory.Save();
Task testTask = new Task("Mow the Lawn", new DateTime(2014, 4, 21));
testTask.Save();
Task testTask2 = new Task("Water the garden", new DateTime(2014, 4, 21));
testTask2.Save();
testCategory.AddTask(testTask);
testCategory.AddTask(testTask2);
List<Task> result = testCategory.GetTasks();
List<Task> testList = new List<Task>{testTask, testTask2};
Assert.Equal(testList, result);
}
public void Test_GetCategories_ReturnsAllTaskCategories()
{
//Arrange
Task testTask = new Task("Mow the lawn", new DateTime(2015, 1, 18));
testTask.Save();
Category testCategory1 = new Category("Home stuff");
testCategory1.Save();
Category testCategory2 = new Category("Work stuff");
testCategory2.Save();
//Act
testTask.AddCategory(testCategory1);
List<Category> result = testTask.GetCategories();
List<Category> testList = new List<Category> {testCategory1};
//Assert
Assert.Equal(testList, result);
}
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 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 void Test_GetTasks_RetrievesAllCategoryTasks()
{
Category testCategory = new Category("Household chores");
testCategory.Save();
Task testTask1 = new Task("Mow the lawn", new DateTime(2014, 4, 21));
testTask1.Save();
Task testTask2 = new Task("Buy plane ticket", new DateTime(2014, 4, 21));
testTask2.Save();
testCategory.AddTask(testTask1);
List<Task> savedTasks = testCategory.GetTasks();
List<Task> testList = new List<Task> {testTask1};
Assert.Equal(testList, savedTasks);
}