public async Task <HttpResponseMessage> GetStudentsAsync([FromUri] StudentFilter filter, [FromUri] StudentSort sort, [FromUri] StudentPage page) { if (filter != null && !filter.EmptyFilterString()) { filter.StringToNameAndSurname(); } StudentList = await studentService.ReadDataAsync(filter, sort, page); if (StudentList.Count() == 0) { return(Request.CreateResponse(HttpStatusCode.NotFound)); } Students = mapper.Map <List <Student> >(StudentList); return(Request.CreateResponse(HttpStatusCode.OK, Students)); }
public async Task <List <StudentModel> > ReadStudentAsync(StudentFilter filter, StudentSort sort, StudentPage page) { string connectionString = @"Data Source = (LocalDB)\MSSQLLocalDB;Initial Catalog = PraksaSQL; Integrated Security = True"; int begginingRow = 1; int endRow = 11; if (page != null) { begginingRow = (page.PageNumber - 1) * page.PageSize; endRow = begginingRow + page.PageSize + 1; } string queryString = "DECLARE @BegginingRow INT = " + begginingRow + ";" + "DECLARE @EndRow INT = " + endRow + ";"; queryString += "SELECT id,ime,prezime FROM (" + "SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNum, id, ime, prezime " + "FROM STUDENT" + ") AS tbl " + "WHERE @BegginingRow < RowNum AND @EndRow > RowNum "; if (filter != null) { if (!filter.EmptyFilterString()) { string FilterString = "'" + String.Join("','", filter.StudentNames) + "'"; queryString += "AND (ime IN (" + FilterString + ") OR (prezime IN (" + FilterString + ")))"; } } if (sort != null) { queryString += " ORDER BY " + sort.OrderBy + " " + sort.Order; } else { queryString += " ORDER BY prezime asc"; } using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); await connection.OpenAsync(); SqlDataReader reader = await command.ExecuteReaderAsync(); // Call Read before accessing data. while (await reader.ReadAsync()) { StudentList.Add(new StudentModel { id = reader.GetGuid(0), name = reader.GetString(1), surname = reader.GetString(2) }); } // Call Close when done reading. reader.Close(); return(StudentList); } }