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);
}
