Paging the huge data that is returned by the Web API

Question

We created the WebAPI for querying an Oracle database. The query returns results that are huge, so it sometimes throws OutOfMemoryException.

The recommendation was to use the Paging concept. I don't understand how the client application will know how many times the API has to be called to get the entire set of the result. Also do I need to create a separate class for the paging or can I operate it in my API controller.

Can anyone please help me with this as this is my first Web API. We cannot create stored procedures for this because we just have read access on the database

public HttpResponseMessage Getdetails([FromUri] string[] id)
{
    string connStr = ConfigurationManager.ConnectionStrings["ProDataConnection"].ConnectionString;
    using (OracleConnection dbconn = new OracleConnection(connStr))
    {
         var inconditions = id.Distinct().ToArray();
         var srtcon = string.Join(",", inconditions);
         DataSet userDataset = new DataSet();
         var strQuery = @"SELECT * from STCD_PRIO_CATEGORY where STPR_STUDY.STD_REF IN(" + srtcon + ")";
         using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
         {
              using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
             {
                 DataTable selectResults = new DataTable();
                 adapter.Fill(selectResults);
                 var returnObject = new { data = selectResults };
                 var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
                 ContentDispositionHeaderValue contentDisposition = null;

                 if (ContentDispositionHeaderValue.TryParse("inline; filename=ProvantisStudyData.json", out contentDisposition))
                {
                     response.Content.Headers.ContentDisposition = contentDisposition;
                }

                return response;
            }
        }
    }
}

Answer 1

The general idea behind paging through the API is that the client will pass the "page" of data they want and the "amount" of records they want.

From there you can structure your query something to the effect of

Select all records, but skip ((Page - 1) * amount) of records and take (amount) of records.

If you use LINQ to SQL there are Take() and Skip() methods that help make this a lot easier to write on code side. If you aren't using LINQ to SQL you'll need to find something Oracle specific.

Final note, since a good API is designed to be "stateless" it will be the requirement of the client to maintain which page they are on when handling previous/next page queries. Typically the page and amount variables are kept in Javascript or even something as simple as hidden variables and can be used to calculate how many pages are available and the like

Here's a basic sample of a WebAPI call that I make that does paging. You may need to modify it a little to support getting all records and potentially anything Oracle specific if LINQ to SQL / EF doesn't support it:

public IActionResult GetProducts(int? page, int? count)
        {
            var takePage = page ?? 1;
            var takeCount = count ?? DefaultPageRecordCount;

            var calls = context.Products
                            .Skip((takePage - 1) * takeCount)
                            .Take(takeCount)
                            .ToList();

            return Json(calls);
        }