private static void UnpackList(SqlDataReader reader, List <Library.List> items)
{
string id = reader["ListId"].ToString();
Library.List list = items.FirstOrDefault(x => x.Id == id) as Library.List;
if (list == null)
{
list = new Library.List
{
Id = id,
Name = reader["ListName"].ToString()
};
items.Add(list);
}
// if the list contains tasks then unpack them
if (reader["TaskId"].GetType() == typeof(DBNull))
{
return;
}
if (list.Tasks == null)
{
list.Tasks = new List <Library.Task>();
}
UnpackTask(reader, list.Tasks);
}
public async Task <IHttpActionResult> GetTasks(string listId, [FromUri] bool?important = null, bool?completed = null)
{
IEnumerable <Library.List> lists = await this.listService.Get <Library.List>(listId, important, completed);
Library.List list = lists?.ToList()?.FirstOrDefault();
return(list != null?this.Ok(list.Tasks) : this.NotFound() as IHttpActionResult);
}
public async Task <IHttpActionResult> Get(string id)
{
IEnumerable <Library.List> lists = await this.listService.Get <Library.List>(id);
Library.List list = lists?.ToList()?.FirstOrDefault();
return(list != null?this.Ok(list) : this.NotFound() as IHttpActionResult);
}
private string BuildListQuery(Library.List list, List <SqlParameter> parameters, bool insert)
{
parameters.Add(new SqlParameter("name", SqlDbType.NVarChar, 50)
{
Value = list.Name
});
if (insert)
{
return($"INSERT INTO List(Id, Name) VALUES (@id, @name);");
}
return($"UPDATE List SET Name = @name WHERE Id = @id;");
}
public async Tasks.Task <T> Create <T>(T item, string listId = null) where T : Library.IIdentifiable
{
this.GenerateId(item);
if (typeof(T) == typeof(Library.List))
{
Library.List list = item as Library.List;
if (list.Tasks != null)
{
list.Tasks.ForEach(this.GenerateId);
}
await this.listRepository.Create <Library.List>(item.Id, list);
return(item);
}
await this.listRepository.Create <T>(listId, item);
return(item);
}
/// <summary>
/// We cannot get a task directly without loading the list unless we stored the tasks in a seperate table.
/// The API however requires us to search tasks by due date or whether they are important.
/// Even if we used a seperate table, this would require us to use a 'Partition Scan Query' which is not as performant as partition and rowkey queries
/// Therefore we have not split tasks into a seperate table. This is also not very performant if a user has a lot of lists, so the right choice it really depends on how we think the lists will be used by the users.
/// These demos are created to discus the pro and cons of different database in Azure, and therefore these performance considerations are a design choice for a specific use case
/// </summary>
private async Task <Tuple <Library.List, Library.Task> > GetListAndTaskByTaskId(string id)
{
var lists = await GetAllLists();
Library.Task task = null;
Library.List list = null;
foreach (var l in lists)
{
var match = l.Tasks.FirstOrDefault(x => x.Id == id);
if (match != null)
{
task = match;
list = l;
break;
}
}
if (task == null)
{
return(null);
}
return(Tuple.Create(list, task));
}
private Task InsertOrUpdate <T>(string id, T item, bool insert = true) where T : Library.IIdentifiable
{
return(Task.Run(() =>
{
using (SqlConnection connection = new SqlConnection(this.connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand())
{
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("id", SqlDbType.NVarChar, 50)
{
Value = id
});
command.Connection = connection;
if (typeof(T) == typeof(Library.List))
{
Library.List list = item as Library.List;
command.CommandText += this.BuildListQuery(list, parameters, insert);
if (list.Tasks != null)
{
command.CommandText += this.BuildTaskQuery(parameters, insert, list.Tasks.ToArray());
}
}
else if (typeof(T) == typeof(Library.Task))
{
Library.Task task = item as Library.Task;
command.CommandText += this.BuildTaskQuery(parameters, insert, task);
}
command.Parameters.AddRange(parameters.ToArray());
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
}
}));
}
public async Task <IHttpActionResult> Put(string id, Library.List list)
{
await this.listService.Replace <Library.List>(id, list);
return(this.Created($"api/lists/{list.Id}", list));
}
