public async Task <CourseDetailViewModel> GetCourseAsync(int id) { /*Per registrare i Log dell'applicazione*/ logger.LogInformation($"Course {id} richiesto"); FormattableString query = $@" SELECT * FROM Courses WHERE Id={id}; SELECT * FROM Lessons WHERE IdCourse={id}"; DataSet dataSet = await db.QueryAsync(query); var courseTable = dataSet.Tables[0]; if (courseTable.Rows.Count != 1) { logger.LogWarning($"Corso {id} non trovato!"); throw new CourseNotFoundException(id); } var courseRow = courseTable.Rows[0]; var courseDetailViewModel = CourseDetailViewModel.FromDataRow(courseRow); var lessonDataTable = dataSet.Tables[1]; foreach (DataRow lessonRow in lessonDataTable.Rows) { LessonViewModel lessonViewModel = LessonViewModel.FromDataRow(lessonRow); courseDetailViewModel.Lezioni.Add(lessonViewModel); } return(courseDetailViewModel); }
public async Task <CourseDetailViewModel> GetCourseAsync(int id) { logger.LogInformation("Course {id} requested", id); FormattableString query = $@"SELECT Id, Title, Description, ImagePath, Author, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses WHERE Id={id} ; SELECT Id, Title, Description, Duration FROM Lessons WHERE CourseId={id}"; DataSet dataSet = await db.QueryAsync(query); //Course var courseTable = dataSet.Tables[0]; if (courseTable.Rows.Count != 1) { logger.LogWarning("Course {id} not found", id); throw new CourseNotFoundException(id); } var courseRow = courseTable.Rows[0]; var courseDetailViewModel = CourseDetailViewModel.FromDataRow(courseRow); //Course lessons var lessonDataTable = dataSet.Tables[1]; foreach (DataRow lessonRow in lessonDataTable.Rows) { LessonViewModel lessonViewModel = LessonViewModel.FromDataRow(lessonRow); courseDetailViewModel.Lessons.Add(lessonViewModel); } return(courseDetailViewModel); }
public async Task <PastryDetailViewModel> GetPastryAsync(int id) { logger.LogInformation("Pastry {id} requested", id); FormattableString query = $@"SELECT Id, Name, Description, Price, Currency, ImagePath, date(InsertDateTime) as InsertDateTime FROM Pastries WHERE Id={id}; SELECT i.Id, i.Name, c.Quantity, c.UoM FROM Ingredients i INNER JOIN Compositions c ON i.Id = c.IdI WHERE c.IdP = {id}"; DataSet dataSet = await db.QueryAsync(query); var pastryTable = dataSet.Tables[0]; if (pastryTable.Rows.Count != 1) { logger.LogWarning("Pastry {id} not found", id); throw new PastryNotFoundException(id); } var pastryRow = pastryTable.Rows[0]; var pastryDetailViewModel = PastryDetailViewModel.FromDataRow(pastryRow); var ingredientsDataTable = dataSet.Tables[1]; foreach (DataRow ingredientRow in ingredientsDataTable.Rows) { IngredientViewModel ingredientViewModel = IngredientViewModel.FromDataRow(ingredientRow); pastryDetailViewModel.Ingredients.Add(ingredientViewModel); } return(pastryDetailViewModel); }
public async Task <List <CourseViewModel> > GetCoursesAsync() { FormattableString query = $"SELECT Id, Title, Author, ImagePath, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses"; DataSet dataSet = await db.QueryAsync(query); var dataTable = dataSet.Tables[0]; var courseList = mapper.Map <List <CourseViewModel> >(dataTable.Rows); //var courseList = new List<CourseViewModel>(); // foreach(DataRow courseRow in dataTable.Rows) { // CourseViewModel course = CourseViewModel.FromDataRow(courseRow); // courseList.Add(course); // } return(courseList); }
public async Task <ProvinciaViewModel> GetProvinciaAsync(int id) { FormattableString query = $"SELECT * FROM province WHERE codiceProvincia={id}"; DataSet dataSet = await db.QueryAsync(query); var provinciaTable = dataSet.Tables[0]; if (provinciaTable.Rows.Count != 1) { throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {id}"); } var provinciaRow = provinciaTable.Rows[0]; var provinciaViewModel = ProvinciaViewModel.FromDataRow(provinciaRow); return(provinciaViewModel); }
public async Task <PasswordDetailViewModel> GetPasswordAsync(string id) { log.LogInformation("password {id} requested", id); FormattableString query = $"SELECT * FROM Passwords WHERE Id = {id}"; DataSet dset = await db.QueryAsync(query); var dtable = dset.Tables[0]; if (dtable.Rows.Count != 1) { log.LogWarning("password {id} not found", id); throw new PasswordNotFoundException(Convert.ToInt32(id)); } var PassRow = dtable.Rows[0]; PasswordDetailViewModel PassDetailViewModel = PasswordDetailViewModel.FromDataRow(PassRow); return(PassDetailViewModel); }
public async Task <LessonDetailViewModel> GetLessonAsync(int id) { FormattableString query = $@"SELECT Id, CourseId, Title, Description, Duration FROM Lessons WHERE ID={id}"; DataSet dataSet = await db.QueryAsync(query); //Course var lessonTable = dataSet.Tables[0]; if (lessonTable.Rows.Count != 1) { logger.LogWarning("Lesson {id} not found", id); throw new LessonNotFoundException(id); } var lessonRow = lessonTable.Rows[0]; var lessonDetailViewModel = LessonDetailViewModel.FromDataRow(lessonRow); return(lessonDetailViewModel); }
public async Task <ListViewModel <SpeseViewModel> > GetSpeseAsync(SpeseListInputModel model) { string mese = DateTime.Now.ToString("MM"); string anno = DateTime.Now.ToString("yyyy"); string mesePrec1 = DateTime.Now.AddMonths(-1).ToString("MM"); string annoPrec1 = DateTime.Now.AddMonths(-1).ToString("yyyy"); string mesePrec2 = DateTime.Now.AddMonths(-2).ToString("MM"); string annoPrec2 = DateTime.Now.AddMonths(-2).ToString("yyyy"); FormattableString query = $@"SELECT IdSpesa, Descrizione, Importo, Valuta, Mese, Anno FROM Spese WHERE Mese LIKE {mese} AND Anno LIKE {anno} ORDER BY IdSpesa DESC LIMIT {model.Limit} OFFSET {model.Offset}; SELECT COUNT(*) FROM Spese WHERE Mese LIKE {mese} AND Anno LIKE {anno}; SELECT COUNT(*) FROM Spese WHERE Mese LIKE {mesePrec1} AND Anno LIKE {annoPrec1}; SELECT COUNT(*) FROM Spese WHERE Mese LIKE {mesePrec2} AND Anno LIKE {annoPrec2}; SELECT SUM(Importo) FROM Spese WHERE Mese LIKE {mese} AND Anno LIKE {anno};"; DataSet dataSet = await db.QueryAsync(query); var dataTable = dataSet.Tables[0]; var speseList = new List <SpeseViewModel>(); foreach (DataRow speseRow in dataTable.Rows) { SpeseViewModel speseViewModel = SpeseViewModel.FromDataRow(speseRow); speseList.Add(speseViewModel); } ListViewModel <SpeseViewModel> result = new ListViewModel <SpeseViewModel> { Results = speseList, TotalCount = Convert.ToInt32(dataSet.Tables[1].Rows[0][0]), TotalMese = Convert.ToInt32(dataSet.Tables[1].Rows[0][0]), Total1MesePrec = Convert.ToInt32(dataSet.Tables[2].Rows[0][0]), Total2MesePrec = Convert.ToInt32(dataSet.Tables[3].Rows[0][0]), TotaleSpese = Convert.ToString(dataSet.Tables[4].Rows[0][0]) }; return(result); }
public async Task <ApplicationUser> FindByIdAsync(string userId, CancellationToken token) { DataSet dataSet = await db.QueryAsync($"SELECT * FROM AspNetUsers WHERE Id={userId}", token); if (dataSet.Tables[0].Rows.Count == 0) { return(null); } return(ApplicationUser.FromDataRow(dataSet.Tables[0].Rows[0])); }
public async Task <RegioneViewModel> GetRegioneAsync(int id) { FormattableString query = $"SELECT * FROM regioni WHERE codiceRegione={id}"; DataSet dataSet = await db.QueryAsync(query); var regioneTable = dataSet.Tables[0]; if (regioneTable.Rows.Count != 1) { throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {id}"); } var regioneRow = regioneTable.Rows[0]; var regioneViewModel = RegioneViewModel.FromDataRow(regioneRow); // Questo codice dovrebbe essere interamente sostituito con la funzione GetRegioneAsync -- INIZIO FormattableString queryCom = $"SELECT * FROM comuni WHERE codiceCatastale={regioneRow["codiceCapoluogo"]}"; DataSet dataSetCom = await db.QueryAsync(queryCom); var comuneTable = dataSetCom.Tables[0]; if (comuneTable.Rows.Count != 1) { throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {regioneRow["codiceCapoluogo"]}"); } var comuneRow = comuneTable.Rows[0]; // Questo codice dovrebbe essere interamente sostituito con la funzione GetRegioneAsync -- FINE var comuneViewModel = ComuneViewModel.FromDataRow(comuneRow); regioneViewModel.codiceCapoluogo = (ComuneViewModel)comuneViewModel; return(regioneViewModel); }
public async Task <List <CourseViewModel> > GetCoursesAsync() { // Otteniamo la tabella risultante, dal interfaccia IDatabaseAccessor che implementa il metodo Query. FormattableString query = $"SELECT Id, Title, ImagePath, Author, Rating, FullPrice_Amount, CurrentPrice_Amount, FullPrice_Currency, CurrentPrice_Currency FROM COURSES;"; DataSet dataSet = await db.QueryAsync(query); // Ottiene il registro del corso [0] var dataTable = dataSet.Tables[0]; // Lista dei corsi dove aggiungerli var courseList = new List <CourseViewModel>(); // Percorriamo tutte le rows del corso foreach (DataRow courseRow in dataTable.Rows) { // Logica di mapping inserita in FromDataRow(courseRow) CourseViewModel course = CourseViewModel.FromDataRow(courseRow); courseList.Add(course); } return(courseList); }
public async Task <CourseDetailViewModel> GetCourseAsync(int id) { FormattableString query = $@"SELECT Id, Title, Description, ImagePath, Author, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses WHERE Id={id} ; SELECT Id, Title, Description, Duration FROM Lessons WHERE CourseId={id}"; DataSet dataSet = await db.QueryAsync(query); //Course var courseTable = dataSet.Tables[0]; if (courseTable.Rows.Count != 1) { throw new InvalidOperationException($"Did not return exactly 1 row for Course {id}"); } var courseRow = courseTable.Rows[0]; var courseDetailViewModel = mapper.Map <CourseDetailViewModel>(courseRow); //Course lessons var lessonDataTable = dataSet.Tables[1]; courseDetailViewModel.Lessons = mapper.Map <List <LessonViewModel> >(lessonDataTable.Rows); return(courseDetailViewModel); }
public async Task <ComuneViewModel> GetComuneAsync(string id) { FormattableString query = $"SELECT * FROM comuni WHERE codiceCatastale={id}"; DataSet dataSet = await db.QueryAsync(query); var comuneTable = dataSet.Tables[0]; if (comuneTable.Rows.Count != 1) { throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {id}"); } var comuneRow = comuneTable.Rows[0]; var comuneViewModel = ComuneViewModel.FromDataRow(comuneRow); FormattableString queryReg = $"SELECT * FROM regioni WHERE codiceRegione={comuneRow["codiceRegione"]}"; DataSet dataSetReg = await db.QueryAsync(queryReg); var regioneTable = dataSetReg.Tables[0]; if (regioneTable.Rows.Count != 1) { throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {comuneRow["regione"]}"); } var regioneRow = regioneTable.Rows[0]; var regioneViewModel = RegioneViewModel.FromDataRow(regioneRow); comuneViewModel.regione = (RegioneViewModel)regioneViewModel; FormattableString queryPro = $"SELECT * FROM province WHERE codiceProvincia={comuneRow["codiceProvincia"]}"; DataSet dataSetPro = await db.QueryAsync(queryPro); var provinciaTable = dataSetPro.Tables[0]; if (provinciaTable.Rows.Count != 1) { throw new InvalidOperationException($"Mi aspettavo che venisse restituita solo una riga della tabella {comuneRow["provincia"]}"); } var provinciaRow = provinciaTable.Rows[0]; var provinciaViewModel = ProvinciaViewModel.FromDataRow(provinciaRow); comuneViewModel.provincia = (ProvinciaViewModel)provinciaViewModel; return(comuneViewModel); }