1. Olivier PAVLIN
  2. SnapObjects
  3. Thursday, 21 November 2019 16:37 PM UTC

How to implement sorting, filtering and paging to my asp.net mvc controller with snapdevelop, in order to use this kind of RESTFUL syntaxe url:

http://www.example.gov/api/v1/magazines.json?year=2011&sort=desc

There is plenty of tutorial on how to do it with EntityFramework...

Michael Kramer Accepted Answer Pending Moderation
  1. Sunday, 24 November 2019 21:21 PM UTC
  2. SnapObjects
  3. # 1

Hey,

Main SQL dialects have paging functionality to minimize amount of data sent from DB server to app. This example MSSQL towards AdventureWorks database where the SELECT returns almost 20'000 rows - unless you do paging.

/* Retrieval args:
   1) pageNo: number
   2) rowsPerPage: number
*/
SELECT P.FirstName, P.MiddleName, P.LastName, E.JobTitle 
FROM Person.Person P 
LEFT JOIN HumanResources.Employee E ON (P.BusinessEntityID = E.BusinessEntityID) 
ORDER BY P.LastName, P.FirstName, P.MiddleName, E.JobTitle 
/* PAGING */
OFFSET :rowsPerPage * IIF(:pageNo > 0, (:pageNo - 1), 0) ROWS 
FETCH NEXT IIF(:rowsPerPage > 0, :rowsPerPage, 1) ROWS ONLY

This I just tested in DW painter to be sure. The IIF-calls are required for DW painter's check of SELECT statement in MS SQL.

Oracle PL/SQL: Samee OFFSET; FETCH syntax. However, no IFF. Instead use DECODE or CASE.

That SQL is the best performing SQL as data volume increases because it never moves more data from DB server to "client" code that absolutely necessary.

Entity Framework can't do it much better.

HTH / Michael

Comment
There are no comments made yet.
Logan Liu @Appeon Accepted Answer Pending Moderation
  1. Sunday, 24 November 2019 15:47 PM UTC
  2. SnapObjects
  3. # 2

Hi Oliver,

For the .NET DataStore, it has some sorting or filtering APIs, but lacks a paging API. You can implement paging by retrieving all rows and taking out some of the rows needed for the page, but this is not efficient. I recommend that you submit an enhancement request.

Alternatively, you can also try SqlModelMapper, which has the ISqlModelMapper.LoadByPage <TModel>(int currentIndex, int pageSize, params object [] parameter) method:

https://docs.appeon.com/appeon_online_help/snapobjects/api_reference/SnapObjects.Data/SqlModelMapper/ISqlModelMapper/Method/LoadByPage.html

Regards, Logan

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.