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