public async Task <ListViewModel <CourseViewModel> > GetCoursesAsync(CourseListInputModel model) { string orderby = model.OrderBy == "CurrentPrice" ? "CurrentPrice_Amount" : model.OrderBy; string direction = model.Ascending ? "ASC" : "DESC"; FormattableString query = $@"SELECT Id, Title, ImagePath, Author, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses WHERE Title LIKE {"%" + model.Search + "%"} ORDER BY {(Sql) orderby} {(Sql) direction} LIMIT {model.Limit} OFFSET {model.Offset}; SELECT COUNT(*) FROM Courses WHERE Title LIKE {"%" + model.Search + "%"}"; DataSet dataSet = await db.QueryAsync(query); var dataTable = dataSet.Tables[0]; var courseList = new List <CourseViewModel>(); foreach (DataRow courseRow in dataTable.Rows) { CourseViewModel courseViewModel = CourseViewModel.FromDataRow(courseRow); courseList.Add(courseViewModel); } ListViewModel <CourseViewModel> result = new ListViewModel <CourseViewModel> { Results = courseList, TotalCount = Convert.ToInt32(dataSet.Tables[1].Rows[0][0]) }; return(result); }
public async Task <ListViewModel <CourseViewModel> > GetCoursesAsync(string search, int page, string orderby, bool ascending, int limit, int offset) { /*Per registrare i Log dell'applicazione*/ logger.LogInformation("Recupero catalogo dei corsi"); /*Per la Paginazione*/ string direction = ascending ? "ASC" : "DESC"; FormattableString query = $@"SELECT * FROM Courses WHERE NomeCorso LIKE {"%" + search + "%"} ORDER BY {(Sql)orderby} {(Sql)direction} LIMIT {limit} OFFSET {offset}; SELECT count(*) FROM Courses WHERE NomeCorso LIKE {"%" + search + "%"}"; DataSet dataSet = await db.QueryAsync(query); var dataTable = dataSet.Tables[0]; var courseList = new List <CourseViewModel>(); foreach (DataRow courseRow in dataTable.Rows) { CourseViewModel course = CourseViewModel.FromDataRow(courseRow); courseList.Add(course); } ListViewModel <CourseViewModel> courseListViewModel = new ListViewModel <CourseViewModel>(); courseListViewModel.Result = courseList; courseListViewModel.TotalCount = (long)dataSet.Tables[1].Rows[0][0]; return(courseListViewModel); }
public async Task <ListViewModel <CourseViewModel> > GetCoursesAsync(CourseListInputModel model) { string orderBy = model.OrderBy == "CurrentPrice" ? "CurrentPrice_Amount" : model.OrderBy; string direction = model.Ascending ? "ASC" : "DESC"; //se é true restituisce ASC, se é false: DESC //quali informazioni estrarre nei confronti di un database? eseguo una query FormattableString query = $@"SELECT Id, Title, ImagePath, Author, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses WHERE Title LIKE {"%" + model.Search + "%"} ORDER BY {(Sql) orderBy} {(Sql) direction} LIMIT {model.Limit} OFFSET {model.Offset}; SELECT COUNT(*) FROM Courses WHERE Title LIKE {"%" + model.Search + "%"}"; DataSet dataSet = await db.ExecuteQueryAsync(query); var dataTable = dataSet.Tables[0]; //primo datatable var courseList = new List <CourseViewModel>(); //aggiungo CourseViewModel ad una lista foreach (DataRow courseRow in dataTable.Rows) //il datatable contiene tutte le righe trovate e cicliamo tutte le Rows in un foreach { CourseViewModel courseViewModel = CourseViewModel.FromDataRow(courseRow); //il metodo FromDataRow conterrà tutti i risultati ciclati e viene richamato. Il tutto mi fa ottenere un oggetto di tipo CourseViewModel courseList.Add(courseViewModel); //ogni oggetto ciclato viene aggiunto alla lista } //creo un istanza dell'oggetto ListViewModel <CourseViewModel> result = new ListViewModel <CourseViewModel> { Results = courseList, //rappresenta l'elenco dei corsi (paginato a 10 corsi per pagina) TotalCount = Convert.ToInt32(dataSet.Tables[1].Rows[0][0]) //leggo il secondo dataset [1] dove viene eseguita la seconda query e della prima riga ottengo i valore della prima colonna, totale di tutti i corsi }; return(result); }
public async Task <List <CourseViewModel> > getCoursesAsync() { FormattableString query = $"SELECT Id, Title, ImagePath, Author, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses"; DataSet query_result = await db.QueryAsync(query); var dataTable = query_result.Tables[0]; var courseList = new List <CourseViewModel>(); foreach (DataRow courseRow in dataTable.Rows) { CourseViewModel course = CourseViewModel.FromDataRow(courseRow); courseList.Add(course); } return(courseList); }
public ListCourseViewModel GetAllCourses(int offset) { var query = $@"SELECT Id, Author, CourseName, Duration FROM Courses ORDER BY CourseName LIMIT 10 OFFSET {offset}; SELECT COUNT(*) FROM Courses;"; DataSet dataSet = _courseRepo.GetAllCourses(query); var dataTable = dataSet.Tables[0]; var courseList = new List <CourseViewModel>(); foreach (DataRow row in dataTable.Rows) { var course = CourseViewModel.FromDataRow(row); courseList.Add(course); } var result = new ListCourseViewModel { CourseList = courseList, Count = Convert.ToInt32(dataSet.Tables[1].Rows[0][0]) }; return(result); }
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); }