public static Item Find(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM `items` WHERE id = @thisId;";
MySqlParameter thisId = new MySqlParameter();
thisId.ParameterName = "@thisId";
thisId.Value = id;
cmd.Parameters.Add(thisId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
int itemId = 0;
string itemDescription = "";
while (rdr.Read())
{
itemId = rdr.GetInt32(0);
itemDescription = rdr.GetString(1);
}
Item foundItem = new Item(itemDescription, itemId);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(foundItem);
}
public List <Item> GetItems()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT items.* FROM categories
JOIN categories_items ON (categories.id = categories_items.category_id)
JOIN items ON (categories_items.item_id = items.id)
WHERE categories.id = @CategoryId;";
MySqlParameter categoryIdParameter = new MySqlParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = _id;
cmd.Parameters.Add(categoryIdParameter);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Item> items = new List <Item> {
};
while (rdr.Read())
{
int itemId = rdr.GetInt32(0);
string itemDescription = rdr.GetString(1);
string dueDate = rdr.GetString(2);
Item newItem = new Item(itemDescription, dueDate, itemId);
items.Add(newItem);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(items);
}
public void Save()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO `items` (`description`, `due_date`, category_id) VALUES (@ItemDescription, @ItemDueDate, @category_id);";
MySqlParameter description = new MySqlParameter();
description.ParameterName = "@ItemDescription";
description.Value = this._description;
cmd.Parameters.Add(description);
MySqlParameter due_date = new MySqlParameter();
due_date.ParameterName = "@ItemDueDate";
due_date.Value = this._dueDate;
cmd.Parameters.Add(due_date);
MySqlParameter categoryId = new MySqlParameter();
categoryId.ParameterName = "@category_id";
categoryId.Value = this._categoryId;
cmd.Parameters.Add(categoryId);
cmd.ExecuteNonQuery();
_id = (int)cmd.LastInsertedId;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public void Edit(string newDescription, DateTime newDuedate)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"UPDATE items SET description = @newDescription, duedate = @newDuedate WHERE id = @searchId;";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = _id;
cmd.Parameters.Add(searchId);
MySqlParameter description = new MySqlParameter();
description.ParameterName = "@newDescription";
description.Value = newDescription;
cmd.Parameters.Add(description);
MySqlParameter duedate = new MySqlParameter();
duedate.ParameterName = "@newDuedate";
duedate.Value = newDuedate;
cmd.Parameters.Add(duedate);
cmd.ExecuteNonQuery();
_description = newDescription;
_duedate = newDuedate;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public static Task Find(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM `tasks` WHERE id = @thisId;";
MySqlParameter thisId = new MySqlParameter();
thisId.ParameterName = "@thisId";
thisId.Value = id;
cmd.Parameters.Add(thisId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
int taskId = 0;
string taskDescription = "";
while (rdr.Read())
{
taskId = rdr.GetInt32(0);
taskDescription = rdr.GetString(1);
}
Task foundTask = new Task(taskDescription, taskId); // This line is new!
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(foundTask); // This line is new!
}
public List <Category> GetCategories()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT categories.* FROM items
JOIN categories_items ON (items.id = categories_items.item_id)
JOIN categories ON (categories_items.category_id = categories.id)
WHERE items.id = @itemId;";
MySqlParameter itemIdParameter = new MySqlParameter();
itemIdParameter.ParameterName = "@itemId";
itemIdParameter.Value = _id;
cmd.Parameters.Add(itemIdParameter);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Category> categories = new List <Category> {
};
while (rdr.Read())
{
int categoryId = rdr.GetInt32(0);
string categoryName = rdr.GetString(1);
Category newCategory = new Category(categoryName, categoryId);
categories.Add(newCategory);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(categories);
}
public static Category Find(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * from `categories` WHERE id = @thisId;";
MySqlParameter thisId = new MySqlParameter();
thisId.ParameterName = "@thisId";
thisId.Value = id;
cmd.Parameters.Add(thisId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
int categoryId = 0;
string categoryName = "";
while (rdr.Read())
{
categoryId = rdr.GetInt32(0);
categoryName = rdr.GetString(1);
}
Category foundCategory = new Category(categoryName, categoryId);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(foundCategory);
}
// public void Save()
// {
// _instances.Add(this);
// }
public static void ClearAll()
{
MySqlConnection conn = DB.Connection();
//Creates conn object representing our connection to the database
conn.Open();
//manually opens the connection ( conn ) with conn.Open()
var cmd = conn.CreateCommand() as MySqlCommand;
//Define cmd as --> creating command --> MySqlCommand... then...
cmd.CommandText = @"DELETE FROM items;";
//...Define CommandText property using SQL statement, which will clear the items table in our database
cmd.ExecuteNonQuery();
//Executes SQL statements that modify data (like deletion)
conn.Close();
//Finally, we make sure to close our connection with Close()...
if (conn != null)
{
conn.Dispose();
}
//...including an if statement that disposes of the connection if it's not null.
}
public void UpdateDescription(string newDescription)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"UPDATE tasks SET description = @newDescription WHERE id = @searchId;";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = _id;
cmd.Parameters.Add(searchId);
MySqlParameter description = new MySqlParameter();
description.ParameterName = "@newDescription";
description.Value = newDescription;
cmd.Parameters.Add(description);
cmd.ExecuteNonQuery();
conn.Close();
}
// public int GetCategoryId()
// {
// return _categoryId;
// }
public void Save()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO items (description) VALUES (@description);";
MySqlParameter description = new MySqlParameter();
description.ParameterName = "@description";
description.Value = this._description;
cmd.Parameters.Add(description);
cmd.ExecuteNonQuery();
_id = (int)cmd.LastInsertedId;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public void Done()
{
this.SetCheckOff(true);
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"UPDATE items SET checkoff = 1 WHERE id = @ItemId;";
MySqlParameter itemIdParameter = new MySqlParameter();
itemIdParameter.ParameterName = "@ItemId";
itemIdParameter.Value = this.GetId();
cmd.Parameters.Add(itemIdParameter);
cmd.ExecuteNonQuery();
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public List <Item> GetItems()
{
List <Item> allCategoryItems = new List <Item> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM items WHERE category_id = @category_id;";
MySqlParameter categoryId = new MySqlParameter();
categoryId.ParameterName = "@category_id";
categoryId.Value = this._id;
cmd.Parameters.Add(categoryId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int itemId = rdr.GetInt32(0);
string itemDescription = rdr.GetString(1);
DateTime itemDate = rdr.GetDateTime(2); // *******
int itemCategoryId = rdr.GetInt32(3);
Item newItem = new Item(itemDescription, itemDate, itemCategoryId, itemId);
allCategoryItems.Add(newItem);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allCategoryItems);
}
public static List <Task> GetAll()
{
List <Task> allTasks = new List <Task> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM tasks;";
var rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int taskId = rdr.GetInt32(0);
string taskName = rdr.GetString(1);
int taskCategoryId = rdr.GetInt32(2);
Task newTask = new Task(taskName, taskCategoryId, taskId);
allTasks.Add(newTask);
}
return(allTasks);
}
public static void DeleteItem(int id)
{
Console.WriteLine(id);
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"DELETE FROM items WHERE id = @thisId;";
MySqlParameter thisId = new MySqlParameter();
thisId.ParameterName = "@thisId";
thisId.Value = id;
cmd.Parameters.Add(thisId);
cmd.ExecuteNonQuery();
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public static Task Find(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM tasks WHERE id = (@searchId);";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = id;
cmd.Parameters.Add(searchId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
int taskId = 0;
string taskName = "";
int taskCategoryId = 0;
while (rdr.Read())
{
taskId = rdr.GetInt32(0);
taskName = rdr.GetString(1);
taskCategoryId = rdr.GetInt32(2);
}
Task newTask = new Task(taskName, taskCategoryId, taskId);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(newTask);
}
public List <Task> GetTasks()
{
List <Task> allCategoryTasks = new List <Task> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM tasks WHERE category_id = @category_id;";
MySqlParameter categoryId = new MySqlParameter();
categoryId.ParameterName = "@category_id";
categoryId.Value = this._id;
cmd.Parameters.Add(categoryId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int taskId = rdr.GetInt32(0);
string taskDescription = rdr.GetString(1);
int taskCategoryId = rdr.GetInt32(2);
string dueDate = rdr.GetDateTime(3).ToString();
Task newTask = new Task(taskDescription, dueDate, taskCategoryId, taskId);
allCategoryTasks.Add(newTask);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allCategoryTasks);
}
public static Item Find(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM items WHERE id = @idSearch;";
cmd.Parameters.Add(new MySqlParameter("@idSearch", id));
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
int itemId = 0;
int itemCategory = 0;
string itemDescription = "";
while (rdr.Read())
{
itemId = rdr.GetInt32(0);
itemDescription = rdr.GetString(1);
itemCategory = rdr.GetInt32(2);
}
Item foundItem = new Item(itemDescription, itemCategory);
foundItem._id = itemId;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(foundItem);
}
public void ClearTasks()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"DELETE FROM tasks WHERE category_id = @CategoryId;";
MySqlParameter categoryId = new MySqlParameter();
categoryId.ParameterName = "@CategoryId";
categoryId.Value = Id;
cmd.Parameters.Add(categoryId);
cmd.ExecuteNonQuery();
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public void Save()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO categories (name) VALUES (@name);";
MySqlParameter name = new MySqlParameter();
name.ParameterName = "@name";
name.Value = this._name;
cmd.Parameters.Add(name);
cmd.ExecuteNonQuery();
_id = (int)cmd.LastInsertedId;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public void Delete()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"DELETE FROM items WHERE id = @thisId;";
MySqlParameter deleteId = new MySqlParameter();
deleteId.ParameterName = "@thisId";
deleteId.Value = _id;
cmd.Parameters.Add(deleteId);
cmd.ExecuteNonQuery();
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
