public async Task <IList <ISession> > GetAll()
{
List <ISession> returnList = new List <ISession>();
List <Models.SessionModel> sessionList = new List <Models.SessionModel>();
LevelDataStore levels = new LevelDataStore();
IList <ITag> levelsList = await levels.GetAll();
CategoryDataStore categories = new CategoryDataStore();
IList <ITag> categoriesList = await categories.GetAll();
TimeSlotDataStore timeSlots = new TimeSlotDataStore();
IList <ITimeSlot> timeSlotList = await timeSlots.GetAll();
RoomDataStore rooms = new RoomDataStore();
IList <IRoom> roomList = await rooms.GetAll();
using (SqlConnection connection = new SqlConnection(Settings.ConnectionString)) {
connection.Open();
using (SqlCommand sessionCommand = new SqlCommand("SELECT * FROM Sessions", connection)) {
using (SqlDataReader dataReader = await sessionCommand.ExecuteReaderAsync()) {
while (await dataReader.ReadAsync())
{
sessionList.Add(new Models.SessionModel(dataReader));
}
}
}
List <Tuple <int, ITag> > TagData = new List <Tuple <int, ITag> >();
using (SqlCommand tagCommand = new SqlCommand("SELECT SessionId, TagId AS Id, Text FROM Tags INNER JOIN SessionTags ON Id = TagId ORDER BY SessionId, TagId;", connection)) {
using (SqlDataReader dataReader = await tagCommand.ExecuteReaderAsync()) {
while (await dataReader.ReadAsync())
{
TagData.Add(new Tuple <int, ITag>(dataReader.GetInt32(0), new Models.TagModel(dataReader)));
}
}
}
ISession sessionWithTags = null;
foreach (Models.SessionModel session in sessionList)
{
sessionWithTags = session
.UpdateLevel(levelsList.FirstOrDefault(l => l.Id == session.LevelId))
.UpdateCategory(categoriesList.FirstOrDefault(l => l.Id == session.CategoryId))
.UpdateTimeSlot(timeSlotList.FirstOrDefault(ts => ts.Id == session.TimeSlotId))
.UpdateRoom(roomList.FirstOrDefault(r => r.Id == session.RoomId));
foreach (Tuple <int, ITag> Tag in TagData.Where(data => data.Item1 == session.Id))
{
sessionWithTags = sessionWithTags.AddTag(Tag.Item2);
}
returnList.Add(sessionWithTags);
}
}
return(returnList);
}
public async Task <ISession> Get(int Id)
{
ISession returnValue = null;
using (SqlConnection connection = new SqlConnection(Settings.ConnectionString)) {
connection.Open();
using (SqlCommand sessionCommand = new SqlCommand("SELECT * FROM Sessions WHERE Id = @Id", connection)) {
sessionCommand.Parameters.Add("Id", SqlDbType.Int).Value = Id;
using (SqlDataReader dataReader = await sessionCommand.ExecuteReaderAsync()) {
if (await dataReader.ReadAsync())
{
returnValue = new Models.SessionModel(dataReader);
}
}
}
if (null != returnValue)
{
using (SqlCommand tagCommand = new SqlCommand("SELECT * FROM Tags WHERE Id IN ( SELECT TagId FROM SessionTags WHERE SessionId = @SessionId );", connection)) {
tagCommand.Parameters.Add("SessionId", SqlDbType.Int).Value = returnValue.Id;
using (SqlDataReader dataReader = await tagCommand.ExecuteReaderAsync()) {
while (await dataReader.ReadAsync())
{
ITag tag = new Models.TagModel(dataReader);
returnValue = returnValue.AddTag(tag);
}
}
}
}
}
LevelDataStore levels = new LevelDataStore();
returnValue = returnValue.UpdateLevel(await levels.Get((returnValue as Models.SessionModel).LevelId));
CategoryDataStore categories = new CategoryDataStore();
returnValue = returnValue.UpdateCategory(await categories.Get((returnValue as Models.SessionModel).CategoryId));
TimeSlotDataStore timeSlots = new TimeSlotDataStore();
returnValue = returnValue.UpdateTimeSlot(await timeSlots.Get((returnValue as Models.SessionModel).TimeSlotId));
RoomDataStore rooms = new RoomDataStore();
return(returnValue.UpdateRoom(await rooms.Get((returnValue as Models.SessionModel).RoomId)));
}