public IList<IToDoItem> GetToDoItems(string idFilter = "")
{
// sql to execute
// ANDREI: changed the sql query
string sql = @"SELECT a.id, a.title, a.[description], a.complete, isnull(b.title, 'N/A') as parent_task_title
from ToDoItems a
left join ToDoItems b on a.parent_task_id = b.id";
// instantiate list to populate
List<IToDoItem> items = new List<IToDoItem>();
// access the database and retrieve data
using (IDbConnection conn = GetConnection())
{
IDbCommand command = conn.CreateCommand();
command.CommandText = sql;
// do we have an id filter?
if (!string.IsNullOrEmpty(idFilter))
{
sql += " where a.id = '@id'";
command.Parameters.Add(new SqlParameter("@id", idFilter));
}
try
{
conn.Open();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
IToDoItem item = new ToDoItem();
item.Id = reader.GetGuid(reader.GetOrdinal("id")).ToString();
item.Title = reader.GetString(reader.GetOrdinal("title"));
item.Description = reader.GetString(reader.GetOrdinal("description"));
item.Complete = reader.GetBoolean(reader.GetOrdinal("complete"));
// ANDREI: set a parent task title property
item.ParentTaskTitle = reader.GetString(reader.GetOrdinal("parent_task_title"));
items.Add(item);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
return items;
}
public IToDoItem Build(ToDoItemContract toDoItemContract)
{
IToDoItem toDoItem = new ToDoItem();
toDoItem.Id = toDoItemContract.Id;
toDoItem.Title = toDoItemContract.Title;
toDoItem.Description = toDoItemContract.Description;
toDoItem.Complete = toDoItemContract.Complete;
return toDoItem;
}
public IToDoItem Build(ToDoItemContract toDoItemContract)
{
IToDoItem toDoItem = new ToDoItem();
toDoItem.Id = toDoItemContract.Id;
toDoItem.Title = toDoItemContract.Title;
toDoItem.Description = toDoItemContract.Description;
toDoItem.Complete = toDoItemContract.Complete;
// ANDREI: mapped the parent task properties
toDoItem.ParentTaskId = toDoItemContract.ParentTaskId;
toDoItem.ParentTaskTitle = toDoItemContract.ParentTaskTitle;
return toDoItem;
}
public IList<IToDoItem> GetToDoItems(string idFilter = "")
{
// sql to execute
string sql = "select * from ToDoItems";
// instantiate list to populate
List<IToDoItem> items = new List<IToDoItem>();
// access the database and retrieve data
using (IDbConnection conn = GetConnection())
{
IDbCommand command = conn.CreateCommand();
command.CommandText = sql;
// do we have an id filter?
if (!string.IsNullOrEmpty(idFilter))
{
sql += " where id = '@id'";
command.Parameters.Add(new SqlParameter("@id", idFilter));
}
try
{
conn.Open();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
IToDoItem item = new ToDoItem();
item.Id = reader.GetGuid(reader.GetOrdinal("id")).ToString();
item.Title = reader.GetString(reader.GetOrdinal("title"));
item.Description = reader.GetString(reader.GetOrdinal("description"));
item.Complete = reader.GetBoolean(reader.GetOrdinal("complete"));
items.Add(item);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
return items;
}